From bryce1@obviously.com Tue Sep 4 04:43:24 2007 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/

From tgl@sss.pgh.pa.us Tue Sep 4 04:56:54 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 04C9D9F9B60 for ; Tue, 4 Sep 2007 01:56:54 -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 10994-03 for ; Tue, 4 Sep 2007 01:56:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 4D22B9F994D for ; Tue, 4 Sep 2007 01:56:51 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l844ukvC014041; Tue, 4 Sep 2007 00:56:46 -0400 (EDT) To: Bryce Nesbitt cc: pgsql-sql@postgresql.org Subject: Re: Difference between "foo is false" and "foo=false"? Partial index on boolean. In-reply-to: <46DCE263.6040207@obviously.com> References: <46DCE263.6040207@obviously.com> Comments: In-reply-to Bryce Nesbitt message dated "Mon, 03 Sep 2007 21:43:15 -0700" Date: Tue, 04 Sep 2007 00:56:46 -0400 Message-ID: <14040.1188881806@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/20 X-Sequence-Number: 29233 Bryce Nesbitt 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... regards, tom lane From bryce1@obviously.com Tue Sep 4 05:08:30 2007 Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 556669F9B66 for ; Tue, 4 Sep 2007 02:08:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 72839-04-3 for ; Tue, 4 Sep 2007 02:08:22 -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 F11C59F95EE for ; Tue, 4 Sep 2007 02:07:07 -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 22:07:05 -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 1772812627D; Mon, 3 Sep 2007 22:07:05 -0700 (PDT) Message-ID: <46DCE7F8.6080000@obviously.com> Date: Mon, 03 Sep 2007 22:07:04 -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 CC: Tom Lane Subject: Re: Difference between "foo is false" and "foo=false"? Partial index on boolean. References: <46DCE263.6040207@obviously.com> <14040.1188881806@sss.pgh.pa.us> In-Reply-To: <14040.1188881806@sss.pgh.pa.us> 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.715 tagged_above=0 required=5 tests=AWL=-0.744, BAYES_50=0.001, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457 X-Spam-Level: X-Archive-Number: 200709/21 X-Sequence-Number: 29234 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/

From dev@archonet.com Tue Sep 4 08:44:09 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 0AE109F960F for ; Tue, 4 Sep 2007 05:44:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 87524-03 for ; Tue, 4 Sep 2007 05:44:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id AF4E19F95FE for ; Tue, 4 Sep 2007 05:44:05 -0300 (ADT) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id DC8D9143982; Tue, 4 Sep 2007 09:43:52 +0100 (BST) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 8F0C3274043; Tue, 4 Sep 2007 09:43:52 +0100 (BST) Message-ID: <46DD1AC8.7050407@archonet.com> Date: Tue, 04 Sep 2007 09:43:52 +0100 From: Richard Huxton User-Agent: Thunderbird 2.0.0.0 (X11/20070326) MIME-Version: 1.0 To: Bryce Nesbitt Cc: pgsql-sql@postgresql.org, Tom Lane Subject: Re: Difference between "foo is false" and "foo=false"? Partial index on boolean. References: <46DCE263.6040207@obviously.com> <14040.1188881806@sss.pgh.pa.us> <46DCE7F8.6080000@obviously.com> In-Reply-To: <46DCE7F8.6080000@obviously.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/25 X-Sequence-Number: 29238 Bryce Nesbitt wrote: > Tom Lane wrote: >> Bryce Nesbitt writes: >> >> 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: > 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* )] It's a prepared query-plan, which means it can't plan to use the index because the next EXECUTE might have reconciled=true. -- Richard Huxton Archonet Ltd