From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-hackers(at)postgreSQL(dot)org |
Cc: | David Hinkle <hinkle(at)cipafilter(dot)com> |
Subject: | Avoiding OOM in a hash join with many duplicate inner keys |
Date: | 2017-02-16 19:02:41 |
Message-ID: | 32013.1487271761@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
The planner doesn't currently worry about work_mem restrictions when
planning a hash join, figuring that the executor should be able to
subdivide the data arbitrarily finely by splitting buckets at runtime.
However there's a thread here:
https://www.postgresql.org/message-id/flat/CACw4T0p4Lzd6VpwptxgPgoTMh2dEKTQBGu7NTaJ1%2BA0PRx1BGg%40mail.gmail.com
exhibiting a case where a hash join was chosen even though a single
value accounts for three-quarters of the inner relation. Bucket
splitting obviously can never separate multiple instances of the
same value, so this choice forced the executor to try to load
three-quarters of the (very large) inner relation into memory at once;
unsurprisingly, it failed.
To fix this, I think we need to discourage use of hash joins whenever
a single bucket is predicted to exceed work_mem, as in the attached
draft patch. The patch results in changing from hash to merge join
in one regression test case, which is fine; that case only cares about
the join order not the types of the joins.
This might be overly aggressive, because it will pretty much shut off
any attempt to use hash joining on a large inner relation unless we
have statistics for it (and those stats are favorable). But having
seen this example, I think we need to be worried.
I'm inclined to treat this as a bug and back-patch it, but I wonder if
anyone is concerned about possible plan destabilization in the back
branches.
regards, tom lane
Attachment | Content-Type | Size |
---|---|---|
check-hash-bucket-size-against-work_mem.patch | text/x-diff | 4.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2017-02-16 19:11:23 | Re: Avoiding OOM in a hash join with many duplicate inner keys |
Previous Message | Corey Huinker | 2017-02-16 18:58:19 | Re: COPY IN/BOTH vs. extended query mode |