On Wed, 1 Apr 2026 at 08:26, Tom Lane <[email protected]> wrote: > Some thoughts: > > * Does it repro without the "vector" extension? Seems unlikely that > that is related, but we're at the grasping-at-straws stage. > > * More grasping at straws: is this stock community Postgres, or > some vendor's modification (eg RDS or Aurora)? > > * It would be worth doing the EXPLAINs with the SETTINGS option, > just to make sure that there's not some non-default setting you > forgot to mention.
Also grasping at straws and wondering if it's related to L3 contention. Hash tables mostly always have very unpredictable memory access which the hardware prefetcher can't deal with. If useful cachelines are being evicted from L3 by other processes, then that'll mean more stalls waiting on RAM when probing the hash table. I tried to see if I could recreate this on a 64 physical core machine, and I can, but to nowhere near the same extent as what Scott showed. work_mem = 200MB drowley@amd3990x:~$ pgbench -n -f bench.sql -T 10 -M prepared postgres | grep latency latency average = 63.556 ms drowley@amd3990x:~$ pgbench -n -f bench.sql -T 10 -M prepared -c 10 -j 10 postgres | grep latency latency average = 66.002 ms drowley@amd3990x:~$ pgbench -n -f bench.sql -T 10 -M prepared -c 30 -j 30 postgres | grep latency latency average = 83.188 ms drowley@amd3990x:~$ pgbench -n -f bench.sql -T 10 -M prepared -c 64 -j 64 postgres | grep latency latency average = 168.449 ms 64 thread is 2.65x slower than 1. work_mem = 10MB drowley@amd3990x:~$ pgbench -n -f bench.sql -T 10 -M prepared postgres | grep latency latency average = 95.239 ms drowley@amd3990x:~$ pgbench -n -f bench.sql -T 10 -M prepared -c 10 -j 10 postgres | grep latency latency average = 101.870 ms drowley@amd3990x:~$ pgbench -n -f bench.sql -T 10 -M prepared -c 30 -j 30 postgres | grep latency latency average = 114.402 ms drowley@amd3990x:~$ pgbench -n -f bench.sql -T 10 -M prepared -c 64 -j 64 postgres | grep latency latency average = 161.147 ms 64 thread is 1.69x slower than 1. So, the slowdown is bigger when the system is under more memory pressure. I'm curious to know how consistent the run times are and if the json_agg() query can be just as slow as the array_agg() one. Could it be that the json_agg() version was just run at a time the server wasn't as busy with other things... ? David
