Skip to content

Commit f343a88

Browse files
tglsfdctglsfdc
committed
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.
1 parent 47f5026 commit f343a88

File tree

11 files changed

+435
-208
lines changed

11 files changed

+435
-208
lines changed

‎src/backend/optimizer/path/Makefile

+1-1
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,6 @@ top_builddir = ../../../..
1313
include $(top_builddir)/src/Makefile.global
1414

1515
OBJS = allpaths.o clausesel.o costsize.o equivclass.o indxpath.o \
16-
joinpath.o joinrels.o orindxpath.o pathkeys.o tidpath.o
16+
joinpath.o joinrels.o pathkeys.o tidpath.o
1717

1818
include $(top_srcdir)/src/backend/common.mk

‎src/backend/optimizer/path/allpaths.c

-11
Original file line numberDiff line numberDiff line change
@@ -362,17 +362,6 @@ set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
362362

363363
/* Mark rel with estimated output rows, width, etc */
364364
set_baserel_size_estimates(root, rel);
365-
366-
/*
367-
* Check to see if we can extract any restriction conditions from join
368-
* quals that are OR-of-AND structures. If so, add them to the rel's
369-
* restriction list, and redo the above steps.
370-
*/
371-
if (create_or_index_quals(root, rel))
372-
{
373-
check_partial_indexes(root, rel);
374-
set_baserel_size_estimates(root, rel);
375-
}
376365
}
377366

378367
/*

‎src/backend/optimizer/path/indxpath.c

+2-1
Original file line numberDiff line numberDiff line change
@@ -1354,7 +1354,8 @@ choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
13541354
* we can remove this limitation. (But note that this also defends
13551355
* against flat-out duplicate input paths, which can happen because
13561356
* match_join_clauses_to_index will find the same OR join clauses that
1357-
* create_or_index_quals has pulled OR restriction clauses out of.)
1357+
* extract_restriction_or_clauses has pulled OR restriction clauses out
1358+
* of.)
13581359
*
13591360
* For the same reason, we reject AND combinations in which an index
13601361
* predicate clause duplicates another clause. Here we find it necessary

‎src/backend/optimizer/path/orindxpath.c

-187
This file was deleted.

‎src/backend/optimizer/plan/planmain.c

+7
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
*/
2121
#include "postgres.h"
2222

23+
#include "optimizer/orclauses.h"
2324
#include "optimizer/pathnode.h"
2425
#include "optimizer/paths.h"
2526
#include "optimizer/placeholder.h"
@@ -194,6 +195,12 @@ query_planner(PlannerInfo *root, List *tlist,
194195
*/
195196
create_lateral_join_info(root);
196197

198+
/*
199+
* Look for join OR clauses that we can extract single-relation
200+
* restriction OR clauses from.
201+
*/
202+
extract_restriction_or_clauses(root);
203+
197204
/*
198205
* We should now have size estimates for every actual table involved in
199206
* the query, and we also know which if any have been deleted from the

‎src/backend/optimizer/util/Makefile

+2-2
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@ subdir = src/backend/optimizer/util
1212
top_builddir = ../../../..
1313
include $(top_builddir)/src/Makefile.global
1414

15-
OBJS = clauses.o joininfo.o pathnode.o placeholder.o plancat.o predtest.o \
16-
relnode.o restrictinfo.o tlist.o var.o
15+
OBJS = clauses.o joininfo.o orclauses.o pathnode.o placeholder.o \
16+
plancat.o predtest.o relnode.o restrictinfo.o tlist.o var.o
1717

1818
include $(top_srcdir)/src/backend/common.mk

0 commit comments

Comments
 (0)