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