Optimize joins when the inner relation can be proven unique

Enterprise / PostgreSQL - Tom Lane [sss.pgh.pa.us] - 7 April 2017 22:20 EDT

If there can certainly be no more than one matching inner row for a given outer row, then the executor can move on to the next outer row as soon as it's found one match; there's no need to continue scanning the inner relation for this outer row. This saves useless scanning in nestloop and hash joins. In merge joins, it offers the opportunity to skip mark/restore processing, because we know we have not advanced past the first possible match for the next outer row.

Of course, the devil is in the details: the proof of uniqueness must depend only on joinquals (not otherquals), and if we want to skip mergejoin mark/restore then it must depend only on merge clauses. To avoid adding more planning overhead than absolutely necessary, the present patch errs in the conservative direction: there are cases where inner_unique or skip_mark_restore processing could be used, but it will not do so because it's not sure that the uniqueness proof depended only on "safe" clauses. This could be improved later.

David Rowley, reviewed and rather heavily editorialized on by me

Discussion: https://postgr.es/m/CAApHDvqF6Sw-TK98bW48TdtFJ+3a7D2mFyZ7++=D-RyPsL76gw@mail.gmail.com

9c7f522 Optimize joins when the inner relation can be proven unique.
contrib/citext/expected/citext.out | 2 +-
contrib/citext/expected/citext_1.out | 2 +-
contrib/postgres_fdw/expected/postgres_fdw.out | 69 +++---
src/backend/commands/explain.c | 17 ++
src/backend/executor/nodeHashjoin.c | 13 +-
src/backend/executor/nodeMergejoin.c | 56 +++--
src/backend/executor/nodeNestloop.c | 14 +-
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/outfuncs.c | 5 +
src/backend/nodes/readfuncs.c | 2 +
src/backend/optimizer/path/costsize.c | 132 ++++++-----
src/backend/optimizer/path/joinpath.c | 68 ++++--
src/backend/optimizer/plan/analyzejoins.c | 173 ++++++++++++++
src/backend/optimizer/plan/createplan.c | 31 ++-
src/backend/optimizer/util/pathnode.c | 32 +--
src/backend/optimizer/util/relnode.c | 8 +-
src/include/nodes/execnodes.h | 4 +
src/include/nodes/plannodes.h | 8 +
src/include/nodes/relation.h | 41 +++-
src/include/optimizer/cost.h | 16 +-
src/include/optimizer/pathnode.h | 8 +-
src/include/optimizer/planmain.h | 3 +
src/test/regress/expected/aggregates.out | 32 +--
src/test/regress/expected/equivclass.out | 22 +-
src/test/regress/expected/join.out | 309 ++++++++++++++++++++++++-
src/test/regress/sql/join.sql | 124 ++++++++++
26 files changed, 987 insertions(+), 206 deletions(-)

Upstream: git.postgresql.org


  • Share