[PERFORM] Fwd: Stalled post to pgsql-performance

'2016-06-01' AND timerange_transaction @> current_timestamp ORDER BY metric_value.id_metric, metric_value.id_asset, date; Which is awesome! Thank you so much for your help, both of you! Now if only we could make hash joins as fast as JSONB hash lookups :) Cheers, Chris.

Re: [PERFORM] Fwd: Slow query from ~7M rows, joined to two tables of ~100 rows each

econds longer <https://explain.depesz.com/s/NgfZ> than it takes without any joins to the dimension tables (3.7 seconds), or explain why the cartesian join helps and/or how we can get the same speedup without materialising it. SELECT id_metric, id_asset, date, value FROM metric_value WHERE date >= '2016-01-01' and date < '2016-06-01' AND timerange_transaction @> current_timestamp ORDER BY date, metric_value.id_metric; Cheers, Chris.

[PERFORM] Fwd: Slow query from ~7M rows, joined to two tables of ~100 rows each

c-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit, compiled from source. - shared_buffers = 15GB, work_mem = 100MB, seq_page_cost = 0.5, random_page_cost = 1.0, cpu_tuple_cost = 0.01. - HP ProLiant DL580 G7, Xeon(R) CPU E7- 4850 @ 2.00GHz * 80 cores, hardware RAID, 3.6 TB SAS array. Thanks again in advance for any suggestions, hints or questions. Cheers, Chris.

Re: [PERFORM] Big number of connections

each individual requires its own database-level > user, pgbouncer would not help at all. > > I would look seriously into getting rid of the always-open requirement > for connections. — Chris Cogdon

[PERFORM] Adding a ROLLUP switches to GroupAggregate unexpectedly

orkarounds I can use for this simple case, such as using a CTE, then doing a rollup on that, but I’m simply reporting what I think is a bug in the query optimizer. Thank you for your attention! Please let me know if there’s any additional information you need, or additional tests you’d like

[PERFORM] Building multiple indexes on one table.

solution. But that doesn't seem to exist either. best regards, chris -- chris ruprecht database grunt and bit pusher extraordinaíre -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] [GENERAL] Approach to Data Summary and Analysis

On Tue, Apr 15, 2014 at 10:56 AM, Chris Curvey wrote: > On Mon, Apr 14, 2014 at 12:27 PM, Robert DiFalco > wrote: > >> I have several related tables that represent a call state. Let's think of >> these as phone calls to simplify things. Sometimes I need to determine t

Re: [PERFORM] [GENERAL] Approach to Data Summary and Analysis

On Mon, Apr 14, 2014 at 12:27 PM, Robert DiFalco wrote: > I have several related tables that represent a call state. Let's think of > these as phone calls to simplify things. Sometimes I need to determine the > last time a user was called, the last time a user answered a call, or the > last time a

[PERFORM] Using a window function in a view

ly with an index scan. explain analyze select * from values_view where fkey1 = 1263; --- Can anyone suggest a way to rewrite this query, or maybe a workaround of some kind? Thanks, Chris

[PERFORM] have: seq scan - want: index scan

Hi guys, PG = 9.1.5 OS = winDOS 2008R8 I have a table that currently has 207 million rows. there is a timestamp field that contains data. more data gets copied from another database into this database. How do I make this do an index scan instead? I did an "analyze audittrailclinical" to no avail.

Re: [PERFORM] Have: Seq Scan - Want: Index Scan - what am I doing wrong?

Thanks Bruce, I have, and I even thought, I understood it :). I just ran an explain analyze on another table - and ever since the query plan changed. It's now using the index as expected. I guess, I have some more reading to do. On Oct 16, 2012, at 20:31 , Bruce Momjian wrote: > > Have yo

Re: [PERFORM] Have: Seq Scan - Want: Index Scan - what am I doing wrong?

