Skip to content

db: Faster SQL cleanup of cm_trigger_jobs

Warren Gifford requested to merge es/faster-cm-trigger-cleanup into main

Created by: eseliger

Found on demo, this used to be slow.

Before

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Delete on cm_trigger_jobs  (cost=0.00..130427.12 rows=233 width=6) (actual time=999.059..999.060 rows=0 loops=1)
   ->  Seq Scan on cm_trigger_jobs  (cost=0.00..130427.12 rows=233 width=6) (actual time=999.058..999.058 rows=0 loops=1)
         Filter: (finished_at < (now() - '100 years'::interval))
         Rows Removed by Filter: 2325550
 Planning Time: 0.059 ms
 Execution Time: 999.080 ms
(6 rows)

After

                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on cm_trigger_jobs  (cost=0.43..1.71 rows=1 width=6) (actual time=0.005..0.005 rows=0 loops=1)
   ->  Index Scan using cm_trigger_jobs_finished_at_idx1 on cm_trigger_jobs  (cost=0.43..1.71 rows=1 width=6) (actual time=0.004..0.004 rows=0 loops=1)
         Index Cond: (finished_at < (now() - '100 years'::interval))
 Planning Time: 0.253 ms
 Execution Time: 0.024 ms

Test plan

Just an index, proof it is good is above. Tests will tell if this can break anything.

Merge request reports