Re: [PERFORM] query optimization

On Wed, Nov 25, 2009 at 5:54 PM, Faheem Mitha wrote: > > Hi Robert, > > Thanks very much for your suggestions. > >>> Hi everybody, >>> >>> I've got two queries that needs optimizing. Actually, there are others, >>> but these are pretty representative. >>> >>> You can see the queries and the corres

Re: [PERFORM] query optimization

Hi Robert, Thanks very much for your suggestions. On Wed, 25 Nov 2009, Robert Haas wrote: On Mon, Nov 23, 2009 at 5:47 PM, Faheem Mitha wrote: Hi everybody, I've got two queries that needs optimizing. Actually, there are others, but these are pretty representative. You can see the quer

Re: [PERFORM] Query times change by orders of magnitude as DB ages

On Wed, Nov 25, 2009 at 7:27 AM, Richard Neill wrote: > Sergey Aleynikov wrote: >> >> Hello, >> >>> * Is there any way I can nail the query planner to a particular query >>> plan, >>> rather than have it keep changing its mind? >> >> All these setting leads to choosing different plans. If you have

Re: [PERFORM] query optimization

On Mon, Nov 23, 2009 at 5:47 PM, Faheem Mitha wrote: > > Hi everybody, > > I've got two queries that needs optimizing. Actually, there are others, but > these are pretty representative. > > You can see the queries and the corresponding plans at > > http://bulldog.duhs.duke.edu/~faheem/snpdb/opt.pd

Re: [PERFORM] Query times change by orders of magnitude as DB ages

On Wed, Nov 25, 2009 at 4:58 PM, Kevin Grittner wrote: > Grzegorz Jaœkiewicz wrote: > > > Other way, is to perform regular cluster && reindex > > If you CLUSTER there is no reason to REINDEX; indexes are rebuilt by > the CLUSTER command. > > Also, if you do a good job with regular VACUUMs, there

Re: [PERFORM] Query times change by orders of magnitude as DB ages

Grzegorz Jaœkiewicz wrote: > Other way, is to perform regular cluster && reindex If you CLUSTER there is no reason to REINDEX; indexes are rebuilt by the CLUSTER command. Also, if you do a good job with regular VACUUMs, there isn't any bloat to fix. In that case a regular CLUSTER would only

Re: [PERFORM] RAID card recommendation

Greg Smith wrote: Jochen Erwied wrote: - Promise Technology Supertrak ES4650 + additional BBU - Adaptec RAID 5405 SGL/256 SATA/SAS + additional BBU - Adaptec RAID 5405Z SGL/512 SATA/SAS I've never seen a Promise controller that had a Linux driver you would want to rely on under any circumsta

Re: [PERFORM] Query times change by orders of magnitude as DB ages

On Wed, Nov 25, 2009 at 4:26 PM, Kevin Grittner wrote: > Richard Neill wrote: > > > In terms of just index bloat, does a regular vacuum help? > > You might want to use the REINDEX command to correct serious index > bloat. A regular vacuum will make dead space available for re-use, > but won't e

Re: [PERFORM] Query times change by orders of magnitude as DB ages

Richard Neill wrote: > In terms of just index bloat, does a regular vacuum help? You might want to use the REINDEX command to correct serious index bloat. A regular vacuum will make dead space available for re-use, but won't eliminate bloat directly. (If run regularly, it will prevent bloat.

Re: [PERFORM] DELETE performance problem