On Oct 16, 2012, at 20:01 , Evgeny Shishkin wrote: > Selecting 5 yours of data is not selective at all, so postgres decides it is > cheaper to do seqscan. > > Do you have an index on patient.dnsortpersonnumber? Can you post a result > from > select count(*) from patient where dnsortpersonnu

[PERFORM] Have: Seq Scan - Want: Index Scan - what am I doing wrong?

Hi guys, PG = 9.1.5 OS = winDOS 2008R8 I have a table that currently has 207 million rows. there is a timestamp field that contains data. more data gets copied from another database into this database. How do I make this do an index scan instead? I did an "analyze audittrailclinical" to no avail.

[PERFORM] Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.

Daniel Farina-4 wrote > > On Fri, Jul 6, 2012 at 4:29 AM, Craig Ringer wrote: >> 1) Truncate each table. It is too slow, I think, especially for empty >> tables. >> >> Really?!? TRUNCATE should be extremely fast, especially on empty tables. >> >> You're aware that you can TRUNCATE m

Re: [PERFORM] Select from sequence in slow query log

ing normal operations, I'm not worrying too much about them now. Thanks Chris On 1 June 2012 14:47, Tom Lane wrote: > Chris Rimmer writes: > > While investigating some performance issues I have been looking at slow > > queries logged to the postgresql.log file. A strange t

[PERFORM] Select from sequence in slow query log

SQL above appears in the source as above (minus the comment) and not as part of any ORM magic. We are using Postgresql 9.0. This seems very strange to me. What could cause a sequence to be locked for such a long time? The sequence in question has cache set at 1. Would setting this higher make any difference? Thanks Chris

Re: [PERFORM] Hardware advice for scalable warehouse db

dexes and a SAN (with SATA disk) for data, what would you choose performance-wise? Again, thanks so much for your help. Best, Chris -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Hardware advice for scalable warehouse db

TB disks as DAS? Thanks so much! Best, Chris [1]: http://www.b2net.co.uk/netapp/fas3000.pdf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Benchmarking a large server

es anyone have any suggestions/experiences in benchmarking storage when the storage is smaller then 2x memory? Thanks, Chris

Re: [PERFORM] Multiple index builds on same table - in one sweep?

ot;|psql ... & ) once the 'serial build' test is done. Maybe, in a future release, somebody will develop something that can create indexes as inactive and have a build tool build and activate them at the same time. Food for thought? On Apr 9, 2011, at 13:10 , Tom Lane wrote: > Chri

[PERFORM] Multiple index builds on same table - in one sweep?

xes in parallel while reading the table only once for all indexes and building them all at the same time. Is there an index build tool that I missed somehow, that can do this? Thanks, Chris. best regards, chris -- chris ruprecht database grunt and bit pusher extraordinaíre -- Sent via pg

Re: [PERFORM] good old VACUUM FULL

On 23/03/11 11:52, felix wrote: I posted many weeks ago about a severe problem with a table that was obviously bloated and was stunningly slow. Up to 70 seconds just to get a row count on 300k rows. I removed the text column, so it really was just a few columns of fixed data. Still very bloated.

[PERFORM] Estimating hot data size

Is this a fair estimate? The reason for doing this is we are looking at new server hardware, and I want to try and get enough ram on the machine to keep the hot data in memory plus provide room for growth. Thanks, Chris Example: *Time* *Total Blocks* 2011-02-16 11:25:34.621874-05 123,260,464,

Re: [PERFORM] Why we don't want hints

robertmh...@gmail.com (Robert Haas) writes: > On Thu, Feb 10, 2011 at 11:45 AM, Kevin Grittner > wrote: >> Well, I'm comfortable digging in my heels against doing *lame* hints >> just because "it's what all the other kids are doing," which I think >> is the only thing which would have satisfied th

Re: [PERFORM] getting the most of out multi-core systems for repeated complex SELECT statements

