Skip site navigation (1) Skip section navigation (2)

Partial index on boolean - Sometimes fails to index scan

From: Bryce Nesbitt <bryce1(at)obviously(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Partial index on boolean - Sometimes fails to index scan
Date: 2007-09-04 07:51:29
Message-ID: 46DD0E81.2000901@obviously.com (view raw or flat)
Thread:
Lists: pgsql-sql
<!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>


Attachment: unknown_filename
Description: text/html (2.8 KB)

In response to

Responses

pgsql-sql by date

Next:From: Marc MaminDate: 2007-09-04 08:35:55
Subject: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
Previous:From: Luiz K. MatsumuraDate: 2007-09-04 07:33:50
Subject: Re: Cast on character columns in views

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group