BUG #18128: a Parallel Hash started with batches 64 but increased to 262144 and FreeableMemory tanked

The following bug has been logged on the website:

Bug reference: 18128
Logged by: Mike Gingerich
Email address: mike(at)muzzylane(dot)com
PostgreSQL version: 13.12
Operating system: linux (AWS RDS)
Description:

April was much slower. March and May batches stayed at 64 instead of
increasing to 262144
Query analysis for April:

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=2290090.33..2761528.80 rows=1737 width=16) (actual
time=6411336.027..6524569.623 rows=30 loops=1)
Group Key: (date_trunc('day'::text, ua2s.create_date))
-> Gather Merge (cost=2290090.33..2742101.86 rows=3881044 width=12)
(actual time=6411273.614..6523404.855 rows=3913584 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=2289090.31..2293133.06 rows=1617102 width=12)
(actual time=6411253.070..6411479.085 rows=1304528 loops=3)
Sort Key: (date_trunc('day'::text, ua2s.create_date))
Sort Method: external merge Disk: 27088kB
Worker 0: Sort Method: external merge Disk: 28456kB
Worker 1: Sort Method: external merge Disk: 28856kB
-> Parallel Hash Join (cost=1569252.29..2067047.83
rows=1617102 width=12) (actual time=6147224.775..6410620.761 rows=1304528
loops=3)
Hash Cond: (a.id = assn.id)
-> Parallel Seq Scan on assignments a
(cost=0.00..425507.72 rows=4060827 width=8) (actual time=0.021..8557.091
rows=2555441 loops=3)
Filter: ((project_user_id = 9999) OR
(partner_user_id = 9999))
Rows Removed by Filter: 14801
-> Parallel Hash (cost=1539558.09..1539558.09
rows=1617376 width=24) (actual time=30937.596..30939.811 rows=1316407
loops=3)
Buckets: 65536 (originally 65536) Batches:
262144 (originally 64) Memory Usage: 6304kB
-> Parallel Hash Join
(cost=944695.82..1539558.09 rows=1617376 width=24) (actual
time=14668.291..15561.377 rows=1316407 loops=3)
Hash Cond: (att.assignment_id = assn.id)
-> Parallel Hash Join
(cost=698851.85..1257806.51 rows=1617376 width=16) (actual
time=11260.639..12985.989 rows=1316407 loops=3)
Hash Cond: (att.id =
u2att.attempt_id)
-> Parallel Seq Scan on attempts att
(cost=0.00..424067.99 rows=8157599 width=12) (actual time=0.198..1936.559
rows=6560058 loops=3)
-> Parallel Hash
(cost=670736.65..670736.65 rows=1617376 width=16) (actual
time=7734.105..7735.455 rows=1316407 loops=3)
Buckets: 131072 Batches: 64
Memory Usage: 4032kB
-> Parallel Hash Join
(cost=453869.58..670736.65 rows=1617376 width=16) (actual
time=5774.624..7359.779 rows=1316407 loops=3)
Hash Cond:
(ua2s.user_attempt_id = u2att.id)
-> Parallel Index Scan
using user_attempt_to_statuses_create_date_day_idx on
user_attempt_to_statuses ua2s (cost=0.57..155748.03 rows=1617376 width=12)
(actual time=0.034..522.963 rows=1316407 loops=3)
Index Cond:
((date_trunc('day'::text, create_date) >= '2023-04-01 00:00:00'::timestamp
without time zone) AND (date_trunc('day'::text, create_date) <= '2023-04-30
00:00:00'::timestamp without time zone))
-> Parallel Hash
(cost=307631.56..307631.56 rows=8412756 width=16) (actual
time=4666.965..4666.978 rows=6577064 loops=3)
Buckets: 131072
Batches: 512 Memory Usage: 3200kB
-> Parallel Seq
Scan on users_to_attempts u2att (cost=0.00..307631.56 rows=8412756
width=16) (actual time=0.013..1750.021 rows=6577064 loops=3)
-> Parallel Hash
(cost=179209.03..179209.03 rows=4061515 width=8) (actual
time=1354.336..1354.340 rows=2570242 loops=3)
Buckets: 131072 Batches: 128 Memory
Usage: 3456kB
-> Parallel Index Only Scan using
assignments_pkey on assignments assn (cost=0.43..179209.03 rows=4061515
width=8) (actual time=0.066..680.073 rows=2570242 loops=3)
Heap Fetches: 244022
Planning Time: 1.605 ms
Execution Time: 6524582.578 ms
(37 rows)