Re: Difference between "foo is false" and "foo=false"? Partial index on boolean. - Mailing list pgsql-sql

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