Re: [PGSQL 8.3.5] Use of a partial indexes

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!

Disk cache could explain the thing, but then why I got the high performances
after the partial index has been created? By chance?

On Monday December 29 2008 15:24:33 Gauthier, Dave wrote:
> 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

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