gnuo...@rcn.com writes: > Time for my pet meme to wiggle out of its hole (next to Phil's, and a > day later). For PG to prosper in the future, it has to embrace the > multi-core/processor/SSD machine at the query level. It has to. And > it has to because the Big Boys already do so, to some exten

Re: [HACKERS] [PERFORM] Slow count(*) again...

mladen.gog...@vmsinfo.com (Mladen Gogala) writes: > I must say that this purist attitude is extremely surprising to > me. All the major DB vendors support optimizer hints, yet in the > Postgres community, they are considered bad with almost religious > fervor. > Postgres community is quite unique w

Re: [HACKERS] [PERFORM] Slow count(*) again...

mladen.gog...@vmsinfo.com (Mladen Gogala) writes: > Hints are not even that complicated to program. The SQL parser should > compile the list of hints into a table and optimizer should check > whether any of the applicable access methods exist in the table. If it > does - use it. If not, ignore it.

Re: [PERFORM] the XID question

kevin.gritt...@wicourts.gov ("Kevin Grittner") writes: > Filip Rembia*kowski wrote: >> 2011/1/19 Charles.Hou : > >>> " select * from mybook" SQL command also increase the XID ? >> >> Yes. Single SELECT is a transaction. Hence, it needs a transaction >> ID. > > No, not in recent versions of Po

Re: [PERFORM] "COPY TO stdout" statements occurrence in log files

msakre...@truviso.com (Maciek Sakrejda) writes: >> Is this normal? I'm afraid because my application doesn't run this kind of >> statement, so how can I know what is doing these commands? Maybe pg_dump? > > I think pg_dump is likely, yes, if you have that scheduled. I don't > think anything in the

Re: [PERFORM] best db schema for time series data?

vindex+lists-pgsql-performa...@apartia.org (Louis-David Mitterrand) writes: > On Tue, Nov 16, 2010 at 11:35:24AM -0500, Chris Browne wrote: >> vindex+lists-pgsql-performa...@apartia.org (Louis-David Mitterrand) >> writes: >> > I have to collect lots of prices from web sites

Re: [PERFORM] best db schema for time series data?

vindex+lists-pgsql-performa...@apartia.org (Louis-David Mitterrand) writes: > I have to collect lots of prices from web sites and keep track of their > changes. What is the best option? > > 1) one 'price' row per price change: > > create table price ( > id_price primary key, >

Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences

gentosa...@gmail.com (A B) writes: > If you just wanted PostgreSQL to go as fast as possible WITHOUT any > care for your data (you accept 100% dataloss and datacorruption if any > error should occur), what settings should you use then? Use /dev/null. It is web scale, and there are good tutorials.

Re: [PERFORM] read only transactions

jnelson+pg...@jamponi.net (Jon Nelson) writes: > Are there any performance implications (benefits) to executing queries > in a transaction where > SET TRANSACTION READ ONLY; > has been executed? Directly? No. Indirectly, well, a *leetle* bit... Transactions done READ ONLY do not generate actual

Re: [PERFORM] large dataset with write vs read clients

mladen.gog...@vmsinfo.com (Mladen Gogala) writes: > I have a logical problem with asynchronous commit. The "commit" > command should instruct the database to make the outcome of the > transaction permanent. The application should wait to see whether the > commit was successful or not. Asynchronous

Re: [PERFORM] large dataset with write vs read clients

cr...@postnewspapers.com.au (Craig Ringer) writes: > Hey, maybe I should try posting YouTube video answers to a few > questions for kicks, see how people react ;-) And make sure it uses the same voice as is used in the "MongoDB is web scale" video, to ensure that people interpret it correctly :-).

Re: [PERFORM] Slow count(*) again...

sgend...@ideasculptor.com (Samuel Gendler) writes: > Geez.  I wish someone would have written something quite so bold as > 'xfs is always faster than ext3' in the standard tuning docs.  I > couldn't find anything that made a strong filesystem > recommendation.  How does xfs compare to ext4?  I woun

Re: [PERFORM] GPU Accelerated Sorting

david_l...@boreham.org (David Boreham) writes: > Feels like I fell through a worm hole in space/time, back to inmos in > 1987, and a guy from marketing has just > walked in the office going on about there's a customer who wants to > use our massively parallel hardware to speed up databases... ...

