Improve attribute delete operation index usage and performance

Gal Katzrequested to merge
gkatz_improve_attribute_delete_operation_index_usage into master

What does this MR do and why?

Attributes::ProjectToSecurityAttributeDestroyService#delete_associations_for_attribute replaces a find_in_batches loop with a limit(BATCH_SIZE).delete_all loop.

find_in_batches orders by id to page through results, but the only index on project_to_security_attributes for this query is on security_attribute_id alone, so PG can't use it to satisfy the ordering. Every batch re-scanned and sorted all remaining rows for the attribute before returning 100, and since rows are deleted as we go, the cost grows roughly quadratically with the number of associations. Dropping the ORDER BY lets the existing index serve the whole query: each batch stops scanning once it hits LIMIT, so the total work is linear in the number of rows deleted.

Changelog: performance
EE: true

Examples:

500 rows, batch size 100: The old approach reads each row about 3 times on average before deleting it, while the new one reads each row once:

Batch 1 Batch 2 Batch 3 Batch 4 Batch 5 Total row reads
Old (find_in_batches) 500 400 300 200 100 1500
New (limit.delete_all) 100 100 100 100 100 500

20,000 rows, batch size 100: the old approach performs about 2 million row reads to delete 20,000 associations, roughly 100x more than the new approach's 20,000.

Batch 1 Batch 2 Batch 3 … Batch 200 Total row reads
Old (find_in_batches) 20,000 19,900 19,800 … 100 ~2,010,000
New (limit.delete_all) 100 100 100 … 100 20,000

Related issue

Evaluate and improve index coverage for delete ... (#571142 - closed) • Gal Katz • 19.5

Query plans

Existing delete operation

Raw SQL 
SELECT "project_to_security_attributes".*
FROM "project_to_security_attributes"
WHERE "project_to_security_attributes"."security_attribute_id" = 1013570
  AND "project_to_security_attributes"."id" > 150
ORDER BY "project_to_security_attributes"."id" ASC
LIMIT 100
Query plan

Full details here

 Limit  (cost=339.67..339.92 rows=100 width=86) (actual time=3.322..3.337 rows=100 loops=1)
   Buffers: shared hit=7 read=59
   I/O Timings: read=3.012 write=0.000
   ->  Sort  (cost=339.67..340.63 rows=383 width=86) (actual time=3.321..3.329 rows=100 loops=1)
         Sort Key: project_to_security_attributes.id
         Sort Method: top-N heapsort  Memory: 41kB
         Buffers: shared hit=7 read=59
         I/O Timings: read=3.012 write=0.000
         ->  Index Scan using index_project_to_security_attributes_on_security_attribute_id on public.project_to_security_attributes  (cost=0.29..325.03 rows=383 width=86) (actual time=1.383..3.238 rows=383 loops=1)
               Index Cond: (project_to_security_attributes.security_attribute_id = 1013570)
               Index Searches: 1
               Filter: (project_to_security_attributes.id > 150)
               Buffers: shared hit=4 read=59
               I/O Timings: read=3.012 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'

New delete operation

Raw SQL 
DELETE FROM project_to_security_attributes
WHERE (project_to_security_attributes.id) IN (
        SELECT
            project_to_security_attributes.id
        FROM
            project_to_security_attributes
        WHERE
            project_to_security_attributes.security_attribute_id = 1013570
        LIMIT 100);
Query plan

Full details here

 Delete on public.project_to_security_attributes  (cost=86.36..377.10 rows=0 width=0) (actual time=0.468..0.470 rows=0 loops=1)
   Buffers: shared hit=419 read=1 dirtied=14
   WAL: records=100 fpi=14 bytes=76630
   I/O Timings: read=0.033 write=0.000
   ->  Nested Loop  (cost=86.36..377.10 rows=100 width=38) (actual time=0.193..0.346 rows=100 loops=1)
         Buffers: shared hit=319 read=1
         I/O Timings: read=0.033 write=0.000
         ->  HashAggregate  (cost=86.08..87.08 rows=100 width=40) (actual time=0.111..0.141 rows=100 loops=1)
               Group Key: "ANY_subquery".id
               Batches: 1  Memory Usage: 32kB
               Buffers: shared hit=20
               I/O Timings: read=0.000 write=0.000
               ->  Subquery Scan on "ANY_subquery"  (cost=0.29..85.83 rows=100 width=40) (actual time=0.035..0.090 rows=100 loops=1)
                     Buffers: shared hit=20
                     I/O Timings: read=0.000 write=0.000
                     ->  Limit  (cost=0.29..84.83 rows=100 width=8) (actual time=0.028..0.067 rows=100 loops=1)
                           Buffers: shared hit=20
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Scan using index_project_to_security_attributes_on_security_attribute_id on public.project_to_security_attributes project_to_security_attributes_1  (cost=0.29..324.07 rows=383 width=8) (actual time=0.027..0.060 rows=100 loops=1)
                                 Index Cond: (project_to_security_attributes_1.security_attribute_id = 1013570)
                                 Index Searches: 1
                                 Buffers: shared hit=20
                                 I/O Timings: read=0.000 write=0.000
         ->  Index Scan using project_to_security_attributes_pkey on public.project_to_security_attributes  (cost=0.29..2.90 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=100)
               Index Cond: (project_to_security_attributes.id = "ANY_subquery".id)
               Index Searches: 100
               Buffers: shared hit=299 read=1
               I/O Timings: read=0.033 write=0.000
Settings: effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4'

The Sort node is gone, id is no longer a Filter, and the Index Scan's actual rows drop from 383 to 100 because the scan now stops at LIMIT.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports