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

Re: Chaotically weird execution plan

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Chaotically weird execution plan
Date: 2008-09-24 03:12:26
Message-ID: 48D9B01A.5010702@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> writes:
>> I'd already written: "If you need the test for status = 1, consider a
>> partial index" when I noticed your schema definition:
> 
>>> "comments_created_by" btree (created_by) WHERE status = 1
> 
>> I find it hard to guess why it's having to recheck the WHERE clause
>> given the use of a partial index that should cover that nicely.
> 
> No, that's operating as designed.  A bitmap scan's RECHECK condition
> is only applied when the bitmap has become lossy due to memory
> pressure.  In that case we have to look at each row on each of the pages
> fingered by the index as containing possible matches ... and we'd better
> check the partial-index qual too, since maybe not all the rows on those
> pages will satisfy it.  In a plain indexscan there is no lossiness
> involved and so the partial-index qual need never be rechecked.

Aah. Thanks very much for the explanation of that, the plan now makes sense.

--
Craig Ringer

In response to

pgsql-performance by date

Next:From: Bruce MomjianDate: 2008-09-24 03:24:57
Subject: Re: Intel's X25-M SSD
Previous:From: Tom LaneDate: 2008-09-24 03:01:10
Subject: Re: Chaotically weird execution plan

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