Deleting queues containing many records is slow, can time out
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Evergreen |
Fix Released
|
Undecided
|
Unassigned |
Bug Description
Evergreen 2.8.1
Deleting an import queue can be very slow if the queue contains many records. On our system, deleting a queue containing 136 bib records (via MARC Batch Import/Export > Inspect Queues) timed out. Here's Postgres EXPLAIN output for the underlying DELETE query:
EXPLAIN ANALYZE DELETE FROM vandelay.bib_queue WHERE id = 3529;
-------
Delete on bib_queue (cost=0.28..6.30 rows=1 width=6) (actual time=0.143..0.143 rows=0 loops=1)
-> Index Scan using bib_queue_pkey on bib_queue (cost=0.28..6.30 rows=1 width=6) (actual time=0.076..0.077 rows=1 loops=1)
Index Cond: (id = 3529)
Planning time: 0.714 ms
Trigger for constraint queued_
Trigger for constraint acq_lineitem_
Trigger for constraint lineitem_
Trigger for constraint bib_match_
Trigger for constraint import_
Execution time: 79458.309 ms
(10 rows)
As you can see, the DELETE took almost 80 seconds, mainly because the queued bib records are deleted via cascade and there are a lot of unindexed foreign key references to vandelay.
After adding indexes to those foreign key references, the main DELETE query sped up substantially:
EXPLAIN ANALYZE DELETE FROM vandelay.bib_queue WHERE id = 3529;
-------
Delete on bib_queue (cost=0.28..6.30 rows=1 width=6) (actual time=0.137..0.137 rows=0 loops=1)
-> Index Scan using bib_queue_pkey on bib_queue (cost=0.28..6.30 rows=1 width=6) (actual time=0.071..0.072 rows=1 loops=1)
Index Cond: (id = 3529)
Planning time: 0.606 ms
Trigger for constraint queued_
Trigger for constraint acq_lineitem_
Trigger for constraint lineitem_
Trigger for constraint bib_match_
Trigger for constraint import_
Execution time: 31.199 ms
(10 rows)
I'll push a commit momentarily that adds those indexes.
Changed in evergreen: | |
assignee: | nobody → Kathy Lussier (klussier) |
Changed in evergreen: | |
milestone: | 2.9-alpha → 2.9-beta |
Changed in evergreen: | |
status: | Fix Committed → Fix Released |
See the top commit in this branch:
http:// git.evergreen- ils.org/ ?p=working/ Evergreen. git;a=shortlog; h=refs/ heads/user/ jeffdavis/ lp1479953- delete- bib-queue- speedup