Skip site navigation (1) Skip section navigation (2)

Re: [PGSQL 8.3.5] Use of a partial indexes

From: Reg Me Please <regmeplease(at)gmail(dot)com>
To: "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: [PGSQL 8.3.5] Use of a partial indexes
Date: 2008-12-29 14:41:17
Message-ID: 200812291541.18362.regmeplease@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Scott MarloweDate: 2008-12-29 15:33:04
Subject: Re: [PGSQL 8.3.5] Use of a partial indexes
Previous:From: Gauthier, DaveDate: 2008-12-29 14:24:33
Subject: Re: [PGSQL 8.3.5] Use of a partial indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group