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>
