Received: from localhost (unknown [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 556669F9B66
	for <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql@postgresql.org>; 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 <bryce1@obviously.com>
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 <tgl@sss.pgh.pa.us>
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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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">&lt;bryce1@obviously.com&gt;</a> writes:
  </pre>
  <blockquote type="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 particular column 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 three indexes, 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) REFERENCES
eg_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.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_.<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>
</body>
</html>