Improve performance of changeset cleaner

Created by: eseliger

• Doesn't need to run that often.
• Improve query performance; tl;dr it got down from 59192ms to 7ms. (it was the #1 worst query in k8s)

Before:

explain analyze
DELETE FROM
  changeset_specs cspecs
WHERE
(
  -- The spec is older than the ChangesetSpecTTL
  created_at < now() - interval '3 days'
  AND
  -- and it was never attached to a batch_spec
  batch_spec_id IS NULL
)
OR
(
  -- The spec is older than the BatchSpecTTL
  created_at < now() - interval '8 days'
  AND
  -- and the batch_spec it is attached to is not applied to a batch_change
  NOT EXISTS (SELECT 1 FROM batch_changes WHERE batch_spec_id = cspecs.batch_spec_id)
  AND
  -- and it is not attached to a changeset
  NOT EXISTS (SELECT 1 FROM changesets WHERE current_spec_id = cspecs.id OR previous_spec_id = cspecs.id)
  AND
  -- and it is not created by SSBC
  NOT (SELECT created_from_raw FROM batch_specs WHERE id = cspecs.batch_spec_id)
);

Plan:

                                                                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on changeset_specs cspecs  (cost=2651.72..9714245.14 rows=30361 width=6) (actual time=59182.238..59182.244 rows=0 loops=1)
   ->  Bitmap Heap Scan on changeset_specs cspecs  (cost=2651.72..9714245.14 rows=30361 width=6) (actual time=59182.235..59182.241 rows=0 loops=1)
         Recheck Cond: ((batch_spec_id IS NULL) OR (created_at < (now() - '8 days'::interval)))
         Filter: (((created_at < (now() - '3 days'::interval)) AND (batch_spec_id IS NULL)) OR ((created_at < (now() - '8 days'::interval)) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2)) AND (NOT (SubPlan 3)) AND (NOT (SubPlan 4))))
         Rows Removed by Filter: 218455
         Heap Blocks: exact=28308
         ->  BitmapOr  (cost=2651.72..2651.72 rows=242891 width=0) (actual time=689.638..689.640 rows=0 loops=1)
               ->  Bitmap Index Scan on changeset_specs_batch_spec_id  (cost=0.00..1.53 rows=1 width=0) (actual time=0.043..0.043 rows=0 loops=1)
                     Index Cond: (batch_spec_id IS NULL)
               ->  Bitmap Index Scan on changeset_specs_created_at  (cost=0.00..2635.01 rows=242891 width=0) (actual time=689.593..689.593 rows=218455 loops=1)
                     Index Cond: (created_at < (now() - '8 days'::interval))
         SubPlan 1
           ->  Seq Scan on batch_changes  (cost=0.00..7.24 rows=1 width=0) (never executed)
                 Filter: (batch_spec_id = cspecs.batch_spec_id)
         SubPlan 2
           ->  Seq Scan on batch_changes batch_changes_1  (cost=0.00..6.59 rows=259 width=8) (actual time=37.595..37.677 rows=260 loops=1)
         SubPlan 3
           ->  Index Only Scan using changesets_changeset_specs on changesets  (cost=0.28..59.84 rows=2 width=0) (actual time=0.260..0.260 rows=0 loops=217483)
                 Filter: ((current_spec_id = cspecs.id) OR (previous_spec_id = cspecs.id))
                 Rows Removed by Filter: 1452
                 Heap Fetches: 63158658
         SubPlan 4
           ->  Index Scan using batch_specs_pkey on batch_specs  (cost=0.28..2.49 rows=1 width=1) (actual time=0.003..0.003 rows=1 loops=216985)
                 Index Cond: (id = cspecs.batch_spec_id)
 Planning Time: 0.438 ms
 JIT:
   Functions: 34
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 9.215 ms, Inlining 23.278 ms, Optimization 395.151 ms, Emission 276.956 ms, Total 704.600 ms
 Execution Time: 59191.755 ms
(30 rows)

After:

Part 1:

explain analyze
DELETE FROM
  changeset_specs
WHERE
  -- The spec is older than the ChangesetSpecTTL
  created_at < now() - interval '3 days'
  AND
  -- and it was never attached to a batch_spec
  batch_spec_id IS NULL;

Plan:

                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on changeset_specs  (cost=0.42..1.92 rows=1 width=6) (actual time=0.058..0.058 rows=0 loops=1)
   ->  Index Scan using changeset_specs_batch_spec_id on changeset_specs  (cost=0.42..1.92 rows=1 width=6) (actual time=0.057..0.057 rows=0 loops=1)
         Index Cond: (batch_spec_id IS NULL)
         Filter: (created_at < (now() - '3 days'::interval))
 Planning Time: 0.116 ms
 Execution Time: 0.083 ms
