Allow join removal in some cases involving a left join to a subquery

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 15 July 2014 20:12 UTC

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

f15821e Allow join removal in some cases involving a left join to a subquery.
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(-)

Upstream: git.postgresql.org


  • Share