Horton

Re: Ignored join clause

Andrew Gierth <andrew@tao11.riddles.org.uk>

View thread View on postgr.es
From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Andreas Karlsson <andreas@proxel.se>
Cc: pgsql-bugs@lists.postgresql.org
Date: 2018-04-19T11:59:40Z
Lists: pgsql-bugs 
>>>>> "Andrew" == Andrew Gierth <andrew@tao11.riddles.org.uk> writes:

 Andrew> My suspicion is that this is an interaction between lateral and
 Andrew> join reordering. Looking into it further.

I think I was wrong, and that in fact this is a much more general
problem which amounts to a lack of communication between
get_joinrel_parampathinfo and extract_actual_join_clauses.

When we build the hash join path between q1 and q2,
get_joinrel_parampathinfo adds the q1.x=t.x clause to the
restrict_clauses list, but it doesn't distinguish it in any way from the
clauses that were there already.

Later when building the final Plan node for the hash join, we call
extract_actual_join_clauses to determine which clauses are join clauses
rather than filters. But this only looks at the RestrictInfo's
is_pushed_down field, and in this case that's wrong; is_pushed_down
isn't set, because the condition really was a join clause at the place
where it was originally written, but the condition has now been moved
from its original place by the parameterization and is effectively
pushed down even though it's not marked as such. So it ends up on the
wrong list.

-- 
Andrew (irc:RhodiumToad)

Commits

  1. Tweak a couple of planner APIs to save recalculating join relids.

  2. Change more places to be less trusting of RestrictInfo.is_pushed_down.

  3. Fix broken extract_actual_join_clauses call in 9.6 postgres_fdw.

  4. Fix incorrect handling of join clauses pushed into parameterized paths.

  5. Handle restriction clause lists more uniformly in postgres_fdw.