action.purge_circulation can be extremely slow

Bug #1778588 reported by Jason Boyer on 2018-06-25
This bug affects 1 person
Affects Status Importance Assigned to Milestone

Bug Description

I have noticed that the action.purge_circulations() db function running extremely slowly and on canceling it the following was referenced in the logs:
 "UPDATE ONLY "action"."usr_circ_history" SET "source_circ" = NULL WHERE $1 OPERATOR(pg_catalog.=) "source_circ""

In the context of: "DELETE FROM action.circulation WHERE id ="

Explain analyze does its thing:

Update on usr_circ_history (cost=0.00..22840.43 rows=1 width=50) (actual time=304.480..304.480 rows=0 loops=1)
   -> Seq Scan on usr_circ_history (cost=0.00..22840.43 rows=1 width=50) (actual time=304.477..304.477 rows=0 loops=1)
         Filter: (source_circ = 1)
         Rows Removed by Filter: 1006736
 Planning time: 0.309 ms
 Execution time: 304.521 ms

Sequential scan of every single circ history entry for every circ aged out.

A simple index on source_circ gives us a new plan:

 Update on usr_circ_history (cost=0.42..8.44 rows=1 width=50) (actual time=0.060..0.060 rows=0 loops=1)
   -> Index Scan using usr_circ_history_source_circ_idx on usr_circ_history (cost=0.42..8.44 rows=1 width=50) (actual time=0.059..0.059 rows=0 loops=1)
         Index Cond: (source_circ = 1)
 Planning time: 0.326 ms
 Execution time: 0.114 ms

That's more like it. Branch on its way.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers