Not sure if this applies to your case, but I've seen cases where an initial run of a particular query is a lot slower than subsequent runs even though no changes were made between the two. I suspect that the initial run did all the disk IO needed to get the data (slow), and that the subsequent runs were just reading the data out of memory (fast) as it was left over in the PG data buffer cache, the server's caches, the disk server's cache, etc... . Try the same query only with different search criteris. IOW, force it to go back out to disk. You may find that the slow performance returns. Good Luck ! -dave -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Reg Me Please Sent: Monday, December 29, 2008 9:09 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: [PGSQL 8.3.5] Use of a partial indexes HI all. I have a 8M+ rows table over which I run a query with a and-only WHERE condition. The table has been periodically VACUUMed and ANALYZEd. In the attempt of speeding that up I added a partial index in order to limit the size of the index. Of course that index is modeled after a "slowly variable" part of the WHERE condition I have in my query. And timings actually dropped dramatically (I do know the problems with caching etc. and I paid attention to that) to about 1/20th (from about 800ms to average 40ms, actually). So I turned to EXPLAIN to see how the partial index was used. Incredibly, the partial index was not used! So I tried to drop the new index and incredibly the performances where still very good. While I can understand that the planner can decide not to use a partial index (despite in my mind it'd make a lot of sense), I'd like to understand how it comes that I get benefits from an enhancement not used! What'd be the explanation (if any) for this behavior? Thanks. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
