pgsql: Extract restriction OR clauses whether or not they are indexable

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Extract restriction OR clauses whether or not they are indexable
Date: 2013-12-30 17:24:57
Message-ID: E1VxgaH-0001SM-C7@gemulon.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Extract restriction OR clauses whether or not they are indexable.

It's possible to extract a restriction OR clause from a join clause that
has the form of an OR-of-ANDs, if each sub-AND includes a clause that
mentions only one specific relation. While PG has been aware of that idea
for many years, the code previously only did it if it could extract an
indexable OR clause. On reflection, though, that seems a silly limitation:
adding a restriction clause can be a win by reducing the number of rows
that have to be filtered at the join step, even if we have to test the
clause as a plain filter clause during the scan. This should be especially
useful for foreign tables, where the change can cut the number of rows that
have to be retrieved from the foreign server; but testing shows it can win
even on local tables. Per a suggestion from Robert Haas.

As a heuristic, I made the code accept an extracted restriction clause
if its estimated selectivity is less than 0.9, which will probably result
in accepting extracted clauses just about always. We might need to tweak
that later based on experience.

Since the code no longer has even a weak connection to Path creation,
remove orindxpath.c and create a new file optimizer/util/orclauses.c.

There's some additional janitorial cleanup of now-dead code that needs
to happen, but it seems like that's a fit subject for a separate commit.

Branch
------
master

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

Modified Files
--------------
src/backend/optimizer/path/Makefile | 2 +-
src/backend/optimizer/path/allpaths.c | 11 -
src/backend/optimizer/path/indxpath.c | 3 +-
src/backend/optimizer/path/orindxpath.c | 187 -----------------
src/backend/optimizer/plan/planmain.c | 7 +
src/backend/optimizer/util/Makefile | 4 +-
src/backend/optimizer/util/orclauses.c | 343 +++++++++++++++++++++++++++++++
src/include/optimizer/orclauses.h | 21 ++
src/include/optimizer/paths.h | 6 -
src/test/regress/expected/join.out | 47 +++++
src/test/regress/sql/join.sql | 12 ++
11 files changed, 435 insertions(+), 208 deletions(-)

Browse pgsql-committers by date

  From Date Subject
Next Message Tom Lane 2013-12-30 17:50:42 pgsql: Remove dead code now that orindxpath.c is history.
Previous Message Andres Freund 2013-12-30 12:46:38 Re: [COMMITTERS] pgsql: Upgrade to Autoconf 2.69