Skip site navigation (1) Skip section navigation (2)

Re: Index performance

From: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Index performance
Date: 2008-01-02 16:31:55
Message-ID: 20080102163155.GX22706@crankycanuck.ca (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2008-01-02 16:42:53
Subject: Re: [HACKERS] Slow count(*)
Previous:From: Andrew SullivanDate: 2008-01-02 16:25:39
Subject: Re: Slow count(*)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group