diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index bf322198a20..fd145421a5b 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2407,7 +2407,7 @@ SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 O --------------------------------------------------------------------------------------------------------------------------- Nested Loop Left Join Output: (13), ft2.c1 - Join Filter: (13 = ft2.c1) + Outer Tuple Filter: (13 = ft2.c1) -> Foreign Scan on public.ft2 Output: ft2.c1 Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE (("C 1" >= 10)) AND (("C 1" <= 15)) ORDER BY "C 1" ASC NULLS LAST diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index a201ed30824..3f6c3dddc41 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -2302,6 +2302,12 @@ ExplainNode(PlanState *planstate, List *ancestors, } break; case T_NestLoop: + show_upper_qual(((NestLoop *) plan)->join.rhs_joinqual, + "Outer Tuple Filter", planstate, ancestors, es); + if (((NestLoop *) plan)->join.rhs_joinqual) + show_instrumentation_count("Rows Unmatched by Outer Tuple Filter", 3, + planstate, es); + show_upper_qual(((NestLoop *) plan)->join.joinqual, "Join Filter", planstate, ancestors, es); if (((NestLoop *) plan)->join.joinqual) @@ -3957,6 +3963,8 @@ show_instrumentation_count(const char *qlabel, int which, if (which == 2) nfiltered = planstate->instrument->nfiltered2; + else if (which == 3) + nfiltered = planstate->instrument->nunmatched; else nfiltered = planstate->instrument->nfiltered1; nloops = planstate->instrument->nloops; diff --git a/src/backend/executor/nodeNestloop.c b/src/backend/executor/nodeNestloop.c index 7f4bf6c4dbb..4a3d7fa0dd4 100644 --- a/src/backend/executor/nodeNestloop.c +++ b/src/backend/executor/nodeNestloop.c @@ -66,6 +66,7 @@ ExecNestLoop(PlanState *pstate) TupleTableSlot *outerTupleSlot; TupleTableSlot *innerTupleSlot; ExprState *joinqual; + ExprState *rhs_joinqual; ExprState *otherqual; ExprContext *econtext; ListCell *lc; @@ -79,6 +80,7 @@ ExecNestLoop(PlanState *pstate) nl = (NestLoop *) node->js.ps.plan; joinqual = node->js.joinqual; + rhs_joinqual = node->js.rhs_joinqual; otherqual = node->js.ps.qual; outerPlan = outerPlanState(node); innerPlan = innerPlanState(node); @@ -152,12 +154,23 @@ ExecNestLoop(PlanState *pstate) } /* - * we have an outerTuple, try to get the next inner tuple. + * we have an outerTuple, try to execute quals related to it. If the + * result is false then we won't get the next inner tuple. Otherwise, + * extract it. */ - ENL1_printf("getting new inner tuple"); + ENL1_printf("executing quals related to outer tuple if any"); - innerTupleSlot = ExecProcNode(innerPlan); - econtext->ecxt_innertuple = innerTupleSlot; + if (rhs_joinqual && !ExecQual(rhs_joinqual, econtext)) + { + InstrCountUnmatched(node, 1); + innerTupleSlot = NULL; + } + else + { + ENL1_printf("getting new inner tuple"); + innerTupleSlot = ExecProcNode(innerPlan); + econtext->ecxt_innertuple = innerTupleSlot; + } if (TupIsNull(innerTupleSlot)) { @@ -314,6 +327,8 @@ ExecInitNestLoop(NestLoop *node, EState *estate, int eflags) nlstate->js.jointype = node->join.jointype; nlstate->js.joinqual = ExecInitQual(node->join.joinqual, (PlanState *) nlstate); + nlstate->js.rhs_joinqual = + ExecInitQual(node->join.rhs_joinqual, (PlanState *) nlstate); /* * detect whether we need only consider the first matching inner tuple diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 178c572b021..51a1a29fd2b 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -232,7 +232,7 @@ static RecursiveUnion *make_recursive_union(List *tlist, static BitmapAnd *make_bitmap_and(List *bitmapplans); static BitmapOr *make_bitmap_or(List *bitmapplans); static NestLoop *make_nestloop(List *tlist, - List *joinclauses, List *otherclauses, List *nestParams, + List *joinclauses, List *rhs_joinclauses, List *otherclauses, List *nestParams, Plan *lefttree, Plan *righttree, JoinType jointype, bool inner_unique); static HashJoin *make_hashjoin(List *tlist, @@ -4352,7 +4352,9 @@ create_nestloop_plan(PlannerInfo *root, Plan *inner_plan; List *tlist = build_path_tlist(root, &best_path->jpath.path); List *joinrestrictclauses = best_path->jpath.joinrestrictinfo; + List *rhs_joinclauses = NIL; List *joinclauses; + List *rhs_otherclauses = NIL; List *otherclauses; Relids outerrelids; List *nestParams; @@ -4397,6 +4399,10 @@ create_nestloop_plan(PlannerInfo *root, extract_actual_join_clauses(joinrestrictclauses, best_path->jpath.path.parent->relids, &joinclauses, &otherclauses); + + extract_actual_join_clauses(best_path->jpath.rhs_joinrinfo, + best_path->jpath.path.parent->relids, + &rhs_joinclauses, &rhs_otherclauses); } else { @@ -4423,6 +4429,7 @@ create_nestloop_plan(PlannerInfo *root, join_plan = make_nestloop(tlist, joinclauses, + rhs_joinclauses, otherclauses, nestParams, outer_plan, @@ -6019,6 +6026,7 @@ make_bitmap_or(List *bitmapplans) static NestLoop * make_nestloop(List *tlist, List *joinclauses, + List *rhs_joinclauses, List *otherclauses, List *nestParams, Plan *lefttree, @@ -6036,6 +6044,24 @@ make_nestloop(List *tlist, node->join.jointype = jointype; node->join.inner_unique = inner_unique; node->join.joinqual = joinclauses; + + { + ListCell *lc; + + /* Remove quals from joinqual which belongs to outer relation */ + foreach(lc, node->join.joinqual) + { + Node *qual = (Node *) lfirst(lc); + + if (!list_member(rhs_joinclauses, qual)) + continue; + + node->join.joinqual = foreach_delete_current(node->join.joinqual, lc); + } + + } + + node->join.rhs_joinqual = rhs_joinclauses; node->nestParams = nestParams; return node; diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 6d23df108da..2499dbfa94b 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -2292,6 +2292,16 @@ set_join_references(PlannerInfo *root, Join *join, int rtoffset) NRM_EQUAL, NUM_EXEC_QUAL((Plan *) join)); + /* Process rhs_joinqual as well */ + join->rhs_joinqual = fix_join_expr(root, + join->rhs_joinqual, + outer_itlist, + inner_itlist, + (Index) 0, + rtoffset, + NRM_EQUAL, + NUM_EXEC_QUAL((Plan *) join)); + /* Now do join-type-specific stuff */ if (IsA(join, NestLoop)) { diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index fc97bf6ee26..bfd5bd3df59 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -2603,6 +2603,14 @@ create_nestloop_path(PlannerInfo *root, pathnode->jpath.innerjoinpath = inner_path; pathnode->jpath.joinrestrictinfo = restrict_clauses; + /* If clause_relids belong to outerpath then add it to rhs_joinrinfo list */ + foreach_node(RestrictInfo, rinfo, restrict_clauses) + { + if (bms_is_subset(rinfo->clause_relids, outer_path->parent->relids)) + pathnode->jpath.rhs_joinrinfo = + lappend(pathnode->jpath.rhs_joinrinfo, rinfo); + } + final_cost_nestloop(root, pathnode, workspace, extra); return pathnode; diff --git a/src/include/executor/instrument.h b/src/include/executor/instrument.h index bfd7b6d8445..c79f67f4d08 100644 --- a/src/include/executor/instrument.h +++ b/src/include/executor/instrument.h @@ -88,6 +88,8 @@ typedef struct Instrumentation double nloops; /* # of run cycles for this node */ double nfiltered1; /* # of tuples removed by scanqual or joinqual */ double nfiltered2; /* # of tuples removed by "other" quals */ + double nunmatched; /* # of tuples removed by quals related to + * only outer tuples */ BufferUsage bufusage; /* total buffer usage */ WalUsage walusage; /* total WAL usage */ } Instrumentation; diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 7f71b7625df..815f7b3fd5b 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1237,6 +1237,11 @@ typedef struct PlanState if (((PlanState *)(node))->instrument) \ ((PlanState *)(node))->instrument->nfiltered2 += (delta); \ } while(0) +#define InstrCountUnmatched(node, delta) \ + do { \ + if (((PlanState *)(node))->instrument) \ + ((PlanState *)(node))->instrument->nunmatched += (delta); \ + } while(0) /* * EPQState is state for executing an EvalPlanQual recheck on a candidate @@ -2124,6 +2129,8 @@ typedef struct JoinState bool single_match; /* True if we should skip to next outer tuple * after finding one inner match */ ExprState *joinqual; /* JOIN quals (in addition to ps.qual) */ + ExprState *rhs_joinqual; /* JOIN quals which can be executed using only + * outer tuple */ } JoinState; /* ---------------- diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 0759e00e96d..dd84cd96815 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2087,6 +2087,7 @@ typedef struct JoinPath Path *innerjoinpath; /* path for the inner side of the join */ List *joinrestrictinfo; /* RestrictInfos to apply to join */ + List *rhs_joinrinfo; /* Outer-side join clauses (filters) */ /* * See the notes for RelOptInfo and ParamPathInfo to understand why diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 52f29bcdb69..8f8030803a5 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -792,6 +792,8 @@ typedef struct Join JoinType jointype; bool inner_unique; List *joinqual; /* JOIN quals (in addition to plan.qual) */ + List *rhs_joinqual; /* JOIN quals which can be executed by using + * only outer tuple */ } Join; /* ---------------- diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 1904eb65bb9..b1ca1608666 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -2189,7 +2189,7 @@ SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON ------------------------------------------------------------------------------------ Aggregate -> Nested Loop Left Join - Join Filter: (tenk1.hundred = 42) + Outer Tuple Filter: (tenk1.hundred = 42) -> Index Only Scan using tenk1_hundred on tenk1 -> Memoize Cache Key: tenk1.hundred diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 55d44a9bcee..1b7d495abf9 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2430,7 +2430,7 @@ where t4.f1 is null; -> Seq Scan on int4_tbl t2 -> Materialize -> Nested Loop Left Join - Join Filter: (t3.f1 > 1) + Outer Tuple Filter: (t3.f1 > 1) -> Seq Scan on int4_tbl t3 Filter: (f1 > 0) -> Materialize @@ -2458,9 +2458,9 @@ from int4_tbl t1 left join int4_tbl t2 on true -> Seq Scan on int4_tbl t1 -> Materialize -> Nested Loop Left Join - Join Filter: (t3.f1 > 0) + Outer Tuple Filter: (t3.f1 > 0) -> Nested Loop Left Join - Join Filter: (t2.f1 > 0) + Outer Tuple Filter: (t2.f1 > 0) -> Seq Scan on int4_tbl t2 -> Materialize -> Seq Scan on int4_tbl t3 @@ -2523,7 +2523,7 @@ select * from int4_tbl t1 QUERY PLAN ------------------------------------------------- Nested Loop Left Join - Join Filter: (t2.f1 = t3.f1) + Outer Tuple Filter: (t2.f1 = t3.f1) -> Nested Loop Left Join -> Nested Loop Left Join -> Seq Scan on int4_tbl t1 @@ -2561,12 +2561,12 @@ select * from int4_tbl t1 left join (int4_tbl t2 left join int4_tbl t3 on t2.f1 > 0) on t2.f1 > 1 left join int4_tbl t4 on t2.f1 > 2 and t3.f1 > 3 where t1.f1 = coalesce(t2.f1, 1); - QUERY PLAN ----------------------------------------------------- + QUERY PLAN +----------------------------------------------------- Nested Loop Left Join - Join Filter: ((t2.f1 > 2) AND (t3.f1 > 3)) + Outer Tuple Filter: ((t2.f1 > 2) AND (t3.f1 > 3)) -> Nested Loop Left Join - Join Filter: (t2.f1 > 0) + Outer Tuple Filter: (t2.f1 > 0) -> Nested Loop Left Join Filter: (t1.f1 = COALESCE(t2.f1, 1)) -> Seq Scan on int4_tbl t1 @@ -2590,9 +2590,9 @@ select * from int4_tbl t1 Hash Right Join Hash Cond: (t2.f1 = t1.f1) -> Nested Loop Left Join - Join Filter: (t2.f1 > 1) + Outer Tuple Filter: (t2.f1 > 1) -> Nested Loop Left Join - Join Filter: (t2.f1 > 0) + Outer Tuple Filter: (t2.f1 > 0) Filter: (t3.f1 IS NULL) -> Seq Scan on int4_tbl t2 -> Materialize @@ -2612,11 +2612,11 @@ select * from int4_tbl t1 QUERY PLAN ----------------------------------------------------------------- Nested Loop Left Join - Join Filter: (t2.f1 > 1) + Outer Tuple Filter: (t2.f1 > 1) -> Hash Right Join Hash Cond: (t2.f1 = t1.f1) -> Nested Loop Left Join - Join Filter: (t2.f1 > 0) + Outer Tuple Filter: (t2.f1 > 0) Filter: (t2.f1 <> COALESCE(t3.f1, '-1'::integer)) -> Seq Scan on int4_tbl t2 -> Materialize @@ -2662,7 +2662,7 @@ on t2.q2 = 123; -> Nested Loop Left Join Join Filter: (t2.q1 = t5.q1) -> Nested Loop Left Join - Join Filter: false + Outer Tuple Filter: false -> Seq Scan on int8_tbl t2 Filter: (q2 = 123) -> Result @@ -2676,13 +2676,13 @@ select * from int8_tbl t1 left join lateral (select * from int8_tbl t3 where t3.q1 = t2.q1 offset 0) s on t2.q1 = 1; - QUERY PLAN -------------------------------------------- + QUERY PLAN +----------------------------------------------- Nested Loop Left Join -> Seq Scan on int8_tbl t1 -> Materialize -> Nested Loop Left Join - Join Filter: (t2.q1 = 1) + Outer Tuple Filter: (t2.q1 = 1) -> Seq Scan on int8_tbl t2 -> Seq Scan on int8_tbl t3 Filter: (q1 = t2.q1) @@ -2700,7 +2700,7 @@ select * from int8_tbl t1 -> Seq Scan on int8_tbl t1 -> Materialize -> Nested Loop Left Join - Join Filter: (t2.q1 = 1) + Outer Tuple Filter: (t2.q1 = 1) -> Seq Scan on int8_tbl t2 -> Function Scan on generate_series (7 rows) @@ -2711,13 +2711,13 @@ select * from int8_tbl t1 left join lateral (select t2.q1 from int8_tbl t3) s on t2.q1 = 1; - QUERY PLAN -------------------------------------------- + QUERY PLAN +----------------------------------------------- Nested Loop Left Join -> Seq Scan on int8_tbl t1 -> Materialize -> Nested Loop Left Join - Join Filter: (t2.q1 = 1) + Outer Tuple Filter: (t2.q1 = 1) -> Seq Scan on int8_tbl t2 -> Seq Scan on int8_tbl t3 (7 rows) @@ -2728,13 +2728,13 @@ select * from onek t1 left join lateral (select * from onek t3 where t3.two = t2.two offset 0) s on t2.unique1 = 1; - QUERY PLAN --------------------------------------------------- + QUERY PLAN +---------------------------------------------------- Nested Loop Left Join -> Seq Scan on onek t1 -> Materialize -> Nested Loop Left Join - Join Filter: (t2.unique1 = 1) + Outer Tuple Filter: (t2.unique1 = 1) -> Seq Scan on onek t2 -> Memoize Cache Key: t2.two @@ -4196,7 +4196,7 @@ select unique1, x from tenk1 left join f_immutable_int4(1) x on unique1 = x; QUERY PLAN ---------------------------------------------------- Nested Loop Left Join - Join Filter: (tenk1.unique1 = 1) + Outer Tuple Filter: (tenk1.unique1 = 1) -> Index Only Scan using tenk1_unique1 on tenk1 -> Materialize -> Result @@ -4253,7 +4253,7 @@ where nt3.id = 1 and ss2.b3; -> Index Scan using nt3_pkey on nt3 Index Cond: (id = 1) -> Nested Loop Left Join - Join Filter: (0 = nt2.nt1_id) + Outer Tuple Filter: (0 = nt2.nt1_id) -> Index Scan using nt2_pkey on nt2 Index Cond: (id = nt3.nt2_id) -> Result @@ -4496,7 +4496,7 @@ select count(*) from ------------------------------------------------------------------------- Aggregate -> Nested Loop Left Join - Join Filter: (a.unique2 = b.unique1) + Outer Tuple Filter: (a.unique2 = b.unique1) -> Nested Loop -> Nested Loop -> Seq Scan on int4_tbl @@ -4533,7 +4533,7 @@ select b.unique1 from -> Nested Loop Left Join -> Seq Scan on int4_tbl i2 -> Nested Loop Left Join - Join Filter: (b.unique1 = 42) + Outer Tuple Filter: (b.unique1 = 42) -> Nested Loop -> Nested Loop -> Seq Scan on int4_tbl i1 @@ -4848,7 +4848,7 @@ select t1.* from Hash Cond: (i8.q2 = i4.f1) -> Nested Loop Left Join Output: t1.f1, i8.q2 - Join Filter: (t1.f1 = '***'::text) + Outer Tuple Filter: (t1.f1 = '***'::text) -> Seq Scan on public.text_tbl t1 Output: t1.f1 -> Materialize @@ -4909,7 +4909,7 @@ select t1.* from Hash Cond: (i8.q2 = i4.f1) -> Nested Loop Left Join Output: t1.f1, i8.q2 - Join Filter: (t1.f1 = '***'::text) + Outer Tuple Filter: (t1.f1 = '***'::text) -> Seq Scan on public.text_tbl t1 Output: t1.f1 -> Materialize @@ -4975,7 +4975,7 @@ select t1.* from Hash Cond: (i8.q2 = i4.f1) -> Nested Loop Left Join Output: t1.f1, i8.q2 - Join Filter: (t1.f1 = '***'::text) + Outer Tuple Filter: (t1.f1 = '***'::text) -> Seq Scan on public.text_tbl t1 Output: t1.f1 -> Materialize @@ -5084,7 +5084,7 @@ left join QUERY PLAN -------------------------------- Nested Loop Left Join - Join Filter: false + Outer Tuple Filter: false -> Result -> Result One-Time Filter: false @@ -5100,16 +5100,16 @@ select 1 from join int4_tbl i42 on ss1.a is null or i8.q1 <> i8.q2 right join (select 2 as b) ss2 on ss2.b < i4.f1; - QUERY PLAN ------------------------------------------------------------ + QUERY PLAN +------------------------------------------------------------- Nested Loop Left Join -> Result -> Nested Loop -> Nested Loop Left Join - Join Filter: NULL::boolean + Outer Tuple Filter: NULL::boolean Filter: (((1) IS NULL) OR (i8.q1 <> i8.q2)) -> Nested Loop Left Join - Join Filter: (i4.f1 IS NOT NULL) + Outer Tuple Filter: (i4.f1 IS NOT NULL) -> Seq Scan on int4_tbl i4 Filter: (2 < f1) -> Materialize @@ -5720,7 +5720,7 @@ from int4_tbl as t1 QUERY PLAN -------------------------------- Nested Loop Left Join - Join Filter: false + Outer Tuple Filter: false -> Seq Scan on int4_tbl t1 -> Result One-Time Filter: false @@ -5742,7 +5742,7 @@ from int4_tbl as t1 QUERY PLAN -------------------------------- Nested Loop Left Join - Join Filter: false + Outer Tuple Filter: false -> Seq Scan on int4_tbl t1 -> Result One-Time Filter: false @@ -5775,7 +5775,7 @@ from int8_tbl t1 QUERY PLAN ------------------------------------------------- Nested Loop Left Join - Join Filter: (t2.q2 < t3.unique2) + Outer Tuple Filter: (t2.q2 < t3.unique2) -> Nested Loop Left Join Join Filter: (t2.q1 > t3.unique1) -> Hash Left Join @@ -5915,7 +5915,7 @@ select 1 from a t1 -> Seq Scan on a t1 -> Materialize -> Nested Loop Left Join - Join Filter: (t2.id = 1) + Outer Tuple Filter: (t2.id = 1) -> Index Only Scan using a_pkey on a t2 Index Cond: (id = 1) -> Seq Scan on a t3 @@ -6122,7 +6122,7 @@ SELECT q2 FROM ------------------------------------------------------ Nested Loop Left Join Output: q2 - Join Filter: NULL::boolean + Outer Tuple Filter: NULL::boolean Filter: (('constant'::text) >= ('constant'::text)) -> Seq Scan on public.int4_tbl Output: int4_tbl.f1 @@ -6144,7 +6144,7 @@ FROM int4_tbl -> Seq Scan on int4_tbl -> Materialize -> Nested Loop Left Join - Join Filter: NULL::boolean + Outer Tuple Filter: NULL::boolean Filter: ((tenk1.unique1 = (42)) OR (tenk1.unique2 = (42))) -> Seq Scan on tenk1 -> Result @@ -7242,7 +7242,7 @@ select * from -------------------------------- Nested Loop Left Join Output: 0, (1), ((1)) - Join Filter: false + Outer Tuple Filter: false -> Result Output: 1 -> Result @@ -7361,7 +7361,7 @@ select * from int8_tbl i8 left join lateral -------------------------------------- Nested Loop Left Join Output: i8.q1, i8.q2, f1, (i8.q2) - Join Filter: false + Outer Tuple Filter: false -> Seq Scan on public.int8_tbl i8 Output: i8.q1, i8.q2 -> Result diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out index 965a3a76161..30b169bcf65 100644 --- a/src/test/regress/expected/predicate.out +++ b/src/test/regress/expected/predicate.out @@ -119,9 +119,9 @@ SELECT * FROM pred_tab t1 QUERY PLAN ------------------------------------------- Nested Loop Left Join - Join Filter: (t2.a IS NOT NULL) + Outer Tuple Filter: (t2.a IS NOT NULL) -> Nested Loop Left Join - Join Filter: (t1.a = 1) + Outer Tuple Filter: (t1.a = 1) -> Seq Scan on pred_tab t1 -> Materialize -> Seq Scan on pred_tab t2 @@ -135,13 +135,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM pred_tab t1 LEFT JOIN pred_tab t2 ON TRUE LEFT JOIN pred_tab t3 ON t2.a IS NULL AND t2.b = 1; - QUERY PLAN ---------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Nested Loop Left Join -> Seq Scan on pred_tab t1 -> Materialize -> Nested Loop Left Join - Join Filter: (false AND (t2.b = 1)) + Outer Tuple Filter: (false AND (t2.b = 1)) -> Seq Scan on pred_tab t2 -> Result One-Time Filter: false @@ -156,9 +156,9 @@ SELECT * FROM pred_tab t1 QUERY PLAN ------------------------------------------- Nested Loop Left Join - Join Filter: (t2.a IS NULL) + Outer Tuple Filter: (t2.a IS NULL) -> Nested Loop Left Join - Join Filter: (t1.a = 1) + Outer Tuple Filter: (t1.a = 1) -> Seq Scan on pred_tab t1 -> Materialize -> Seq Scan on pred_tab t2 @@ -191,12 +191,12 @@ EXPLAIN (COSTS OFF) SELECT * FROM pred_tab t1 LEFT JOIN pred_tab t2 ON t1.a = 1 LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1; - QUERY PLAN ---------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Nested Loop Left Join - Join Filter: ((t2.a IS NOT NULL) OR (t2.b = 1)) + Outer Tuple Filter: ((t2.a IS NOT NULL) OR (t2.b = 1)) -> Nested Loop Left Join - Join Filter: (t1.a = 1) + Outer Tuple Filter: (t1.a = 1) -> Seq Scan on pred_tab t1 -> Materialize -> Seq Scan on pred_tab t2 @@ -210,13 +210,13 @@ EXPLAIN (COSTS OFF) SELECT * FROM pred_tab t1 LEFT JOIN pred_tab t2 ON TRUE LEFT JOIN pred_tab t3 ON (t2.a IS NULL OR t2.c IS NULL) AND t2.b = 1; - QUERY PLAN ---------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Nested Loop Left Join -> Seq Scan on pred_tab t1 -> Materialize -> Nested Loop Left Join - Join Filter: (false AND (t2.b = 1)) + Outer Tuple Filter: (false AND (t2.b = 1)) -> Seq Scan on pred_tab t2 -> Result One-Time Filter: false @@ -228,12 +228,12 @@ EXPLAIN (COSTS OFF) SELECT * FROM pred_tab t1 LEFT JOIN pred_tab t2 ON t1.a = 1 LEFT JOIN pred_tab t3 ON t2.a IS NULL OR t2.c IS NULL; - QUERY PLAN ---------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Nested Loop Left Join - Join Filter: ((t2.a IS NULL) OR (t2.c IS NULL)) + Outer Tuple Filter: ((t2.a IS NULL) OR (t2.c IS NULL)) -> Nested Loop Left Join - Join Filter: (t1.a = 1) + Outer Tuple Filter: (t1.a = 1) -> Seq Scan on pred_tab t1 -> Materialize -> Seq Scan on pred_tab t2 @@ -301,13 +301,13 @@ SELECT 1 FROM pred_tab t1 (pred_tab t2 LEFT JOIN pred_tab t3 ON t2.a = t3.a) ON TRUE LEFT JOIN pred_tab t4 ON t1.a IS NULL AND t1.b = 1 RIGHT JOIN pred_tab t5 ON t1.b = t5.b; - QUERY PLAN ---------------------------------------------------- + QUERY PLAN +---------------------------------------------------------- Hash Right Join Hash Cond: (t1.b = t5.b) -> Nested Loop Left Join -> Nested Loop Left Join - Join Filter: (false AND (t1.b = 1)) + Outer Tuple Filter: (false AND (t1.b = 1)) -> Seq Scan on pred_tab t1 -> Result One-Time Filter: false