Re: [PGSQL 8.3.5] Use of a partial indexes

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(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Reg Me Please
Sent: Monday, December 29, 2008 9:09 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] [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