Allow join removal in some cases involving a left join to a subquery.
We can remove a left join to a relation if the relation's output is
provably distinct for the columns involved in the join clause (considering
only equijoin clauses) and the relation supplies no variables needed above
the join. Previously, the join removal logic could only prove distinctness
by reference to unique indexes of a table. This patch extends the logic
to consider subquery relations, wherein distinctness might be proven by
reference to GROUP BY, DISTINCT, etc.
We actually already had some code to check that a subquery's output was
provably distinct, but it was hidden inside pathnode.c; which was a pretty
bad place for it really, since that file is mostly boilerplate Path
construction and comparison. Move that code to analyzejoins.c, which is
arguably a more appropriate location, and is certainly the site of the
new usage for it.
David Rowley, reviewed by Simon Riggs
Branch
------
master
Details
-------
http://git.postgresql.org/pg/commitdiff/f15821eefd70941d4ec5bd77d75c1da5534ba6b2
Modified Files
--------------
src/backend/optimizer/plan/analyzejoins.c | 295 +++++++++++++++++++++++++++--
src/backend/optimizer/util/pathnode.c | 192 ++-----------------
src/include/optimizer/planmain.h | 2 +
src/test/regress/expected/join.out | 79 ++++++++
src/test/regress/sql/join.sql | 35 ++++
5 files changed, 417 insertions(+), 186 deletions(-)