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

[Bryce Nesbitt]

Tom Lane wrote: Bryce Nesbitt <[EMAIL PROTECTED]> writes: Could someone explain the difference between "foo=false" and "foo is false", for a boolean type column? 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: Indexes:     "eg_vehicle_event_pkey" PRIMARY KEY, btree (vehicle_event_id)     "no_duplicate_events" UNIQUE, btree (cso_id, event_type, "timestamp", fob_number, hardware_number)     "eg_ve_reconciled_full" btree (reconciled)     "eg_ve_reconciled_partial" btree (reconciled) WHERE reconciled = false     "eg_ve_reconciled_partial_is" btree (reconciled) WHERE reconciled IS FALSE Foreign-key constraints:     "fk_event_admin" FOREIGN KEY (admin_id) REFERENCES eg_admin(admin_id)     "fkd28396aacabde72e" FOREIGN KEY (vehicle_id) REFERENCES eg_vehicle(vehicle_id)     "fkd28396aaf61930e0" FOREIGN KEY (member_id) REFERENCES eg_member(member_id) 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 )] -- ---- Visit http://www.obviously.com/