Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17

Scott Carey <scott(dot)carey(at)algonomy(dot)com> writes:
> I did not mention a few values that differ between the servers that
> reproduce this, like autovacuum tuning parameters and
> maintenance_work_men. adding settings to the explain gives a couple more,
> unlikely to be related to the problem:

> Settings: temp_buffers = '512MB', work_mem = '1000MB',
> effective_io_concurrency = '16', effective_cache_size = '150GB'

Of course your test case is controlling for work_mem, but
I wonder whether temp_buffers could affect this. I think
that those are only used for user-defined temp tables, not
the temp files a batched hashjoin creates, but maybe I'm
misremembering.

> While writing this, I decided to test out a few more vector extension test
> cases, and discovered something new and mind boggling: :
> On systems that reproduces the problem, if I create a new test database,
> then test the query in that database, the problem does not occur.

That is a very strong clue. Check for property differences (e.g.
with psql's "\l+" and "\drds") between the new test database and
the database where you see the problem.

regards, tom lane