On Wed, Nov 25, 2009 at 4:13 PM, Luca Tettamanti wrote: > > > DELETE FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.annotation_id = > t2.annotation_id) > > performs event better: > > Seq Scan on t1 (cost=0.00..170388415.89 rows=22937406 width=6) (actual > time=272.625..561241.294 rows=26185953

Re: [PERFORM] DELETE performance problem

On Wed, Nov 25, 2009 at 04:22:47PM +0100, marcin mank wrote: > On Tue, Nov 24, 2009 at 2:37 PM, Luca Tettamanti wrote: > >         ->  HashAggregate  (cost=1031681.15..1033497.20 rows=181605 > > width=8) (a > > ctual time=571807.575..610178.552 rows=26185953 loops=1) > > > This is Your problem.

Re: [PERFORM] DELETE performance problem

On Tue, Nov 24, 2009 at 2:37 PM, Luca Tettamanti wrote: >         ->  HashAggregate  (cost=1031681.15..1033497.20 rows=181605 width=8) > (a > ctual time=571807.575..610178.552 rows=26185953 loops=1) This is Your problem. The system`s estimate for the number of distinct annotation_ids in t2 is w

Re: [PERFORM] How exactly does Analyze work?

On Wednesday 25 November 2009 05:34:26 Richard Neill wrote: > Dear All, > > Thanks very much for your help so far. My understanding of PG is getting > a lot better! > > I wonder if I've understood analyze properly: I'm not sure I quite > understand how specific the statistics gathered actually ar

Re: [PERFORM] How exactly does Analyze work?

Richard Neill writes: > In particular, what happens in the following case: >1. I start with have a table with 100 million rows, and column wid has > linearly distributed values from 45-90. (wid is indexed) >2. I run vacuum analyze >3. I insert about 2 million rows, all of whic

Re: [PERFORM] Best possible way to insert and get returned ids

On Mon, Nov 23, 2009 at 3:53 PM, Jason Dictos wrote: > Is an INSERT command with a SELECT statement in the RETURNING * parameter > faster than say an INSERT and then a SELECT? Does the RETURNING * parameter > simply amount to a normal SELECT command on the added rows? We need to > basically insert

[PERFORM] How exactly does Analyze work?

Dear All, Thanks very much for your help so far. My understanding of PG is getting a lot better! I wonder if I've understood analyze properly: I'm not sure I quite understand how specific the statistics gathered actually are. In particular, what happens in the following case: 1. I start

Re: [PERFORM] Query times change by orders of magnitude as DB ages

Sergey Aleynikov wrote: Hello, * Is there any way I can nail the query planner to a particular query plan, rather than have it keep changing its mind? All these setting leads to choosing different plans. If you have small number of complex sensitive queires, you can run explain on them with c

Re: [PERFORM] Query times change by orders of magnitude as DB ages

Matthew Wakeling wrote: On Wed, 25 Nov 2009, Richard Neill wrote: On Sun, 22 Nov 2009, Richard Neill wrote: Worse still, doing a cluster of most of the tables and vacuum full analyze In fact, cluster is exactly the command you are looking for. It will drop the indexes, do a complete table

Re: [PERFORM] Query times change by orders of magnitude as DB ages

On Wed, 25 Nov 2009, Richard Neill wrote: On Sun, 22 Nov 2009, Richard Neill wrote: Worse still, doing a cluster of most of the tables and vacuum full analyze Why are you doing a vacuum full? That command is not meant to be used except in the most unusual of circumstances, as it causes bloat

Re: [PERFORM] Query times change by orders of magnitude as DB ages

Matthew Wakeling wrote: On Sun, 22 Nov 2009, Richard Neill wrote: Worse still, doing a cluster of most of the tables and vacuum full analyze Why are you doing a vacuum full? That command is not meant to be used except in the most unusual of circumstances, as it causes bloat to indexes. We

Re: [PERFORM] Query times change by orders of magnitude as DB ages

Matthew Wakeling wrote: On Sun, 22 Nov 2009, Richard Neill wrote: Worse still, doing a cluster of most of the tables and vacuum full analyze Why are you doing a vacuum full? That command is not meant to be used except in the most unusual of circumstances, as it causes bloat to indexes. We

Re: [PERFORM] RAID card recommendation

--- On Tue, 24/11/09, Scott Marlowe wrote: > Jochen Erwied > > wrote: > > > > Since I'm currently looking at upgrading my own > database server, maybe some > > of the experts can give a comment on one of the > following controllers: > > > > - Promise Technology Supertrak ES4650 + additional > BB

Re: [PERFORM] Strange performance degradation

On Tue, 24 Nov 2009, Denis Lussier wrote: Bouncing the app will roll back the transactions. Depends on the application. Some certainly use a shutdown hook to flush data out to a database cleanly. Obviously if you kill -9 it, then all bets are off. Matthew -- Software suppliers are trying t