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

Index performance

From: "Brian Modra" <epailty(at)googlemail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Index performance
Date: 2008-01-02 15:53:35
Message-ID: 5a9699850801020753q7d0637dal27cb9d3c83075fef@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

I have a table with a primarry key made of two columns.
One of these has about 150 distinct values which are unique IDs, and
the other has over 3 million almost unique data values.
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?

I tried creating a partial index where equal to the ID column, and
that sped it up cconsiderably and consistently, but this is a headache
to maintain.

Any ideas?
Thanks
Brian

-- 
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

Responses

pgsql-hackers by date

Next:From: Andrew SullivanDate: 2008-01-02 16:25:39
Subject: Re: Slow count(*)
Previous:From: A. KretschmerDate: 2008-01-02 15:50:52
Subject: Re: Slow count(*)

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