Skip site navigation (1) Skip section navigation (2)

pgsql: Weaken the planner's tests for relevant joinclauses.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Weaken the planner's tests for relevant joinclauses.
Date: 2012-04-13 20:07:36
Message-ID: E1SImmO-0001tv-Dz@gemulon.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-committers
Weaken the planner's tests for relevant joinclauses.

We should be willing to cross-join two small relations if that allows us
to use an inner indexscan on a large relation (that is, the potential
indexqual for the large table requires both smaller relations).  This
worked in simple cases but fell apart as soon as there was a join clause
to a fourth relation, because the existence of any two-relation join clause
caused the planner to not consider clauseless joins between other base
relations.  The added regression test shows an example case adapted from
a recent complaint from Benoit Delbosc.

Adjust have_relevant_joinclause, have_relevant_eclass_joinclause, and
has_relevant_eclass_joinclause to consider that a join clause mentioning
three or more relations is sufficient grounds for joining any subset of
those relations, even if we have to do so via a cartesian join.  Since such
clauses are relatively uncommon, this shouldn't affect planning speed on
typical queries; in fact it should help a bit, because the latter two
functions in particular get significantly simpler.

Although this is arguably a bug fix, I'm not going to risk back-patching
it, since it might have currently-unforeseen consequences.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/e3ffd05b02468b1a53de31a322cedf195576a625

Modified Files
--------------
src/backend/optimizer/path/equivclass.c |   96 ++++++-------------------------
src/backend/optimizer/path/joinrels.c   |   14 ++---
src/backend/optimizer/util/joininfo.c   |   24 ++++++--
src/test/regress/expected/join.out      |   51 ++++++++++++++++
src/test/regress/sql/join.sql           |   18 ++++++
5 files changed, 110 insertions(+), 93 deletions(-)

pgsql-committers by date

Next:From: Robert HaasDate: 2012-04-13 20:43:26
Subject: pgsql: Add Peter Geoghegan as additional author of pg_stat_statements.
Previous:From: Guillaume LelargeDate: 2012-04-13 19:57:58
Subject: Re: [COMMITTERS] pgsql: Add new replication mode synchronous_commit = 'write'.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group