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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2008-01-02 16:25:39 | Re: Slow count(*) |
Previous Message | A. Kretschmer | 2008-01-02 15:50:52 | Re: Slow count(*) |