Plan flip on query for PG16 vs 17
SELECT
COUNT(*)
FROM
"merge_requests"
WHERE (NOT EXISTS (
SELECT
1
FROM
"banned_users"
WHERE (banned_users.user_id = (merge_requests.author_id + 0))))
AND "merge_requests"."target_project_id" = 16567575
Here is the plan for pg16:
Aggregate (cost=2256.86..2256.87 rows=1 width=8) (actual time=22.899..22.900 rows=1 loops=1)
Buffers: shared hit=37138
I/O Timings: read=0.000 write=0.000
-> Nested Loop Anti Join (cost=1.00..2255.90 rows=386 width=0) (actual time=0.045..22.424 rows=9271 loops=1)
Buffers: shared hit=37138
I/O Timings: read=0.000 write=0.000
-> Index Scan using index_on_merge_requests_for_latest_diffs on public.merge_requests (cost=0.57..1011.36 rows=773 width=4) (actual time=0.015..10.901 rows=9271 loops=1)
Index Cond: (merge_requests.target_project_id = 16567575)
Buffers: shared hit=9322
I/O Timings: read=0.000 write=0.000
-> Index Only Scan using banned_users_pkey on public.banned_users (cost=0.43..1.84 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=9271)
Index Cond: (banned_users.user_id = (merge_requests.author_id + 0))
Heap Fetches: 0
Buffers: shared hit=27816
I/O Timings: read=0.000 write=0.000
Here is the plan for pg17 (reconstructed from the JSON-ified plan in the spreadsheet):
Aggregate (cost=28112.54..28112.55 rows=1 width=8) (actual time=17.605..22.876 rows=1 loops=1)
Buffers: shared hit=36075
-> Gather (cost=28112.42..28112.53 rows=1 width=8) (actual time=17.347..22.871 rows=2 loops=1)
Buffers: shared hit=36075
-> Aggregate (cost=27112.42..27112.43 rows=1 width=8) (actual time=14.414..14.415 rows=1 loops=2)
Buffers: shared hit=36075
-> Nested Loop (cost=1.00..27102.10 rows=4131 width=0) (actual time=0.052..14.126 rows=4498 loops=2)
Buffers: shared hit=36075
Join Type: Anti
-> Index Scan using index_merge_requests_on_target_project_id_and_created_at_and_id (cost=0.57..19664.54 rows=8262 width=4) (actual time=0.021..7.890 rows=4498 loops=2)
Buffers: shared hit=9086
Index Cond: (merge_requests.target_project_id = 16567575)
-> Index Only Scan using banned_users_pkey (cost=0.43..0.99 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=8995)
Buffers: shared hit=26989
Index Cond: (banned_users.user_id = (merge_requests.author_id + 0))
Planning Time: 0.369 ms
Execution Time: 22.919 ms
The planner chooses a worse plan, but execution is roughly just as costly.
Edited by Jon Jenkins