Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)

On 4/3/26 00:43, Tom Lane wrote:
> Tomas Vondra <tomas(at)vondra(dot)me> writes:
>> An interesting question is "What changed in PG16?" causing the query to
>> fail, when it worked OK on earlier versions.
>
> "git bisect" could be informative here. I agree with trying to
> minimize the query first, though --- else you may waste time
> going down blind alleys, as a result of planner changes changing
> the join order without affecting the critical executor behavior.
>

I did a bit of bisecting today (with the full query), and unsurprisingly
it started failing at:

commit 11c2d6fdf5af1aacec9ca2005543f1b0fc4cc364 (HEAD ->
hashjoin-explosion-bisect)
Author: Thomas Munro <tmunro(at)postgresql(dot)org>
Date: Fri Mar 31 11:01:51 2023 +1300

Parallel Hash Full Join.

Full and right outer joins were not supported in the initial
implementation of Parallel Hash Join because of deadlock hazards
(see discussion). Therefore FULL JOIN inhibited parallelism, as
the other join strategies can't do that in parallel either.

...

Although, it's a bit strange, AFAIK the query does not have any full
outer join. Also, for me it now fails like this:

Sat Apr 4 04:00:58 PM CEST 2026
ERROR: invalid DSA memory alloc request size 1811939328
CONTEXT: parallel worker
Sat Apr 4 04:02:04 PM CEST 2026

I believe it's the same issue (I still get the same tempfile explosion).

After a bit of trial-and-error I managed to reduce the query to a single
join:

SET parallel_setup_cost = 0;
SET cpu_tuple_cost = 1;
SET enable_nestloop = off;

EXPLAIN ANALYZE SELECT *
FROM attachments.folders
LEFT OUTER JOIN events.contributions
ON events.contributions.id = attachments.folders.contribution_id;

The trick is to force it to do a parallel hash join by adjusting the CPU
costs. I don't think it can be reduced even further, even just switching
to an inner join makes it work fine.

At this point I was suspecting the data distributions for the join
columns may be somewhat weird, causing issues for the hashjoin batching.
For events.contributions.id it's perfectly fine - it's entirely unique,
with each ID having 1 entry. Unsurprisingly, because it's the PK. But
for attachments.folders.contribution_id I see this:

SELECT contribution_id, count(*) FROM attachments.folders
GROUP BY contribution_id ORDER BY 2 DESC;

contribution_id | count
-----------------+--------
| 464515
5492978 | 67
4117499 | 42
4045045 | 41
...

So there's ~500k entries with NULL, that can't possibly match to
anything (right)? I assume we still add them to the hash, though.
Because if I explicitly filter them out, it starts working fine:

EXPLAIN ANALYZE SELECT *
FROM attachments.folders
LEFT OUTER JOIN events.contributions
ON events.contributions.id = attachments.folders.contribution_id
WHERE attachments.folders.contribution_id IT NOT NULL;
...
Planning Time: 0.192 ms
Execution Time: 670.950 ms

and when I invert the condition (to IS NULL), it stats failing pretty
much right away.

regards

--
Tomas Vondra