join plan with unexpected var clauses
Hi,
At a customer we came across a curious plan (see attached testcase).
Given the testcase we see that the outer semi join tries to join the
outer with the inner table id columns, even though the middle table id
column is also there. Is this expected behavior?
The reason i'm asking is two-fold:
- the inner hash table now is bigger than i'd expect and has columns
that you would normally not select on.
- the middle join now projects the inner as result, which is quite
suprising and seems invalid from a SQL standpoint.
Plan:
Finalize Aggregate
Output: count(*)
-> Gather
Output: (PARTIAL count(*))
Workers Planned: 4
-> Partial Aggregate
Output: PARTIAL count(*)
-> Parallel Hash Semi Join
Hash Cond: (_outer.id3 = _inner.id2)
-> Parallel Seq Scan on public._outer
Output: _outer.id3, _outer.extra1
-> Parallel Hash
Output: middle.id1, _inner.id2
-> Parallel Hash Semi Join
Output: middle.id1, _inner.id2
Hash Cond: (middle.id1 = _inner.id2)
-> Parallel Seq Scan on public.middle
Output: middle.id1
-> Parallel Hash
Output: _inner.id2
-> Parallel Seq Scan on
public._inner
Output: _inner.id2
Kind regards,
Luc
Swarm64
Attachments:
Luc Vlaming <luc@swarm64.com> writes:
Given the testcase we see that the outer semi join tries to join the
outer with the inner table id columns, even though the middle table id
column is also there. Is this expected behavior?
I don't see anything greatly wrong with it. The planner has concluded
that _inner.id2 and middle.id1 are part of an equivalence class, so it
can form the top-level join by equating _outer.id3 to either of them.
AFAIR that choice is made at random --- there's certainly not any logic
that thinks about "well, the intermediate join output could be a bit
narrower if we choose this one instead of that one".
I think "made at random" actually boils down to "take the first usable
member of the equivalence class". If I switch around the wording of
the first equality condition:
... select 1 from _inner where middle.id1 = _inner.id2
then I get a plan where the top join uses middle.id1. However,
it's still propagating both middle.id1 and _inner.id2 up through
the bottom join, so that isn't buying anything efficiency-wise.
regards, tom lane