From: Richard Huxton <dev@archonet.com>
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.
Date: Tue, 04 Sep 2007 09:43:52 +0100
Message-ID: <46DD1AC8.7050407@archonet.com> (raw)
In-Reply-To: <46DCE7F8.6080000@obviously.com>
References: <46DCE263.6040207@obviously.com>
<14040.1188881806@sss.pgh.pa.us>
<46DCE7F8.6080000@obviously.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, bryce1@obviously.com, tgl@sss.pgh.pa.us
Subject: Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.
In-Reply-To: <46DD1AC8.7050407@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox