Inefficient asset.copy barcode index slows down circ transactions in pg 8.3
Bug #587028 reported by
James Fournie
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';
Changed in evergreen: | |
status: | New → Fix Committed |
importance: | Undecided → High |
Changed in evergreen: | |
status: | Fix Committed → Fix Released |
To post a comment you must log in.
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.