Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.

Tom Lane wrote: <blockquote cite="mid:14040.1188881806@sss.pgh.pa.us" type="cite"><pre wrap="">Bryce Nesbitt <a
class="moz-txt-link-rfc2396E"href="mailto:bryce1@obviously.com"><bryce1@obviously.com></a> writes:
</pre><blockquotetype="cite"><pre wrap="">Could someone explain
 
the difference between "foo=false" and "foo is false", for a boolean
type column?   </pre></blockquote><pre wrap="">
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...</pre></blockquote> Thanks, and Got It.  This
particularcolumn is:<br />     reconciled       | boolean                     | not null<br /> On PostgreSQL 8.1.9.<br
/><br/><br /> So given all that, why would the Hibernate query fail to use the partial index?   I eventually created
threeindexes, and only the hideously large full index increases performance:<br /><br /><tt>Indexes:<br />    
"eg_vehicle_event_pkey"PRIMARY KEY, btree (vehicle_event_id)<br />     "no_duplicate_events" UNIQUE, btree (cso_id,
event_type,"timestamp", fob_number, hardware_number)<br />     "eg_ve_reconciled_full" btree (reconciled)<br />    
"eg_ve_reconciled_partial"btree (reconciled) WHERE reconciled = false<br />     "eg_ve_reconciled_partial_is" btree
(reconciled)WHERE reconciled IS FALSE<br /> Foreign-key constraints:<br />     "fk_event_admin" FOREIGN KEY (admin_id)
REFERENCESeg_admin(admin_id)<br />     "fkd28396aacabde72e" FOREIGN KEY (vehicle_id) REFERENCES
eg_vehicle(vehicle_id)<br/>     "fkd28396aaf61930e0" FOREIGN KEY (member_id) REFERENCES eg_member(member_id)<br
/></tt><br/><br /> Only the full index prevents a "false" scan from taking 4 seconds:<br /><br /><tt>LOG:  duration:
4260.575ms  statement: EXECUTE C_50292  [PREPARE:  select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from
EG_VEHICLE_EVENTvehicleeve0_ where (vehicleeve0_.CSO_ID=$1 ) and (vehicleeve0_.<b>RECONCILED=$2</b> )]</tt><br /><br
/><br/><pre class="moz-signature" cols="100">-- 
 
----
Visit <a class="moz-txt-link-freetext" href="http://www.obviously.com/">http://www.obviously.com/</a>
</pre>