Re: [PERFORM] Testing Sandforce SSD

j...@commandprompt.com ("Joshua D. Drake") writes: > On Sat, 2010-07-24 at 16:21 -0400, Greg Smith wrote: >> Greg Smith wrote: >> > Note that not all of the Sandforce drives include a capacitor; I hope >> > you got one that does! I wasn't aware any of the SF drives with a >> > capacitor on them

Re: [PERFORM] Testing Sandforce SSD

g...@2ndquadrant.com (Greg Smith) writes: > Yeb Havinga wrote: >> * What filesystem to use on the SSD? To minimize writes and maximize >> chance for seeing errors I'd choose ext2 here. > > I don't consider there to be any reason to deploy any part of a > PostgreSQL database on ext2. The potential

Re: [PERFORM] planner index choice

Hi, Hrm ... are you *certain* that's an 8.4 server? Yep. # psql -U postgres -d db psql (8.4.4) db=# select version(); version --

[PERFORM] planner index choice

Hi there, I have a simple query where I don't understand the planner's choice to use a particular index. The main table looks like this: # \d sq_ast_attr_val Table "public.sq_ast_attr_val" Column| Type | Modifiers -+-

Re: [PERFORM] PostgreSQL as a local in-memory cache

swamp...@noao.edu (Steve Wampler) writes: > Or does losing WAL files mandate a new initdb? Losing WAL would mandate initdb, so I'd think this all fits into the set of stuff worth putting onto ramfs/tmpfs. Certainly it'll all be significant to the performance focus. -- select 'cbbrowne' || '@' ||

Re: [PERFORM] PostgreSQL as a local in-memory cache

"jgard...@jonathangardner.net" writes: > My question is how can I configure the database to run as quickly as > possible if I don't care about data consistency or durability? That > is, the data is updated so often and it can be reproduced fairly > rapidly so that if there is a server crash or ran

[PERFORM] stats collector suddenly causing lots of IO

I have a lot of centos servers which are running postgres. Postgres isn't used that heavily on any of them, but lately, the stats collector process keeps causing tons of IO load. It seems to happen only on servers with centos 5. The versions of postgres that are running are: 8.1.18 8.2.6 8.3.1 8

Re: [PERFORM] significant slow down with various LIMIT

I'm also wondering if a re-clustering of the table would work based on the index that's used. such that: CLUSTER core_object USING plugins_plugin_addr_oid_id; and see if that makes any change in the differences that your seeing. On 04/13/2010 02:24 PM, Kevin Grittner wrote: > norn wrote: > >

[PERFORM] stats collector suddenly causing lots of IO

I have a lot of centos servers which are running postgres. Postgres isn't used that heavily on any of them, but lately, the stats collector process keeps causing tons of IO load. It seems to happen only on servers with centos 5. The versions of postgres that are running are: 8.1.18 8.2.6 8.3.1 8

Re: [PERFORM] mysql to postgresql, performance questions

t...@sss.pgh.pa.us (Tom Lane) writes: > "Ross J. Reedstrom" writes: >> On Sat, Mar 20, 2010 at 10:47:30PM -0500, Andy Colson wrote: >>> (I added the "and trust" as an after thought, because I do have one very >>> important 100% uptime required mysql database that is running. Its my >>> MythTV b

Re: [PERFORM] mysql to postgresql, performance questions

reeds...@rice.edu ("Ross J. Reedstrom") writes: > http://www.mythtv.org/wiki/PostgreSQL_Support That's a pretty hostile presentation... The page has had two states: a) In 2008, someone wrote up... After some bad experiences with MySQL (data loss by commercial power failure, very bad

Re: [PERFORM] Is DBLINK transactional

cr...@postnewspapers.com.au (Craig Ringer) writes: > On 13/03/2010 5:54 AM, Jeff Davis wrote: >> On Fri, 2010-03-12 at 12:07 -0500, Merlin Moncure wrote: >>> of course. You can always explicitly open a transaction on the remote >>> side over dblink, do work, and commit it at the last possible mom

Re: [PERFORM] [HACKERS] full text search index scan query plan changed in 8.4.2?

Josh Berkus wrote: Xufei, List changed to psql-performance, which is where this discussion belongs. I am testing the index used by full text search recently. I have install 8.3.9 and 8.4.2 separately. In 8.3.9, the query plan is like: postgres=# explain SELECT s.name as source , t.name as

Re: [PERFORM] How to troubleshoot high mem usage by postgres?

ne with: php -f test3.php Note my comment in the php file <<<<<< UNCOMMENT THIS LINE AND MEMORY ISSUE IS FIXED Thanks for the help everyone. Chris <> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to troubleshoot high mem usage by postgres?

res log file? If not, where can I find it? Thanks again, Chris -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] How to troubleshoot high mem usage by postgres?

extension is at fault here. Regardless of who/what is at fault, I need to fix it. And to do that I need to find out what isn't getting released properly. How would I go about that? Thanks, Chris -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] Optimizer + bind variables

David Kerr wrote: On Wed, Nov 04, 2009 at 07:43:16AM +0800, Craig Ringer wrote: - David Kerr wrote: - > Does/is it possible for the PG optimizer come up with differnet plans when - > you're using bind variables vs when you send static values? - - Yes, if the bind variable form causes your DB ac

Re: [PERFORM] database size growing continously

le (); Do this regularly to keep the index sizes in check. - Chris Peter Meszaros wrote: > Hi All, > > I use postgresql 8.3.7 as a huge queue. There is a very simple table > with six columns and two indices, and about 6 million records are > written into it in every day contino

Re: [PERFORM] Databases vs Schemas

function over the entire pg_class table. By increasing the cost, it now only runs the function over the rows returned by the other items in the where clause. -chris

Re: [PERFORM] improving my query plan

Kevin Kempter wrote: Hi all; I have a simple query against two very large tables ( > 800million rows in theurl_hits_category_jt table and 9.2 million in the url_hits_klk1 table ) I have indexes on the join columns and I've run an explain. also I've set the default statistics to 250 for bot

Re: [PERFORM] Greenplum MapReduce

Suvankar Roy wrote: Hi all, Has anybody worked on Greenplum MapReduce programming ? It's a commercial product, you need to contact greenplum. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make cha

FW: [PERFORM] Performance 8.4.0

August 2009 11:26 PM To: Chris Dunn Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Performance 8.4.0 On Fri, Jul 31, 2009 at 12:22 AM, Chris Dunn wrote: > constraint_exclusion = on This is critical if you need it, but a waste of CPU time if you don't. Other than that your pa

[PERFORM] load / stress testing

Hi, Everyone says "load test using your app" - out of interest how does everyone do that at the database level? I've tried playr (https://area51.myyearbook.com/trac.cgi/wiki/Playr) but haven't been able to get it working properly. I'm not sure what other tools are available. TIA. -- Postgr

[PERFORM] Performance 8.4.0

Hi, I would like to know if my configuration is ok, We run a web application with high transaction rate and the database machine on Mondays / Tuesdays is always at 100% CPU with no IO/Wait . the machine is a Dual Xeon Quad core, 12gb RAM, 4gb/s Fibre Channel on Netapp SAN, with pg_xlog on separ

Re: [PERFORM] Will Postgres ever lock with read only queries?

Robert James wrote: Thanks for the replies. I'm running Postgres 8.2 on Windows XP, Intel Core Duo (though Postgres seems to use only one 1 core). A single query can only use one core, but it will use both if multiple queries come in. The queries are self joins on very large tables, with l

Re: [PERFORM] Will Postgres ever lock with read only queries?

Robert James wrote: Hi. I'm seeing some weird behavior in Postgres. I'm running read only queries (SELECT that is - no UPDATE or DELETE or INSERT is happening at all). I can run one rather complicated query and the results come back... eventually. Likewise with another. But, when I run bot

