diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 0ef70ad7f1..df6d706770 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -3498,8 +3498,24 @@ bool relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel, List *restrictlist, List *exprlist, List *oprlist) +{ + return relation_has_unique_index_ext(root, rel, restrictlist, + exprlist, oprlist, NULL); +} + +/* + * relation_has_unique_index_ext + * Used as the relation_has_unique_index_for, but if extra_clauses doesn't NULL, + * return baserestrictinfo clauses which were used to derive uniqueness. + */ +bool +relation_has_unique_index_ext(PlannerInfo *root, RelOptInfo *rel, + List *restrictlist, + List *exprlist, List *oprlist, + List **extra_clauses) { ListCell *ic; + List *exprs; Assert(list_length(exprlist) == list_length(oprlist)); @@ -3554,6 +3570,8 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel, IndexOptInfo *ind = (IndexOptInfo *) lfirst(ic); int c; + exprs = NIL; + /* * If the index is not unique, or not immediately enforced, or if it's * a partial index that doesn't match the query, it's useless here. @@ -3600,7 +3618,16 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel, if (match_index_to_operand(rexpr, c, ind)) { + MemoryContext oldContext = MemoryContextSwitchTo(root->planner_cxt); + matched = true; /* column is unique */ + + /* Store a filter for subsequent operations. */ + if (bms_is_empty(rinfo->left_relids) || + bms_is_empty(rinfo->right_relids)) + exprs = lappend(exprs, rinfo); + + MemoryContextSwitchTo(oldContext); break; } } @@ -3643,7 +3670,11 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel, /* Matched all key columns of this index? */ if (c == ind->nkeycolumns) + { + if (extra_clauses) + *extra_clauses = exprs; return true; + } } return false; diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c index bdd9effd38..68edc9d2d7 100644 --- a/src/backend/optimizer/plan/analyzejoins.c +++ b/src/backend/optimizer/plan/analyzejoins.c @@ -33,6 +33,19 @@ #include "optimizer/tlist.h" #include "utils/lsyscache.h" +/* + * UniqueRelInfo caches a fact that a relation is unique when being joined + * to other relation(s) specified by outerrelids. + * extra_clauses contains additional clauses from a baserestrictinfo list that + * were used to prove uniqueness. We cache it for SJ checking procedure: SJ can + * be removed if outer relation contains strictly the same set of clauses. + */ +typedef struct UniqueRelInfo +{ + Relids outerrelids; + List *extra_clauses; +} UniqueRelInfo; + bool enable_self_join_removal; /* local functions */ @@ -42,14 +55,15 @@ static void remove_rel_from_query(PlannerInfo *root, int relid, static List *remove_rel_from_joinlist(List *joinlist, int relid, int *nremoved); static bool rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel); static bool rel_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, - List *clause_list); + List *clause_list, List **extra_clauses); static Oid distinct_col_search(int colno, List *colnos, List *opids); static bool is_innerrel_unique_for(PlannerInfo *root, Relids joinrelids, Relids outerrelids, RelOptInfo *innerrel, JoinType jointype, - List *restrictlist); + List *restrictlist, + List **extra_clauses); static void change_rinfo(RestrictInfo* rinfo, Index from, Index to); static Bitmapset* change_relid(Relids relids, Index oldId, Index newId); static void change_varno(Expr *expr, Index oldRelid, Index newRelid); @@ -293,7 +307,7 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo) * Now that we have the relevant equality join clauses, try to prove the * innerrel distinct. */ - if (rel_is_distinct_for(root, innerrel, clause_list)) + if (rel_is_distinct_for(root, innerrel, clause_list, NULL)) return true; /* @@ -669,9 +683,14 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel) * Note that the passed-in clause_list may be destructively modified! This * is OK for current uses, because the clause_list is built by the caller for * the sole purpose of passing to this function. + * + * outer_exprs contains right sides of baserestrictinfo clauses looks like + * x = const if distinctness is derived from such clauses, not joininfo clause. + * Pass NULL for the param value, if it is not interested. */ static bool -rel_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *clause_list) +rel_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *clause_list, + List **extra_clauses) { /* * We could skip a couple of tests here if we assume all callers checked @@ -684,10 +703,11 @@ rel_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *clause_list) { /* * Examine the indexes to see if we have a matching unique index. - * relation_has_unique_index_for automatically adds any usable + * relation_has_unique_index_ext automatically adds any usable * restriction clauses for the rel, so we needn't do that here. */ - if (relation_has_unique_index_for(root, rel, clause_list, NIL, NIL)) + if (relation_has_unique_index_ext(root, rel, clause_list, NIL, NIL, + extra_clauses)) return true; } else if (rel->rtekind == RTE_SUBQUERY) @@ -1001,9 +1021,21 @@ innerrel_is_unique(PlannerInfo *root, JoinType jointype, List *restrictlist, bool force_cache) +{ + return innerrel_is_unique_ext(root, joinrelids, outerrelids, innerrel, + jointype, restrictlist, force_cache, NULL); +} + +bool +innerrel_is_unique_ext(PlannerInfo *root, Relids joinrelids, Relids outerrelids, + RelOptInfo *innerrel, JoinType jointype, + List *restrictlist, bool force_cache, + List **extra_clauses) { MemoryContext old_context; ListCell *lc; + UniqueRelInfo *uniqueRelInfo; + List *outer_exprs = NIL; /* Certainly can't prove uniqueness when there are no joinclauses */ if (restrictlist == NIL) @@ -1025,10 +1057,14 @@ innerrel_is_unique(PlannerInfo *root, */ foreach(lc, innerrel->unique_for_rels) { - Relids unique_for_rels = (Relids) lfirst(lc); + uniqueRelInfo = (UniqueRelInfo *) lfirst(lc); - if (bms_is_subset(unique_for_rels, outerrelids)) + if (bms_is_subset(uniqueRelInfo->outerrelids, outerrelids)) + { + if (extra_clauses) + *extra_clauses = uniqueRelInfo->extra_clauses; return true; /* Success! */ + } } /* @@ -1045,7 +1081,7 @@ innerrel_is_unique(PlannerInfo *root, /* No cached information, so try to make the proof. */ if (is_innerrel_unique_for(root, joinrelids, outerrelids, innerrel, - jointype, restrictlist)) + jointype, restrictlist, &outer_exprs)) { /* * Cache the positive result for future probes, being sure to keep it @@ -1058,10 +1094,15 @@ innerrel_is_unique(PlannerInfo *root, * supersets of them anyway. */ old_context = MemoryContextSwitchTo(root->planner_cxt); + uniqueRelInfo = palloc(sizeof(UniqueRelInfo)); + uniqueRelInfo->extra_clauses = outer_exprs; + uniqueRelInfo->outerrelids = bms_copy(outerrelids); innerrel->unique_for_rels = lappend(innerrel->unique_for_rels, - bms_copy(outerrelids)); + uniqueRelInfo); MemoryContextSwitchTo(old_context); + if (extra_clauses) + *extra_clauses = outer_exprs; return true; /* Success! */ } else @@ -1107,7 +1148,8 @@ is_innerrel_unique_for(PlannerInfo *root, Relids outerrelids, RelOptInfo *innerrel, JoinType jointype, - List *restrictlist) + List *restrictlist, + List **extra_clauses) { List *clause_list = NIL; ListCell *lc; @@ -1149,7 +1191,7 @@ is_innerrel_unique_for(PlannerInfo *root, } /* Let rel_is_distinct_for() do the hard work */ - return rel_is_distinct_for(root, innerrel, clause_list); + return rel_is_distinct_for(root, innerrel, clause_list, extra_clauses); } typedef struct ChangeVarnoContext @@ -1395,6 +1437,7 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark, if (leftOp != NULL && equal(leftOp, rightOp)) { NullTest *nullTest = makeNode(NullTest); + nullTest->arg = leftOp; nullTest->nulltesttype = IS_NOT_NULL; nullTest->argisrow = false; @@ -1638,6 +1681,70 @@ split_selfjoin_quals(PlannerInfo *root, List *joinquals, List **selfjoinquals, *otherjoinquals = ojoinquals; } +/* + * Check for a case when uniqueness [partly] derived from a baserestrictinfo + * clause. In this case we have a chance to return the only one row (if such + * clauses on both sides of SJ is equal) or nothing - if they are different. + */ +static bool +degenerate_case(RelOptInfo *outer, List *uclauses, Index relid) +{ + ListCell *lc; + + foreach(lc, uclauses) + { + RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc); + Expr *clause; + Const *c1; + bool matched = false; + ListCell *olc; + + /* Only filters like x == const we should consider. */ + Assert(bms_is_empty(rinfo->left_relids) || + bms_is_empty(rinfo->right_relids)); + + clause = (Expr *) (rinfo->outer_is_left ? + get_rightop(rinfo->clause) : get_leftop(rinfo->clause)); + c1 = (Const *) (rinfo->outer_is_left ? + get_leftop(rinfo->clause) : get_rightop(rinfo->clause)); + Assert(IsA(c1, Const) && outer->relid > 0 && relid > 0); + + clause = (Expr *) copyObject(clause); + change_varno(clause, relid, outer->relid); + + /* + * Compare these left and right sides with corresponding sides of + * the outer's filters. If no one detected - return immediately. + */ + foreach(olc, outer->baserestrictinfo) + { + RestrictInfo *orinfo = lfirst_node(RestrictInfo, olc); + Expr *oclause; + Expr *c2; + + if (orinfo->mergeopfamilies == NIL) + /* Don't consider clauses which aren't similar to 'F(X)=G(Y)' */ + continue; + + oclause = (Expr *) (bms_is_empty(orinfo->left_relids) ? + get_rightop(orinfo->clause) : get_leftop(orinfo->clause)); + c2 = (Expr *) (bms_is_empty(orinfo->left_relids) ? + get_leftop(orinfo->clause) : get_rightop(orinfo->clause)); + + if (equal(clause, oclause) && equal(c1,c2)) + { + matched = true; + break; + } + } + + if (!matched) + return false; + } + + return true; +} + /* * Find and remove unique self joins in a group of base relations that have * the same Oid. @@ -1663,13 +1770,14 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) while ((k = bms_next_member(relids, k)) > 0) { RelOptInfo *inner = root->simple_rel_array[k]; - List *restrictlist; - List *selfjoinquals; - List *otherjoinquals; + List *restrictlist; + List *selfjoinquals; + List *otherjoinquals; ListCell *lc; bool jinfo_check = true; PlanRowMark *omark = NULL; PlanRowMark *imark = NULL; + List *uclauses = NIL; /* A sanity check: the relations have the same Oid. */ Assert(root->simple_rte_array[k]->relid == @@ -1745,9 +1853,13 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids) * join quals are selfjoin quals. Otherwise we could end up * putting false negatives in the cache. */ - if (!innerrel_is_unique(root, joinrelids, outer->relids, - inner, JOIN_INNER, selfjoinquals, - list_length(otherjoinquals) == 0)) + if (!innerrel_is_unique_ext(root, joinrelids, outer->relids, + inner, JOIN_INNER, selfjoinquals, + list_length(otherjoinquals) == 0, + &uclauses)) + continue; + + if (!degenerate_case(outer, uclauses, inner->relid)) continue; /* See for row marks. */ diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h index 3d95e6bfc8..b93bc29f0f 100644 --- a/src/include/optimizer/paths.h +++ b/src/include/optimizer/paths.h @@ -76,6 +76,9 @@ extern void create_index_paths(PlannerInfo *root, RelOptInfo *rel); extern bool relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel, List *restrictlist, List *exprlist, List *oprlist); +extern bool relation_has_unique_index_ext(PlannerInfo *root, RelOptInfo *rel, + List *restrictlist, List *exprlist, + List *oprlist, List **extra_clauses); extern bool indexcol_is_bool_constant_for_query(PlannerInfo *root, IndexOptInfo *index, int indexcol); diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h index 86247cb9ce..ef963e4bcf 100644 --- a/src/include/optimizer/planmain.h +++ b/src/include/optimizer/planmain.h @@ -108,6 +108,10 @@ extern bool query_is_distinct_for(Query *query, List *colnos, List *opids); extern bool innerrel_is_unique(PlannerInfo *root, Relids joinrelids, Relids outerrelids, RelOptInfo *innerrel, JoinType jointype, List *restrictlist, bool force_cache); +extern bool innerrel_is_unique_ext(PlannerInfo *root, Relids joinrelids, + Relids outerrelids, RelOptInfo *innerrel, + JoinType jointype, List *restrictlist, + bool force_cache, List **uclauses); extern List * remove_useless_self_joins(PlannerInfo *root, List *jointree); /* diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 9d0674285c..ae0bec82ae 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -5099,11 +5099,165 @@ on true; -> Seq Scan on int8_tbl y (7 rows) +-- +-- SJR corner case: uniqueness of an inner is [partially] derived from +-- baserestrictinfo clauses. +-- XXX: We really should allow SJR for these corner cases? +-- +INSERT INTO sj VALUES (3, 1, 3); +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3; + QUERY PLAN +------------------------------ + Nested Loop + Join Filter: (j1.b = j2.b) + -> Seq Scan on sj j1 + Filter: (a = 2) + -> Seq Scan on sj j2 + Filter: (a = 3) +(6 rows) + +SELECT * FROM sj j1, sj j2 +WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3; -- Return one column + a | b | c | a | b | c +---+---+---+---+---+--- + 2 | 1 | 1 | 3 | 1 | 3 +(1 row) + +explain (costs off) -- Remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2; + QUERY PLAN +----------------------------------------- + Seq Scan on sj j2 + Filter: ((b IS NOT NULL) AND (a = 2)) +(2 rows) + +SELECT * FROM sj j1, sj j2 +WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2; -- Return one column + a | b | c | a | b | c +---+---+---+---+---+--- + 2 | 1 | 1 | 2 | 1 | 1 +(1 row) + +explain (costs off) -- Remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1; + QUERY PLAN +----------------------------------------- + Seq Scan on sj j2 + Filter: ((b IS NOT NULL) AND (a = 1)) +(2 rows) + +SELECT * FROM sj j1, sj j2 +WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1; -- Return no columns + a | b | c | a | b | c +---+---+---+---+---+--- +(0 rows) + +explain (costs off) -- Shuffle a clause. Remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1; + QUERY PLAN +----------------------------------------- + Seq Scan on sj j2 + Filter: ((b IS NOT NULL) AND (a = 1)) +(2 rows) + +SELECT * FROM sj j1, sj j2 +WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1; -- Return no columns + a | b | c | a | b | c +---+---+---+---+---+--- +(0 rows) + +-- Functional index +CREATE UNIQUE INDEX sj_fn_idx ON sj((a * a)); +explain (costs off) -- Remove SJ + SELECT * FROM sj j1, sj j2 + WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 1; + QUERY PLAN +----------------------------------------------- + Seq Scan on sj j2 + Filter: ((b IS NOT NULL) AND ((a * a) = 1)) +(2 rows) + +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 + WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 2; + QUERY PLAN +------------------------------- + Nested Loop + Join Filter: (j1.b = j2.b) + -> Seq Scan on sj j1 + Filter: ((a * a) = 1) + -> Seq Scan on sj j2 + Filter: ((a * a) = 2) +(6 rows) + +-- Multiple filters +CREATE UNIQUE INDEX sj_temp_idx1 ON sj(a,b,c); +explain (costs off) -- Remove SJ + SELECT * FROM sj j1, sj j2 + WHERE j1.b = j2.b AND j1.a = 2 AND j1.c = 3 AND j2.a = 2 AND 3 = j2.c; + QUERY PLAN +----------------------------------------------------- + Seq Scan on sj j2 + Filter: ((b IS NOT NULL) AND (a = 2) AND (c = 3)) +(2 rows) + +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 + WHERE j1.b = j2.b AND 2 = j1.a AND j1.c = 3 AND j2.a = 1 AND 3 = j2.c; + QUERY PLAN +--------------------------------------- + Nested Loop + Join Filter: (j1.b = j2.b) + -> Seq Scan on sj j1 + Filter: ((2 = a) AND (c = 3)) + -> Seq Scan on sj j2 + Filter: ((c = 3) AND (a = 1)) +(6 rows) + +CREATE UNIQUE INDEX sj_temp_idx ON sj(a,b); +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2; + QUERY PLAN +------------------------------ + Nested Loop + Join Filter: (j1.b = j2.b) + -> Seq Scan on sj j1 + Filter: (a = 2) + -> Seq Scan on sj j2 +(5 rows) + +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 2 = j2.a; + QUERY PLAN +------------------------------ + Nested Loop + Join Filter: (j1.b = j2.b) + -> Seq Scan on sj j2 + Filter: (2 = a) + -> Seq Scan on sj j1 +(5 rows) + +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND (j1.a = 1 OR j2.a = 1); + QUERY PLAN +--------------------------------------------------------------- + Nested Loop + Join Filter: ((j1.b = j2.b) AND ((j1.a = 1) OR (j2.a = 1))) + -> Seq Scan on sj j1 + -> Materialize + -> Seq Scan on sj j2 +(5 rows) + +DROP INDEX sj_fn_idx, sj_temp_idx1, sj_temp_idx; -- Test that OR predicated are updated correctly after join removal CREATE TABLE tab_with_flag ( id INT PRIMARY KEY, is_flag SMALLINT); CREATE INDEX idx_test_is_flag ON tab_with_flag (is_flag); explain (costs off) -SELECT COUNT(*) FROM tab_with_flag WHERE (is_flag IS NULL OR is_flag = 0) AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3)); +SELECT COUNT(*) FROM tab_with_flag +WHERE + (is_flag IS NULL OR is_flag = 0) + AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3)); QUERY PLAN ---------------------------------------------------------------------------------- Aggregate @@ -5240,17 +5394,51 @@ SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code; -- We can remove the join even if we find the join can't duplicate rows and -- the base quals of each side are different. In the following case we end up -- moving quals over to s1 to make it so it can't match any rows. -create table sl(a int, b int); +create table sl(a int, b int, c int); create unique index on sl(a, b); vacuum analyze sl; -- Both sides are unique, but base quals are different explain (costs off) select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1 and t2.b = 2; - QUERY PLAN ------------------------------------------------------ - Seq Scan on sl t2 - Filter: ((a IS NOT NULL) AND (b = 2) AND (b = 1)) -(2 rows) + QUERY PLAN +------------------------------ + Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sl t1 + Filter: (b = 1) + -> Seq Scan on sl t2 + Filter: (b = 2) +(6 rows) + +-- Check NullTest in baserestrictinfo list +explain (costs off) +select * from sl t1, sl t2 +where t1.a = t2.a and t1.b = 1 and t2.b = 2 + and t1.c IS NOT NULL and t2.c IS NOT NULL + and t2.b IS NOT NULL and t1.b IS NOT NULL + and t1.a IS NOT NULL and t2.a IS NOT NULL; + QUERY PLAN +--------------------------------------------------------------------------------------- + Nested Loop + Join Filter: (t1.a = t2.a) + -> Seq Scan on sl t1 + Filter: ((c IS NOT NULL) AND (b IS NOT NULL) AND (a IS NOT NULL) AND (b = 1)) + -> Seq Scan on sl t2 + Filter: ((c IS NOT NULL) AND (b IS NOT NULL) AND (a IS NOT NULL) AND (b = 2)) +(6 rows) + +explain (verbose, costs off) +select * from sl t1, sl t2 +where t1.b = t2.b and t2.a = 3 and t1.a = 3 + and t1.c IS NOT NULL and t2.c IS NOT NULL + and t2.b IS NOT NULL and t1.b IS NOT NULL + and t1.a IS NOT NULL and t2.a IS NOT NULL; + QUERY PLAN +--------------------------------------------------------------------------------------------- + Seq Scan on public.sl t2 + Output: t2.a, t2.b, t2.c, t2.a, t2.b, t2.c + Filter: ((t2.c IS NOT NULL) AND (t2.b IS NOT NULL) AND (t2.a IS NOT NULL) AND (t2.a = 3)) +(3 rows) -- Join qual isn't mergejoinable, but inner is unique. explain (COSTS OFF) diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 53d0aa98eb..d132eedf18 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1829,11 +1829,69 @@ left join (select coalesce(y.q1, 1) from int8_tbl y on true) z on true; +-- +-- SJR corner case: uniqueness of an inner is [partially] derived from +-- baserestrictinfo clauses. +-- XXX: We really should allow SJR for these corner cases? +-- + +INSERT INTO sj VALUES (3, 1, 3); + +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3; +SELECT * FROM sj j1, sj j2 +WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3; -- Return one column + +explain (costs off) -- Remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2; +SELECT * FROM sj j1, sj j2 +WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2; -- Return one column + +explain (costs off) -- Remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1; +SELECT * FROM sj j1, sj j2 +WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1; -- Return no columns + +explain (costs off) -- Shuffle a clause. Remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1; +SELECT * FROM sj j1, sj j2 +WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1; -- Return no columns + +-- Functional index +CREATE UNIQUE INDEX sj_fn_idx ON sj((a * a)); +explain (costs off) -- Remove SJ + SELECT * FROM sj j1, sj j2 + WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 1; +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 + WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 2; + +-- Multiple filters +CREATE UNIQUE INDEX sj_temp_idx1 ON sj(a,b,c); +explain (costs off) -- Remove SJ + SELECT * FROM sj j1, sj j2 + WHERE j1.b = j2.b AND j1.a = 2 AND j1.c = 3 AND j2.a = 2 AND 3 = j2.c; +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 + WHERE j1.b = j2.b AND 2 = j1.a AND j1.c = 3 AND j2.a = 1 AND 3 = j2.c; + +CREATE UNIQUE INDEX sj_temp_idx ON sj(a,b); +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2; +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 2 = j2.a; +explain (costs off) -- Don't remove SJ + SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND (j1.a = 1 OR j2.a = 1); +DROP INDEX sj_fn_idx, sj_temp_idx1, sj_temp_idx; + -- Test that OR predicated are updated correctly after join removal CREATE TABLE tab_with_flag ( id INT PRIMARY KEY, is_flag SMALLINT); CREATE INDEX idx_test_is_flag ON tab_with_flag (is_flag); explain (costs off) -SELECT COUNT(*) FROM tab_with_flag WHERE (is_flag IS NULL OR is_flag = 0) AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3)); +SELECT COUNT(*) FROM tab_with_flag +WHERE + (is_flag IS NULL OR is_flag = 0) + AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3)); DROP TABLE tab_with_flag; -- HAVING clause @@ -1886,7 +1944,7 @@ SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code; -- We can remove the join even if we find the join can't duplicate rows and -- the base quals of each side are different. In the following case we end up -- moving quals over to s1 to make it so it can't match any rows. -create table sl(a int, b int); +create table sl(a int, b int, c int); create unique index on sl(a, b); vacuum analyze sl; @@ -1894,6 +1952,20 @@ vacuum analyze sl; explain (costs off) select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1 and t2.b = 2; +-- Check NullTest in baserestrictinfo list +explain (costs off) +select * from sl t1, sl t2 +where t1.a = t2.a and t1.b = 1 and t2.b = 2 + and t1.c IS NOT NULL and t2.c IS NOT NULL + and t2.b IS NOT NULL and t1.b IS NOT NULL + and t1.a IS NOT NULL and t2.a IS NOT NULL; +explain (verbose, costs off) +select * from sl t1, sl t2 +where t1.b = t2.b and t2.a = 3 and t1.a = 3 + and t1.c IS NOT NULL and t2.c IS NOT NULL + and t2.b IS NOT NULL and t1.b IS NOT NULL + and t1.a IS NOT NULL and t2.a IS NOT NULL; + -- Join qual isn't mergejoinable, but inner is unique. explain (COSTS OFF) SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a AND n2.a = 1;