Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 271B29F9DC8
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  4 Sep 2007 04:51:39 -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 40675-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  4 Sep 2007 04:51:33 -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 907FE9F9C62
	for <pgsql-sql@postgresql.org>; Tue,  4 Sep 2007 04:51:33 -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;
	Tue, 04 Sep 2007 00:51:31 -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 009DB126277
	for <pgsql-sql@postgresql.org>; Tue,  4 Sep 2007 00:51:30 -0700 (PDT)
Message-ID: <46DD0E81.2000901@obviously.com>
Date: Tue, 04 Sep 2007 00:51:29 -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
Subject: Partial index on boolean - Sometimes fails to index scan
References: <46DCE263.6040207@obviously.com>
In-Reply-To: <46DCE263.6040207@obviously.com>
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.759 tagged_above=0 required=5 tests=AWL=-0.700,
	BAYES_50=0.001, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457
X-Spam-Level: 
X-Archive-Number: 200709/24
X-Sequence-Number: 29237

<!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">
This is a reformulation of an earlier question.  I've got a confusing
case of a partial index not working.  The column in question is a
not-null boolean, which is false only for the most recent entries into
the table.<br>
<tt><br>
# 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);<br>
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------<br>
 Aggregate  (cost=49184.62..49184.64 rows=1 width=4) (actual
time=2017.793..2017.794 rows=1 loops=1)<br>
   -&gt;  Seq Scan on eg_vehicle_event vehicleeve0_ 
(cost=0.00..49162.93 rows=8679 width=4) (actual time=1202.175..2006.169
rows=10342 loops=1)<br>
         Filter: ((cso_id = 2) AND (NOT reconciled))<br>
Total runtime: 2018.052 ms<br>
<br>
stage=# create index eg_ve_reconciled_partial on eg_vehicle_event
(reconciled) where reconciled=false;<br>
<br>
stage=# select pg_total_relation_size('eg_ve_reconciled_partial');<br>
204800<br>
<br>
# 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);<br>
QUERY PLAN <br>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
 Aggregate  (cost=81.75..81.76 rows=1 width=4) (actual
time=56.218..56.219 rows=1 loops=1)<br>
   -&gt;  Index Scan using eg_ve_reconciled_partial on eg_vehicle_event
vehicleeve0_  (cost=0.00..60.05 rows=8679 width=4) (actual
time=0.118..44.647 rows=10342 loops=1)<br>
         Index Cond: (reconciled = false)<br>
         Filter: (cso_id = 2)<br>
 Total runtime: 56.312 ms</tt><br>
<br>
<br>
<hr size="2" width="100%">Which is all good.  But the Hibernate version
of query still takes several seconds, and still appears in my pg_log
slow query log:<br>
<br>
<tt>LOG:  duration: 2248.662 ms  statement: EXECUTE C_51443  [PREPARE: 
select count(vehicleeve0_.VEHICLE_EVENT_ID) as x0_0_ from
EG_VEHICLE_EVENT vehicleeve0_ where (vehicleeve0_.CSO_ID=$1
)and(vehicleeve0_.RECONCILED=$2 )]<br>
</tt><br>
A full index on 'reconciled' speeds up the query.  But why should the
partial index not also do it?  Any idea why apparently identical
queries give different partial index scan results?  PostgreSQL 8.1.9.<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>