From bryce1@obviously.com Tue Sep 4 07:51:39 2007 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 271B29F9DC8 for ; 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 ; 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 ; 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 ; 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 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 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.
# 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=49184.62..49184.64 rows=1 width=4) (actual time=2017.793..2017.794 rows=1 loops=1) -> 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) Filter: ((cso_id = 2) AND (NOT reconciled)) Total runtime: 2018.052 ms 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'); 204800 # 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.218..56.219 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.118..44.647 rows=10342 loops=1) Index Cond: (reconciled = false) Filter: (cso_id = 2) Total runtime: 56.312 ms

Which is all good. But the Hibernate version of query still takes several seconds, and still appears in my pg_log slow query log:

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

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

From bryce1@obviously.com Tue Sep 4 14:40:11 2007 Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 7AC549F9B79 for ; Tue, 4 Sep 2007 11:40:11 -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 82509-06 for ; Tue, 4 Sep 2007 11:40:08 -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 375549F96D0 for ; Tue, 4 Sep 2007 11:40: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; Tue, 04 Sep 2007 07:40: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 9B80C126286 for ; Tue, 4 Sep 2007 07:40:04 -0700 (PDT) Message-ID: <46DD6E44.4000505@obviously.com> Date: Tue, 04 Sep 2007 07:40: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 Subject: Re: Partial index on boolean - Sometimes fails to index scan References: <46DCE263.6040207@obviously.com> <46DD0E81.2000901@obviously.com> In-Reply-To: <46DD0E81.2000901@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.798 tagged_above=0 required=5 tests=AWL=-0.661, BAYES_50=0.001, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.457 X-Spam-Level: X-Archive-Number: 200709/35 X-Sequence-Number: 29248 Richard Huxton provided the answer: It's a prepared query-plan, which means it can't plan to use the index because the next EXECUTE might have reconciled=true.

Bryce Nesbitt wrote:

...Which is all good. But the Hibernate version of query still takes several seconds, and still appears in my pg_log slow query log:

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