pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSignificant performance issues with array_agg() + HashAggregate plans on Postgres 17
29+ messages / 6 participants
[nested] [flat]
* Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
@ 2026-03-31 09:28 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Scott Carey @ 2026-03-31 09:28 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
@ 2026-03-31 12:03 ` David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
0 siblings, 1 reply; 29+ messages in thread
From: David Rowley @ 2026-03-31 12:03 UTC (permalink / raw)
To: Scott Carey <scott.carey@algonomy.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
@ 2026-03-31 17:55 ` Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
0 siblings, 1 reply; 29+ messages in thread
From: Scott Carey @ 2026-03-31 17:55 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
@ 2026-03-31 18:06 ` Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Scott Carey @ 2026-03-31 18:06 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
>>
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
@ 2026-03-31 19:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-31 22:48 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
0 siblings, 2 replies; 29+ messages in thread
From: Tom Lane @ 2026-03-31 19:26 UTC (permalink / raw)
To: Scott Carey <scott.carey@algonomy.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-03-31 22:48 ` David Rowley <dgrowleyml@gmail.com>
2026-04-01 07:18 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
1 sibling, 1 reply; 29+ messages in thread
From: David Rowley @ 2026-03-31 22:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Carey <scott.carey@algonomy.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-03-31 22:48 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
@ 2026-04-01 07:18 ` Scott Carey <scott.carey@algonomy.com>
2026-04-01 11:12 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Scott Carey @ 2026-04-01 07:18 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-03-31 22:48 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-04-01 07:18 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
@ 2026-04-01 11:12 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: David Rowley @ 2026-04-01 11:12 UTC (permalink / raw)
To: Scott Carey <scott.carey@algonomy.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-01 07:04 ` Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 29+ messages in thread
From: Scott Carey @ 2026-04-01 07:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
@ 2026-04-01 13:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 17:50 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
0 siblings, 2 replies; 29+ messages in thread
From: Tom Lane @ 2026-04-01 13:44 UTC (permalink / raw)
To: Scott Carey <scott.carey@algonomy.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-01 17:50 ` Scott Carey <scott.carey@algonomy.com>
1 sibling, 0 replies; 29+ messages in thread
From: Scott Carey @ 2026-04-01 17:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-02 07:23 ` Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 29+ messages in thread
From: Scott Carey @ 2026-04-02 07:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
@ 2026-04-02 17:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-03 04:19 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Frank Heikens <frank@elevarq.com>
0 siblings, 2 replies; 29+ messages in thread
From: Tom Lane @ 2026-04-02 17:38 UTC (permalink / raw)
To: Scott Carey <scott.carey@algonomy.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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;
Attachments:
[text/plain] hashaggperformance.sql (1.2K, ../../3579239.1775151498@sss.pgh.pa.us/2-hashaggperformance.sql)
download | inline:
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;
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-02 19:08 ` Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 29+ messages in thread
From: Scott Carey @ 2026-04-02 19:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Davis <pgsql@j-davis.com>; David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
>
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
@ 2026-04-02 22:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
0 siblings, 2 replies; 29+ messages in thread
From: Tom Lane @ 2026-04-02 22:03 UTC (permalink / raw)
To: Scott Carey <scott.carey@algonomy.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-03 19:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
1 sibling, 1 reply; 29+ messages in thread
From: Tom Lane @ 2026-04-03 19:24 UTC (permalink / raw)
To: Scott Carey <scott.carey@algonomy.com>; +Cc: Jeff Davis <pgsql@j-davis.com>; David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-03 19:36 ` Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Jeff Davis @ 2026-04-03 19:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott.carey@algonomy.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
@ 2026-04-03 19:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 20:01 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-04 00:21 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
0 siblings, 2 replies; 29+ messages in thread
From: Tom Lane @ 2026-04-03 19:56 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: Scott Carey <scott.carey@algonomy.com>; David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-03 20:01 ` Jeff Davis <pgsql@j-davis.com>
1 sibling, 0 replies; 29+ messages in thread
From: Jeff Davis @ 2026-04-03 20:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Carey <scott.carey@algonomy.com>; David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-04 00:21 ` David Rowley <dgrowleyml@gmail.com>
2026-04-04 12:18 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tomas Vondra <tomas@vondra.me>
2026-04-07 21:58 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
1 sibling, 2 replies; 29+ messages in thread
From: David Rowley @ 2026-04-04 00:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Davis <pgsql@j-davis.com>; Scott Carey <scott.carey@algonomy.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-04 00:21 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
@ 2026-04-04 12:18 ` Tomas Vondra <tomas@vondra.me>
2026-04-11 02:09 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 29+ messages in thread
From: Tomas Vondra @ 2026-04-04 12:18 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeff Davis <pgsql@j-davis.com>; Scott Carey <scott.carey@algonomy.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-04 00:21 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-04-04 12:18 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tomas Vondra <tomas@vondra.me>
@ 2026-04-11 02:09 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 29+ messages in thread
From: David Rowley @ 2026-04-11 02:09 UTC (permalink / raw)
To: Tomas Vondra <tomas@vondra.me>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Jeff Davis <pgsql@j-davis.com>; Scott Carey <scott.carey@algonomy.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-04 00:21 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
@ 2026-04-07 21:58 ` Jeff Davis <pgsql@j-davis.com>
2026-04-11 02:42 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 29+ messages in thread
From: Jeff Davis @ 2026-04-07 21:58 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Carey <scott.carey@algonomy.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-04 00:21 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-04-07 21:58 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
@ 2026-04-11 02:42 ` David Rowley <dgrowleyml@gmail.com>
2026-04-14 20:42 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 29+ messages in thread
From: David Rowley @ 2026-04-11 02:42 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott.carey@algonomy.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-04 00:21 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-04-07 21:58 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-11 02:42 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
@ 2026-04-14 20:42 ` Jeff Davis <pgsql@j-davis.com>
2026-04-20 04:40 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Jeff Davis @ 2026-04-14 20:42 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott.carey@algonomy.com>; pgsql-performance@lists.postgresql.org
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
Attachments:
[text/x-patch] v2-0001-Account-for-memory-context-totals-at-the-time-of-.patch (11.9K, ../../ee1f6701d76b3ef5f511d494c337bc3d76f8bfab.camel@j-davis.com/2-v2-0001-Account-for-memory-context-totals-at-the-time-of-.patch)
download | inline diff:
From 0d5673e156554c9d95a72c6c7949be9b2d5adb3e Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Tue, 7 Apr 2026 15:47:32 -0700
Subject: [PATCH v2] Account for memory context totals at the time of
allocation.
When a new block is allocated by the underlying allocator, add it to
the current context as well as the totals for all ancestors.
Previously, we tracked only the memory for the current context, and
calculated the total recursively when needed. That led to N^2 behavior
in HashAgg when each group created its own subcontext.
Discussion: https://postgr.es/m/3579197.1775151468.0@sss.pgh.pa.us
---
src/backend/utils/mmgr/aset.c | 24 +++++++++++++-----------
src/backend/utils/mmgr/bump.c | 9 +++++----
src/backend/utils/mmgr/generation.c | 9 +++++----
src/backend/utils/mmgr/mcxt.c | 27 +++++++++++++++------------
src/backend/utils/mmgr/slab.c | 10 ++++++----
src/include/nodes/memnodes.h | 13 +++++++++++++
6 files changed, 57 insertions(+), 35 deletions(-)
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index 6a9ea367107..0f280089bc3 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -422,8 +422,8 @@ AllocSetContextCreateInternal(MemoryContext parent,
parent,
name);
- ((MemoryContext) set)->mem_allocated =
- KeeperBlock(set)->endptr - ((char *) set);
+ MemoryContextUpdateAllocation((MemoryContext) set,
+ KeeperBlock(set)->endptr - ((char *) set));
return (MemoryContext) set;
}
@@ -525,7 +525,7 @@ AllocSetContextCreateInternal(MemoryContext parent,
parent,
name);
- ((MemoryContext) set)->mem_allocated = firstBlockSize;
+ MemoryContextUpdateAllocation((MemoryContext) set, firstBlockSize);
return (MemoryContext) set;
}
@@ -589,7 +589,8 @@ AllocSetReset(MemoryContext context)
else
{
/* Normal case, release the block */
- context->mem_allocated -= block->endptr - ((char *) block);
+ MemoryContextUpdateAllocation(context,
+ -(block->endptr - ((char *) block)));
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, block->freeptr - ((char *) block));
@@ -696,7 +697,8 @@ AllocSetDelete(MemoryContext context)
AllocBlock next = block->next;
if (!IsKeeperBlock(set, block))
- context->mem_allocated -= block->endptr - ((char *) block);
+ MemoryContextUpdateAllocation(context,
+ -(block->endptr - ((char *) block)));
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, block->freeptr - ((char *) block));
@@ -758,7 +760,7 @@ AllocSetAllocLarge(MemoryContext context, Size size, int flags)
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, ALLOC_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAllocation(context, blksize);
block->aset = set;
block->freeptr = block->endptr = ((char *) block) + blksize;
@@ -967,7 +969,7 @@ AllocSetAllocFromNewBlock(MemoryContext context, Size size, int flags,
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, ALLOC_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAllocation(context, blksize);
block->aset = set;
block->freeptr = ((char *) block) + ALLOC_BLOCKHDRSZ;
@@ -1144,7 +1146,8 @@ AllocSetFree(void *pointer)
if (block->next)
block->next->prev = block->prev;
- set->header.mem_allocated -= block->endptr - ((char *) block);
+ MemoryContextUpdateAllocation((MemoryContext) set,
+ -(block->endptr - ((char *) block)));
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, block->freeptr - ((char *) block));
@@ -1307,9 +1310,8 @@ AllocSetRealloc(void *pointer, Size size, int flags)
VALGRIND_MEMPOOL_CHANGE(set, block, newblock, ALLOC_BLOCKHDRSZ);
block = newblock;
- /* updated separately, not to underflow when (oldblksize > blksize) */
- set->header.mem_allocated -= oldblksize;
- set->header.mem_allocated += blksize;
+ MemoryContextUpdateAllocation((MemoryContext) set,
+ (ssize_t) blksize - (ssize_t) oldblksize);
block->freeptr = block->endptr = ((char *) block) + blksize;
diff --git a/src/backend/utils/mmgr/bump.c b/src/backend/utils/mmgr/bump.c
index bfb5a114147..1af3a62755b 100644
--- a/src/backend/utils/mmgr/bump.c
+++ b/src/backend/utils/mmgr/bump.c
@@ -235,7 +235,7 @@ BumpContextCreate(MemoryContext parent, const char *name, Size minContextSize,
MemoryContextCreate((MemoryContext) set, T_BumpContext, MCTX_BUMP_ID,
parent, name);
- ((MemoryContext) set)->mem_allocated = allocSize;
+ MemoryContextUpdateAllocation((MemoryContext) set, allocSize);
return (MemoryContext) set;
}
@@ -341,7 +341,7 @@ BumpAllocLarge(MemoryContext context, Size size, int flags)
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, Bump_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAllocation(context, blksize);
/* the block is completely full */
block->freeptr = block->endptr = ((char *) block) + blksize;
@@ -481,7 +481,7 @@ BumpAllocFromNewBlock(MemoryContext context, Size size, int flags,
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, Bump_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAllocation(context, blksize);
/* initialize the new block */
BumpBlockInit(set, block, blksize);
@@ -626,7 +626,8 @@ BumpBlockFree(BumpContext *set, BumpBlock *block)
/* release the block from the list of blocks */
dlist_delete(&block->node);
- ((MemoryContext) set)->mem_allocated -= ((char *) block->endptr - (char *) block);
+ MemoryContextUpdateAllocation((MemoryContext) set,
+ -((char *) block->endptr - (char *) block));
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, ((char *) block->endptr - (char *) block));
diff --git a/src/backend/utils/mmgr/generation.c b/src/backend/utils/mmgr/generation.c
index 609c9bdc9a6..2e84a4fba93 100644
--- a/src/backend/utils/mmgr/generation.c
+++ b/src/backend/utils/mmgr/generation.c
@@ -273,7 +273,7 @@ GenerationContextCreate(MemoryContext parent,
parent,
name);
- ((MemoryContext) set)->mem_allocated = firstBlockSize;
+ MemoryContextUpdateAllocation((MemoryContext) set, firstBlockSize);
return (MemoryContext) set;
}
@@ -388,7 +388,7 @@ GenerationAllocLarge(MemoryContext context, Size size, int flags)
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, Generation_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAllocation(context, blksize);
/* block with a single (used) chunk */
block->context = set;
@@ -513,7 +513,7 @@ GenerationAllocFromNewBlock(MemoryContext context, Size size, int flags,
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, Generation_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAllocation(context, blksize);
/* initialize the new block */
GenerationBlockInit(set, block, blksize);
@@ -697,7 +697,8 @@ GenerationBlockFree(GenerationContext *set, GenerationBlock *block)
/* release the block from the list of blocks */
dlist_delete(&block->node);
- ((MemoryContext) set)->mem_allocated -= block->blksize;
+ MemoryContextUpdateAllocation((MemoryContext) set,
+ -(ssize_t) block->blksize);
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, block->blksize);
diff --git a/src/backend/utils/mmgr/mcxt.c b/src/backend/utils/mmgr/mcxt.c
index 073bdb35d2a..65b4514d77e 100644
--- a/src/backend/utils/mmgr/mcxt.c
+++ b/src/backend/utils/mmgr/mcxt.c
@@ -696,6 +696,7 @@ MemoryContextSetParent(MemoryContext context, MemoryContext new_parent)
if (context->parent)
{
MemoryContext parent = context->parent;
+ MemoryContext ancestor;
if (context->prevchild != NULL)
context->prevchild->nextchild = context->nextchild;
@@ -707,11 +708,17 @@ MemoryContextSetParent(MemoryContext context, MemoryContext new_parent)
if (context->nextchild != NULL)
context->nextchild->prevchild = context->prevchild;
+
+ /* subtract this context's total from old parent chain */
+ for (ancestor = parent; ancestor != NULL; ancestor = ancestor->parent)
+ ancestor->total_allocated -= context->total_allocated;
}
/* And relink */
if (new_parent)
{
+ MemoryContext ancestor;
+
Assert(MemoryContextIsValid(new_parent));
context->parent = new_parent;
context->prevchild = NULL;
@@ -719,6 +726,10 @@ MemoryContextSetParent(MemoryContext context, MemoryContext new_parent)
if (new_parent->firstchild != NULL)
new_parent->firstchild->prevchild = context;
new_parent->firstchild = context;
+
+ /* add this context's total to new parent chain */
+ for (ancestor = new_parent; ancestor != NULL; ancestor = ancestor->parent)
+ ancestor->total_allocated += context->total_allocated;
}
else
{
@@ -810,21 +821,12 @@ MemoryContextIsEmpty(MemoryContext context)
Size
MemoryContextMemAllocated(MemoryContext context, bool recurse)
{
- Size total = context->mem_allocated;
-
Assert(MemoryContextIsValid(context));
if (recurse)
- {
- for (MemoryContext curr = context->firstchild;
- curr != NULL;
- curr = MemoryContextTraverseNext(curr, context))
- {
- total += curr->mem_allocated;
- }
- }
-
- return total;
+ return context->total_allocated;
+ else
+ return context->mem_allocated;
}
/*
@@ -1166,6 +1168,7 @@ MemoryContextCreate(MemoryContext node,
node->parent = parent;
node->firstchild = NULL;
node->mem_allocated = 0;
+ node->total_allocated = 0;
node->prevchild = NULL;
node->name = name;
node->ident = NULL;
diff --git a/src/backend/utils/mmgr/slab.c b/src/backend/utils/mmgr/slab.c
index dd1db9566d1..a38860fbc28 100644
--- a/src/backend/utils/mmgr/slab.c
+++ b/src/backend/utils/mmgr/slab.c
@@ -461,7 +461,7 @@ SlabReset(MemoryContext context)
VALGRIND_MEMPOOL_FREE(slab, block);
free(block);
- context->mem_allocated -= slab->blockSize;
+ MemoryContextUpdateAllocation(context, -(ssize_t) slab->blockSize);
}
/* walk over blocklist and free the blocks */
@@ -481,7 +481,8 @@ SlabReset(MemoryContext context)
VALGRIND_MEMPOOL_FREE(slab, block);
free(block);
- context->mem_allocated -= slab->blockSize;
+ MemoryContextUpdateAllocation(context,
+ -(ssize_t) slab->blockSize);
}
}
@@ -597,7 +598,7 @@ SlabAllocFromNewBlock(MemoryContext context, Size size, int flags)
VALGRIND_MEMPOOL_ALLOC(slab, block, Slab_BLOCKHDRSZ);
block->slab = slab;
- context->mem_allocated += slab->blockSize;
+ MemoryContextUpdateAllocation(context, slab->blockSize);
/* use the first chunk in the new block */
chunk = SlabBlockGetChunk(slab, block, 0);
@@ -836,7 +837,8 @@ SlabFree(void *pointer)
VALGRIND_MEMPOOL_FREE(slab, block);
free(block);
- slab->header.mem_allocated -= slab->blockSize;
+ MemoryContextUpdateAllocation((MemoryContext) slab,
+ -(ssize_t) slab->blockSize);
}
/*
diff --git a/src/include/nodes/memnodes.h b/src/include/nodes/memnodes.h
index 4b24778fe1e..ca5b63b07d8 100644
--- a/src/include/nodes/memnodes.h
+++ b/src/include/nodes/memnodes.h
@@ -123,6 +123,7 @@ typedef struct MemoryContextData
bool isReset; /* T = no space allocated since last reset */
bool allowInCritSection; /* allow palloc in critical section */
Size mem_allocated; /* track memory allocated for this context */
+ Size total_allocated; /* memory including child contexts */
const MemoryContextMethods *methods; /* virtual function table */
MemoryContext parent; /* NULL if no parent (toplevel context) */
MemoryContext firstchild; /* head of linked list of children */
@@ -136,6 +137,18 @@ typedef struct MemoryContextData
/* utils/palloc.h contains typedef struct MemoryContextData *MemoryContext */
+/*
+ * Add allocation to the given context and all ancestors.
+ */
+static inline void
+MemoryContextUpdateAllocation(MemoryContext context, ssize_t amount)
+{
+ context->mem_allocated += amount;
+
+ for (MemoryContext curr = context; curr != NULL; curr = curr->parent)
+ curr->total_allocated += amount;
+}
+
/*
* MemoryContextIsValid
* True iff memory context is valid.
--
2.43.0
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-04 00:21 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-04-07 21:58 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-11 02:42 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-04-14 20:42 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
@ 2026-04-20 04:40 ` David Rowley <dgrowleyml@gmail.com>
2026-05-05 01:27 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 29+ messages in thread
From: David Rowley @ 2026-04-20 04:40 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott.carey@algonomy.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-04 00:21 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-04-07 21:58 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-11 02:42 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-04-14 20:42 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Jeff Davis <pgsql@j-davis.com>
2026-04-20 04:40 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
@ 2026-05-05 01:27 ` Jeff Davis <pgsql@j-davis.com>
0 siblings, 0 replies; 29+ messages in thread
From: Jeff Davis @ 2026-05-05 01:27 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott.carey@algonomy.com>; pgsql-performance@lists.postgresql.org
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
Attachments:
[text/x-patch] v4-0001-Refactor-memory-accounting-into-inline-function.patch (8.4K, ../../8179bf4b80956ffc983d48e127980ea84b0fe0da.camel@j-davis.com/2-v4-0001-Refactor-memory-accounting-into-inline-function.patch)
download | inline diff:
From 93ab0ff7e857acecdefc0a9c01b2d6d805c90559 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Wed, 29 Apr 2026 11:19:53 -0700
Subject: [PATCH v4 1/3] Refactor memory accounting into inline function.
---
src/backend/utils/mmgr/aset.c | 21 ++++++++++-----------
src/backend/utils/mmgr/bump.c | 8 ++++----
src/backend/utils/mmgr/generation.c | 6 +++---
src/backend/utils/mmgr/slab.c | 8 ++++----
src/include/utils/memutils_internal.h | 12 ++++++++++++
5 files changed, 33 insertions(+), 22 deletions(-)
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index 6a9ea367107..3ddef26a3d3 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -422,8 +422,8 @@ AllocSetContextCreateInternal(MemoryContext parent,
parent,
name);
- ((MemoryContext) set)->mem_allocated =
- KeeperBlock(set)->endptr - ((char *) set);
+ MemoryContextUpdateAlloc((MemoryContext) set,
+ KeeperBlock(set)->endptr - ((char *) set));
return (MemoryContext) set;
}
@@ -525,7 +525,7 @@ AllocSetContextCreateInternal(MemoryContext parent,
parent,
name);
- ((MemoryContext) set)->mem_allocated = firstBlockSize;
+ MemoryContextUpdateAlloc((MemoryContext) set, firstBlockSize);
return (MemoryContext) set;
}
@@ -589,7 +589,7 @@ AllocSetReset(MemoryContext context)
else
{
/* Normal case, release the block */
- context->mem_allocated -= block->endptr - ((char *) block);
+ MemoryContextUpdateAlloc(context, -(block->endptr - ((char *) block)));
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, block->freeptr - ((char *) block));
@@ -696,7 +696,7 @@ AllocSetDelete(MemoryContext context)
AllocBlock next = block->next;
if (!IsKeeperBlock(set, block))
- context->mem_allocated -= block->endptr - ((char *) block);
+ MemoryContextUpdateAlloc(context, -(block->endptr - ((char *) block)));
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, block->freeptr - ((char *) block));
@@ -758,7 +758,7 @@ AllocSetAllocLarge(MemoryContext context, Size size, int flags)
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, ALLOC_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAlloc(context, blksize);
block->aset = set;
block->freeptr = block->endptr = ((char *) block) + blksize;
@@ -967,7 +967,7 @@ AllocSetAllocFromNewBlock(MemoryContext context, Size size, int flags,
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, ALLOC_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAlloc(context, blksize);
block->aset = set;
block->freeptr = ((char *) block) + ALLOC_BLOCKHDRSZ;
@@ -1144,7 +1144,7 @@ AllocSetFree(void *pointer)
if (block->next)
block->next->prev = block->prev;
- set->header.mem_allocated -= block->endptr - ((char *) block);
+ MemoryContextUpdateAlloc(&set->header, -(block->endptr - ((char *) block)));
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, block->freeptr - ((char *) block));
@@ -1307,9 +1307,8 @@ AllocSetRealloc(void *pointer, Size size, int flags)
VALGRIND_MEMPOOL_CHANGE(set, block, newblock, ALLOC_BLOCKHDRSZ);
block = newblock;
- /* updated separately, not to underflow when (oldblksize > blksize) */
- set->header.mem_allocated -= oldblksize;
- set->header.mem_allocated += blksize;
+ MemoryContextUpdateAlloc(&set->header,
+ (ssize_t) blksize - (ssize_t) oldblksize);
block->freeptr = block->endptr = ((char *) block) + blksize;
diff --git a/src/backend/utils/mmgr/bump.c b/src/backend/utils/mmgr/bump.c
index bfb5a114147..1deb73f451a 100644
--- a/src/backend/utils/mmgr/bump.c
+++ b/src/backend/utils/mmgr/bump.c
@@ -235,7 +235,7 @@ BumpContextCreate(MemoryContext parent, const char *name, Size minContextSize,
MemoryContextCreate((MemoryContext) set, T_BumpContext, MCTX_BUMP_ID,
parent, name);
- ((MemoryContext) set)->mem_allocated = allocSize;
+ MemoryContextUpdateAlloc((MemoryContext) set, allocSize);
return (MemoryContext) set;
}
@@ -341,7 +341,7 @@ BumpAllocLarge(MemoryContext context, Size size, int flags)
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, Bump_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAlloc(context, blksize);
/* the block is completely full */
block->freeptr = block->endptr = ((char *) block) + blksize;
@@ -481,7 +481,7 @@ BumpAllocFromNewBlock(MemoryContext context, Size size, int flags,
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, Bump_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAlloc(context, blksize);
/* initialize the new block */
BumpBlockInit(set, block, blksize);
@@ -626,7 +626,7 @@ BumpBlockFree(BumpContext *set, BumpBlock *block)
/* release the block from the list of blocks */
dlist_delete(&block->node);
- ((MemoryContext) set)->mem_allocated -= ((char *) block->endptr - (char *) block);
+ MemoryContextUpdateAlloc((MemoryContext) set, -((char *) block->endptr - (char *) block));
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, ((char *) block->endptr - (char *) block));
diff --git a/src/backend/utils/mmgr/generation.c b/src/backend/utils/mmgr/generation.c
index 609c9bdc9a6..ddcd25c1e9d 100644
--- a/src/backend/utils/mmgr/generation.c
+++ b/src/backend/utils/mmgr/generation.c
@@ -388,7 +388,7 @@ GenerationAllocLarge(MemoryContext context, Size size, int flags)
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, Generation_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAlloc(context, blksize);
/* block with a single (used) chunk */
block->context = set;
@@ -513,7 +513,7 @@ GenerationAllocFromNewBlock(MemoryContext context, Size size, int flags,
/* Make a vchunk covering the new block's header */
VALGRIND_MEMPOOL_ALLOC(set, block, Generation_BLOCKHDRSZ);
- context->mem_allocated += blksize;
+ MemoryContextUpdateAlloc(context, blksize);
/* initialize the new block */
GenerationBlockInit(set, block, blksize);
@@ -697,7 +697,7 @@ GenerationBlockFree(GenerationContext *set, GenerationBlock *block)
/* release the block from the list of blocks */
dlist_delete(&block->node);
- ((MemoryContext) set)->mem_allocated -= block->blksize;
+ MemoryContextUpdateAlloc((MemoryContext) set, -((ssize_t) block->blksize));
#ifdef CLOBBER_FREED_MEMORY
wipe_mem(block, block->blksize);
diff --git a/src/backend/utils/mmgr/slab.c b/src/backend/utils/mmgr/slab.c
index dd1db9566d1..f27d9683db6 100644
--- a/src/backend/utils/mmgr/slab.c
+++ b/src/backend/utils/mmgr/slab.c
@@ -461,7 +461,7 @@ SlabReset(MemoryContext context)
VALGRIND_MEMPOOL_FREE(slab, block);
free(block);
- context->mem_allocated -= slab->blockSize;
+ MemoryContextUpdateAlloc(context, -((ssize_t) slab->blockSize));
}
/* walk over blocklist and free the blocks */
@@ -481,7 +481,7 @@ SlabReset(MemoryContext context)
VALGRIND_MEMPOOL_FREE(slab, block);
free(block);
- context->mem_allocated -= slab->blockSize;
+ MemoryContextUpdateAlloc(context, -((ssize_t) slab->blockSize));
}
}
@@ -597,7 +597,7 @@ SlabAllocFromNewBlock(MemoryContext context, Size size, int flags)
VALGRIND_MEMPOOL_ALLOC(slab, block, Slab_BLOCKHDRSZ);
block->slab = slab;
- context->mem_allocated += slab->blockSize;
+ MemoryContextUpdateAlloc(context, slab->blockSize);
/* use the first chunk in the new block */
chunk = SlabBlockGetChunk(slab, block, 0);
@@ -836,7 +836,7 @@ SlabFree(void *pointer)
VALGRIND_MEMPOOL_FREE(slab, block);
free(block);
- slab->header.mem_allocated -= slab->blockSize;
+ MemoryContextUpdateAlloc(&slab->header, -((ssize_t) slab->blockSize));
}
/*
diff --git a/src/include/utils/memutils_internal.h b/src/include/utils/memutils_internal.h
index 475e91b336b..8f793f3c781 100644
--- a/src/include/utils/memutils_internal.h
+++ b/src/include/utils/memutils_internal.h
@@ -157,6 +157,18 @@ extern void MemoryContextCreate(MemoryContext node,
MemoryContext parent,
const char *name);
+/*
+ * MemoryContextUpdateAlloc()
+ *
+ * Update allocation total.
+ */
+static inline void
+MemoryContextUpdateAlloc(MemoryContext context, ssize_t size)
+{
+ Assert(size >= 0 || -size <= context->mem_allocated);
+ context->mem_allocated += size;
+}
+
extern void *MemoryContextAllocationFailure(MemoryContext context, Size size,
int flags);
--
2.43.0
[text/x-patch] v4-0002-Memory-Pools.patch (14.4K, ../../8179bf4b80956ffc983d48e127980ea84b0fe0da.camel@j-davis.com/3-v4-0002-Memory-Pools.patch)
download | inline diff:
From 55766fc0354e25621d922bb588a248196d124051 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Mon, 4 May 2026 14:24:40 -0700
Subject: [PATCH v4 2/3] Memory Pools.
A memory context can have a memory pool, which tracks the memory for
that context as well as all subcontexts in a single total, and also
holds an (unenforced) limit. Memory pools are automatically inherited
by child contexts.
Memory Pools solve two problems:
First, the previous implementation in MemoryContextMemAllocated()
recursively walked through subcontexts to total the mem_allocated
fields. For hash aggregates that used a child memory context for each
group, it was too slow. Now, it just reads the single total.
Second, with the block size doubling behavior, it was likely that a
single small chunk allocation could cause a block allocation that
overwhelmed a small value of work_mem. Hash Aggregation protected
against this by constraining maxBlockSize for known contexts, but not
their subcontexts. Now, subcontexts constrain the block size using the
inherited memory pool's limit.
Suggested-by: David Rowley <dgrowleyml@gmail.com>
Discussion: https://postgr.es/m/3579239.1775151498@sss.pgh.pa.us
---
src/backend/utils/mmgr/aset.c | 8 +-
src/backend/utils/mmgr/bump.c | 7 +-
src/backend/utils/mmgr/generation.c | 7 +-
src/backend/utils/mmgr/mcxt.c | 110 +++++++++++++++++++++++++-
src/include/nodes/memnodes.h | 16 ++++
src/include/utils/memutils.h | 1 +
src/include/utils/memutils_internal.h | 76 +++++++++++++++++-
src/tools/pgindent/typedefs.list | 1 +
8 files changed, 218 insertions(+), 8 deletions(-)
diff --git a/src/backend/utils/mmgr/aset.c b/src/backend/utils/mmgr/aset.c
index 3ddef26a3d3..8981c128672 100644
--- a/src/backend/utils/mmgr/aset.c
+++ b/src/backend/utils/mmgr/aset.c
@@ -867,6 +867,7 @@ AllocSetAllocFromNewBlock(MemoryContext context, Size size, int flags,
Size blksize;
Size required_size;
Size chunk_size;
+ Size blockSizeLimit;
/* due to the keeper block set->blocks should always be valid */
Assert(set->blocks != NULL);
@@ -931,8 +932,11 @@ AllocSetAllocFromNewBlock(MemoryContext context, Size size, int flags,
*/
blksize = set->nextBlockSize;
set->nextBlockSize <<= 1;
- if (set->nextBlockSize > set->maxBlockSize)
- set->nextBlockSize = set->maxBlockSize;
+
+ blockSizeLimit = MemoryContextBlockSizeLimit(context, set->initBlockSize,
+ set->maxBlockSize);
+ if (set->nextBlockSize > blockSizeLimit)
+ set->nextBlockSize = blockSizeLimit;
/* Choose the actual chunk size to allocate */
chunk_size = GetChunkSizeFromFreeListIdx(fidx);
diff --git a/src/backend/utils/mmgr/bump.c b/src/backend/utils/mmgr/bump.c
index 1deb73f451a..154ebdebd12 100644
--- a/src/backend/utils/mmgr/bump.c
+++ b/src/backend/utils/mmgr/bump.c
@@ -457,15 +457,18 @@ BumpAllocFromNewBlock(MemoryContext context, Size size, int flags,
BumpBlock *block;
Size blksize;
Size required_size;
+ Size blockSizeLimit;
/*
* The first such block has size initBlockSize, and we double the space in
* each succeeding block, but not more than maxBlockSize.
*/
blksize = set->nextBlockSize;
+ blockSizeLimit = MemoryContextBlockSizeLimit(context, set->initBlockSize,
+ set->maxBlockSize);
set->nextBlockSize <<= 1;
- if (set->nextBlockSize > set->maxBlockSize)
- set->nextBlockSize = set->maxBlockSize;
+ if (set->nextBlockSize > blockSizeLimit)
+ set->nextBlockSize = blockSizeLimit;
/* we'll need space for the chunk, chunk hdr and block hdr */
required_size = chunk_size + Bump_CHUNKHDRSZ + Bump_BLOCKHDRSZ;
diff --git a/src/backend/utils/mmgr/generation.c b/src/backend/utils/mmgr/generation.c
index ddcd25c1e9d..cade3ae3fe9 100644
--- a/src/backend/utils/mmgr/generation.c
+++ b/src/backend/utils/mmgr/generation.c
@@ -488,15 +488,18 @@ GenerationAllocFromNewBlock(MemoryContext context, Size size, int flags,
GenerationBlock *block;
Size blksize;
Size required_size;
+ Size blockSizeLimit;
/*
* The first such block has size initBlockSize, and we double the space in
* each succeeding block, but not more than maxBlockSize.
*/
blksize = set->nextBlockSize;
+ blockSizeLimit = MemoryContextBlockSizeLimit(context, set->initBlockSize,
+ set->maxBlockSize);
set->nextBlockSize <<= 1;
- if (set->nextBlockSize > set->maxBlockSize)
- set->nextBlockSize = set->maxBlockSize;
+ if (set->nextBlockSize > blockSizeLimit)
+ set->nextBlockSize = blockSizeLimit;
/* we'll need space for the chunk, chunk hdr and block hdr */
required_size = chunk_size + Generation_CHUNKHDRSZ + Generation_BLOCKHDRSZ;
diff --git a/src/backend/utils/mmgr/mcxt.c b/src/backend/utils/mmgr/mcxt.c
index 073bdb35d2a..472123612f2 100644
--- a/src/backend/utils/mmgr/mcxt.c
+++ b/src/backend/utils/mmgr/mcxt.c
@@ -664,6 +664,86 @@ MemoryContextSetIdentifier(MemoryContext context, const char *id)
context->ident = id;
}
+/*
+ * Add a memory pool to an existing context. It may already be a member of an
+ * outer pool owned by an ancestor, but it may not already own its memory
+ * pool.
+ *
+ * Currently, the context must have no child contexts, but that restriction
+ * could be lifted if necessary.
+ */
+MemoryPool *
+MemoryContextCreatePool(MemoryContext context, Size limit)
+{
+ MemoryPool *pool = &context->pool;
+
+ Assert(MemoryContextIsValid(context));
+ Assert(limit > 0);
+ Assert(context->firstchild == NULL);
+ Assert(!MemoryContextOwnsPool(context));
+
+ /* outer may already be set */
+
+ pool->limit = limit;
+ pool->allocated = context->mem_allocated;
+
+ return pool;
+}
+
+/* helper for MemoryContextSetParent() */
+static void
+MemoryContextTransferPool(MemoryContext context, MemoryContext new_parent)
+{
+ MemoryPool *old_outer_pool = context->pool.outer;
+ MemoryPool *new_outer_pool = NULL;
+ Size subtree_mem;
+
+ if (new_parent)
+ new_outer_pool = MemoryContextGetPool(new_parent);
+
+ if (!old_outer_pool && !new_outer_pool)
+ return;
+
+ if (old_outer_pool == new_outer_pool)
+ return;
+
+ /*
+ * If the context's pool is inherited from some ancestor context, we need
+ * to calculate the total for this subtree. If it's owned, we can just use
+ * the pool's total.
+ */
+ if (MemoryContextOwnsPool(context))
+ subtree_mem = context->pool.allocated;
+ else
+ subtree_mem = MemoryContextMemAllocated(context, true);
+
+ /* subtract from old subtree */
+ for (MemoryPool *pool = old_outer_pool; pool != NULL; pool = pool->outer)
+ pool->allocated -= subtree_mem;
+
+ /* add to new subtree */
+ for (MemoryPool *pool = new_outer_pool; pool != NULL; pool = pool->outer)
+ pool->allocated += subtree_mem;
+
+ /*
+ * If it's not the owner of its pool, subcontexts could refer to pools
+ * owned by the context's old ancestors. Search subtree for references to
+ * old_outer_pool and replace with new_outer_pool. NB: old_outer_pool or
+ * new_outer_pool might be NULL here.
+ */
+ if (!MemoryContextOwnsPool(context))
+ {
+ for (MemoryContext cxt = context->firstchild; cxt != NULL;
+ cxt = MemoryContextTraverseNext(cxt, context))
+ {
+ if (cxt->pool.outer == old_outer_pool)
+ cxt->pool.outer = new_outer_pool;
+ }
+ }
+
+ context->pool.outer = new_outer_pool;
+}
+
/*
* MemoryContextSetParent
* Change a context to belong to a new parent (or no parent).
@@ -692,6 +772,12 @@ MemoryContextSetParent(MemoryContext context, MemoryContext new_parent)
if (new_parent == context->parent)
return;
+ /*
+ * This must happen here while we still have information about the
+ * existing ancestor.
+ */
+ MemoryContextTransferPool(context, new_parent);
+
/* Delink from existing parent, if any */
if (context->parent)
{
@@ -726,6 +812,10 @@ MemoryContextSetParent(MemoryContext context, MemoryContext new_parent)
context->prevchild = NULL;
context->nextchild = NULL;
}
+
+#ifdef MEMORY_CONTEXT_CHECKING
+ MemoryContextCheck(context);
+#endif
}
/*
@@ -1090,18 +1180,30 @@ MemoryContextStatsPrint(MemoryContext context, void *passthru,
level, name, stats_string, truncated_ident)));
}
+#ifdef MEMORY_CONTEXT_CHECKING
+
+static void
+MemoryContextCheckPool(MemoryContext context)
+{
+ if (MemoryContextOwnsPool(context))
+ Assert(context->pool.allocated == MemoryContextMemAllocated(context, true));
+
+ if (context->parent)
+ Assert(context->pool.outer == MemoryContextGetPool(context->parent));
+}
+
/*
* MemoryContextCheck
* Check all chunks in the named context and its children.
*
* This is just a debugging utility, so it's not fancy.
*/
-#ifdef MEMORY_CONTEXT_CHECKING
void
MemoryContextCheck(MemoryContext context)
{
Assert(MemoryContextIsValid(context));
context->methods->check(context);
+ MemoryContextCheckPool(context);
for (MemoryContext curr = context->firstchild;
curr != NULL;
@@ -1109,8 +1211,10 @@ MemoryContextCheck(MemoryContext context)
{
Assert(MemoryContextIsValid(curr));
curr->methods->check(curr);
+ MemoryContextCheckPool(curr);
}
}
+
#endif
/*
@@ -1166,6 +1270,9 @@ MemoryContextCreate(MemoryContext node,
node->parent = parent;
node->firstchild = NULL;
node->mem_allocated = 0;
+ node->pool.limit = 0;
+ node->pool.allocated = 0;
+ node->pool.outer = NULL;
node->prevchild = NULL;
node->name = name;
node->ident = NULL;
@@ -1180,6 +1287,7 @@ MemoryContextCreate(MemoryContext node,
parent->firstchild = node;
/* inherit allowInCritSection flag from parent */
node->allowInCritSection = parent->allowInCritSection;
+ node->pool.outer = MemoryContextGetPool(parent);
}
else
{
diff --git a/src/include/nodes/memnodes.h b/src/include/nodes/memnodes.h
index 4b24778fe1e..8c3bc7b7d30 100644
--- a/src/include/nodes/memnodes.h
+++ b/src/include/nodes/memnodes.h
@@ -114,6 +114,21 @@ typedef struct MemoryContextMethods
} MemoryContextMethods;
+/*
+ * Memory Pools account for memory usage.
+ *
+ * If limit > 0, then this pool is owned by the containing context, and
+ * 'allocated' is valid. If limit == 0, the pool was inherited from the parent
+ * context, and 'allocated' is invalid.
+ */
+typedef struct MemoryPool
+{
+ Size limit; /* limit (not enforced) */
+ Size allocated; /* allocation total for subtree */
+ struct MemoryPool *outer; /* outer containing pool */
+} MemoryPool;
+
+
typedef struct MemoryContextData
{
pg_node_attr(abstract) /* there are no nodes of this type */
@@ -123,6 +138,7 @@ typedef struct MemoryContextData
bool isReset; /* T = no space allocated since last reset */
bool allowInCritSection; /* allow palloc in critical section */
Size mem_allocated; /* track memory allocated for this context */
+ MemoryPool pool; /* accounting, if enabled */
const MemoryContextMethods *methods; /* virtual function table */
MemoryContext parent; /* NULL if no parent (toplevel context) */
MemoryContext firstchild; /* head of linked list of children */
diff --git a/src/include/utils/memutils.h b/src/include/utils/memutils.h
index 11ab1717a16..e531876ba66 100644
--- a/src/include/utils/memutils.h
+++ b/src/include/utils/memutils.h
@@ -86,6 +86,7 @@ extern bool MemoryContextIsEmpty(MemoryContext context);
extern Size MemoryContextMemAllocated(MemoryContext context, bool recurse);
extern void MemoryContextMemConsumed(MemoryContext context,
MemoryContextCounters *consumed);
+extern MemoryPool *MemoryContextCreatePool(MemoryContext owner, Size limit);
extern void MemoryContextStats(MemoryContext context);
extern void MemoryContextStatsDetail(MemoryContext context,
int max_level, int max_children,
diff --git a/src/include/utils/memutils_internal.h b/src/include/utils/memutils_internal.h
index 8f793f3c781..8cacd8abb8c 100644
--- a/src/include/utils/memutils_internal.h
+++ b/src/include/utils/memutils_internal.h
@@ -17,6 +17,7 @@
#define MEMUTILS_INTERNAL_H
#include "utils/memutils.h"
+#include "port/pg_bitutils.h"
/* These functions implement the MemoryContext API for AllocSet context. */
extern void *AllocSetAlloc(MemoryContext context, Size size, int flags);
@@ -157,16 +158,89 @@ extern void MemoryContextCreate(MemoryContext node,
MemoryContext parent,
const char *name);
+static inline bool
+MemoryPoolIsValid(MemoryPool *pool)
+{
+ if (pool->limit > 0)
+ return true;
+
+ Assert(pool->allocated == 0);
+ return false;
+}
+
+static inline bool
+MemoryContextOwnsPool(MemoryContext context)
+{
+ return MemoryPoolIsValid(&context->pool);
+}
+
+static inline MemoryPool *
+MemoryPoolOuter(MemoryPool *pool)
+{
+ Assert(pool->outer == NULL || MemoryPoolIsValid(pool->outer));
+ return pool->outer;
+}
+
+/* get owned or inherited pool for memory context, or NULL */
+static inline MemoryPool *
+MemoryContextGetPool(MemoryContext context)
+{
+ MemoryPool *pool;
+
+ if (MemoryContextOwnsPool(context))
+ pool = &context->pool;
+ else
+ pool = context->pool.outer;
+
+ Assert(pool == NULL || MemoryPoolIsValid(pool));
+ return pool;
+}
+
+/*
+ * If the context is part of a memory pool, calculate a reasonable max block
+ * size given the memory pool limit. This avoids a case where a small chunk
+ * request causes a large block allocation that overwhelms the limit.
+ * Calculated dynamically because the limit could change, e.g. during
+ * MemoryContextSetParent().
+ */
+static inline Size
+MemoryContextBlockSizeLimit(MemoryContext context, Size min, Size max)
+{
+ MemoryPool *pool = MemoryContextGetPool(context);
+ Size maxBlockSize;
+
+ if (pool != NULL)
+ {
+ /* reasonable value chosen as 1/16th of the limit */
+ maxBlockSize = pg_prevpower2_size_t(pool->limit / 16);
+
+ maxBlockSize = Min(maxBlockSize, max);
+ maxBlockSize = Max(maxBlockSize, min);
+ }
+ else
+ maxBlockSize = max;
+
+ return maxBlockSize;
+}
+
/*
* MemoryContextUpdateAlloc()
*
- * Update allocation total.
+ * Update allocation total for context and pool(s).
*/
static inline void
MemoryContextUpdateAlloc(MemoryContext context, ssize_t size)
{
Assert(size >= 0 || -size <= context->mem_allocated);
context->mem_allocated += size;
+
+ for (MemoryPool *pool = MemoryContextGetPool(context);
+ pool != NULL;
+ pool = MemoryPoolOuter(pool))
+ {
+ Assert(size >= 0 || -size <= pool->allocated);
+ pool->allocated += size;
+ }
}
extern void *MemoryContextAllocationFailure(MemoryContext context, Size size,
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 0abdb2d37e2..9fe2b799476 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1754,6 +1754,7 @@ MemoryContextData
MemoryContextId
MemoryContextMethodID
MemoryContextMethods
+MemoryPool
MemoryStatsPrintFunc
MergeAction
MergeActionState
--
2.43.0
[text/x-patch] v4-0003-Update-Hash-Aggregation-to-use-memory-pools.patch (7.3K, ../../8179bf4b80956ffc983d48e127980ea84b0fe0da.camel@j-davis.com/4-v4-0003-Update-Hash-Aggregation-to-use-memory-pools.patch)
download | inline diff:
From baf0e7b28111337c6b9f138ddea7c2ef137fd930 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Mon, 4 May 2026 14:29:35 -0700
Subject: [PATCH v4 3/3] Update Hash Aggregation to use memory pools.
This reverts commit 50a38f65177ea7858bc97f71ba0757ba04c1c167, which is
no longer needed.
---
src/backend/executor/execUtils.c | 69 +++++++-------------------------
src/backend/executor/nodeAgg.c | 37 +++++------------
src/include/executor/executor.h | 1 -
3 files changed, 24 insertions(+), 83 deletions(-)
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index 1eb6b9f1f40..4b8d1c900b3 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -234,13 +234,21 @@ FreeExecutorState(EState *estate)
MemoryContextDelete(estate->es_query_cxt);
}
-/*
- * Internal implementation for CreateExprContext() and CreateWorkExprContext()
- * that allows control over the AllocSet parameters.
+/* ----------------
+ * CreateExprContext
+ *
+ * Create a context for expression evaluation within an EState.
+ *
+ * An executor run may require multiple ExprContexts (we usually make one
+ * for each Plan node, and a separate one for per-output-tuple processing
+ * such as constraint checking). Each ExprContext has its own "per-tuple"
+ * memory context.
+ *
+ * Note we make no assumption about the caller's memory context.
+ * ----------------
*/
-static ExprContext *
-CreateExprContextInternal(EState *estate, Size minContextSize,
- Size initBlockSize, Size maxBlockSize)
+ExprContext *
+CreateExprContext(EState *estate)
{
ExprContext *econtext;
MemoryContext oldcontext;
@@ -263,9 +271,7 @@ CreateExprContextInternal(EState *estate, Size minContextSize,
econtext->ecxt_per_tuple_memory =
AllocSetContextCreate(estate->es_query_cxt,
"ExprContext",
- minContextSize,
- initBlockSize,
- maxBlockSize);
+ ALLOCSET_DEFAULT_SIZES);
econtext->ecxt_param_exec_vals = estate->es_param_exec_vals;
econtext->ecxt_param_list_info = estate->es_param_list_info;
@@ -295,51 +301,6 @@ CreateExprContextInternal(EState *estate, Size minContextSize,
return econtext;
}
-/* ----------------
- * CreateExprContext
- *
- * Create a context for expression evaluation within an EState.
- *
- * An executor run may require multiple ExprContexts (we usually make one
- * for each Plan node, and a separate one for per-output-tuple processing
- * such as constraint checking). Each ExprContext has its own "per-tuple"
- * memory context.
- *
- * Note we make no assumption about the caller's memory context.
- * ----------------
- */
-ExprContext *
-CreateExprContext(EState *estate)
-{
- return CreateExprContextInternal(estate, ALLOCSET_DEFAULT_SIZES);
-}
-
-
-/* ----------------
- * CreateWorkExprContext
- *
- * Like CreateExprContext, but specifies the AllocSet sizes to be reasonable
- * in proportion to work_mem. If the maximum block allocation size is too
- * large, it's easy to skip right past work_mem with a single allocation.
- * ----------------
- */
-ExprContext *
-CreateWorkExprContext(EState *estate)
-{
- Size maxBlockSize;
-
- maxBlockSize = pg_prevpower2_size_t(work_mem * (Size) 1024 / 16);
-
- /* But no bigger than ALLOCSET_DEFAULT_MAXSIZE */
- maxBlockSize = Min(maxBlockSize, ALLOCSET_DEFAULT_MAXSIZE);
-
- /* and no smaller than ALLOCSET_DEFAULT_INITSIZE */
- maxBlockSize = Max(maxBlockSize, ALLOCSET_DEFAULT_INITSIZE);
-
- return CreateExprContextInternal(estate, ALLOCSET_DEFAULT_MINSIZE,
- ALLOCSET_DEFAULT_INITSIZE, maxBlockSize);
-}
-
/* ----------------
* CreateStandaloneExprContext
*
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index 925caadd2ce..01334d6012e 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1866,12 +1866,9 @@ static void
hash_agg_check_limits(AggState *aggstate)
{
uint64 ngroups = aggstate->hash_ngroups_current;
- Size meta_mem = MemoryContextMemAllocated(aggstate->hash_metacxt,
- true);
- Size entry_mem = MemoryContextMemAllocated(aggstate->hash_tuplescxt,
- true);
- Size tval_mem = MemoryContextMemAllocated(aggstate->hashcontext->ecxt_per_tuple_memory,
- true);
+ Size meta_mem = MemoryContextGetPool(aggstate->hash_metacxt)->allocated;
+ Size entry_mem = MemoryContextGetPool(aggstate->hash_tuplescxt)->allocated;
+ Size tval_mem = MemoryContextGetPool(aggstate->hashcontext->ecxt_per_tuple_memory)->allocated;
Size total_mem = meta_mem + entry_mem + tval_mem;
bool do_spill = false;
@@ -1998,13 +1995,13 @@ hash_agg_update_metrics(AggState *aggstate, bool from_tape, int npartitions)
static void
hash_create_memory(AggState *aggstate)
{
- Size maxBlockSize = ALLOCSET_DEFAULT_MAXSIZE;
-
/*
* The hashcontext's per-tuple memory will be used for byref transition
* values and returned by AggCheckCallContext().
*/
- aggstate->hashcontext = CreateWorkExprContext(aggstate->ss.ps.state);
+ aggstate->hashcontext = CreateExprContext(aggstate->ss.ps.state);
+ MemoryContextCreatePool(aggstate->hashcontext->ecxt_per_tuple_memory,
+ work_mem * (Size) 1024);
/*
* The meta context will be used for the bucket array of
@@ -2017,6 +2014,7 @@ hash_create_memory(AggState *aggstate)
aggstate->hash_metacxt = AllocSetContextCreate(aggstate->ss.ps.state->es_query_cxt,
"HashAgg meta context",
ALLOCSET_DEFAULT_SIZES);
+ MemoryContextCreatePool(aggstate->hash_metacxt, work_mem * (Size) 1024);
/*
* The hash entries themselves, which include the grouping key
@@ -2025,29 +2023,12 @@ hash_create_memory(AggState *aggstate)
* entire hash table is reset. The bump allocator is faster for
* allocations and avoids wasting space on the chunk header or
* power-of-two allocations.
- *
- * Like CreateWorkExprContext(), use smaller sizings for smaller work_mem,
- * to avoid large jumps in memory usage.
- */
-
- /*
- * Like CreateWorkExprContext(), use smaller sizings for smaller work_mem,
- * to avoid large jumps in memory usage.
*/
- maxBlockSize = pg_prevpower2_size_t(work_mem * (Size) 1024 / 16);
-
- /* But no bigger than ALLOCSET_DEFAULT_MAXSIZE */
- maxBlockSize = Min(maxBlockSize, ALLOCSET_DEFAULT_MAXSIZE);
-
- /* and no smaller than ALLOCSET_DEFAULT_INITSIZE */
- maxBlockSize = Max(maxBlockSize, ALLOCSET_DEFAULT_INITSIZE);
aggstate->hash_tuplescxt = BumpContextCreate(aggstate->ss.ps.state->es_query_cxt,
"HashAgg hashed tuples",
- ALLOCSET_DEFAULT_MINSIZE,
- ALLOCSET_DEFAULT_INITSIZE,
- maxBlockSize);
-
+ ALLOCSET_DEFAULT_SIZES);
+ MemoryContextCreatePool(aggstate->hash_tuplescxt, work_mem * (Size) 1024);
}
/*
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 33bbdbfeffb..1e72fbef6f9 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -653,7 +653,6 @@ extern void end_tup_output(TupOutputState *tstate);
extern EState *CreateExecutorState(void);
extern void FreeExecutorState(EState *estate);
extern ExprContext *CreateExprContext(EState *estate);
-extern ExprContext *CreateWorkExprContext(EState *estate);
extern ExprContext *CreateStandaloneExprContext(void);
extern void FreeExprContext(ExprContext *econtext, bool isCommit);
extern void ReScanExprContext(ExprContext *econtext);
--
2.43.0
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-03 19:24 ` Jeff Davis <pgsql@j-davis.com>
1 sibling, 0 replies; 29+ messages in thread
From: Jeff Davis @ 2026-04-03 19:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Scott Carey <scott.carey@algonomy.com>; +Cc: David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 07:04 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 07:23 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Re: Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-04-03 04:19 ` Frank Heikens <frank@elevarq.com>
1 sibling, 0 replies; 29+ messages in thread
From: Frank Heikens @ 2026-04-03 04:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Carey <scott.carey@algonomy.com>; Jeff Davis <pgsql@j-davis.com>; David Rowley <dgrowleyml@gmail.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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;
Attachments:
[application/octet-stream] 0001-Throttle-hash_agg_check_limits-to-avoid-O-N-2-contex.patch (4.1K, ../../540A0AD2-7CAA-4535-82F7-FC9055437B20@elevarq.com/3-0001-Throttle-hash_agg_check_limits-to-avoid-O-N-2-contex.patch)
download | inline diff:
From 3c8e3e34dc79109394b948330dfa4e32666b57e0 Mon Sep 17 00:00:00 2001
From: Frank Heikens <fheikens@users.noreply.github.com>
Date: Thu, 2 Apr 2026 21:01:19 -0700
Subject: [PATCH] Throttle hash_agg_check_limits to avoid O(N^2) context
traversal
hash_agg_check_limits() calls MemoryContextMemAllocated() with
recurse=true after every new hash group addition. This traverses
all child memory contexts to sum their mem_allocated, which is O(C)
where C is the number of child contexts.
Some aggregate transition functions create per-group child contexts.
For example, array_append (when used as an aggregate SFUNC) creates
an expanded-array object with a private AllocSet for each group.
With N groups the total traversal cost is O(N * C) = O(N^2), which
becomes the dominant cost once tens of thousands of groups are
present in the hash table.
On a reproducer with 50K groups using a user-defined array_agg
with array_append as SFUNC, the unthrottled check accounts for
roughly 97% of the query time: ~5 seconds versus ~150 ms for the
actual aggregate computation.
Fix by throttling the recursive memory check: once the group count
exceeds 1024, only perform the full check every 1024 new groups.
This bounds the spill-detection latency to at most 1024 groups'
worth of memory while keeping the per-check overhead constant.
Benchmarks on 50K groups: 4.6 s -> 152 ms (30x).
Benchmarks on 100K groups: 35 s -> 2.9 s (12x).
Benchmarks on 500K groups: 672 s -> 1.7 s (395x).
No regressions observed for built-in aggregates, non-array aggregates,
or batched (low work_mem) workloads. Spill behavior (batch count,
disk usage) is unchanged.
---
src/backend/executor/nodeAgg.c | 38 +++++++++++++++++++++++++++-------
1 file changed, 31 insertions(+), 7 deletions(-)
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index 925caadd2ce..9a23dedeccd 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1861,20 +1861,44 @@ hash_agg_set_limits(double hashentrysize, double input_groups, int used_bits,
* After adding a new group to the hash table, check whether we need to enter
* spill mode. Allocations may happen without adding new groups (for instance,
* if the transition state size grows), so this check is imperfect.
+ *
+ * Note: MemoryContextMemAllocated with recurse=true traverses all child
+ * contexts, making it O(C) where C is the child count. Some aggregate
+ * transition functions create per-group child contexts (for example,
+ * array_append creates expanded-array objects each owning a private
+ * AllocSet). Calling this function after every new group would then be
+ * O(N*C) ≈ O(N^2) total, which becomes the dominant cost with tens of
+ * thousands of groups. We avoid that by throttling: once there are more
+ * than 1024 groups, we only run the full check every 1024th group. This
+ * caps the overshoot to at most 1024 groups' worth of memory before spill
+ * mode is entered.
*/
static void
hash_agg_check_limits(AggState *aggstate)
{
uint64 ngroups = aggstate->hash_ngroups_current;
- Size meta_mem = MemoryContextMemAllocated(aggstate->hash_metacxt,
- true);
- Size entry_mem = MemoryContextMemAllocated(aggstate->hash_tuplescxt,
- true);
- Size tval_mem = MemoryContextMemAllocated(aggstate->hashcontext->ecxt_per_tuple_memory,
- true);
- Size total_mem = meta_mem + entry_mem + tval_mem;
+ Size meta_mem;
+ Size entry_mem;
+ Size tval_mem;
+ Size total_mem;
bool do_spill = false;
+ /*
+ * Throttle the expensive recursive MemoryContextMemAllocated calls.
+ * Below 1024 groups the quadratic cost is negligible; above that we
+ * check every 1024 groups to keep the overhead linear.
+ */
+ if (ngroups > 1024 && (ngroups & 0x3FF) != 0)
+ return;
+
+ meta_mem = MemoryContextMemAllocated(aggstate->hash_metacxt,
+ true);
+ entry_mem = MemoryContextMemAllocated(aggstate->hash_tuplescxt,
+ true);
+ tval_mem = MemoryContextMemAllocated(aggstate->hashcontext->ecxt_per_tuple_memory,
+ true);
+ total_mem = meta_mem + entry_mem + tval_mem;
+
#ifdef USE_INJECTION_POINTS
if (ngroups >= 1000)
{
--
2.33.0
[text/markdown] BENCHMARK_SUMMARY.md (1.7K, ../../540A0AD2-7CAA-4535-82F7-FC9055437B20@elevarq.com/4-BENCHMARK_SUMMARY.md)
download | inline:
# Benchmark summary
PG 19devel, aarch64-darwin, debug build (-O0, cassert).
All runs: max_parallel_workers_per_gather = 0.
## Tom's reproducer (50K groups × 4 elems, 200K rows)
| Test | Unpatched | Patched | Change |
|------|:---------:|:-------:|:------:|
| custom array_agg, 200MB (run 1) | 4,563 ms | 155 ms | 29x faster |
| custom array_agg, 200MB (run 2) | 5,025 ms | 152 ms | 33x faster |
| custom array_agg, 200MB (run 3) | 6,451 ms | 152 ms | 42x faster |
| custom array_agg, 4MB (run 1) | 388 ms | 283 ms | 1.4x faster |
| built-in array_agg, 200MB | 102 ms | 103 ms | unchanged |
Plan structure, batch count, and memory usage: identical.
## Synthetic workloads (5M total elements)
| Workload | work_mem | Unpatched | Patched | Change |
|----------|:--------:|:---------:|:-------:|:------:|
| 100K grp × 50 elem | 256MB | 35.0 s | 2.9 s | 12x |
| 100K grp × 50 elem | 4MB | 6.1 s | 5.9 s | — |
| 10K grp × 500 elem | 256MB | 2.7 s | 2.5 s | — |
| 10K grp × 500 elem | 4MB | 4.5 s | 4.5 s | — |
| 1K grp × 5K elem | 256MB | 2.4 s | 2.5 s | — |
| 500K grp × 2 elem | 256MB | 672 s | 1.7 s | 395x |
| 500K grp × 2 elem | 4MB | 5.4 s | 1.8 s | 3x |
## Non-array aggregates (unchanged)
| Aggregate | Unpatched | Patched |
|-----------|:---------:|:-------:|
| count(*) 100K groups | 1.9 s | 1.9 s |
| sum() 100K groups | 1.9 s | 1.9 s |
| string_agg 10K groups | 0.6 s | 0.6 s |
| built-in array_agg 256MB | 2.9 s | 2.9 s |
## Spill Behavior (work_mem = 4MB, 100K groups)
| Metric | Unpatched | Patched |
|--------|:---------:|:-------:|
| Batches | 61 | 61 |
| Memory Usage | 8,547 kB | 8,753 kB |
| Disk Usage | 143,872 kB | 143,440 kB |
^ permalink raw reply [nested|flat] 29+ messages in thread
end of thread, other threads:[~2026-05-05 01:27 UTC | newest]
Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-31 09:28 Significant performance issues with array_agg() + HashAggregate plans on Postgres 17 Scott Carey <scott.carey@algonomy.com>
2026-03-31 12:03 ` David Rowley <dgrowleyml@gmail.com>
2026-03-31 17:55 ` Scott Carey <scott.carey@algonomy.com>
2026-03-31 18:06 ` Scott Carey <scott.carey@algonomy.com>
2026-03-31 19:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-03-31 22:48 ` David Rowley <dgrowleyml@gmail.com>
2026-04-01 07:18 ` Scott Carey <scott.carey@algonomy.com>
2026-04-01 11:12 ` David Rowley <dgrowleyml@gmail.com>
2026-04-01 07:04 ` Scott Carey <scott.carey@algonomy.com>
2026-04-01 13:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-01 17:50 ` Scott Carey <scott.carey@algonomy.com>
2026-04-02 07:23 ` Scott Carey <scott.carey@algonomy.com>
2026-04-02 17:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-02 19:08 ` Scott Carey <scott.carey@algonomy.com>
2026-04-02 22:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 19:36 ` Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-04-03 20:01 ` Jeff Davis <pgsql@j-davis.com>
2026-04-04 00:21 ` David Rowley <dgrowleyml@gmail.com>
2026-04-04 12:18 ` Tomas Vondra <tomas@vondra.me>
2026-04-11 02:09 ` David Rowley <dgrowleyml@gmail.com>
2026-04-07 21:58 ` Jeff Davis <pgsql@j-davis.com>
2026-04-11 02:42 ` David Rowley <dgrowleyml@gmail.com>
2026-04-14 20:42 ` Jeff Davis <pgsql@j-davis.com>
2026-04-20 04:40 ` David Rowley <dgrowleyml@gmail.com>
2026-05-05 01:27 ` Jeff Davis <pgsql@j-davis.com>
2026-04-03 19:24 ` Jeff Davis <pgsql@j-davis.com>
2026-04-03 04:19 ` Frank Heikens <frank@elevarq.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox