Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2B4869F9AC9 for ; Tue, 4 Sep 2007 01:43:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 08584-03 for ; Tue, 4 Sep 2007 01:43:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from outA.internet-mail-service.net (outA.internet-mail-service.net [216.240.47.224]) by postgresql.org (Postfix) with ESMTP id 2D3409F96AD for ; Tue, 4 Sep 2007 01:43:17 -0300 (ADT) Received: from mx0.idiom.com (HELO idiom.com) (216.240.32.160) by out.internet-mail-service.net (qpsmtpd/0.40) with ESMTP; Mon, 03 Sep 2007 21:43:16 -0700 Received: from [192.168.1.109] (adsl-71-135-174-255.dsl.pltn13.pacbell.net [71.135.174.255]) by idiom.com (Postfix) with ESMTP id A101612626A for ; Mon, 3 Sep 2007 21:43:15 -0700 (PDT) Message-ID: <46DCE263.6040207@obviously.com> Date: Mon, 03 Sep 2007 21:43:15 -0700 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.4) Gecko/20061107 SUSE/1.1.2-1.1 SeaMonkey/1.1.2 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Difference between "foo is false" and "foo=false"? Partial index on boolean. Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.666 tagged_above=0 required=5 tests=AWL=-0.793, BAYES_50=0.001, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457 X-Spam-Level: X-Archive-Number: 200709/19 X-Sequence-Number: 29232 Expecting to save 4 seconds per query, I built a partial index on a table, and was surprised that it did not work. Could someone explain the difference between "foo=false" and "foo is false", for a boolean type column?
stage=# create index eg_ve_reconciled_partial on eg_vehicle_event (reconciled) where reconciled=false; stage=# select pg_total_relation_size('eg_ve_reconciled_partial'); pg_total_relation_size ------------------------ 8192 stage=# explain select count(*) from EG_VEHICLE_EVENT where reconciled is false; -------------------------------------------------------------------------- Aggregate (cost=33169.57..33169.58 rows=1 width=0) -> Seq Scan on eg_vehicle_event (cost=0.00..33169.57 rows=1 width=0) Filter: (reconciled IS FALSE) stage=# explain select count(*) from EG_VEHICLE_EVENT where reconciled=false; ------------------------------------------------------------------------------------------------------- Aggregate (cost=1.02..1.03 rows=1 width=0) -> Index Scan using eg_ve_reconciled_partial on eg_vehicle_event (cost=0.00..1.01 rows=1 width=0) Index Cond: (reconciled = false)

The problem is that my test query above is fast, but the real query from Hibernate is still dog slow. Here's the pg_log entry:

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 )]

I tried building two indexes, one for "is false" one for "=false", but the Hibernate query is still slow. Yet the hand-run version uses the index easily:

stage=# explain analyze select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=2 )and(vehicleeve0_.RECONCILED=false); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=81.75..81.76 rows=1 width=4) (actual time=56.153..56.154 rows=1 loops=1) -> Index Scan using eg_ve_reconciled_partial on eg_vehicle_event vehicleeve0_ (cost=0.00..60.05 rows=8679 width=4) (actual time=0.126..44.548 rows=10345 loops=1) Index Cond: (reconciled = false) Filter: (cso_id = 2) Total runtime: 64.825 ms (5 rows)

-- 
----
Visit http://www.obviously.com/