Re: Poor performance on 9.4.4 when matching multiple columns from left side of LATERAL join

[Steven Grimm <sgrimm@xxxxxxxxxxxxxx>]

David Rowley

November 14, 2015 at 12:32 AM

The problem is that the optimizer is unable to use hash join or merge joins when you have the IN() condition as the join condition, the reason for this is that you're effectively saying to join on any of 3 conditions: settings.owner_id = mid.id1 OR settings.owner_id = mid.id2 OR settings.owner_id = mid.id3. If you think how a hash join works then 1 hash table is no good here, as you effectively have 3 possible keys for the hash table, the executor would have to build 3 tables to make that possible, but we only ever build 1 in PostgreSQL. As you may know, a hash table is a very efficient data structure for key value lookups, but there can only be a single key.  Merge join has the same problem because it's only possible to have a single sort order.

Thanks, that's the key thing I was missing. I was expecting it to see that there were exactly three conditions (as opposed to a variable number) and evaluate "build 3 hash tables" as a possible execution plan. Knowing that it doesn't do that completely explains the behavior I'm seeing.

It is puzzling that if, as suggested by someone else in the thread, I expand IN(a,b,c) to (x = a OR x = b OR x = c) it gets substantially faster, though still obviously falls afoul of the problem you describe above (~4 seconds instead of ~6 seconds). Should those two be equivalent?

-Steve