Skip to content

Fix timeout error while deleting approval rules from scan result policy

Sashi Kumar Kumaresan requested to merge sk/394720-fix-delete-scan-result-policies into master

What does this MR do and why?

Addresses #394720 (closed)

This change fixes db timeout exception occurred when approval rules associated to scan result policies are deleted. The fix would be use order_hint option in each_batch helper as mentioned in code docs:

order_hint - An optional column to append to the ORDER BY id clause to help the query planner. PostgreSQL might perform badly with a LIMIT 1 because the planner is guessing that scanning the index in ID order will come across the desired row in less time it will take the planner than using another index. The order_hint does not affect the search results. For example, ORDER BY id ASC, updated_at ASC means the same thing as ORDER BY id ASC.

DB Queries

Before

SELECT
    "approval_merge_request_rules"."id" 
FROM
    "approval_merge_request_rules" 
WHERE
    "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 4954 
ORDER BY
    "approval_merge_request_rules"."id" ASC LIMIT 1

Plan : console.postgre.ai

 Limit  (cost=0.56..1833.53 rows=1 width=8) (actual time=797874.308..797874.339 rows=1 loops=1)
   Buffers: shared hit=45662158 read=1244338 dirtied=15108 written=385
   I/O Timings: read=726258.153 write=15.711
   ->  Index Scan using approval_merge_request_rules_pkey on public.approval_merge_request_rules  (cost=0.56..4584236.00 rows=2501 width=8) (actual time=797874.304..797874.305 rows=1 loops=1)
         Filter: (approval_merge_request_rules.security_orchestration_policy_configuration_id = 4954)
         Rows Removed by Filter: 58927958
         Buffers: shared hit=45662158 read=1244338 dirtied=15108 written=385
         I/O Timings: read=726258.153 write=15.711

Time: 13.298 min
  - planning: 1.446 ms
  - execution: 13.298 min
    - I/O read: 12.104 min
    - I/O write: 15.711 ms

Shared buffers:
  - hits: 45662158 (~348.40 GiB) from the buffer pool
  - reads: 1244338 (~9.50 GiB) from the OS file cache, including disk I/O
  - dirtied: 15108 (~118.00 MiB)
  - writes: 385 (~3.00 MiB)

After

SELECT
    "approval_merge_request_rules"."id" 
FROM
    "approval_merge_request_rules" 
WHERE
    "approval_merge_request_rules"."security_orchestration_policy_configuration_id" = 4954 
ORDER BY
    "approval_merge_request_rules"."id" ASC,
    created_at ASC LIMIT 1

Plan: console.postgres.ai

Limit  (cost=3436.35..3436.35 rows=1 width=16) (actual time=78.186..78.189 rows=1 loops=1)
   Buffers: shared hit=9 read=116
   I/O Timings: read=75.138 write=0.000
   ->  Sort  (cost=3436.35..3442.60 rows=2501 width=16) (actual time=78.184..78.185 rows=1 loops=1)
         Sort Key: approval_merge_request_rules.id, approval_merge_request_rules.created_at
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=9 read=116
         I/O Timings: read=75.138 write=0.000
         ->  Index Scan using idx_approval_merge_request_rules_on_sec_orchestration_config_id on public.approval_merge_request_rules  (cost=0.56..3423.84 rows=2501 width=16) (actual time=5.109..77.418 rows=2697 loops=1)
               Index Cond: (approval_merge_request_rules.security_orchestration_policy_configuration_id = 4954)
               Buffers: shared hit=3 read=116
               I/O Timings: read=75.138 write=0.000

Time: 78.714 ms
  - planning: 0.483 ms
  - execution: 78.231 ms
    - I/O read: 75.138 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 9 (~72.00 KiB) from the buffer pool
  - reads: 116 (~928.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports