Thread
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Disk-based Hash Aggregation.
- 1f39bce02154 13.0 cited
-
Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Scott Carey <scott.carey@algonomy.com> — 2026-03-31T09:28:59Z
Problem Summary: A simple aggregate using array_agg goes significantly faster and faster the *smaller* the (work_mem * hash_mem_multiplier), with the same simple query plan: HashAggregate over a sequential scan. Changing to a simple aggregate, such as max() does not have this behavior and is always fast. Switching to another aggregate that grows in size for each element, such as json_agg or string_agg also does not have this behavior. If I add an order by clause inside array_agg, performance significantly improves as it changes from a HashAggregate of a sequential scan to a GroupAggregate over a sort over a sequential scan. Something seems specifically broken with array_agg + HashAggregate. These queries are anywhere from 10x to 1000x slower on Postgres 17.9 than they were on Postgres 12.19 on production data. Some of our OLTP queries have gone from minutes to 6 hours to complete. I do not know if this happens on Postgres 18, I can confirm it also happens on Postgres 16.8. I do not know about 13 through 15. Below is a simplified reproduction with a test table below: show server_version; server_version ---------------- 17.9 create table array_agg_test(product_id bigint not null, region_id bigint not null, available boolean not null); insert into array_agg_test (product_id, region_id, available) SELECT generate_series(1, 50000) as product_id, (ARRAY[1,2,3,4])[floor(random()*4)+1] as region_id, true as available; insert into array_agg_test (product_id, region_id, available) SELECT generate_series(1, 50000) as product_id, (ARRAY[11,12,13,14])[floor(random()*4)+1] as region_id, true as available; insert into array_agg_test (product_id, region_id, available) SELECT generate_series(1, 50000) as product_id, (ARRAY[111,112,113,114])[floor(random()*4)+1] as region_id, true as available; insert into array_agg_test (product_id, region_id, available) SELECT generate_series(1, 50000) as product_id, (ARRAY[1111,1112,1113,1114])[floor(random()*4)+1] as region_id, true as available; vacuum analyze array_agg_test; We now have a table with 200000 rows, 50000 distinct product_id with 4 rows each, with a distinct region_id. It is simple enough that default statistics are fine here; we want to trigger HashAgg over SeqScan anyway. Other query plans that avoid HashAggregate don't have the issue -- index scans, group aggregate are fine. set hash_mem_multiplier = 2; set work_mem = "100MB"; explain (analyze, buffers) select product_id, array_agg(region_id) from array_agg_test group by product_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=4274.00..4771.49 rows=49749 width=40) (actual time=4628.278..4643.765 rows=50000 loops=1) Group Key: product_id Batches: 1 Memory Usage: 55649kB Buffers: shared hit=1274 -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.030..16.694 rows=200000 loops=1) Buffers: shared hit=1274 Planning Time: 0.067 ms Execution Time: 4648.698 ms set work_mem = "20MB"; explain (analyze, buffers) select product_id, array_agg(region_id) from array_agg_test group by product_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=16086.50..18537.11 rows=49749 width=40) (actual time=2568.837..2672.140 rows=50000 loops=1) Group Key: product_id Planned Partitions: 4 Batches: 5 Memory Usage: 40954kB Disk Usage: 3352kB Buffers: shared hit=1274, temp read=243 written=594 -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.013..15.266 rows=200000 loops=1) Buffers: shared hit=1274 Planning Time: 0.051 ms Execution Time: 2674.329 ms set work_mem = "10MB"; explain (analyze, buffers) select product_id, array_agg(region_id) from array_agg_test group by product_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=16086.50..18537.11 rows=49749 width=40) (actual time=635.816..888.167 rows=50000 loops=1) Group Key: product_id Planned Partitions: 8 Batches: 9 Memory Usage: 20474kB Disk Usage: 7272kB Buffers: shared hit=1274, temp read=566 written=1388 -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.018..12.689 rows=200000 loops=1) Buffers: shared hit=1274 Planning Time: 0.057 ms Execution Time: 890.987 ms set work_mem = "5MB"; explain (analyze, buffers) select product_id, array_agg(region_id) from array_agg_test group by product_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=16086.50..18537.11 rows=49749 width=40) (actual time=172.948..341.847 rows=50000 loops=1) Group Key: product_id Planned Partitions: 16 Batches: 17 Memory Usage: 10234kB Disk Usage: 7080kB Buffers: shared hit=1274, temp read=731 written=1553 -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.010..11.715 rows=200000 loops=1) Buffers: shared hit=1274 Planning Time: 0.064 ms Execution Time: 344.248 ms set work_mem = "1MB"; explain (analyze, buffers) select product_id, array_agg(region_id) from array_agg_test group by product_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=16086.50..18537.11 rows=49749 width=40) (actual time=56.102..144.350 rows=50000 loops=1) Group Key: product_id Planned Partitions: 64 Batches: 65 Memory Usage: 2050kB Disk Usage: 12200kB Buffers: shared hit=1274, temp read=892 written=2374 -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.017..12.346 rows=200000 loops=1) Buffers: shared hit=1274 Planning Time: 0.053 ms Execution Time: 147.254 ms Below this work_mem size it chooses a GroupAggregate and sorted scan set hash_mem_multiplier = 20; explain (analyze, buffers) select product_id, array_agg(region_id) from array_agg_test group by product_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=16086.50..18537.11 rows=49749 width=40) (actual time=654.729..890.816 rows=50000 loops=1) Group Key: product_id Planned Partitions: 8 Batches: 9 Memory Usage: 20480kB Disk Usage: 7264kB Buffers: shared read=1274, temp read=561 written=1384 -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.044..18.521 rows=200000 loops=1) Buffers: shared read=1274 Planning Time: 0.067 ms Execution Time: 893.208 ms Note the performance is a function of hash_mem_multiplier * work_mem, as it seems to be related to the number of Batches. The more batches the faster it goes. Below, note json_agg does not have this problem: set hash_mem_multiplier = 2; set work_mem = "500MB"; explain (analyze, buffers) select product_id, json_agg(region_id) from array_agg_test group by product_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=4274.00..4895.86 rows=49749 width=40) (actual time=110.975..122.781 rows=50000 loops=1) Group Key: product_id Batches: 1 Memory Usage: 67089kB Buffers: shared read=1274 -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.034..17.659 rows=200000 loops=1) Buffers: shared read=1274 Planning: Buffers: shared hit=10 Planning Time: 0.054 ms Execution Time: 124.929 ms and adding a useless 'order by' clause inside array_agg triggers a GroupAggregate which is ok as well: explain (analyze, buffers) select product_id, array_agg(region_id order by available) from array_agg_test group by product_id; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=20883.64..22881.13 rows=49749 width=40) (actual time=40.013..73.385 rows=50000 loops=1) Group Key: product_id Buffers: shared hit=5 read=1274 -> Sort (cost=20883.64..21383.64 rows=200000 width=17) (actual time=40.000..46.090 rows=200000 loops=1) Sort Key: product_id, available Sort Method: quicksort Memory: 13957kB Buffers: shared hit=5 read=1274 -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=17) (actual time=0.033..17.911 rows=200000 loops=1) Buffers: shared read=1274 Planning: Buffers: shared hit=7 Planning Time: 0.063 ms Execution Time: 74.823 ms The "missing time" here is in between the end of the sequential scan, which takes < 20ms, and the 'start' of the GroupAggregate, which in the worst case example here is several seconds later. I am fairly stuck here. I am looking at modifying client code to use json_agg instead of array_agg where possible as a work-around, but ideally that would not be needed, array_agg shouldn't be significantly different. A secondary observation, related but not the issue at hand: The row size estimate for the aggregate is always `width=40` here, no matter how large the resulting arrays are expected to be. In extreme cases this can lead to hash memory consumption that is far larger than predicted. On postgres 12 a several years ago, I once saw a query with work_mem 1000MB use up 290GB and crash the server as it was running a complex json_agg across a large number of values per bucket and the query planner did not expect to store json data so large per output row of the aggregate. Disk backed aggregates now prevent the crash, but it would probably help the query planner if array_agg (and other accumulating aggregators like json_agg) could provide an output size estimate that is a function of the number of expected elements aggregated over. Thanks in advance for any help here! Scott Carey -
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
David Rowley <dgrowleyml@gmail.com> — 2026-03-31T12:03:13Z
On Tue, 31 Mar 2026 at 22:29, Scott Carey <scott.carey@algonomy.com> wrote: > A simple aggregate using array_agg goes significantly faster and faster the smaller the (work_mem * hash_mem_multiplier), with the same simple query plan: HashAggregate over a sequential scan. Changing to a simple aggregate, such as max() does not have this behavior and is always fast. Switching to another aggregate that grows in size for each element, such as json_agg or string_agg also does not have this behavior. If I add an order by clause inside array_agg, performance significantly improves as it changes from a HashAggregate of a sequential scan to a GroupAggregate over a sort over a sequential scan. Something seems specifically broken with array_agg + HashAggregate. > > These queries are anywhere from 10x to 1000x slower on Postgres 17.9 than they were on Postgres 12.19 on production data. Some of our OLTP queries have gone from minutes to 6 hours to complete. I do not know if this happens on Postgres 18, I can confirm it also happens on Postgres 16.8. I do not know about 13 through 15. I tried and failed to recreate this locally on 17.9. For me the json_agg query is slower than array_agg(). I tried making the table 10x bigger and still don't see the same issue. The one with more work_mem and fewer batches is always faster for me. Is the machine under a lot of memory pressure and swapping pages to disk? Maybe you need to consider running a lower work_mem setting. How much RAM is installed in this machine? > set hash_mem_multiplier = 2; > set work_mem = "100MB"; > > explain (analyze, buffers) select product_id, array_agg(region_id) from array_agg_test group by product_id; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=4274.00..4771.49 rows=49749 width=40) (actual time=4628.278..4643.765 rows=50000 loops=1) > Group Key: product_id > Batches: 1 Memory Usage: 55649kB > Buffers: shared hit=1274 > -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.030..16.694 rows=200000 loops=1) > Buffers: shared hit=1274 > Planning Time: 0.067 ms > Execution Time: 4648.698 ms > Below, note json_agg does not have this problem: > > set hash_mem_multiplier = 2; > set work_mem = "500MB"; > > explain (analyze, buffers) select product_id, json_agg(region_id) from array_agg_test group by product_id; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=4274.00..4895.86 rows=49749 width=40) (actual time=110.975..122.781 rows=50000 loops=1) > Group Key: product_id > Batches: 1 Memory Usage: 67089kB > Buffers: shared read=1274 > -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 width=16) (actual time=0.034..17.659 rows=200000 loops=1) > Buffers: shared read=1274 > Planning: > Buffers: shared hit=10 > Planning Time: 0.054 ms > Execution Time: 124.929 ms What changed here apart from the aggregate function? Why are the buffers being read on this run and not the previous? Same machine? Was there a restart? json_agg allocates slightly more memory per agg state than array_agg. You can see that in the reported Hash Aggregate memory usage and I expect the actual transition function call between array_agg() and json_agg() not to differ very much in cost, so it very much feels like something else is going on here. David
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Scott Carey <scott.carey@algonomy.com> — 2026-03-31T17:55:42Z
On Tue, Mar 31, 2026 at 5:03 AM David Rowley <dgrowleyml@gmail.com> wrote: > > I tried and failed to recreate this locally on 17.9. For me the > json_agg query is slower than array_agg(). I tried making the table > 10x bigger and still don't see the same issue. The one with more > work_mem and fewer batches is always faster for me. > > Is the machine under a lot of memory pressure and swapping pages to > disk? Maybe you need to consider running a lower work_mem setting. How > much RAM is installed in this machine? > The machine has 768GB of RAM and about 35% CPU used at the time of these tests. It is AlmaLinux 9, with 50GB shared_buffers. OS available memory is > 600GB (filled with pagecache). The system is under heavy load, with many large sequential scans on 1GB to 80GB tables with OLAP queries and large batch updates at any given time. The system RAM buffers disk access relatively well, there is a constant stream of 100MB/sec to 200MB/sec from disk with bursts to 2000MB/sec off disk (NVMe RAID) from time to time but iowait is generally low (0.2%). The problem reproduces on my Ubuntu 25.10 laptop at idle with a near empty db with 32MB shared_buffers. It also reproduces on the read-only streaming standby server which is extremely idle and swimming in just as much RAM.. > > > set hash_mem_multiplier = 2; > > set work_mem = "100MB"; > > > > explain (analyze, buffers) select product_id, array_agg(region_id) from > array_agg_test group by product_id; > > QUERY PLAN > > > ----------------------------------------------------------------------------------------------------------------------------- > > HashAggregate (cost=4274.00..4771.49 rows=49749 width=40) (actual > time=4628.278..4643.765 rows=50000 loops=1) > > Group Key: product_id > > Batches: 1 Memory Usage: 55649kB > > Buffers: shared hit=1274 > > -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 > width=16) (actual time=0.030..16.694 rows=200000 loops=1) > > Buffers: shared hit=1274 > > Planning Time: 0.067 ms > > Execution Time: 4648.698 ms > > > Below, note json_agg does not have this problem: > > > > set hash_mem_multiplier = 2; > > set work_mem = "500MB"; > > > > explain (analyze, buffers) select product_id, json_agg(region_id) from > array_agg_test group by product_id; > > QUERY PLAN > > > ----------------------------------------------------------------------------------------------------------------------------- > > HashAggregate (cost=4274.00..4895.86 rows=49749 width=40) (actual > time=110.975..122.781 rows=50000 loops=1) > > Group Key: product_id > > Batches: 1 Memory Usage: 67089kB > > Buffers: shared read=1274 > > -> Seq Scan on array_agg_test (cost=0.00..3274.00 rows=200000 > width=16) (actual time=0.034..17.659 rows=200000 loops=1) > > Buffers: shared read=1274 > > Planning: > > Buffers: shared hit=10 > > Planning Time: 0.054 ms > > Execution Time: 124.929 ms > > What changed here apart from the aggregate function? Why are the > buffers being read on this run and not the previous? Same machine? Was > there a restart? > I waited a few minutes, it is a busy server. I can run the example back to back witn no significant change other than the buffer hits going up, the pages are in OS page cache if I wait a bit, and even if they are on disk its nVME SSD raid 10 and the table is 'tiny' for this server. > > json_agg allocates slightly more memory per agg state than array_agg. > You can see that in the reported Hash Aggregate memory usage and I > expect the actual transition function call between array_agg() and > json_agg() not to differ very much in cost, so it very much feels like > something else is going on here. > There are some other differences from a default config. The database was created with `initdb -E UTF-8` Other non-default values in postgresql.conf that might be related: max_files_per_process = 4000 (we have some partitioned tables with a lot of partitions) effective_io_concurrency = 16 lc_messages = 'en_US.UTF-8' lc_monetary = 'en_US.UTF-8' lc_numeric = 'en_US.UTF-8' lc_time = 'en_US.UTF-8' default_text_search_config = 'pg_catalog.english' \l+ shows encoding UTF8, Locale Provider libc, Collate en_US.UTF-8 and Ctype en_US.UTF-8 I don't know what other differences there could be, other than OS. This reproduces for me on Linux with the above on a RHEL 9 clone (pg 17) or with Ubuntu 25.10 (pg 16) so I suspect it is not too picky about the distro used. -Scott > > David >
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Scott Carey <scott.carey@algonomy.com> — 2026-03-31T18:06:36Z
On Tue, Mar 31, 2026 at 10:55 AM Scott Carey <scott.carey@algonomy.com> wrote: > > On Tue, Mar 31, 2026 at 5:03 AM David Rowley <dgrowleyml@gmail.com> wrote: > >> >> I tried and failed to recreate this locally on 17.9. For me the >> json_agg query is slower than array_agg(). I tried making the table >> 10x bigger and still don't see the same issue. The one with more >> work_mem and fewer batches is always faster for me. > > > I don't know what other differences there could be, other than OS. This > reproduces for me on Linux with the above on a RHEL 9 clone (pg 17) or with > Ubuntu 25.10 (pg 16) so I suspect it is not too picky about the distro used. > > -Scott > I thought of two other possible differences: extensions and JIT: List of installed extensions Name | Version | Default version | Schema | Description --------------------+---------+-----------------+------------+----------------------------------------------------------- pg_stat_statements | 1.11 | 1.11 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language vector | 0.8.2 | 0.8.2 | public | vector data type and ivfflat and hnsw access methods show jit; jit ----- on select pg_jit_available(); pg_jit_available ------------------ t > > >> >> David >> > -
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Tom Lane <tgl@sss.pgh.pa.us> — 2026-03-31T19:26:09Z
Scott Carey <scott.carey@algonomy.com> writes: >> On Tue, Mar 31, 2026 at 5:03 AM David Rowley <dgrowleyml@gmail.com> wrote: >>> I tried and failed to recreate this locally on 17.9. For me the >>> json_agg query is slower than array_agg(). I tried making the table >>> 10x bigger and still don't see the same issue. The one with more >>> work_mem and fewer batches is always faster for me. >> I don't know what other differences there could be, other than OS. This >> reproduces for me on Linux with the above on a RHEL 9 clone (pg 17) or with >> Ubuntu 25.10 (pg 16) so I suspect it is not too picky about the distro used. Like David, I can't reproduce the described behavior. I tried on RHEL8/x86_64 and on macOS/M4, and got runtimes that barely vary across different work_mem settings, all sub-100ms. It should be noted that I tested v17 branch tip not precisely 17.9 --- but there's nothing in the commit log to suggest that we changed v17's behavior since February. One thing I find interesting is that your results show significantly more memory consumption as well as runtime. I had to add a run with work_mem = "200MB" to get the no-batching behavior you show at work_mem = "100MB", and then my results look like $ egrep 'Exec|Batches' v17.out Batches: 1 Memory Usage: 17937kB Execution Time: 62.494 ms Planned Partitions: 4 Batches: 5 Memory Usage: 9009kB Disk Usage: 3744kB Execution Time: 80.044 ms Planned Partitions: 16 Batches: 17 Memory Usage: 2385kB Disk Usage: 7112kB Execution Time: 93.572 ms Planned Partitions: 32 Batches: 33 Memory Usage: 1393kB Disk Usage: 14088kB Execution Time: 97.021 ms Planned Partitions: 64 Batches: 65 Memory Usage: 1089kB Disk Usage: 12200kB Execution Time: 98.887 ms Execution Time: 120.179 ms Planned Partitions: 32 Batches: 33 Memory Usage: 1073kB Disk Usage: 14088kB Execution Time: 98.609 ms Batches: 1 Memory Usage: 67089kB Execution Time: 110.035 ms Execution Time: 82.040 ms Your memory-usage numbers are integer multiples of mine. That makes little sense either. It seems like the planner is choosing the same plans for me as for you, other than having a higher cutoff for when not to select batching. So this is an executor issue not a planner issue. 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. regards, tom lane
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
David Rowley <dgrowleyml@gmail.com> — 2026-03-31T22:48:55Z
On Wed, 1 Apr 2026 at 08:26, Tom Lane <tgl@sss.pgh.pa.us> 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
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Scott Carey <scott.carey@algonomy.com> — 2026-04-01T07:04:49Z
On Tue, Mar 31, 2026 at 12:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Scott Carey <scott.carey@algonomy.com> writes: > >> On Tue, Mar 31, 2026 at 5:03 AM David Rowley <dgrowleyml@gmail.com> > wrote: > >>> I tried and failed to recreate this locally on 17.9. For me the > >>> json_agg query is slower than array_agg(). I tried making the table > >>> 10x bigger and still don't see the same issue. The one with more > >>> work_mem and fewer batches is always faster for me. > > >> I don't know what other differences there could be, other than OS. This > >> reproduces for me on Linux with the above on a RHEL 9 clone (pg 17) or > with > >> Ubuntu 25.10 (pg 16) so I suspect it is not too picky about the distro > used. > > Like David, I can't reproduce the described behavior. I tried on > RHEL8/x86_64 and on macOS/M4, and got runtimes that barely vary > across different work_mem settings, all sub-100ms. It should be > noted that I tested v17 branch tip not precisely 17.9 --- but there's > nothing in the commit log to suggest that we changed v17's behavior > since February. > > One thing I find interesting is that your results show significantly > more memory consumption as well as runtime. I had to add a run with > work_mem = "200MB" to get the no-batching behavior you show at > work_mem = "100MB", and then my results look like > > The memory difference is strange. I now have 6 systems that I have tested this on. One of them behaves just like yours above, with the same memory usage and appropriate performance. 5 batches and 9009kB at work_mem = "100MB"; The other 5 all misbehave and have ~ 50x worse performance when there is only one batch. (work_mem 1000MB). These use a little bit over 3x the memory for the single batch. > Some thoughts: > > * Does it repro without the "vector" extension? Seems unlikely that > that is related, but we're at the grasping-at-straws stage. > > Although I cannot remove the vector extension safely in production, I tried adding the extension to the system that does not reproduce the problem, and that did not trigger it. > * More grasping at straws: is this stock community Postgres, or > some vendor's modification (eg RDS or Aurora)? > This is from the pgdg repo for RHEL 9, from the postgresql.org website. > > * 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. > 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' 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. e.g. create database test; \c test .... run all the commands in my first email In the 'test' database everything is fine. Queries are fast, memory use is the same as yours. If I go back to the production database, the problem occurs again. One thing in common for the systems with the problem is that they had the pgvector extension installed on them for a while, and have gone through some pg_update cycles. I have no idea where to go from here on identifying why one database would behave like this but not the other -- on the same posrgres instance. This _could_ still be the pgvector extension, or at least something to do with using it through a pg_upgrade. The extension upgrade was executed after pg_update, but maybe something is wrong with that for this extension. Before discovering the above, my next plan was to set up linux perf and capture some OS level profiling on one of the near idle read-only standbys that show the problem, to see if there is something we can see there. I haven't investigated how to get decent stacks from that, I assume installing some debug packages from the repo would enable that but I have done no research and haven't attempted to use `perf` on postgres before. > > regards, tom lane >
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Scott Carey <scott.carey@algonomy.com> — 2026-04-01T07:18:22Z
On Tue, Mar 31, 2026 at 3:49 PM David Rowley <dgrowleyml@gmail.com> wrote: > On Wed, 1 Apr 2026 at 08:26, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > 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 can reproduce it on some near idle systems as well as the busy one. Queries are sometimes slowed by 10% to 30% on the busy system vs the near idle readonly streaming replica. Cache contention and memory contention can certainly slow hash access. And although we often model a hash as O(1) access, there is no such thing as true O(1) performance scaling for random memory access on today's hardware. The difference between the speed of accessing something entirely in L1/L2 cache vs something mostly in RAM is pretty huge. > > 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... ? > The run times are consistent enough (within a 15% range most of the time on the busy server), yet we are talking about a 50x performance difference, dwarfing the time variation. I have run each of these tests dozens of times now, back-to-back or with a delay, and this is extremely consistent no matter the timing. On the low load servers or laptop, it is even more consistent. The only time it is 'unexpectedly fast' is if I do something that triggers a different query plan, like one that uses sort + group aggregate or one that does an index scan. Every time it does the HashAggregate over the sequential scan it reproduces. -Scott > David >
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
David Rowley <dgrowleyml@gmail.com> — 2026-04-01T11:12:18Z
On Wed, 1 Apr 2026 at 20:18, Scott Carey <scott.carey@algonomy.com> wrote: > The run times are consistent enough (within a 15% range most of the time on the busy server), yet we are talking about a 50x performance difference, dwarfing the time variation. If you're able to install the debug symbols and run perf top or perf record and send us the perf report for one of the problem machines, that might yield something interesting. David
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-01T13:44:41Z
Scott Carey <scott.carey@algonomy.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
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Scott Carey <scott.carey@algonomy.com> — 2026-04-01T17:50:53Z
On Wed, Apr 1, 2026 at 6:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > Scott Carey <scott.carey@algonomy.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. > > modifying temp_buffers does not affect it. The 5 systems reproducing the problem have various values for the settings above, some unset. > > 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. > > It is a strong clue that I don't know how to leverage. \l+ has no differences other than an access privilege. \drds is new to me, but it reports "did not find any settings" for both the database with the problem and tne new test one without. At this point, I wonder if there is some residual strangeness since the reproducing examples are all 'old' databases that have gone through many pg_upgrades over the years. They also 'leapt' from version 12 to 17. I suppose I could try a brand new test case on a v12 system (assuming I can find a yum repo that still has that or a system that still has the packages), then upgrade to v17 and see if that reproduces the problem. I will probably attempt adding debug symbols and linux 'perf' first. regards, tom lane > -
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Scott Carey <scott.carey@algonomy.com> — 2026-04-02T07:23:43Z
On Wed, Apr 1, 2026 at 6:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > 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. > > I have discovered the root cause. This database is old. It pre-dates Postgres 8.4 which introduced array_agg. Apparently, in some version prior to 8.4 array_agg was added as a user function, defined as below for bigint: create AGGREGATE array_agg( BASETYPE = bigint, SFUNC = array_append, STYPE = bigint[], INITCOND = '{}' ); So if you create a test database and run the previous test, performance will be fine and the query will be fast. Then run: create AGGREGATE array_agg(BASETYPE = bigint, SFUNC = array_append,STYPE = bigint[], INITCOND = '{}'); It will be slow and reproduce this behavior. This leaves a few open questions: Why would this run so much more slowly after updating from postgres 12 to 17? It is a user defined aggregate, although maybe not as optimized as the intrinsic one it shouldn't behave this way. If instead I create the same thing with a different name: "array_agg_alt" , the performance of that aggregate is awful too when combined with HashAggregate query plans. So this is not due to name aliasing. How many other user defined aggregates does this affect? Would it affect simple ones like an alternate for "min" or only those that grow in size and accumulate data? It looks like I can fix this with a simple "drop aggregate array_agg(bigint)", as the built-in function remains after removing this. But I am left wondering how many user defined aggregates have a similar problem. A bit more history / info in case someone stumbles upon this: The fact that the problem did not reproduce on a new / fresh database on the same postgres instance that otherwise had the problem was a huge clue. I just had to think more about all the ways the databases differ. Server settings, hardware, background activity -- these could all be ruled out now as they were the same for both. My first thoughts were related to the age of the database and all of the upgrade cycles it had been through, and the size of the database. There are tens of thousands of tables in the production db (many partition tables). But one of the systems reproducing the problem identically (my laptop) had the same schema, but almost no partition tables and only small test data. So I assumed the pure db size was not to blame. The fresh database was an almost empty schema, the one with the problem was large, old, and crufty. Schemas for this system are built from scratch regularly for testing by running a sequence of schema update files -- almost 2000 of them, essentially a history of all schema updates since the birth of the database. So I decided to do a binary search 'bisect' on these 2000 update files, halving the number of candidate changes with each iteration. It turned out one of the earliest schema changes was the one to blame, adding a user defined array_agg. This was dated from 2008, before Postgres had its own built-in array_agg function. This did not cause any problems until the upgrade from Postgres 12 to 17 triggered this behavior. One of my test systems on Postgres 16 also reproduces the problem, so I assume this was introduced between version 13 and 16 inclusive. -
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-02T17:38:18Z
Scott Carey <scott.carey@algonomy.com> writes: > I have discovered the root cause. > This database is old. It pre-dates Postgres 8.4 which introduced > array_agg. Apparently, in some version prior to 8.4 array_agg was added > as a user function, defined as below for bigint: > create AGGREGATE array_agg( > BASETYPE = bigint, > SFUNC = array_append, > STYPE = bigint[], > INITCOND = '{}' > ); > So if you create a test database and run the previous test, performance > will be fine and the query will be fast. Then run: > create AGGREGATE array_agg(BASETYPE = bigint, SFUNC = array_append,STYPE = > bigint[], INITCOND = '{}'); > It will be slow and reproduce this behavior. Thank you for running that to ground! I confirm your results that v13 and up are far slower for this example than v12 was. > Why would this run so much more slowly after updating from postgres 12 to > 17? It is a user defined aggregate, although maybe not as optimized as > the intrinsic one it shouldn't behave this way. I did some bisecting using the attached simplified test case, and found that the query execution time jumps from circa 60ms to circa 7500ms here: 1f39bce021540fde00990af55b4432c55ef4b3c7 is the first bad commit commit 1f39bce021540fde00990af55b4432c55ef4b3c7 Author: Jeff Davis <jdavis@postgresql.org> Date: Wed Mar 18 15:42:02 2020 -0700 Disk-based Hash Aggregation. While performing hash aggregation, track memory usage when adding new groups to a hash table. If the memory usage exceeds work_mem, enter "spill mode". (Times quoted are on a Mac M4 Pro, but in assert-enabled builds so maybe not directly comparable to production.) I'm bemused as to why: the test case has work_mem set high enough that we shouldn't be triggering spill mode, so why did this change affect it at all? regards, tom lane -
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Scott Carey <scott.carey@algonomy.com> — 2026-04-02T19:08:13Z
On Thu, Apr 2, 2026, 10:38 Tom Lane <tgl@sss.pgh.pa.us> wrote: > > I did some bisecting using the attached simplified test case, and found > that the query execution time jumps from circa 60ms to circa 7500ms here: > > 1f39bce021540fde00990af55b4432c55ef4b3c7 is the first bad commit > commit 1f39bce021540fde00990af55b4432c55ef4b3c7 > Author: Jeff Davis <jdavis@postgresql.org> > Date: Wed Mar 18 15:42:02 2020 -0700 > > Disk-based Hash Aggregation. > > While performing hash aggregation, track memory usage when adding new > groups to a hash table. If the memory usage exceeds work_mem, enter > "spill mode". > > (Times quoted are on a Mac M4 Pro, but in assert-enabled builds so > maybe not directly comparable to production.) > > I'm bemused as to why: the test case has work_mem set high enough that > we shouldn't be triggering spill mode, so why did this change affect > it at all? > Even stranger, the more spills induced via smaller work_mem the faster it goes. This suggests something getting more expensive as the hash table gets larger. Significantly more, like O(n^2) or worse. I wonder if it is the size of the hash table itself (entry count) or the size of the entries? Does a table with one row matching each entry have the problem or only when the hash bucket is hit multiple times and values aggregated? Why is the reported size used so much larger with the custom function? I have some experiments in mind that could answer some of these. Tracking hash table memory usage dynamically can be tricky. I would imagine that user defined aggregates make it more difficult. > regards, tom lane > >
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-02T22:03:13Z
Scott Carey <scott.carey@algonomy.com> writes: > On Thu, Apr 2, 2026, 10:38 Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I did some bisecting using the attached simplified test case, and found >> that the query execution time jumps from circa 60ms to circa 7500ms here: >> ... >> I'm bemused as to why: the test case has work_mem set high enough that >> we shouldn't be triggering spill mode, so why did this change affect >> it at all? > Even stranger, the more spills induced via smaller work_mem the faster it > goes. > This suggests something getting more expensive as the hash table gets > larger. Significantly more, like O(n^2) or worse. Yeah. I watched this query (at work_mem=200MB) with "perf", and I find that essentially all of the runtime is spent here: --96.39%--agg_fill_hash_table (inlined) | --95.95%--lookup_hash_entries | --95.77%--initialize_hash_entry (inlined) | --95.72%--hash_agg_check_limits | --95.72%--MemoryContextMemAllocated | --83.22%--MemoryContextTraverseNext (inlined) | --3.97%--MemoryContextTraverseNext (inlined) Drilling down further, the step that is slow is hash_agg_check_limits's Size tval_mem = MemoryContextMemAllocated(aggstate->hashcontext->ecxt_per_tuple_memory, true); and a look at the memory context tree explains why: ExecutorState: 32768 total in 3 blocks; 15768 free (5 chunks); 17000 used ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used HashAgg hashed tuples: 2097040 total in 9 blocks; 1045752 free; 1051288 used HashAgg meta context: 1056816 total in 2 blocks; 4328 free (0 chunks); 1052488 used ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used ExprContext: 8192 total in 1 blocks; 7952 free (1 chunks); 240 used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used expanded array: 1024 total in 1 blocks; 448 free (0 chunks); 576 used ... quite a few more ... 26174 more child contexts containing 26802176 total in 26174 blocks; 11725952 free (0 chunks); 15076224 used So the main problem here is we're leaking the arrays made by array_agg, and a secondary problem is that that drives the cost of hash_agg_check_limits to an unacceptable level. regards, tom lane -
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Frank Heikens <frank@elevarq.com> — 2026-04-03T04:19:34Z
Hi, I've been investigating a performance problem with user-defined aggregates that use array_append as the transition function (SFUNC = array_append, STYPE = bigint[]). These aggregates become catastrophically slow with HashAggregate when many groups are present -- a workload demonstrated. Root cause ---------- hash_agg_check_limits() calls MemoryContextMemAllocated(ctx, true) after every new group addition. The recursive flag causes it to traverse all child memory contexts, which is O(C) per call. array_append creates an expanded-array object per group, each owning a private AllocSet child context. With N groups, the total traversal cost becomes O(N * C) ≈ O(N^2). This completely dominates the actual aggregate computation once the group count reaches tens of thousands. Reproducer (Tom's test case) --------------------------------- CREATE AGGREGATE array_agg( BASETYPE = bigint, SFUNC = array_append, STYPE = bigint[], INITCOND = '{}'); CREATE TABLE array_agg_test( product_id bigint NOT NULL, region_id bigint NOT NULL, available boolean NOT NULL); INSERT INTO array_agg_test SELECT generate_series(1, 50000), (ARRAY[1,2,3,4])[floor(random()*4)+1], true; -- (repeat 3 more times with region arrays [11..14], [111..114], [1111..1114]) VACUUM ANALYZE array_agg_test; SET work_mem = '200MB'; EXPLAIN (ANALYZE, BUFFERS) SELECT product_id, array_agg(region_id) FROM array_agg_test GROUP BY product_id; -- 50K groups, ~4 rows per group -- Without patch: ~5 seconds -- With patch: ~150 ms -- Built-in array_agg: ~100 ms Fix --- The attached patch throttles the recursive memory check: once the group count exceeds 1024, the full check only runs every 1024th new group. Below 1024 groups, behavior is unchanged. This caps the spill-detection latency to at most 1024 groups' worth of memory. For typical work_mem settings, that's a few megabytes of potential overshoot -- well within the existing tolerance of the check, which was already described as "imperfect" in its own comments. The patch is purely local to hash_agg_check_limits() in nodeAgg.c: about 15 lines of functional change plus comments. Benchmarks (PG 19devel, debug build, -O0) ------------------------------------------ Tom Lane reproducer (50K groups): custom array_agg 200MB: 4.6 s -> 152 ms (30x) custom array_agg 4MB: 388 ms -> 283 ms (no regression) built-in array_agg: 102 ms -> 103 ms (unchanged) Synthetic (100K groups x 50 elems): custom 256MB: 35.0 s -> 2.9 s (12x) custom 4MB: 6.1 s -> 5.9 s (unchanged) Extreme (500K groups x 2 elems): custom 256MB: 672 s -> 1.7 s (395x) Non-array aggregates (count, sum, string_agg): unchanged Built-in array_agg: unchanged Spill behavior (batches, disk usage): unchanged Regards, Frank Heikens > On Apr 2, 2026, at 10:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Scott Carey <scott.carey@algonomy.com> writes: >> I have discovered the root cause. >> This database is old. It pre-dates Postgres 8.4 which introduced >> array_agg. Apparently, in some version prior to 8.4 array_agg was added >> as a user function, defined as below for bigint: > >> create AGGREGATE array_agg( >> BASETYPE = bigint, >> SFUNC = array_append, >> STYPE = bigint[], >> INITCOND = '{}' >> ); > >> So if you create a test database and run the previous test, performance >> will be fine and the query will be fast. Then run: >> create AGGREGATE array_agg(BASETYPE = bigint, SFUNC = array_append,STYPE = >> bigint[], INITCOND = '{}'); > >> It will be slow and reproduce this behavior. > > Thank you for running that to ground! I confirm your results that v13 > and up are far slower for this example than v12 was. > >> Why would this run so much more slowly after updating from postgres 12 to >> 17? It is a user defined aggregate, although maybe not as optimized as >> the intrinsic one it shouldn't behave this way. > > I did some bisecting using the attached simplified test case, and found > that the query execution time jumps from circa 60ms to circa 7500ms here: > > 1f39bce021540fde00990af55b4432c55ef4b3c7 is the first bad commit > commit 1f39bce021540fde00990af55b4432c55ef4b3c7 > Author: Jeff Davis <jdavis@postgresql.org> > Date: Wed Mar 18 15:42:02 2020 -0700 > > Disk-based Hash Aggregation. > > While performing hash aggregation, track memory usage when adding new > groups to a hash table. If the memory usage exceeds work_mem, enter > "spill mode". > > (Times quoted are on a Mac M4 Pro, but in assert-enabled builds so > maybe not directly comparable to production.) > > I'm bemused as to why: the test case has work_mem set high enough that > we shouldn't be triggering spill mode, so why did this change affect > it at all? > > regards, tom lane > > CREATE AGGREGATE array_agg( > BASETYPE = bigint, > SFUNC = array_append, > STYPE = bigint[], > INITCOND = '{}' > ); > > drop table if exists array_agg_test; > > create table array_agg_test(product_id bigint not null, region_id bigint > not null, available boolean not null); > > insert into array_agg_test (product_id, region_id, available) SELECT > generate_series(1, 50000) as product_id, > (ARRAY[1,2,3,4])[floor(random()*4)+1] as region_id, true as available; > > insert into array_agg_test (product_id, region_id, available) SELECT > generate_series(1, 50000) as product_id, > (ARRAY[11,12,13,14])[floor(random()*4)+1] as region_id, true as available; > > insert into array_agg_test (product_id, region_id, available) SELECT > generate_series(1, 50000) as product_id, > (ARRAY[111,112,113,114])[floor(random()*4)+1] as region_id, true as > available; > > insert into array_agg_test (product_id, region_id, available) SELECT > generate_series(1, 50000) as product_id, > (ARRAY[1111,1112,1113,1114])[floor(random()*4)+1] as region_id, true as > available; > > vacuum analyze array_agg_test; > > \set ECHO all > > -- set hash_mem_multiplier = 2; > set work_mem = "200MB"; > > explain (analyze, buffers) select product_id, array_agg(region_id) from > array_agg_test group by product_id; -
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-03T19:24:15Z
I wrote: > So the main problem here is we're leaking the arrays made by > array_agg, and a secondary problem is that that drives the > cost of hash_agg_check_limits to an unacceptable level. After further study I no longer think there's a leak. This test query involves 50000 GROUP BY groups, and we have an array being accumulated for each one. The difference between the fast array_agg implementation and the slow one is just that the fast one keeps all its working state in the aggregate context, while the slow one makes a separate sub-context for each "expanded array". v12 creates 50000 "expanded arrays" too, but it's not noticeably slow. So the problem is exactly that repeating hash_agg_check_limits each time we start a new group is O(N^2), because if there is a sub-context for each group then MemoryContextMemAllocated requires O(N) time. The other little problem with this approach is acknowledged in the comments: * ... Allocations may happen without adding new groups (for instance, * if the transition state size grows), so this check is imperfect. So really the whole thing is kind of unsatisfactory. I'm not seeing cheap ways to make it better though. A very quick and dirty idea is to tell MemoryContextMemAllocated not to recurse, which would restore it to constant-time. But that would make the results much less accurate in cases like this one. regards, tom lane
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Jeff Davis <pgsql@j-davis.com> — 2026-04-03T19:24:49Z
On Thu, 2026-04-02 at 18:03 -0400, Tom Lane wrote: > and a secondary problem is that that drives the > cost of hash_agg_check_limits to an unacceptable level. I recall some discussion about whether the memory accounting would recurse to child contexts at the time MemoryContextGetMemAllocated() is called, or whether it would update the parent contexts at the time a new block is allocated in a subcontext. Using the latter strategy would solve the high cost when there are many subcontexts. Regards, Jeff Davis
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Jeff Davis <pgsql@j-davis.com> — 2026-04-03T19:36:23Z
On Fri, 2026-04-03 at 15:24 -0400, Tom Lane wrote: > After further study I no longer think there's a leak. This > test query involves 50000 GROUP BY groups, and we have an > array being accumulated for each one. I was coming to a similar conclusion, though trying to work through the details of expanded arrays and how the datums are copied around during aggregation. I don't (yet) see a problem with the correctness of the memory handling there. A lot of tiny memory contexts are not ideal, but as long as it's one per group, that seems within reason. > So really the whole thing is kind of unsatisfactory. > I'm not seeing cheap ways to make it better though. > > A very quick and dirty idea is to tell MemoryContextMemAllocated > not to recurse, which would restore it to constant-time. But > that would make the results much less accurate in cases like > this one. One idea would be to update parent contexts' memory totals recursively each time a subcontext allocates a new block. Block allocations are infrequent enough that may be acceptable. If we are worried about affecting unrelated cases, we could set an "accounting_enabled" flag for the contexts we care about, which would be automatically inherited by subcontexts, and then stop recursing up when that flag is false. Regards, Jeff Davis
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Tom Lane <tgl@sss.pgh.pa.us> — 2026-04-03T19:56:22Z
Jeff Davis <pgsql@j-davis.com> writes: > One idea would be to update parent contexts' memory totals recursively > each time a subcontext allocates a new block. Block allocations are > infrequent enough that may be acceptable. > If we are worried about affecting unrelated cases, we could set an > "accounting_enabled" flag for the contexts we care about, which would > be automatically inherited by subcontexts, and then stop recursing up > when that flag is false. Yeah, I was speculating about similar ideas. Since mem_allocated is only changed after a malloc() or free() call, it probably wouldn't add too much overhead to propagate that up to parent contexts. I agree with having a flag to prevent the propagation from going up further than we actually care about, though. Would it make sense to accumulate those values in a separate field child_mem_allocated, rather than redefining what mem_allocated means? regards, tom lane
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Jeff Davis <pgsql@j-davis.com> — 2026-04-03T20:01:38Z
On Fri, 2026-04-03 at 15:56 -0400, Tom Lane wrote: > Would it make sense to accumulate those values in a separate field > child_mem_allocated, rather than redefining what mem_allocated > means? I think so unless we can't afford the new field for some reason. It would be convenient to have the single-context-total available when deleting the context. I'll try a quick patch. I'll need to be sure that we can properly decrement the total in all paths. Regards, Jeff Davis
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
David Rowley <dgrowleyml@gmail.com> — 2026-04-04T00:21:52Z
On Sat, 4 Apr 2026 at 08:56, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Jeff Davis <pgsql@j-davis.com> writes: > > One idea would be to update parent contexts' memory totals recursively > > each time a subcontext allocates a new block. Block allocations are > > infrequent enough that may be acceptable. > > > If we are worried about affecting unrelated cases, we could set an > > "accounting_enabled" flag for the contexts we care about, which would > > be automatically inherited by subcontexts, and then stop recursing up > > when that flag is false. > > Yeah, I was speculating about similar ideas. Since mem_allocated > is only changed after a malloc() or free() call, it probably > wouldn't add too much overhead to propagate that up to parent > contexts. I agree with having a flag to prevent the propagation > from going up further than we actually care about, though. > > Would it make sense to accumulate those values in a separate field > child_mem_allocated, rather than redefining what mem_allocated > means? A slight variation on this that I was thinking of would be to introduce a MemoryPool struct that could be tagged onto a MemoryContext which contains a pool_limit. A child MemoryContext would, by default, inherit its parent's MemoryPool. On malloc/free, if the owning context has a non-null MemoryPool, the MemoryPool's memory_allocated is updated. At a safe point in nodeAgg.c, we'd check if the pool limit has been reached. I assume there's some simple inline function that just checks if memory_allocated is greater than pool_limit. Doing it this way would mean there's no need to recursively propagate the mentioned child_mem_allocated field up the hierarchy, as there is only a single field to update if the MemoryPool field is set. David
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Tomas Vondra <tomas@vondra.me> — 2026-04-04T12:18:14Z
On 4/4/26 02:21, David Rowley wrote: > On Sat, 4 Apr 2026 at 08:56, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> Jeff Davis <pgsql@j-davis.com> writes: >>> One idea would be to update parent contexts' memory totals recursively >>> each time a subcontext allocates a new block. Block allocations are >>> infrequent enough that may be acceptable. >> >>> If we are worried about affecting unrelated cases, we could set an >>> "accounting_enabled" flag for the contexts we care about, which would >>> be automatically inherited by subcontexts, and then stop recursing up >>> when that flag is false. >> >> Yeah, I was speculating about similar ideas. Since mem_allocated >> is only changed after a malloc() or free() call, it probably >> wouldn't add too much overhead to propagate that up to parent >> contexts. I agree with having a flag to prevent the propagation >> from going up further than we actually care about, though. >> >> Would it make sense to accumulate those values in a separate field >> child_mem_allocated, rather than redefining what mem_allocated >> means? > > A slight variation on this that I was thinking of would be to > introduce a MemoryPool struct that could be tagged onto a > MemoryContext which contains a pool_limit. A child MemoryContext > would, by default, inherit its parent's MemoryPool. On malloc/free, if > the owning context has a non-null MemoryPool, the MemoryPool's > memory_allocated is updated. At a safe point in nodeAgg.c, we'd check > if the pool limit has been reached. I assume there's some simple > inline function that just checks if memory_allocated is greater than > pool_limit. Doing it this way would mean there's no need to > recursively propagate the mentioned child_mem_allocated field up the > hierarchy, as there is only a single field to update if the MemoryPool > field is set. > This reminds me the discussions in 2022 about having a global memory limit, and in particular this PoC patch [1] with a MemoryPool doing roughly what you're describing here (at least I think). [1] https://www.postgresql.org/message-id/4fb99fb7-8a6a-2828-dd77-e2f1d75c7dd0%40enterprisedb.com -- Tomas Vondra
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Jeff Davis <pgsql@j-davis.com> — 2026-04-07T21:58:22Z
On Sat, 2026-04-04 at 13:21 +1300, David Rowley wrote: > A slight variation on this that I was thinking of would be to > introduce a MemoryPool struct that could be tagged onto a > MemoryContext which contains a pool_limit. A child MemoryContext > would, by default, inherit its parent's MemoryPool. On malloc/free, > if > the owning context has a non-null MemoryPool, the MemoryPool's > memory_allocated is updated. At a safe point in nodeAgg.c, we'd check > if the pool limit has been reached. I assume there's some simple > inline function that just checks if memory_allocated is greater than > pool_limit. Doing it this way would mean there's no need to > recursively propagate the mentioned child_mem_allocated field up the > hierarchy, as there is only a single field to update if the > MemoryPool > field is set. I like that idea, because it could also be a good place to hold a max block size for that tree of contexts. That's important to ensure that the block size is significantly less than work_mem. But it also means there's only one pool in any given subtree (unless you mean that we should make that work somehow), which is an awkward requirement, especially with MemoryContextSetParent(). Regards, Jeff Davis
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
David Rowley <dgrowleyml@gmail.com> — 2026-04-11T02:09:45Z
On Sun, 5 Apr 2026 at 01:18, Tomas Vondra <tomas@vondra.me> wrote: > This reminds me the discussions in 2022 about having a global memory > limit, and in particular this PoC patch [1] with a MemoryPool doing > roughly what you're describing here (at least I think). > > [1] > https://www.postgresql.org/message-id/4fb99fb7-8a6a-2828-dd77-e2f1d75c7dd0%40enterprisedb.com I think the ideas are quite different. I see in that patch you're raising an ERROR if the memory usage goes over some threshold. What I had in mind was adding lightweight opt-in infrastructure to allow code to quickly check how much memory is being consumed by a MemoryContext and all of its child contexts. David
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
David Rowley <dgrowleyml@gmail.com> — 2026-04-11T02:42:54Z
On Wed, 8 Apr 2026 at 09:58, Jeff Davis <pgsql@j-davis.com> wrote: > > On Sat, 2026-04-04 at 13:21 +1300, David Rowley wrote: > > A slight variation on this that I was thinking of would be to > > introduce a MemoryPool struct that could be tagged onto a > > MemoryContext which contains a pool_limit. A child MemoryContext > > would, by default, inherit its parent's MemoryPool. On malloc/free, > > if > > the owning context has a non-null MemoryPool, the MemoryPool's > > memory_allocated is updated. At a safe point in nodeAgg.c, we'd check > > if the pool limit has been reached. I assume there's some simple > > inline function that just checks if memory_allocated is greater than > > pool_limit. Doing it this way would mean there's no need to > > recursively propagate the mentioned child_mem_allocated field up the > > hierarchy, as there is only a single field to update if the > > MemoryPool > > field is set. > > I like that idea, because it could also be a good place to hold a max > block size for that tree of contexts. That's important to ensure that > the block size is significantly less than work_mem. > > But it also means there's only one pool in any given subtree (unless > you mean that we should make that work somehow), which is an awkward > requirement, especially with MemoryContextSetParent(). I had imagined we'd have an Assert to ensure there's no other MemoryPool in the context hierarchy. I guess it would be possible for a MemoryPool to have a MemoryPool *next field and chain them, but since we've no need for that right now, I imagine it's fine to disallow that. If we did allow chaining, it might get complex as each MemoryPool would be allocated in a different context. Removing items from that linked list might be tricky. I did expect that we'd have some function like; MemoryPool *MemoryContextCreateMemoryPool(MemoryContext ctx, size_t bytes_limit) and either have that recursively populate the MemoryContext's memory_pool field for that and all child contexts with new MemoryPool which is palloc'd in ctx, or just have that function insist that no child contexts have been created yet. When we create a new context check if has a parent, and if that parent has a non-NULL memory_pool, then set the memory_pool field to that value. However, on looking at hash_create_memory() in nodeAgg.c, we create the hash_metacxt and hash_tuplescxt with aggstate->ss.ps.state->es_query_cxt as the parent of both. Since we won't want to add a MemoryPool to the parent of those contexts, we might need some way to have an existing context "join" an existing MemoryPool. That would mean an API more like: MemoryPool *MemoryPoolCreate(size_t bytes_limit); then void MemoryContextJoinPool(MemoryContext ctx, MemoryPool *pool); All the code that currently does "+= mem_allocated" or "-= mem_allocated" would need extra code to do "if (context->memory_pool != NULL) context->memory_pool->memory_allocated (+/-)= blksize;". I.e., no looping up the hierarchy. I imagined we'd document that a MemoryPool is designed to more easily keep track of malloc'd memory for a given (or group of) MemoryContext and all of its child contexts so that the total memory allocated can easily be checked without recursively visiting the memory_used fields in all child contexts. Also, that it is intended for short-lived contexts. Any longer-lived usages would mean we'd have MemoryPools in contexts that live longer than the query, or an executor node and that would mean we'd have to code up the ability to have multiple MemoryPools in the hierarchy, which seems more complex than what we need today. Maybe in the future there'd be some need to have MemoryPools with a hard limit that ERRORs when it goes above a threshold, but that's not what we need for nodeAgg.c. That seems to be more along the lines of what Tomas was mentioning in regards to the "Add the ability to limit the amount of memory that can be allocated to backends" thread. David
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Jeff Davis <pgsql@j-davis.com> — 2026-04-14T20:42:47Z
On Sat, 2026-04-11 at 14:42 +1200, David Rowley wrote: > I had imagined we'd have an Assert to ensure there's no other > MemoryPool in the context hierarchy. I guess it would be possible for > a MemoryPool to have a MemoryPool *next field and chain them, but > since we've no need for that right now, I imagine it's fine to > disallow that. The thread started with a user-defined aggregate making a memory context per group. If it was doing some more complex things with MemoryContextSetParent, are we sure we can just disallow it? It's hard for me to say for sure what a user-defined aggregate might want to do. > Since we won't want to add a MemoryPool to the parent of > those contexts, we might need some way to have an existing context > "join" an existing MemoryPool. That would mean an API more like: > MemoryPool *MemoryPoolCreate(size_t bytes_limit); then void > MemoryContextJoinPool(MemoryContext ctx, MemoryPool *pool); Right. > All the code that currently does "+= mem_allocated" or "-= > mem_allocated" would need extra code to do "if (context->memory_pool > != NULL) context->memory_pool->memory_allocated (+/-)= blksize;". > I.e., no looping up the hierarchy. OK. > I imagined we'd document that a MemoryPool is designed to more easily > keep track of malloc'd memory for a given (or group of) MemoryContext > and all of its child contexts so that the total memory allocated can > easily be checked without recursively visiting the memory_used fields > in all child contexts. Also, that it is intended for short-lived > contexts. Any longer-lived usages would mean we'd have MemoryPools in > contexts that live longer than the query, or an executor node and > that > would mean we'd have to code up the ability to have multiple > MemoryPools in the hierarchy, which seems more complex than what we > need today. Yeah, I hope we don't need to go that far. > Maybe in the future there'd be some need to have MemoryPools with a > hard limit that ERRORs when it goes above a threshold, but that's not > what we need for nodeAgg.c. That seems to be more along the lines of > what Tomas was mentioning in regards to the "Add the ability to limit > the amount of memory that can be allocated to backends" thread. Agreed. Attached v2-0001 simply tracks the totals for all contexts, and works across MemoryContextSetParent(). I was (and still am) slightly worried that it will cause a regression, but some basic pgbench runs didn't show any slowdown. Can you suggest some settings that might focus more on allocation performance? Assuming we only want to track for a subtree, then the two approaches suggested are: 1. Have some "invalid" marker that only tracks the memory upward as far as it's needed/enabled, then stops. This adds a bit of complexity because: a. to enable it after the context is created, we need a new memory context method to give the current allocated total so we can properly initialize the size b. when doing MemoryContextSetParent(), we need to subtract from the old subtree and add to the new subtree, and if the current subtree isn't already tracked, then we need to recalculate 2. The memory pools as you suggest. The complexity here is: a. in MemoryContextSetParent() for the same reason as above b. mixing subtrees gets messy, and I'm not sure we can just disallow that. Does that mean MemoryContextSetParent() would just fail? c. joining in to an existing pool -- not terribly complex, but adds to the API I think if we are taking on the complexity with memory pools, we should do so with the idea that they'll be useful beyond just optimizing the size calculation. For instance, carrying around information that it's part of a "work mem" context, which can do things like limit the max block size. Regards, Jeff Davis
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
David Rowley <dgrowleyml@gmail.com> — 2026-04-20T04:40:37Z
On Wed, 15 Apr 2026 at 08:42, Jeff Davis <pgsql@j-davis.com> wrote: > Attached v2-0001 simply tracks the totals for all contexts, and works > across MemoryContextSetParent(). I was (and still am) slightly worried > that it will cause a regression, but some basic pgbench runs didn't > show any slowdown. Can you suggest some settings that might focus more > on allocation performance? The patch in [1] might be worth testing. I tried the following on an AMD Zen 2 machine. Master: postgres=# select r, pg_allocate_memory_test(530000,530000,1024::bigint*1024*1024*1024,'generation') from generate_series(1,3)r; r | pg_allocate_memory_test ----+------------------------- 1 | 0.058918 2 | 0.057592 3 | 0.055907 With your patch: postgres=# select r, pg_allocate_memory_test(530000,530000,1024::bigint*1024*1024*1024,'generation') from generate_series(1,3)r; r | pg_allocate_memory_test ----+------------------------- 1 | 0.071015 2 | 0.071987 3 | 0.071948 (+24% slower) I went for 530000 as the allocChunkLimit was 512KB. I believe it's smaller for aset.c, which might mean a bigger overhead for that context type. > Assuming we only want to track for a subtree, then the two approaches > suggested are: > > 1. Have some "invalid" marker that only tracks the memory upward as far > as it's needed/enabled, then stops. This adds a bit of complexity > because: > a. to enable it after the context is created, we need a new memory > context method to give the current allocated total so we can properly > initialize the size > b. when doing MemoryContextSetParent(), we need to subtract from the > old subtree and add to the new subtree, and if the current subtree > isn't already tracked, then we need to recalculate > > 2. The memory pools as you suggest. The complexity here is: > a. in MemoryContextSetParent() for the same reason as above > b. mixing subtrees gets messy, and I'm not sure we can just disallow > that. Does that mean MemoryContextSetParent() would just fail? I was maybe wrong about just not bothering to handle MemoryContextSetParent(), but I'm not all that sure where the complexity is. Shouldn't it just be a matter of: If the context has a MemoryPool set, check if the parent has one too, if not just swap parents out as the pool belongs to the context that's changing parent. Else, gather memory totals for the swapping context and subtract from the MemoryPool, set the context being reparented's pool to NULL and change parent. else (no pool is set), just swap parent... I think. I think there might also need to be a check to see if the new parent has a pool and ERROR if it does. Maybe that's the messy part? > c. joining in to an existing pool -- not terribly complex, but adds > to the API > > I think if we are taking on the complexity with memory pools, we should > do so with the idea that they'll be useful beyond just optimizing the > size calculation. For instance, carrying around information that it's > part of a "work mem" context, which can do things like limit the max > block size. You might be right. It would be interesting to know if those overheads are lower with the MemoryPool idea. I suspect it'll be quite a bit less overhead as there's no pointer chasing when the MemoryPool isn't set, and it should be very fast to check that as we have to access the MemoryContext's mem_allocated field anyway, so we could expect that the cacheline for that will be loaded, or will be about to get loaded anyway, depending on which order you do the accounting in. David [1] https://postgr.es/m/CAApHDvox3Ro8mZJxignuyB-dGXJ9=wQNEkOFni9025GP=rOKkg@mail.gmail.com
-
Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
Jeff Davis <pgsql@j-davis.com> — 2026-05-05T01:27:03Z
On Mon, 2026-04-20 at 16:40 +1200, David Rowley wrote: > I was maybe wrong about just not bothering to handle > MemoryContextSetParent(), but I'm not all that sure where the > complexity is. Shouldn't it just be a matter of: > > If the context has a MemoryPool set, check if the parent has one too, > if not just swap parents out as the pool belongs to the context > that's changing parent. > Else, gather memory totals for the swapping context and subtract > from the MemoryPool, set the context being reparented's pool to NULL > and change parent. > else (no pool is set), just swap parent... I think. > > I think there might also need to be a check to see if the new parent > has a pool and ERROR if it does. Maybe that's the messy part? Patches attached. I implemented everything, such that we don't need to ERROR. It feels slightly over-engineered, but I just didn't like the idea of erroring on what seem to be valid operations. Given the inheritance behavior, you may not even be trying to use memory pools, and then SetParent can still fail, and then what do you do? Notes: * It adds 3 extra fields to MemoryContextData inline. The out of line approaches are not very clean: if we allocate in the context itself reset will throw it away; if we allocate in the parent context then we would need to move the allocation on SetParent(); allocating in the caller means the caller needs to track it even though it has the same lifetime; and I'm not sure it's a good idea to use malloc() directly. * The "limit" terminology is a bit awkward because it doesn't really enforce anything it just adjusts the max block size. Maybe there's a better term for that? * allocChunkLimit is not recalculated after SetParent(). I don't think that's a correctness issue, but I might need to add some more comments. I like the idea that memory contexts can inherit some information about work_mem. I've wanted that to be possible for a while, and if we think this is a good approach then we can expand it to other places in the executor. Regards, Jeff Davis