From: Tom Lane <tgl@sss.pgh.pa.us>
To: moj@dshare.de
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19030: Hash join leads to extremely high memory usage
In-reply-to: <19030-944dd78d7ef94c0f@postgresql.org>
References: <19030-944dd78d7ef94c0f@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Sat, 23 Aug 2025 15:30:13 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <51460.1755988143.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 23 Aug 2025 18:29:03 -0400
Message-ID: <51461.1755988143@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/51461.1755988143%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> Description
> - Two tables, left join
> - The left table has significantly fewer rows than the right table
> - The left table has very large rows (many columns with high memory usag=
e)
> - The left table has many null values in the join column
> - A hash join is used
> - The hash node is built from the left table
> - The query results in extremely high memory usage (100x work_mem in the
> example, > 1000x in real case)

If this is specific to the case of many null join values, it's a known
problem that I have a patch in the queue for [1].

On your example, I get this on HEAD:


                                                             QUERY PLAN   =
                                                          =

--------------------------------------------------------------------------=
----------------------------------------------------------
 Hash Right Join  (cost=3D459884.00..1367112.00 rows=3D10000000 width=3D65=
) (actual time=3D2557.730..11727.429 rows=3D10000000.00 loops=3D1)
   Hash Cond: (right_.c =3D left_.c)
   Buffers: shared read=3D201216, temp read=3D131098 written=3D131098
   ->  Seq Scan on right_  (cost=3D0.00..283520.00 rows=3D20000000 width=3D=
8) (actual time=3D0.060..806.467 rows=3D20000000.00 loops=3D1)
         Buffers: shared read=3D83520
   ->  Hash  (cost=3D217696.00..217696.00 rows=3D10000000 width=3D65) (act=
ual time=3D2494.680..2494.680 rows=3D10000000.00 loops=3D1)
         Buckets: 131072 (originally 131072)  Batches: 16384 (originally 2=
56)  Memory Usage: 440509kB
         Buffers: shared read=3D117696, temp written=3D46737
         ->  Seq Scan on left_  (cost=3D0.00..217696.00 rows=3D10000000 wi=
dth=3D65) (actual time=3D0.096..481.139 rows=3D10000000.00 loops=3D1)
               Buffers: shared read=3D117696
 Planning:
   Buffers: shared hit=3D136 read=3D35
   Memory: used=3D21kB  allocated=3D32kB
 Planning Time: 0.703 ms
 Execution Time: 11946.100 ms
(15 rows)

and this with the aforesaid patch:

                                                             QUERY PLAN   =
                                                          =

--------------------------------------------------------------------------=
----------------------------------------------------------
 Hash Right Join  (cost=3D459884.00..1367112.00 rows=3D10000000 width=3D65=
) (actual time=3D1553.342..6589.352 rows=3D10000000.00 loops=3D1)
   Hash Cond: (right_.c =3D left_.c)
   Buffers: shared hit=3D188 read=3D201028, temp read=3D128626 written=3D1=
28626
   ->  Seq Scan on right_  (cost=3D0.00..283520.00 rows=3D20000000 width=3D=
8) (actual time=3D0.099..750.684 rows=3D20000000.00 loops=3D1)
         Buffers: shared hit=3D94 read=3D83426
   ->  Hash  (cost=3D217696.00..217696.00 rows=3D10000000 width=3D65) (act=
ual time=3D1551.739..1551.740 rows=3D5000000.00 loops=3D1)
         Buckets: 131072  Batches: 256  Memory Usage: 2906kB
         Buffers: shared hit=3D94 read=3D117602, temp written=3D93662
         ->  Seq Scan on left_  (cost=3D0.00..217696.00 rows=3D10000000 wi=
dth=3D65) (actual time=3D0.106..470.671 rows=3D10000000.00 loops=3D1)
               Buffers: shared hit=3D94 read=3D117602
 Planning:
   Memory: used=3D20kB  allocated=3D32kB
 Planning Time: 0.122 ms
 Execution Time: 6827.197 ms
(14 rows)

(Hmm, looking at this, it's apparent that the patch causes the
Hash node not to count the null-keyed rows in its EXPLAIN output.
On the one hand, that's an accurate reflection of how much went into
the hash table, but on the other hand it's pretty confusing.)

I doubt we'd consider back-patching such a change, but if you want
to see it happen for v19, you could help by reviewing/testing.

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/3061845.1746486714%40sss.pg=
h.pa.us