Message-ID: <46DD1AC8.7050407@archonet.com>
Date: Tue, 04 Sep 2007 09:43:52 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Difference between "foo is false" and "foo=false"? Partial
	index on boolean.
References: <46DCE263.6040207@obviously.com> <14040.1188881806@sss.pgh.pa.us>
	<46DCE7F8.6080000@obviously.com>
In-Reply-To: <46DCE7F8.6080000@obviously.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Bryce Nesbitt wrote:
> Tom Lane wrote:
>> Bryce Nesbitt <bryce1@obviously.com> writes:
>>
>> They give different results for NULL --- specifically, NULL for the
>> former and FALSE for the latter.  Don't blame me, it's in the spec...
> Thanks, and Got It.  This particular column is:
>     reconciled       | boolean                     | not null
> On PostgreSQL 8.1.9.

> So given all that, why would the Hibernate query fail to use the partial 
> index?   I eventually created three indexes, and only the hideously large full 
> index increases performance:

> Only the full index prevents a "false" scan from taking 4 seconds:
> 
> LOG:  duration: 4260.575 ms  statement: EXECUTE C_50292  [PREPARE:  select 
> count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ 
> where (vehicleeve0_.CSO_ID=$1 ) and (vehicleeve0_.*RECONCILED=$2* )]

It's a prepared query-plan, which means it can't plan to use the index 
because the next EXECUTE might have reconciled=true.

-- 
   Richard Huxton
   Archonet Ltd