On Wed, 11 Aug 2021 at 00:10, Yura Sokolov <y.sokolov@postgrespro.ru> wrote:
> Attached v2.
Eyeballing this it looks fine, but I was a little nervous backpatching
without testing it properly, so I ended up provisioning a machine with
256GB and doing a round of testing.
I just created the most simple table I could:
create table a (a bigserial, b int);
and inserted 2^31 rows.
insert into a (b) values(1);
insert into a (b) select b from a; -- repeated until I got 2^31 rows.
set work_mem = '256GB';
set max_parallel_workers_per_gather = 0;
I could recreate the issue described with the following query:
explain (analyze , timing off) select a from a group by a;
After watching perf top for a while it switched to:
98.90% postgres [.] tuplehash_grow
0.36% [kernel] [k] change_p4d_range
0.24% postgres [.] LookupTupleHashEntry
0.09% postgres [.] tts_minimal_store_tuple
0.07% [kernel] [k] vm_normal_page
0.02% [kernel] [k] __softirqentry_text_start
0.02% postgres [.] heap_fill_tuple
0.02% postgres [.] AllocSetAlloc
After patching I got:
explain (analyze , timing off) select a from a group by a;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
HashAggregate (cost=35149810.71..53983243.28 rows=1883343257
width=8) (actual rows=2147483648 loops=1)
Group Key: a
Batches: 1 Memory Usage: 201334801kB
-> Seq Scan on a (cost=0.00..30441452.57 rows=1883343257 width=8)
(actual rows=2147483648 loops=1)
Planning Time: 0.105 ms
Execution Time: 2173480.905 ms
(6 rows)
Time: 2173482.166 ms (36:13.482)
And, since I only had 256GB of memory on this machine and couldn't
really do 2^32 groups, I dropped SH_FILLFACTOR to 0.4 and
SH_MAX_FILLFACTOR to 0.48 and tried again to ensure I got the hash
table full message:
postgres=# explain (analyze on , timing off) select a from a group by a;
ERROR: hash table size exceeded
Time: 1148554.672 ms (19:08.555)
After doing that, I felt a bit better about batch-patching it, so I did.
Thanks for the patch.
David
