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@muzzylane.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)