This is a reformulation of an earlier question. I've got a confusing case of a partial index not working. The column
inquestion is a not-null boolean, which is false only for the most recent entries into the table.<br /><tt><br /> #
explainanalyze 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 /> ->
SeqScan on eg_vehicle_event vehicleeve0_ (cost=0.00..49162.93 rows=8679 width=4) (actual time=1202.175..2006.169
rows=10342loops=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 /> -> Index Scan
usingeg_ve_reconciled_partial on eg_vehicle_event vehicleeve0_ (cost=0.00..60.05 rows=8679 width=4) (actual
time=0.118..44.647rows=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
Hibernateversion 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
apparentlyidentical 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>