Re: [PERFORM] More speed counting rows

unt at the moment of the request? If it needs to be more real-time, you could expand on this by adding post insert/delete triggers that automatically update the counts table to keep it current. In my case it just wasn't necessary. - Chris -- Sent via pgsql-performance mail

Re: [PERFORM] Master/Slave, DB separation or just spend $$$?

kelv...@gmail.com (Kelvin Quee) writes: > I will go look at Slony now. It's worth looking at, but it is not always to be assumed that replication will necessarily improve scalability of applications; it's not a "magic wand" to wave such that "presto, it's all faster!" Replication is helpful from

Re: [PERFORM] Can Postgres use an INDEX over an OR?

Віталій Тимчишин wrote: 2009/7/20 Robert James > Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I do WHERE y, it does so as well, but when I do WHERE x OR y, it doesn't. Why is this so? It's not clever enough. Of cours

Re: [PERFORM] tsvector_update_trigger performance?

Dimitri Fontaine wrote: Hi, Le 24 juin 09 à 18:29, Alvaro Herrera a écrit : Oleg Bartunov wrote: On Wed, 24 Jun 2009, Chris St Denis wrote: Is tsvector_update_trigger() smart enough to not bother updating a tsvector if the text in that column has not changed? no, you should do check

[PERFORM] tsvector_update_trigger performance?

Is tsvector_update_trigger() smart enough to not bother updating a tsvector if the text in that column has not changed? If not, can I make my own update trigger with something like if new.description != old.description return tsvector_update_trigger('fti_all', 'pg_catalog.english',

Re: [PERFORM] How would you store read/unread topic status?

Mathieu Nebra wrote: Alexander Staubo a écrit : On Tue, Jun 23, 2009 at 1:12 PM, Mathieu Nebra wrote: This "flags" table has more or less the following fields: UserID - TopicID - LastReadAnswerID We are doing pretty much same thing. My problem is that everytime a user RE

Re: [PERFORM] superlative missuse

cl...@uah.es (Angel Alvarez) writes: > more optimal plan... > morreoptimal configuration... > > we suffer a 'more optimal' superlative missuse > > there is not so 'more optimal' thing but a simple 'better' thing. > > im not native english speaker but i think it still applies. If I wanted to be p

Re: [PERFORM] Any better plan for this query?..

Dimitri wrote: Hi Craig, yes, you detailed very well the problem! :-) all those CHAR columns are so just due historical issues :-) as well they may contains anything else and not only numbers, that's why.. Also, all data inside are fixed, so VARCHAR will not save place, or what kind of performan

Re: [PERFORM] I have a fusion IO drive available for testing

craig_ja...@emolecules.com (Craig James) writes: > Dave Cramer wrote: >> So I tried writing directly to the device, gets around 250MB/s, >> reads at around 500MB/s >> >> The client is using redhat so xfs is not an option. > > I'm using Red Hat and XFS, and have been for years. Why is XFS not an opt

Re: [PERFORM] scheduling autovacuum at lean hours only.

mallah.raj...@gmail.com (Rajesh Kumar Mallah) writes: > why is it not a good idea to give end users control over when they > want to run it ? It's not a particularly good idea to give end users things that they are likely then to *immediately* use to shoot themselves in the foot. Turning off vacu

Re: [PERFORM] PG performance in high volume environment (many INSERTs and lots of aggregation reporting)