(6 rows)

Part 2:

explain analyze
WITH candidates AS (
  SELECT cs.id
  FROM changeset_specs cs
  JOIN batch_specs bs ON bs.id = cs.batch_spec_id
  LEFT JOIN batch_changes bc ON bs.id = bc.batch_spec_id
  LEFT JOIN changesets c ON (c.current_spec_id = cs.id OR c.previous_spec_id = cs.id)
  WHERE
    -- The spec is older than the BatchSpecTTL
    cs.created_at < now() - interval '8 days'
    -- and it is not created by SSBC
    AND NOT bs.created_from_raw
    -- and the batch spec it is attached to is not applied to a batch change
    AND bc.id IS NULL
    -- and it is not attached to a changeset
    AND c.id IS NULL
  FOR UPDATE OF cs
)
DELETE FROM changeset_specs
WHERE
  id IN (SELECT id FROM candidates);

Plan:

                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on changeset_specs  (cost=791.93..794.16 rows=1 width=38) (actual time=6.355..6.361 rows=0 loops=1)
   CTE candidates
     ->  LockRows  (cost=122.41..791.49 rows=1 width=32) (actual time=6.350..6.354 rows=0 loops=1)
           ->  Nested Loop Left Join  (cost=122.41..791.48 rows=1 width=32) (actual time=6.349..6.353 rows=0 loops=1)
                 Filter: (c.id IS NULL)
                 Rows Removed by Filter: 183
                 ->  Nested Loop  (cost=110.94..778.86 rows=1 width=26) (actual time=0.555..0.859 rows=183 loops=1)
                       ->  Hash Right Join  (cost=110.52..117.79 rows=1 width=20) (actual time=0.522..0.530 rows=8 loops=1)
                             Hash Cond: (bc.batch_spec_id = bs.id)
                             Filter: (bc.id IS NULL)
                             Rows Removed by Filter: 27
                             ->  Seq Scan on batch_changes bc  (cost=0.00..6.59 rows=259 width=22) (actual time=0.024..0.085 rows=260 loops=1)
                             ->  Hash  (cost=110.08..110.08 rows=35 width=14) (actual time=0.386..0.387 rows=35 loops=1)
                                   Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                   ->  Seq Scan on batch_specs bs  (cost=0.00..110.08 rows=35 width=14) (actual time=0.014..0.364 rows=35 loops=1)
                                         Filter: (NOT created_from_raw)
                                         Rows Removed by Filter: 596
                       ->  Index Scan using changeset_specs_batch_spec_id on changeset_specs cs  (cost=0.42..644.27 rows=1680 width=22) (actual time=0.009..0.037 rows=23 loops=8)
                             Index Cond: (batch_spec_id = bs.id)
                             Filter: (created_at < (now() - '8 days'::interval))
                             Rows Removed by Filter: 1
                 ->  Bitmap Heap Scan on changesets c  (cost=11.47..12.60 rows=2 width=30) (actual time=0.029..0.029 rows=1 loops=183)
                       Recheck Cond: ((current_spec_id = cs.id) OR (previous_spec_id = cs.id))
                       Heap Blocks: exact=183
                       ->  BitmapOr  (cost=11.47..11.47 rows=2 width=0) (actual time=0.027..0.027 rows=0 loops=183)
                             ->  Bitmap Index Scan on changesets_changeset_specs  (cost=0.00..0.29 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=183)
                                   Index Cond: (current_spec_id = cs.id)
                             ->  Bitmap Index Scan on changesets_changeset_specs  (cost=0.00..11.18 rows=1 width=0) (actual time=0.026..0.026 rows=1 loops=183)
                                   Index Cond: (previous_spec_id = cs.id)
   ->  Nested Loop  (cost=0.45..2.67 rows=1 width=38) (actual time=6.353..6.355 rows=0 loops=1)
         ->  HashAggregate  (cost=0.02..0.03 rows=1 width=40) (actual time=6.353..6.354 rows=0 loops=1)
               Group Key: candidates.id
               ->  CTE Scan on candidates  (cost=0.00..0.02 rows=1 width=40) (actual time=6.351..6.352 rows=0 loops=1)
         ->  Index Scan using changeset_specs_pkey on changeset_specs  (cost=0.42..2.64 rows=1 width=14) (never executed)
               Index Cond: (id = candidates.id)
 Planning Time: 1.080 ms
 Execution Time: 6.624 ms
(37 rows)

Test plan

Query plans attached above, and test suite adjusted.