Inefficient asset.copy barcode index slows down circ transactions in pg 8.3

Bug #587028 reported by James Fournie
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
Fix Released
High
Unassigned

Bug Description

EXPLAIN ANALYZE SELECT * FROM asset.copy where barcode = 'something' AND deleted = 'f';
- does a seq scan

EXPLAIN ANALYZE SELECT * FROM asset.copy where barcode = 'something' AND deleted IS FALSE
- does an index scan

cstore generates queries that look like the former and ends up doing sequential scans several times on every checkout/checkin. The index looks like this:

CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted IS FALSE;

In order to use the index and save hundreds of ms off of those queries, the index should probably be changed to something like:

CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted IS FALSE OR deleted = 'f';

Revision history for this message
Dan Scott (denials) wrote :

This looks good to me. I can confirm that the change made a significant difference to the results of EXPLAIN ANALYZE on our 8.3 system (from 1419 ms to 0.060 ms) for the deleted = 'f' case.

Revision history for this message
Mike Rylander (mrylander) wrote : Re: [Bug 587028] Re: Inefficient asset.copy barcode index slows down circ transactions in pg 8.3

On Tue, Jun 1, 2010 at 12:35 PM, Dan Scott <email address hidden> wrote:
> This looks good to me. I can confirm that the change made a significant
> difference to the results of EXPLAIN ANALYZE on our 8.3 system (from
> 1419 ms to 0.060 ms) for the deleted = 'f' case.
>

[snip]

> In order to use the index and save hundreds of ms off of those queries, the index should probably be changed to something like:
>
> CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted IS FALSE OR deleted = 'f';
>

cstore will only ever generate a query with the = operator for boolean
columns. All that's needed in the WHERE clause for the index is
"WHERE deleted = 'f'". Dan, do you want to do the upgrade script, or
shall I?

--
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / The Evergreen Experts
 | phone: 1-877-OPEN-ILS (673-6457)
 | email: <email address hidden>
 | web: http://www.esilibrary.com

Revision history for this message
Dan Scott (denials) wrote :

http://www.postgresql.org/docs/8.3/static/datatype-boolean.html says "Using the key words TRUE and FALSE is preferred (and SQL-compliant).", so for the minimal "make cstore happy" case we should probably go with:

CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE;

(which I have confirmed provides the same results as deleted = 'f'). If we want to support ad-hoc queries or other tools that might generate the IS FALSE expression, then the overhead of:

CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;

should be miniscule. I'd suggest going with the latter, personally.

I've also bugged #postgresql to say "Hey, shouldn't the planner recognize that IS FALSE and deleted = FALSE are the same damn thing"; there was some interest, so who knows, maybe the mess will be cleared up by 9.0?

Revision history for this message
Dan Scott (denials) wrote :

Mmm. And things like vandelay.match_bib_record() have used the IS FALSE construct since 1.4, so I think we would be better off with the "deleted = FALSE OR deleted IS FALSE" belt-and-suspenders approach. I'll work up a patch for this (there seem to be only four such indexes in trunk).

Revision history for this message
Dan Scott (denials) wrote :

Committed http://svn.open-ils.org/trac/ILS/changeset/16588 to trunk to address all four existing partial indexes on boolean columns. If it's good, I think we should backport it at least to 1.6.0.x

Dan Scott (denials)
Changed in evergreen:
status: New → Fix Committed
importance: Undecided → High
James Fournie (jfournie)
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.