Tom Lane wrote:

Bryce Nesbitt <bryce1@obviously.com> 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/