Re: Index performance

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.

A