Re: [PGSQL 8.3.5] Use of a partial indexes

Only one question remains in my mind:

why the planner is not using the partial index?

The partial index is covering 2 predicates out of the 3 used in the where
condition. Actually there is a boolean flag (to exclude "disabled" rows),
a timestamp (for row age) and an int8 (a FK to another table).
The first two are in the partial index in order to exclude "disabled" and
older rows. The int8 is the "random" key I mentioned earlier.

So the WHERE condition reads like:

flag AND tstz >= '2008-01-01'::timestamptz and thekey=42

I can see in the EXPLAIN that there is no mention to the partial index.
Please keep in mind that the table has 8+M rows, few of which are flagged,
about 70% don't match the age limit and few dozens match the key.
In my opinion the partial index should help a lot.

--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

On Monday December 29 2008 16:36:49 Scott Ribe wrote:
> > The WHERE condition can be divided into a "slowly changing" part and in
> > a "random" one. The random part is the one I change at every query to
> > avoid result caching.
>
> The first query will leave in cache at least many of the index pages needed
> by the second query, and likely actual rows needed by the second query.
>
> > Disk cache could explain the thing, but then why I got the high
> > performances after the partial index has been created? By chance?
>
> Creating the partial index reads rows, and the pages are left in the disk
> cache. The only way to do proper comparisons is to reboot between trials in
> order to compare queries with cold caches, or use the latter of multiple
> runs in order to compare queries with hot caches.