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

Hi,

I can reproduce the performance getting much worse in 16, using the
provided SQL scripts. This is what I see:

14: 1551.363 ms
15: 1385.414 ms
16: 161571.400 ms
17: 156434.543 ms
18: 159095.001 ms

I'm attaching the explains for 15+16. I don't know what's causing it,
but I have a couple interesting observations.

1) If I disable parallel query, the timings change to

14: 3990.439 ms
15: 3518.453 ms
16: 3606.460 ms
17: 3591.039 ms
18: 3617.872 ms

So no regression in this case. It seems to be related to parallelism.

2) There seems to be an explosion of temporary files. We don't have that
in explain, but I queried pg_stat_database before/after the query, and
there's huge difference. Both start at

temp_files | 112
temp_bytes | 1942275280

so 112 files, ~2GB disk space. But after the query, 15 says

temp_files | 721
temp_bytes | 2755839184

while 16 has

temp_files | 2078995
temp_bytes | 70607906000

2M files and 70GB? Wow!

3) Indeed, before the query completes the pgsql_tmp directory has this:

63M pgsql_tmp3499395.0.fileset
63G pgsql_tmp3499395.1.fileset
95M pgsql_tmp3499395.2.fileset
95M pgsql_tmp3499395.3.fileset
127M pgsql_tmp3499395.4.fileset

So I guess that's one of the parallel hash joins doing something, and
consuming 63GB of disk space? I don't see anything suspicious in the
plan, but I assume parallel HJ may not report the relevant stats.

FWIW bumping up work_mem (to 64MB) solved this with the sample data.

I suspect this is going to be something like the hash join explosion,
where we just happen to add more and more batches. I don't have time to
investigate this more at the moment.

regards

--
Tomas Vondra