Re: [PGSQL 8.3.5] Use of a partial indexes

On Mon, Dec 29, 2008 at 7:41 AM, Reg Me Please <regmeplease(at)gmail(dot)com> wrote:
> Hi.
>
> 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 planner seems to be smart enough to "learn" while working but then
> I should see a change in the EXPLAIN output, which never happens.
>
> I also tried to restart PostgreSQL in order to force a cache flush, but
> again, once the new performances are in the don't get out!

I'm guessing that what happened is that when you created the partial
index, pgsql read the whole table in, and the OS cached it.
Subsequent accesses hit either pgsql's shared_buffers or the OS cache.

You could try unmounting and remounting the partition in addition to
restarting pgsql and see if that helps, or for sure, reboot.

Also, for the partial index to be chosen, it has to match pretty much
exactly the where clause.