Обсуждение: Potential bug introduced in PG17 with query parallelization - plan flip

Hello!

My organization GitLab is planning on upgrading to PG17 in the near
future, and we performed a series of automated regression tests
against a set of sample queries. Most of the plans looked identical or
better between 16 and 17, but one that stood out was for the following
query:

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

(I can provide schemata for the relevant tables if need be.)

In PG16, we the planner suggests a simple index scan:

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


However, on 17, a "more expensive" plan is proposed that does a gather
between workers, but largely performs the exact same:

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

(Sorry for the formatting differences, I had to rehydrate this one from JSON)

I don't know enough about planner internals to specifically track this
code, but I did see that this patch has been added to 17 that deals
with parallelizing:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e08d74ca1

Since both queries perform roughly the same in terms of buffers read
and execution time (both DBs have the exact or near-exact same heap
snapshot), I wonder if there's an issue with the cost calculation
method for the more "expensive" plan?

-- 
Jon Jenkins
Senior Backend Engineer | GitLab

On Fri, 31 Oct 2025 at 10:40, Jon Jenkins <jjenkins@gitlab.com> wrote:
>          ->  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)

> However, on 17, a "more expensive" plan is proposed that does a gather
> between workers, but largely performs the exact same:

>         ->  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)

In the two fragments above, you'll notice the row estimates are quite
different. I suspect this is the reason v17 went with the parallel
plan. What could cause this would be just a difference in sampled rows
from ANALYZE. If you're still in a testing environment, do you see the
plan change back to the non-parallel version if you run ANALYZE on the
merge_requests table and then run the query again? Try it a few times.
You might find that the rows that are sampled sometimes leads to
better or worse estimates.  It's a bit of a fact of life if you have
large skewed data as it's not practical to remember how many of each
and every value exist in the statistics. You could increase the number
by increasing the statistics targets for that column, per [1]. That
might still not be enough, however. If you can't go high enough, you
might be able to do something by setting it to not gather stats for
this column and then tuning n_distinct for the column to some value
that suits. The drawback there is that doing that is fraught with the
possible difficulties of having the planner always thinking there's
the same number of rows for every target_project_id. You might end up
doing an Index Scan when a Seq Scan is better (which it could be if
you had a particular target_project_id that dominated the table).

> I don't know enough about planner internals to specifically track this
> code, but I did see that this patch has been added to 17 that deals
> with parallelizing:
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e08d74ca1

This isn't related to what you're seeing.

David

[1] https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-SET-STATISTICS