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 <[EMAIL PROTECTED]> 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. > > A > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > -- 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 South Africa ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
