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/