On 04/26/2013 12:14 PM, Galen Charlton wrote:
And if the evidence of these explains is any guide, although the query
parser has gotten smarter since 8.4, even in 9.1 the query parser
isn't quite smart enough to detect *all* of the variants of asserting
a condition on a boolean column and pick the best index:
I think this is a false test. You don't have an index on
asset.copy(barcode). See:
Nor is 9.2:
on my 9.2:
explain analyze select id from asset.copy where barcode = 'abc' and
deleted is not true;;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..11.16 rows=2 width=8) (actual time=0.027..0.027
rows=0 loops=1)
-> Append (cost=0.00..11.16 rows=2 width=8) (actual
time=0.027..0.027 rows=0 loops=1)
-> Index Scan using barcode_test on copy (cost=0.00..9.66
rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=1)
Index Cond: (barcode = 'abc'::text)
Filter: (deleted IS NOT TRUE)
-> Seq Scan on unit copy (cost=0.00..1.50 rows=1 width=8)
(actual time=0.007..0.007 rows=0 loops=1)
Filter: ((deleted IS NOT TRUE) AND (barcode = 'abc'::text))
Rows Removed by Filter: 5
Total runtime: 0.052 ms
Notice it does use an index if it is present and will filter after the
fact. So I am not sure the partial is still needed.
JD
--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579
