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 (closed) 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.