[PERFORM] sub-select in IN clause results in sequential scan

Postgres consistently does a sequential scan on the child partitions for this query select * from partitioned_table where partitioned_column > current_timestamp - interval 8 days where x in (select yy from z where colname like 'aaa%') If I replace the query with select * from partitioned_table w

Mesa (master): Merge branch 'mesa_7_6_branch'

Re: [PERFORM] Postgresql optimisation

> From: Matthew Wakeling > > Perhaps reading the other replies in the thread before > replying yourself might be advisable, because this previous > reply directly contradicts you: > > On Wed, 28 Oct 2009, Kevin Grittner wrote: > > I recommend VACUUM ANALYZE of the table(s) after this step. With

Re: [PERFORM] Postgresql optimisation

On Wed, 28 Oct 2009, Dave Dutcher wrote: Also if you switch to truncate then you should ANALYSE the tables after you finish inserting. Note that VACUUM ANALYSE is not necessary after a truncate/insert because there should be no dead tuples to vacuum. Perhaps reading the other replies in the th

Re: [PERFORM] Postgresql optimisation

> -Original Message- > From: Denis BUCHER > > And each morning huge tables are DELETED and all data is > INSERTed new from a script. (Well, "huge" is very relative, > it's only 400'000 records) If you are deleting ALL rows in the tables, then I would suggest using TRUNCATE instead of DE

Re: [PERFORM] Postgresql optimisation

On Wed, 28 Oct 2009, Denis BUCHER wrote: For now, we only planned a VACUUM ANALYSE eacha night. You really want to be on a later release than 8.1 for an app that is heavily deleting things every day. The answer to most VACUUM problems is "VACUUM more often, preferrably with autovacuum", and

Re: [PERFORM] Postgresql optimisation

Kevin Grittner a écrit : >> And each morning ... all data is INSERTed new > > I recommend VACUUM ANALYZE of the table(s) after this step. Without > that, the first query to read each tuple sets its hint bits and > rewrites it, causing a surprising delay at unpredictable times > (although heavier

Re: [PERFORM] Postgresql optimisation

Denis BUCHER wrote: > And each morning ... all data is INSERTed new I recommend VACUUM ANALYZE of the table(s) after this step. Without that, the first query to read each tuple sets its hint bits and rewrites it, causing a surprising delay at unpredictable times (although heavier near the star

Re: [PERFORM] Postgresql optimisation

2009/10/28 Denis BUCHER > Grzegorz Jaśkiewicz a écrit : > > > > > > On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER > > wrote: > > > > Dear all, > > > > I need to optimize a database used by approx 10 people, I don't need > to > > have the perfect confi

Re: [PERFORM] Postgresql optimisation

Grzegorz Jaśkiewicz a écrit : > > > On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER > wrote: > > Dear all, > > I need to optimize a database used by approx 10 people, I don't need to > have the perfect config, simply to avoid stupid bottle necks and f

Re: [PERFORM] Postgresql optimisation

On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER wrote: > Dear all, > > I need to optimize a database used by approx 10 people, I don't need to > have the perfect config, simply to avoid stupid bottle necks and follow > the best practices... > > The database is used from a web interface the whole wo

[PERFORM] Postgresql optimisation

Dear all, I need to optimize a database used by approx 10 people, I don't need to have the perfect config, simply to avoid stupid bottle necks and follow the best practices... The database is used from a web interface the whole work day with "normal" requests (nothing very special). And each mor