[OPEN-ILS-GENERAL] SQL Query Performance

[Galen Charlton]

Hi,

On Fri, Apr 26, 2013 at 11:51 AM, Dan Scott <dan at coffeecode.net> wrote:
> And deliberately so, because at the time the index was adjusted to be
> duplicative, PostgreSQL (8.4 IIRC?) was not smart enough to know that
> "deleted = FALSE" was the same as "deleted IS FALSE"; and as we were
> often writing ad hoc queries that used one or the other style, we were
> missing the index and going into table scan mode. Which sucks for
> performance.

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:

explain select id from asset.copy where barcode = 'abc' and deleted is not true;
                                QUERY PLAN
---------------------------------------------------------------------------
 Result  (cost=0.00..337028.88 rows=2 width=8)
   ->  Append  (cost=0.00..337028.88 rows=2 width=8)
         ->  Seq Scan on copy  (cost=0.00..337016.88 rows=1 width=8)
               Filter: ((deleted IS NOT TRUE) AND (barcode = 'abc'::text))
         ->  Seq Scan on unit copy  (cost=0.00..12.00 rows=1 width=8)
               Filter: ((deleted IS NOT TRUE) AND (barcode = 'abc'::text))
(6 rows)

Nor is 9.2:

explain select id from asset.copy where barcode = 'abc' and deleted is not true;
                                QUERY PLAN
---------------------------------------------------------------------------
 Result  (cost=0.00..830938.40 rows=2 width=8)
   ->  Append  (cost=0.00..830938.40 rows=2 width=8)
         ->  Seq Scan on copy  (cost=0.00..830936.90 rows=1 width=8)
               Filter: ((deleted IS NOT TRUE) AND (barcode = 'abc'::text))
         ->  Seq Scan on unit copy  (cost=0.00..1.50 rows=1 width=8)
               Filter: ((deleted IS NOT TRUE) AND (barcode = 'abc'::text))

To be sure, I don't think there's any code in Evergreen that would
generate a "boolean is not true" clause, but I would suggest that the
Evergreen commit logs do contain a lot of useful pointers to folks
interested in the reasoning behind the choices that the various
contributors to the project have made over the years.

Regards,

Galen
--
Galen Charlton
Manager of Implementation
Equinox Software, Inc. / The Open Source Experts
email:  gmc at esilibrary.com
direct: +1 770-709-5581
cell:   +1 404-984-4366
skype:  gmcharlt
web:    http://www.esilibrary.com/
Supporting Koha and Evergreen: http://koha-community.org &
http://evergreen-ils.org