Re: Tuning to speed select - Mailing list pgsql-general
| From | Tom Laudeman |
|---|---|
| Subject | Re: Tuning to speed select |
| Date | |
| Msg-id | 44DA4B68.9080206@virginia.edu Whole thread Raw |
| In response to | Re: Tuning to speed select (Michael Fuhr <mike@fuhr.org>) |
| Responses |
Re: Tuning to speed select
(Michael Fuhr <mike@fuhr.org>)
Re: Tuning to speed select (Reece Hart <reece@harts.net>) Re: Tuning to speed select ("Merlin Moncure" <mmoncure@gmail.com>) |
| List | pgsql-general |
Michael,
Great suggestion. I've read about CLUSTER, but never had a chance to use it. The only problem is that this table with 9 million records has 5 or 6 indexes. It is hard to pick the most used, but I'll bet CLUSTER will make at least one of the queries run very fast, especially for an index with a small number of distinct values.
The speed of the query is (as Michael implies) limited to the rate at which the disk can seek and read. I have done experiments with views and cursors; there was no improvement in speed. I've also tried only pulling back primary keys in the hope that a smaller amount of data would more quickly be read into memory. No speed increase. I have also raised all the usual memory limits, with the expected results (slight speed improvements).
I'll try CLUSTER (I'm looking forward to that test), but if we really need speed, it will probably be necessary to create copies of the table, or copy portions of the table elsewhere (essentially creating materialized views, I suppose). I'm still trying to get my science compatriot here to tell me which index he most wants to improve, then I'll CLUSTER the table on that index.
Thanks!
Tom
Michael Fuhr wrote:
Great suggestion. I've read about CLUSTER, but never had a chance to use it. The only problem is that this table with 9 million records has 5 or 6 indexes. It is hard to pick the most used, but I'll bet CLUSTER will make at least one of the queries run very fast, especially for an index with a small number of distinct values.
The speed of the query is (as Michael implies) limited to the rate at which the disk can seek and read. I have done experiments with views and cursors; there was no improvement in speed. I've also tried only pulling back primary keys in the hope that a smaller amount of data would more quickly be read into memory. No speed increase. I have also raised all the usual memory limits, with the expected results (slight speed improvements).
I'll try CLUSTER (I'm looking forward to that test), but if we really need speed, it will probably be necessary to create copies of the table, or copy portions of the table elsewhere (essentially creating materialized views, I suppose). I'm still trying to get my science compatriot here to tell me which index he most wants to improve, then I'll CLUSTER the table on that index.
Thanks!
Tom
Michael Fuhr wrote:
On Wed, Aug 09, 2006 at 03:46:38PM +0200, Martijn van Oosterhout wrote:On Wed, Aug 09, 2006 at 09:19:31AM -0400, Tom Laudeman wrote:Is there a tuning parameter I can change to increase speed of selects? Clearly, there's already some buffering going on since selecting an indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 seconds on the second try (from pgsql).Your OS is probably buffering, 1GB of RAM holds a lot of data. You can try increasing the shared_buffers parameter, but if the delay is getting data from the disk, that won't really help you.If most of your queries use the same index then clustering on that index might speed up initial (i.e., not-cached) queries by reducing the number of disk pages that need to be read. See the documentation for more information. http://www.postgresql.org/docs/8.1/interactive/sql-cluster.html
-- Tom Laudeman twl8n@virginia.edu (434) 924-2456 http://www.people.virginia.edu/~twl8n/ http://laudeman.com/
pgsql-general by date: