Unexpectedly huge memory usage (over 180x of work_mem) during hash join... confirmed by TopMemoryContext results (postgresql 13.10)

Hi,

During investigation of the OOM server crash (with more than 30GB available memory) I found it was triggered by a single query.

related settings:

work_mem=32MB

hash_mem_multiplier=1

max_parallel_workers_per_gather=3

Now the result of the investigation performed on an idle server with no other activity on it.

Now to results:

Query and explain (analyze, costs, buffers, timing) results:

explain (analyze, costs, buffers, timing) select * from employer_manager WHERE employer_id IN (
            SELECT employer_id
            FROM
              employer e
            WHERE
            EXISTS (
                SELECT 1
                FROM employer_service es
                     JOIN account_service_package_entry aspe
                       ON (aspe.service_package_id = es.employer_service_id)
                     JOIN account_service_event ase
                       ON (ase.account_service_package_entry_id = aspe.id)
                     JOIN vacancy v
                       ON (ase.object_id = v.vacancy_id)
                WHERE v.employer_id = e.employer_id AND es.price = 0
              )
          );

                                                                                                QUERY PLAN                                                                                                
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=12483578.55..12699754.67 rows=4951913 width=183) (actual time=402888.266..541201.083 rows=3456146 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   Buffers: shared hit=10227924, temp read=5404033 written=9746488
   ->  Parallel Hash Semi Join  (cost=12483478.55..12650135.54 rows=1597391 width=183) (actual time=402879.754..403927.356 rows=864036 loops=4)
         Hash Cond: (employer_manager.employer_id = v.employer_id)
         Buffers: shared hit=10227924, temp read=5404033 written=9746488
         ->  Parallel Seq Scan on employer_manager  (cost=0.00..61704.37 rows=3902987 width=183) (actual time=0.015..926.040 rows=3030730 loops=4)
               Buffers: shared hit=226745
         ->  Parallel Hash  (cost=12470696.28..12470696.28 rows=1173692 width=8) (actual time=400745.411..400757.615 rows=315704 loops=4)
               Buckets: 1048576  Batches: 8  Memory Usage: 14432kB
               Buffers: shared hit=10000933, temp read=5185403 written=9531604
               ->  Parallel Hash Semi Join  (cost=5419572.63..12470696.28 rows=1173692 width=8) (actual time=381755.186..400673.065 rows=315704 loops=4)
                     Hash Cond: (e.employer_id = v.employer_id)
                     Buffers: shared hit=10000933, temp read=5185403 written=9527768
                     ->  Parallel Seq Scan on employer e  (cost=0.00..65876.37 rows=2986447 width=4) (actual time=0.015..547.631 rows=2313235 loops=4)
                           Buffers: shared hit=360119
                     ->  Parallel Hash  (cost=4389870.76..4389870.76 rows=94549378 width=4) (actual time=300367.731..300370.699 rows=10515574 loops=4)
                           Buckets: 1048576 (originally 1048576)  Batches: 131072 (originally 512)  Memory Usage: 287136kB
                           Buffers: shared hit=9640814, temp read=4068338 written=7441540
                           ->  Parallel Hash Join  (cost=1522656.32..4389870.76 rows=94549378 width=4) (actual time=113906.860..121650.966 rows=10515574 loops=4)
                                 Hash Cond: (aspe.service_package_id = es.employer_service_id)
                                 Buffers: shared hit=9640814, temp read=3570214 written=3573964
                                 ->  Parallel Hash Join  (cost=1139337.57..3828356.61 rows=132999548 width=8) (actual time=87792.230..102392.631 rows=60107033 loops=4)
                                       Hash Cond: (ase.account_service_package_entry_id = aspe.id)
                                       Buffers: shared hit=8740464, temp read=2645196 written=2648224
                                       ->  Parallel Hash Join  (cost=669912.02..3179027.68 rows=132999548 width=8) (actual time=44539.790..74472.198 rows=60107033 loops=4)
                                             Hash Cond: (ase.object_id = v.vacancy_id)
                                             Buffers: shared hit=7347306, temp read=1656073 written=1657576
                                             ->  Parallel Seq Scan on account_service_event ase  (cost=0.00..2275263.13 rows=172006123 width=8) (actual time=0.011..20798.324 rows=133306300 loops=4)
                                                   Buffers: shared hit=5552019
                                             ->  Parallel Hash  (cost=414267.15..414267.15 rows=23473845 width=8) (actual time=7923.194..7923.195 rows=18198520 loops=4)
                                                   Buckets: 1048576  Batches: 128  Memory Usage: 30528kB
                                                   Buffers: shared hit=1795287, temp written=247932
                                                   ->  Parallel Seq Scan on vacancy v  (cost=0.00..414267.15 rows=23473845 width=8) (actual time=0.011..5221.328 rows=18198520 loops=4)
                                                         Buffers: shared hit=1795287
                                       ->  Parallel Hash  (cost=297333.92..297333.92 rows=15801812 width=8) (actual time=4352.140..4352.141 rows=12280833 loops=4)
                                             Buckets: 1048576  Batches: 128  Memory Usage: 23296kB
                                             Buffers: shared hit=1393158, temp written=167644
                                             ->  Parallel Seq Scan on account_service_package_entry aspe  (cost=0.00..297333.92 rows=15801812 width=8) (actual time=0.015..2520.045 rows=12280833 loops=4)
                                                   Buffers: shared hit=1393158
                                 ->  Parallel Hash  (cost=258846.24..258846.24 rows=11429325 width=4) (actual time=3424.467..3424.468 rows=8908616 loops=4)
                                       Buckets: 1048576  Batches: 64  Memory Usage: 30080kB
                                       Buffers: shared hit=900350, temp written=103252
                                       ->  Parallel Seq Scan on employer_service es  (cost=0.00..258846.24 rows=11429325 width=4) (actual time=0.010..2087.361 rows=8908616 loops=4)
                                             Filter: (price = 0)
                                             Rows Removed by Filter: 3624132
                                             Buffers: shared hit=900350
 Planning:
   Buffers: shared hit=236
 Planning Time: 25.494 ms
 Execution Time: 541359.085 ms

There are no big missestimations in rows, but during moments of high memory usage each parallel worker used over 5GB of RAM (so total over 20GB of RAM 3 worker + leader).

Memory context results of single worker:

TopMemoryContext: 3357032 total in 16 blocks; 46064 free (16 chunks); 3310968 used
...
  TopPortalContext: 8192 total in 1 blocks; 7656 free (0 chunks); 536 used
    PortalHoldContext: 24632 total in 2 blocks; 7384 free (0 chunks); 17248 used
    PortalContext: 540672 total in 69 blocks; 8568 free (10 chunks); 532104 used:
      ExecutorState: 1082567568 total in 124072 blocks; 4073608 free (7018 chunks); 1078493960 used
        HashTableContext: 74808 total in 5 blocks; 25008 free (3 chunks); 49800 used
          HashBatchContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        HashTableContext: 74808 total in 5 blocks; 25056 free (3 chunks); 49752 used
          HashBatchContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        HashTableContext: 40960 total in 3 blocks; 12328 free (6 chunks); 28632 used
          HashBatchContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        HashTableContext: 4156883208 total in 124074 blocks; 146264 free (30 chunks); 4156736944 used
          HashBatchContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        HashTableContext: 8192 total in 1 blocks; 4376 free (7 chunks); 3816 used
          HashBatchContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
        ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
...
        ExprContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
  Relcache by OID: 16384 total in 2 blocks; 1424 free (2 chunks); 14960 used
  CacheMemoryContext: 1163704 total in 14 blocks; 197832 free (0 chunks); 965872 used
    relation rules: 24576 total in 6 blocks; 4896 free (1 chunks); 19680 used: pg_settings
    index info: 2048 total in 2 blocks; 528 free (1 chunks); 1520 used: pg_toast_2619_index
....
    48 more child contexts containing 91136 total in 83 blocks; 27632 free (45 chunks); 63504 used
  PrivateRefCount: 8192 total in 1 blocks; 2616 free (0 chunks); 5576 used
  MdSmgr: 8192 total in 1 blocks; 3880 free (2 chunks); 4312 used
  LOCALLOCK hash: 32768 total in 3 blocks; 16824 free (8 chunks); 15944 used
  Timezones: 104120 total in 2 blocks; 2616 free (0 chunks); 101504 used
  ErrorContext: 8192 total in 1 blocks; 7928 free (4 chunks); 264 used
Grand total: 5245755200 bytes in 248596 blocks; 4995280 free (7224 chunks); 5240759920 used