Use InOperatorOptimization for delete_software_license_policies
What does this MR do and why?
Addresses #432749 (closed)
Query Plan
SELECT
*
FROM
(WITH RECURSIVE "array_cte" AS MATERIALIZED (SELECT
"scan_result_policies"."id"
FROM
"scan_result_policies"
WHERE
"scan_result_policies"."security_orchestration_policy_configuration_id" = 4261
AND "scan_result_policies"."project_id" =43110842),
"recursive_keyset_cte" AS ((SELECT
NULL::integer AS id,
array_cte_id_array,
software_license_policies_id_array,
0::bigint AS count
FROM
(SELECT
ARRAY_AGG("array_cte"."id") AS array_cte_id_array,
ARRAY_AGG("software_license_policies"."id") AS software_license_policies_id_array
FROM
(SELECT
"array_cte"."id"
FROM
array_cte) array_cte
LEFT JOIN
LATERAL (SELECT
"software_license_policies"."id" AS id
FROM
"software_license_policies"
WHERE
"software_license_policies"."project_id" =43110842
AND "software_license_policies"."scan_result_policy_id" = "array_cte"."id"
ORDER BY
"software_license_policies"."id" DESC LIMIT 1) software_license_policies
ON TRUE
WHERE
"software_license_policies"."id" IS NOT NULL) array_scope_lateral_query LIMIT 1)
UNION
ALL (SELECT
recursive_keyset_cte.software_license_policies_id_array[position],
array_cte_id_array,
recursive_keyset_cte.software_license_policies_id_array[:position_query.position-1]||next_cursor_values.id||recursive_keyset_cte.software_license_policies_id_array[position_query.position 1:],
recursive_keyset_cte.count 1
FROM
recursive_keyset_cte,
LATERAL (SELECT
id,
position
FROM
UNNEST(software_license_policies_id_array) WITH ORDINALITY AS u(id,
position)
WHERE
id IS NOT NULL
ORDER BY
1 DESC LIMIT 1) AS position_query,
LATERAL (SELECT
"record"."id"
FROM
(
VALUES
(NULL)) AS nulls
LEFT JOIN
(SELECT
"software_license_policies"."id" AS id
FROM
"software_license_policies"
WHERE
"software_license_policies"."project_id" =43110842
AND "software_license_policies"."scan_result_policy_id" = recursive_keyset_cte.array_cte_id_array[position]
AND ("software_license_policies"."id" < recursive_keyset_cte.software_license_policies_id_array[position])
ORDER BY
"software_license_policies"."id" DESC LIMIT 1) record
ON TRUE LIMIT 1) AS next_cursor_values )) SELECT
id
FROM
"recursive_keyset_cte" AS "software_license_policies"
WHERE
(
count <> 0
)) software_license_policies LIMIT 100 Limit (cost=1608.39..1611.66 rows=100 width=4) (actual time=1.310..33.920 rows=100 loops=1)
Buffers: shared hit=44248
I/O Timings: read=0.000 write=0.000
-> CTE Scan on recursive_keyset_cte software_license_policies (cost=1608.39..1610.66 rows=100 width=4) (actual time=1.309..33.900 rows=100 loops=1)
Filter: (software_license_policies.count <> 0)
Rows Removed by Filter: 1
Buffers: shared hit=44248
I/O Timings: read=0.000 write=0.000
CTE array_cte
-> Index Scan using index_scan_result_policies_on_position_in_configuration on public.scan_result_policies (cost=0.43..3.45 rows=1 width=8) (actual time=0.043..0.044 rows=1 loops=1)
Index Cond: ((scan_result_policies.security_orchestration_policy_configuration_id = 4261) AND (scan_result_policies.project_id = 43110842))
Buffers: shared hit=7
I/O Timings: read=0.000 write=0.000
CTE recursive_keyset_cte
-> Recursive Union (cost=15.65..1604.94 rows=101 width=76) (actual time=0.757..33.771 rows=101 loops=1)
Buffers: shared hit=44248
I/O Timings: read=0.000 write=0.000
-> Limit (cost=15.65..15.67 rows=1 width=76) (actual time=0.756..0.761 rows=1 loops=1)
Buffers: shared hit=448
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on array_scope_lateral_query (cost=15.65..15.67 rows=1 width=76) (actual time=0.755..0.760 rows=1 loops=1)
Buffers: shared hit=448
I/O Timings: read=0.000 write=0.000
-> Aggregate (cost=15.65..15.66 rows=1 width=64) (actual time=0.754..0.758 rows=1 loops=1)
Buffers: shared hit=448
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=15.60..15.64 rows=1 width=12) (actual time=0.743..0.748 rows=1 loops=1)
Buffers: shared hit=448
I/O Timings: read=0.000 write=0.000
-> CTE Scan on array_cte (cost=0.00..0.02 rows=1 width=8) (actual time=0.045..0.046 rows=1 loops=1)
Buffers: shared hit=7
I/O Timings: read=0.000 write=0.000
-> Subquery Scan on software_license_policies_1 (cost=15.60..15.61 rows=1 width=4) (actual time=0.696..0.699 rows=1 loops=1)
Filter: (software_license_policies_1.id IS NOT NULL)
Rows Removed by Filter: 0
Buffers: shared hit=441
I/O Timings: read=0.000 write=0.000
-> Limit (cost=15.60..15.60 rows=1 width=4) (actual time=0.694..0.696 rows=1 loops=1)
Buffers: shared hit=441
I/O Timings: read=0.000 write=0.000
-> Sort (cost=15.60..15.60 rows=1 width=4) (actual time=0.694..0.695 rows=1 loops=1)
Sort Key: software_license_policies_2.id DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=441
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_software_license_policies_unique_on_project_and_scan_policy on public.software_license_policies software_license_policies_2 (cost=0.42..15.59 rows=1 width=4) (actual time=0.022..0.606 rows=456 loops=1)
Index Cond: ((software_license_policies_2.project_id = 43110842) AND (software_license_policies_2.scan_result_policy_id = array_cte.id))
Buffers: shared hit=438
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=15.76..158.72 rows=10 width=76) (actual time=0.327..0.329 rows=1 loops=100)
Buffers: shared hit=43800
I/O Timings: read=0.000 write=0.000
-> Nested Loop (cost=0.15..1.95 rows=10 width=80) (actual time=0.005..0.006 rows=1 loops=100)
I/O Timings: read=0.000 write=0.000
-> WorkTable Scan on recursive_keyset_cte (cost=0.00..0.20 rows=10 width=72) (actual time=0.000..0.000 rows=1 loops=100)
I/O Timings: read=0.000 write=0.000
-> Limit (cost=0.15..0.16 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=100)
I/O Timings: read=0.000 write=0.000
-> Sort (cost=0.15..0.18 rows=10 width=12) (actual time=0.003..0.003 rows=1 loops=100)
Sort Key: u.id DESC
Sort Method: quicksort Memory: 25kB
I/O Timings: read=0.000 write=0.000
-> Function Scan on unnest u (cost=0.00..0.10 rows=10 width=12) (actual time=0.002..0.002 rows=1 loops=100)
Filter: (u.id IS NOT NULL)
Rows Removed by Filter: 0
I/O Timings: read=0.000 write=0.000
-> Limit (cost=15.60..15.64 rows=1 width=4) (actual time=0.321..0.321 rows=1 loops=100)
Buffers: shared hit=43800
I/O Timings: read=0.000 write=0.000
-> Nested Loop Left Join (cost=15.60..15.64 rows=1 width=4) (actual time=0.320..0.320 rows=1 loops=100)
Buffers: shared hit=43800
I/O Timings: read=0.000 write=0.000
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=100)
I/O Timings: read=0.000 write=0.000
-> Limit (cost=15.60..15.61 rows=1 width=4) (actual time=0.320..0.320 rows=1 loops=100)
Buffers: shared hit=43800
I/O Timings: read=0.000 write=0.000
-> Sort (cost=15.60..15.61 rows=1 width=4) (actual time=0.319..0.319 rows=1 loops=100)
Sort Key: software_license_policies_3.id DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=43800
I/O Timings: read=0.000 write=0.000
-> Index Scan using idx_software_license_policies_unique_on_project_and_scan_policy on public.software_license_policies software_license_policies_3 (cost=0.43..15.59 rows=1 width=4) (actual time=0.004..0.262 rows=406 loops=100)
Index Cond: ((software_license_policies_3.project_id = 43110842) AND (software_license_policies_3.scan_result_policy_id = (recursive_keyset_cte.array_cte_id_array)[u."position"]))
Filter: (software_license_policies_3.id < (recursive_keyset_cte.software_license_policies_id_array)[u."position"])
Rows Removed by Filter: 50
Buffers: shared hit=43800
I/O Timings: read=0.000 write=0.000
Time: 37.017 ms
- planning: 2.699 ms
- execution: 34.318 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 44248 (~345.70 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Sashi Kumar Kumaresan