[OPEN-ILS-GENERAL] SQL Query Performance

[Joshua D. Drake]

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