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@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Reg Me Please > Sent: Monday, December 29, 2008 9:09 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: [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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
