Thanks, I think you have me on the right track. I'm testing a vacuum
analyse now to see how long it takes, and then I'll set it up to
automatically run every night (so that it has a chance to complete
before about 6am.)
On 02/01/2008, Andrew Sullivan <ajs(at)crankycanuck(dot)ca> wrote:
> On Wed, Jan 02, 2008 at 05:53:35PM +0200, Brian Modra wrote:
> > This table is added to in real time, at least 10 rows per second.
> [. . .]
> > If I do a select which uses the pkey index, where equal to the ID
> > column, and greater than one of the values, which should return about
> > 1500 rows, it sometimes takes 1/2 minute to return, and other times
> > takes only seconds.
> > Is it the number of rows being added in real time, that is maybe
> > causing the index to be locked?
> No, it's probably a bad plan. A minimum 10 rows/second is probably just
> making the statistics for the table look bad. You likely want to SET
> STATISTICS wider on the 1st (~150 distinct values) column, and then run
> ANALYSE on the table very frequently. Are you updating or deleting at all?
> If so, that will also affect things: you need to perform very frequent
> VACUUM on that table in that case.
> Aside from that generic advice, it's impossible to say more without EXPLAIN
> ANALYSE output for the slow and fast examples.
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 183 8059
6 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
In response to
pgsql-hackers by date
|Next:||From: Gokulakannan Somasundaram||Date: 2008-01-03 07:04:00|
|Subject: Re: Table rewrites vs. pending AFTER triggers|
|Previous:||From: Sam Mason||Date: 2008-01-03 00:41:04|
|Subject: Re: Dynamic Partitioning using Segment Visibility Maps|