phoenix.ki...@gmail.com (Phoenix Kiula) writes: > [Ppsted similar note to PG General but I suppose it's more appropriate > in this list. Apologies for cross-posting.] > > Hi. Further to my bafflement with the "count(*)" queries as described > in this thread: > > http://archives.postgresql.org/pgsql

Re: [PERFORM] left join + case - how is it processed?

Tom Lane wrote: Chris writes: I can see it's doing the extra filter step at the start (4th line) which is not present without the coalesce/case statement. I just don't understand why it's being done at that stage. It's not that hard to understand. With the original view

Re: [PERFORM] left join + case - how is it processed?

I thought the where condition would cut down on the rows returned, then the case statement would take effect to do the null check. It seems to be doing it in reverse ?? # explain analyze SELECT * from sq_vw_ast_perm where assetid='30748'; It aperas to me that both of your statements have whe

Re: [PERFORM] left join + case - how is it processed?

The reason why the CASE is affecting your query planning is because you are using a query that compares assetid to a constant: SELECT * from sq_vw_ast_perm where assetid='30748'; When PostgreSQL evaluates this statement, assetid gets expanded either into a case statement (with your first view

[PERFORM] left join + case - how is it processed?

Hi all, I have a view that looks like this: SELECT CASE WHEN r.assetid IS NULL THEN p.assetid ELSE r.assetid END AS assetid, CASE WHEN r.userid IS NULL THEN p.userid ELSE r.userid END AS userid, p.permission, p."gra

Re: [PERFORM] limit clause produces wrong query plan

Andrus wrote: Scott, And how exactly should it be optimized? If a query is even moderately interesting, with a few joins and a where clause, postgresql HAS to create the rows that come before your offset in order to assure that it's giving you the right rows. SELECT ... FROM bigtable ORDER B

Re: [PERFORM] Intel's X25-M SSD

[EMAIL PROTECTED] ("Merlin Moncure") writes: > I think the SSD manufacturers made a tactical error chasing the > notebook market when they should have been chasing the server > market... That's a very good point; I agree totally! -- output = reverse("moc.enworbbc" "@" "enworbbc") http://www3.symp

Re: [PERFORM] Incorrect estimates on correlated filters

On Wed, Aug 13, 2008 at 10:59 AM, Decibel! <[EMAIL PROTECTED]> wrote: > On Aug 12, 2008, at 4:59 PM, Chris Kratz wrote: > >> Ran into a re-occuring performance problem with some report queries again >> today. In a nutshell, we have filters on either multiple joined tables

[PERFORM] Incorrect estimates on correlated filters

ery. This would keep the incorrect estimates from snowballing up through the chain of joins. Are there any other solutions to this problem? Thanks, -Chris

Re: [PERFORM] Trigger is not firing immediately

Praveen wrote: > > Hi All, > I am having a trigger in table, If I update the the table manually > trigger is firing immediately(say 200ms per row), But if I update the > table through procedure the trigger is taking time to fire(say 7 to 10 > seconds per row). > > Please tell me what kind o

Re: [PERFORM] [QUESTION]Concurrent Access

[EMAIL PROTECTED] ("Leví Teodoro da Silva") writes: > Hi guys, How are you ? > I am from Brazil and i work for a little company and it company is working is > medium-big project and we want to use PostGree like the DataBase > system, but i got some questions. > I want to know if the PostGree has l

Re: [PERFORM] Federated Postgresql architecture ?

[EMAIL PROTECTED] (Josh Berkus) writes: > Jonah, > >> Hmm, I didn't think the Skype tools could really provide federated >> database functionality without a good amount of custom work. Or, am I >> mistaken? > > Sure, what do you think pl/proxy is for? Ah, but the thing is, it changes the model fr

Re: [PERFORM] [pgsql-performance] function difference(geometry,geometry) is SLOW!

is function. Postgis is an independent project and you might want to ask there: http://www.postgis.org/mailman/listinfo/postgis-users or http://www.faunalia.com/cgi-bin/mailman/listinfo/gfoss (italian). Anyway, as long as you just compute the difference between 2 given shapes, no index can help you. In

Re: [PERFORM] Adding "LIMIT 1" kills performance.

On Fri, May 30, 2008 at 02:23:46AM +0930, Shane Ambler wrote: > Chris Shoemaker wrote: >> [Attn list-queue maintainers: Please drop the earlier version >> of this email that I accidentally sent from an unsubscribed address. ] >> >> Hi, >> I'm having a stra

[PERFORM] OVERLAPS is slow

I'm doing some analysis on temporal usages, and was hoping to make use of OVERLAPS, but it does not appear that it makes use of indices. Couching this in an example... I created a table, t1, thus: metadata=# \d t1 Table "public.t1" Column | Type

[PERFORM] Adding "LIMIT 1" kills performance.

e "LIMIT 1" is present? Is there anything I can do to speed this query up? Thanks. -chris production=> select version(); version -

Re: [PERFORM] RAID controllers for Postgresql on large setups

Joshua, did you try to run the 345 on an IBM ServeRAID 6i? I have one in mine, but I never actually ran any speed test. Do you have any benchmarks that I could run and compare? best regards, chris -- chris ruprecht database grunt and bit pusher extraordinaíre On May 12, 2008, at 22:11

Re: [PERFORM] Replication Syatem

[EMAIL PROTECTED] ("Gauri Kanekar") writes: > Basically we have some background process which updates "table1" and > we don't want the application to make any changes to "table1" while > vacuum. Vacuum requires exclusive lock on "table1" and if any of > the background or application is ON vacuum d

Re: [PERFORM] Replication Syatem

[EMAIL PROTECTED] ("Gauri Kanekar") writes: > We have a table "table1" which get insert and updates daily in high > numbers, bcoz of which its size is increasing and we have to vacuum > it every alternate day. Vacuuming "table1" take almost 30min and > during that time the site is down. We need to

Re: [PERFORM] Where do a novice do to make it run faster?

[EMAIL PROTECTED] ("A B") writes: > So, it is time to improve performance, it is running to slow. > AFAIK (as a novice) there are a few general areas: > > 1) hardware > 2) rewriting my queries and table structures > 3) using more predefined queries > 4) tweek parameters in the db conf files > > Of

