Skip to content

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