Re: [PERFORM] Message queue table..

[EMAIL PROTECTED] (Jesper Krogh) writes: > I have this "message queue" table.. currently with 8m+ > records. Picking the top priority messages seem to take quite > long.. it is just a matter of searching the index.. (just as explain > analyze tells me it does). > > Can anyone digest further optimiz

Re: [PERFORM] Background writer underemphasized ...

[EMAIL PROTECTED] (Marinos Yannikos) writes: > This helped with our configuration: > bgwriter_delay = 1ms # 10-1ms between rounds > bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round FYI, I'd be inclined to reduce both of those numbers, as it should reduce the vari

Re: [PERFORM] Oddly slow queries

[EMAIL PROTECTED] (Thomas Spreng) writes: > On 16.04.2008, at 01:24, PFC wrote: >> >>> The queries in question (select's) occasionally take up to 5 mins >>> even if they take ~2-3 sec under "normal" conditions, there are no >>> sequencial scans done in those queries. There are not many users >>> co

Re: [PERFORM] Creating large database of MD5 hash values

1. Which datatype should I use to represent the hash value? UUIDs are also 16 bytes... md5's are always 32 characters long so probably varchar(32). 2. Does it make sense to denormalize the hash set relationships? The general rule is normalize as much as possible then only denormalize whe

Re: [PERFORM] bulk insert performance problem

Craig Ringer wrote: Christian Bourque wrote: Hi, I have a performance problem with a script that does massive bulk insert in 6 tables. When the script starts the performance is really good but will degrade minute after minute and take almost a day to finish! Would I be correct in guessing th

Re: [PERFORM] postgresql is slow with larger table even it is in RAM

[EMAIL PROTECTED] ("sathiya psql") writes: > On Tue, Mar 25, 2008 at 2:09 PM, jose > javier parra sanchez <[EMAIL PROTECTED]> wrote: > > > It's been said zillions of > times on the maillist. Using a select >

Re: [PERFORM] slow pg_connect()

* Read about configuring and using persistent database connections (http://www.php.net/manual/en/function.pg-pconnect.php) with PHP Though make sure you understand the ramifications of using persistent connections. You can quickly exhaust your connections by using this and also cause other

  1   2   3   4   5   >