Re: NOT IN subquery optimization - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: NOT IN subquery optimization |
| Date | |
| Msg-id | 6635.1585081751@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: NOT IN subquery optimization ("Li, Zheng" <zhelli@amazon.com>) |
| Responses |
Re: NOT IN subquery optimization
|
| List | pgsql-hackers |
"Li, Zheng" <zhelli@amazon.com> writes:
> Here is the latest rebased patch.
I noticed that the cfbot is failing to test this because of some trivial
merge conflicts, so here's a re-rebased version.
I haven't reviewed this in any detail, but here's a couple of notes
from having quickly looked through the patch:
* I find it entirely unacceptable to stick some planner temporary
fields into struct SubLink. If you need that storage you'll have
to find some other place to put it. But in point of fact I don't
think you need it; it doesn't look to me to be critical to generate
the subquery's plan any earlier than make_subplan would have done it.
Moreover, you should really strive to *not* do that, because it's
likely to get in the way of other future optimizations. As the
existing comment in make_subplan already suggests, we might want to
delay subplan planning even further than that in future.
* I'm also not too happy with the (undocumented) rearrangement of
reduce_outer_joins. There's a specific sequence of processing that
that's involved in, as documented at the top of prepjointree.c, and
I doubt that you can just randomly call it from other places and expect
good results. In particular, since JOIN alias var flattening won't have
happened yet when this code is being run from pull_up_sublinks, it's
unlikely that reduce_outer_joins will reliably get the same answers it
would get normally. I also wonder whether it's safe to make the
parsetree changes it makes earlier than normal, and whether it will be
problematic to run it twice on the same tree, and whether its rather
indirect connection to distribute_qual_to_rels is going to misbehave.
* The proposed test additions seem to about triple the runtime of
subselect.sql. This seems excessive. I also wonder why it's necessary
for this test to build its own large tables; couldn't it re-use ones
that already exist in the regression database?
* Not really sure that we need a new planner GUC for this, but if we
do, it needs to be documented.
regards, tom lane
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5da0528..16ce707 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -992,7 +992,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
* preprocessing.
*/
if (hasOuterJoins)
- reduce_outer_joins(root);
+ reduce_outer_joins(parse);
/*
* If we have any RTE_RESULT relations, see if they can be deleted from
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 3650e83..fa64281 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -39,6 +39,8 @@
#include "utils/syscache.h"
+bool enable_not_in_transform;
+
typedef struct convert_testexpr_context
{
PlannerInfo *root;
@@ -158,8 +160,7 @@ get_first_col_type(Plan *plan, Oid *coltype, int32 *coltypmod,
* subquery itself is in a resjunk tlist entry whose value is uninteresting).
*/
static Node *
-make_subplan(PlannerInfo *root, Query *orig_subquery,
- SubLinkType subLinkType, int subLinkId,
+make_subplan(PlannerInfo *root, SubLink *sublink,
Node *testexpr, bool isTopQual)
{
Query *subquery;
@@ -171,6 +172,9 @@ make_subplan(PlannerInfo *root, Query *orig_subquery,
Plan *plan;
List *plan_params;
Node *result;
+ Query *orig_subquery = (Query *) sublink->subselect;
+ SubLinkType subLinkType = sublink->subLinkType;
+ int subLinkId = sublink->subLinkId;
/*
* Copy the source Query node. This is a quick and dirty kluge to resolve
@@ -216,24 +220,33 @@ make_subplan(PlannerInfo *root, Query *orig_subquery,
/* plan_params should not be in use in current query level */
Assert(root->plan_params == NIL);
- /* Generate Paths for the subquery */
- subroot = subquery_planner(root->glob, subquery,
- root,
- false, tuple_fraction);
+ if(sublink->subroot != NULL &&
+ sublink->subplan != NULL)
+ {
+ subroot = (PlannerInfo *) sublink->subroot;
+ plan = (Plan *) sublink->subplan;
+ }
+ else
+ {
+ /* Generate Paths for the subquery */
+ subroot = subquery_planner(root->glob, subquery,
+ root,
+ false, tuple_fraction);
+
+ /*
+ * Select best Path and turn it into a Plan. At least for now, there
+ * seems no reason to postpone doing that.
+ */
+ final_rel = fetch_upper_rel(subroot, UPPERREL_FINAL, NULL);
+ best_path = get_cheapest_fractional_path(final_rel, tuple_fraction);
+
+ plan = create_plan(subroot, best_path);
+ }
/* Isolate the params needed by this specific subplan */
plan_params = root->plan_params;
root->plan_params = NIL;
- /*
- * Select best Path and turn it into a Plan. At least for now, there
- * seems no reason to postpone doing that.
- */
- final_rel = fetch_upper_rel(subroot, UPPERREL_FINAL, NULL);
- best_path = get_cheapest_fractional_path(final_rel, tuple_fraction);
-
- plan = create_plan(subroot, best_path);
-
/* And convert to SubPlan or InitPlan format. */
result = build_subplan(root, plan, subroot, plan_params,
subLinkType, subLinkId,
@@ -1173,6 +1186,389 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
return expression_tree_walker(node, inline_cte_walker, context);
}
+/* Returns a List of Nodes from the testexpr of an Any SubLink */
+static List *
+getTestExpr(SubLink *sublink)
+{
+ Node * testexpr = sublink->testexpr;
+ Assert(testexpr);
+
+ /* single expression */
+ if(IsA(testexpr, OpExpr))
+ {
+ OpExpr *opexpr = (OpExpr *) testexpr;
+ Node *testnode = linitial(opexpr->args);
+
+ return list_make1(testnode);
+ }
+ /* multi-expression */
+ else if(IsA(testexpr, BoolExpr))
+ {
+ BoolExpr *bexpr = (BoolExpr *) testexpr;
+ ListCell *lc;
+ Node *node;
+ List *result = NULL;
+
+ foreach(lc, bexpr->args)
+ {
+ node = lfirst(lc);
+ if(IsA(node, OpExpr))
+ {
+ OpExpr *expr = (OpExpr *) node;
+ result = lappend(result, linitial(expr->args));
+ }
+ else
+ {
+ elog(ERROR, "unrecognized node type for testexpr: %d",
+ (int) nodeTag(node));
+ }
+ }
+ return result;
+ }
+ else
+ {
+ elog(ERROR, "unrecognized node type for testexpr: %d",
+ (int) nodeTag(testexpr));
+ }
+}
+
+/* Try to reduce outer joins if there is one in the Query */
+static void
+reduce_outer_joins_NOT_IN(Query *parse)
+{
+ ListCell *lc;
+ RangeTblEntry *rte;
+
+ foreach(lc, parse->rtable)
+ {
+ rte = (RangeTblEntry *) lfirst(lc);
+ /* try to reduce outer joins if there is one */
+ if (rte->rtekind == RTE_JOIN &&
+ IS_OUTER_JOIN(rte->jointype))
+ {
+ reduce_outer_joins(parse);
+ return;
+ }
+ }
+}
+
+/*
+ * Make clause NOT EXISTS
+ * (select 1 from t2 where p) for the NOT IN to ANTI JOIN
+ * transformation.
+ */
+static
+Node *
+makeExistsTest_NOT_IN(Query *subselect)
+{
+ BoolExpr *notExpr = makeNode(BoolExpr);
+ SubLink *exists = makeNode(SubLink);
+ Query *selectOne = copyObject(subselect);
+ Const *oneconst;
+ TargetEntry *dummyte;
+
+ /* modify subselect target list to contain a dummy const 1 */
+ oneconst = makeConst(INT4OID,
+ -1,
+ InvalidOid,
+ sizeof(int32),
+ Int32GetDatum(1),
+ false, /* isnull */
+ true); /* pass by value */
+ dummyte = makeTargetEntry((Expr *) oneconst,
+ 1,
+ "one",
+ false /* resjunk */ );
+ selectOne->targetList = list_make1(dummyte);
+
+ /* make EXISTS(select 1 from t2 where p) */
+ exists->subLinkType = EXISTS_SUBLINK;
+ exists->subLinkId = 0;
+ exists->subselect = (Node *) selectOne;
+ exists->location = -1;
+ exists->subroot = NULL;
+ exists->subplan = NULL;
+
+ /* make NOT EXISTS(select 1 from t2 where p) */
+ notExpr->boolop = NOT_EXPR;
+ notExpr->args = list_make1(exists);
+ notExpr->location = -1;
+
+ return (Node *) notExpr;
+}
+
+/*
+ *Allow transformation from NOT IN query to ANTI JOIN if ALL of the
+ * following conditions are true:
+ * 1. The GUC enable_not_in_transform is set to true.
+ * 2. the NOT IN subquery is not hashable, in which case an expensive
+ * subplan will be generated if we don't transform.
+ * 3.. the subquery does not define any CTE.
+ */
+static bool
+allow_NOT_IN_transformation(PlannerInfo *root,
+ SubLink *sublink)
+{
+ Query *subselect = (Query *) sublink->subselect;
+ PlannerInfo *subroot;
+ double tuple_fraction;
+ RelOptInfo *final_rel;
+ Path *best_path;
+ Plan *plan;
+
+ if(! enable_not_in_transform)
+ return false;
+
+ /*
+ * Can't flatten if it contains WITH. (We could arrange to pull up the
+ * WITH into the parent query's cteList, but that risks changing the
+ * semantics, since a WITH ought to be executed once per associated query
+ * call.) Note that convert_ANY_sublink_to_join doesn't have to reject
+ * this case, since it just produces a subquery RTE that doesn't have to
+ * get flattened into the parent query.
+ */
+ if(subselect->cteList)
+ return false;
+
+ /* For ALL and ANY subplans, we will be
+ * able to stop evaluating if the test condition fails or matches, so very
+ * often not all the tuples will be retrieved; for lack of a better idea,
+ * specify 50% retrieval.
+ */
+ tuple_fraction = 0.5;
+ /*
+ * Generate Paths for the subquery, use a copied version of the subquery
+ * so that the existing one doesn't get modified.
+ */
+ subroot = subquery_planner(root->glob, copyObject(subselect),
+ root, false, tuple_fraction);
+
+ /* Select best Path and turn it into a Plan. */
+ final_rel = fetch_upper_rel(subroot, UPPERREL_FINAL, NULL);
+ best_path = get_cheapest_fractional_path(final_rel, tuple_fraction);
+
+ plan = create_plan(subroot, best_path);
+
+ sublink->subroot = (Node *) subroot;
+ sublink->subplan = (Node *) plan;
+ /*
+ * Punt if subplan is hashable since using hashed subplan is almost like
+ * doing a Hash Anti Join, we probably can't do better than that.
+ */
+ if(subplan_is_hashable(plan))
+ {
+ return false;
+ }
+
+ return true;
+}
+
+/*
+ * do the following NOT IN to ANTI JOIN conversions:
+ *
+ * When x is non-nullable:
+ * t1.x not in (t2.y where p) => ANTI JOIN
+ * t1, t2 on join condition (t1.x=t2.y or t2.y IS NULL) and p.
+ * the above predicate "t2.y IS NULL" can be removed if y
+ * is also non-nullable.
+ *
+ * When x is nullable:
+ * t1.x not in (t2.y where p) => ANTI JOIN
+ * t1 (Filter: t1.x is not null or not exists (select 1 from t2 where p)),
+ * t2 on join condition (t1.x=t2.y or t2.y is null) and p.
+ * the above predicate "t2.y IS NULL" can be removed if y
+ * is also non-nullable.
+ *
+ * The multi-expression case is just ANDs of the single-
+ * expression case.
+ */
+static bool
+convert_NOT_IN_to_join(PlannerInfo *root, Node **quals,
+ SubLink *sublink, List *subquery_vars,
+ Node **pullout)
+{
+ Query *parse = root->parse;
+ Query *subselect = (Query *) sublink->subselect;
+ List *testnodes = getTestExpr(sublink);
+ bool outerNonNull;
+ bool innerNonNull;
+ NullTest *nt;
+
+ /*
+ * Try reduce_outer_joins since outer join affects the nullability test that's coming up next.
+ * We have to call reduce_outer_joins for outer and inner query separately because we
+ * don't have a global range table yet.
+ */
+ reduce_outer_joins_NOT_IN(parse);
+
+ reduce_outer_joins_NOT_IN(subselect);
+
+ Assert(testnodes);
+ outerNonNull =
+ list_hasnonnullable(testnodes, parse);
+ innerNonNull =
+ list_hasnonnullable(subselect->targetList, subselect);
+
+ /* Single-expression case, do the following:
+ * When x is non-nullable:
+ * t1.x not in (t2.y where p) => ANTI JOIN
+ * t1, t2 on join condition (t1.x=t2.y or t2.y IS NULL) and p.
+ * the above predicate "t2.y IS NULL" can be removed if y
+ * is also non-nullable.
+ *
+ * When x is nullable:
+ * t1.x not in (t2.y where p) => ANTI JOIN
+ * t1 (Filter: t1.x is not null or not exists (select 1 from t2 where p)),
+ * t2 on join condition (t1.x=t2.y or t2.y is null) and p.
+ * the above predicate "t2.y IS NULL" can be removed if y
+ * is also non-nullable.
+ */
+ if(IsA(*quals, OpExpr))
+ {
+ /* Add "OR y IS NULL" if y is nullable */
+ if(!innerNonNull)
+ {
+ /* make expr y IS NULL */
+ nt = makeNode(NullTest);
+ nt->arg = (Expr *)linitial(subquery_vars);
+ nt->nulltesttype = IS_NULL;
+ nt->argisrow = false;
+ nt->location = -1;
+
+ /* make orclause (x = y OR y IS NULL) */
+ *quals = (Node *)make_orclause(list_make2(*quals,
+ (Node *)nt));
+ }
+
+ /*
+ * if x is nullable, make the following filter for t1 :
+ * x IS NOT NULL or NOT EXISTS (select 1 from t2 where p)
+ */
+ if(!outerNonNull)
+ {
+ Node * existsTest = NULL;
+
+ /* make expr x IS NOT NULL */
+ nt = makeNode(NullTest);
+ nt->arg = (Expr *) linitial(testnodes);
+ nt->nulltesttype = IS_NOT_NULL;
+ nt->argisrow = false;
+ nt->location = -1;
+
+ existsTest = makeExistsTest_NOT_IN(subselect);
+ /* make x IS NOT NULL OR NOT EXISTS (select 1 from t2 where p) */
+ *pullout = (Node *)make_orclause(list_make2(
+ (Node *) nt, existsTest));
+ }
+ }
+ /*
+ * Multi-expression case:
+ * If all xi's are nullable:
+ * (x1, x2, ... xn) not in (y1, y2, ... yn ) =>
+ * ANTI JOIN t1,
+ * t2 on join condition:
+ * ((t1.x1 = t2.y1) and ... (t1.xi = t2.yi) ... and
+ * (t1.xn = t2.yn)) is NOT FALSE.
+ *
+ * If at least one xi is non-nuallable:
+ * (x1, x2, ... xn) not in (y1, y2, ... yn ) =>
+ * ANTI JOIN t1,
+ * t2 on join condition:
+ * (t1.x1 = t2.y1 or t2.y1 is NULL) and ...
+ * (t1.xi = t2.yi or t2.yi is NULL or t1.xi is NULL) ... and
+ * (t1.xn = t2.yn or t2.yn is NULL).
+ */
+ else if(IsA(*quals, BoolExpr))
+ {
+ /*
+ * Add IS NOT FALSE on top of the join condition if ALL x_i's are nullable
+ */
+ if(!outerNonNull)
+ {
+ BooleanTest *btest;
+
+ btest = makeNode(BooleanTest);
+ btest->arg = (Expr *) *quals;
+ btest->booltesttype = IS_NOT_FALSE;
+ *quals = (Node *) btest;
+ }
+ else
+ {
+ ListCell *qualc;
+ TargetEntry *te;
+ ListCell *xc = list_head(testnodes);
+ ListCell *yc = list_head(subquery_vars);
+ ListCell *ytlc = list_head(subselect->targetList);
+ List *quallist = ((BoolExpr *)*quals)->args;
+ Node *joinCondition = NULL;
+ bool xnonNull;
+ bool ynonNull;
+
+ /* Reconstruct quals in the loop */
+ *quals = NULL;
+ foreach(qualc, quallist)
+ {
+ te = (TargetEntry *)lfirst(ytlc);
+ /* x_i = y_i */
+ joinCondition = lfirst(qualc);
+ ynonNull = is_node_nonnullable((Node*)te, subselect);
+
+ /* append y_i IS NULL to x_i = y_i if y_i is non-nullable */
+ if(!ynonNull)
+ {
+ /* make expr y_i IS NULL */
+ nt = makeNode(NullTest);
+ nt->arg = (Expr *)lfirst(yc);
+ nt->nulltesttype = IS_NULL;
+ nt->argisrow = false;
+ nt->location = -1;
+
+ /* make orclause (x_i = y_i OR y_i IS NULL) */
+ joinCondition = (Node *)make_orclause(list_make2(joinCondition,
+ (Node *)nt));
+ }
+
+ /*
+ * Append "OR x_i is null" to the join condition if x_i is nullable.
+ * Notice at least one x_i should be non-nullable because the all
+ * x_i's nullable case is handled earlier by adding "IS NOT FALSE"
+ * on top of the join condition.
+ */
+ xnonNull = is_node_nonnullable(lfirst(xc), parse);
+ if(!xnonNull)
+ {
+ /* make expr x_i IS NULL */
+ nt = makeNode(NullTest);
+ nt->arg = (Expr *)lfirst(xc);
+ nt->nulltesttype = IS_NULL;
+ nt->argisrow = false;
+ nt->location = -1;
+
+ /* make orclause (x_i = y_i OR y_i IS NULL OR x_i IS NULL) */
+ joinCondition = (Node *)make_orclause(list_make2(joinCondition,
+ (Node *)nt));
+ }
+
+ /*
+ * Now append joinCondition to quals as one andclause.
+ * (x_i = y_i OR y_i IS NULL OR x_i IS NULL) AND
+ * (x_j = y_j OR y_j IS NULL OR x_j IS NULL)...
+ */
+ *quals = (Node *)make_andclause(list_make2(*quals, joinCondition));
+ xc = lnext(testnodes, xc);
+ yc = lnext(subquery_vars, yc);
+ ytlc = lnext(subselect->targetList, ytlc);
+ }
+ }
+ }
+ /* quals should be either OpExpr or BoolExpr, otherwise don't convert */
+ else
+ {
+ return false;
+ }
+
+ return true;
+}
/*
* convert_ANY_sublink_to_join: try to convert an ANY SubLink to a join
@@ -1210,7 +1606,8 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
*/
JoinExpr *
convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
- Relids available_rels)
+ bool under_not, Node **pullout,
+ Relids available_rels)
{
JoinExpr *result;
Query *parse = root->parse;
@@ -1254,6 +1651,12 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
if (contain_volatile_functions(sublink->testexpr))
return NULL;
+ if (under_not &&
+ ! allow_NOT_IN_transformation(root, sublink))
+ {
+ return NULL;
+ }
+
/* Create a dummy ParseState for addRangeTableEntryForSubquery */
pstate = make_parsestate(NULL);
@@ -1293,10 +1696,28 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
quals = convert_testexpr(root, sublink->testexpr, subquery_vars);
/*
+ * Try converting x NOT IN (y) to ANTI JOIN.
+ */
+ if(under_not &&
+ !convert_NOT_IN_to_join(root, &quals,
+ sublink, subquery_vars, pullout))
+ {
+ /*
+ * In theory, we shouldn't get here since allow_NOT_IN_transformation()
+ * has already ruled out cases that shouldn't be transformed. In other words,
+ * I expect convert_NOT_IN_to_join to always return true, but just in case
+ * it fails, reset parse->rtable which has been changed a few lines above.
+ */
+ parse->rtable = list_delete(parse->rtable, rte);
+ return NULL;
+ }
+
+ /*
* And finally, build the JoinExpr node.
*/
result = makeNode(JoinExpr);
- result->jointype = JOIN_SEMI;
+ /* NOT IN will be converted to ANTI JOIN */
+ result->jointype = under_not ? JOIN_ANTI : JOIN_SEMI;
result->isNatural = false;
result->larg = NULL; /* caller must fill this in */
result->rarg = (Node *) rtr;
@@ -1885,9 +2306,7 @@ process_sublinks_mutator(Node *node, process_sublinks_context *context)
* Now build the SubPlan node and make the expr to return.
*/
return make_subplan(context->root,
- (Query *) sublink->subselect,
- sublink->subLinkType,
- sublink->subLinkId,
+ sublink,
testexpr,
context->isTopQual);
}
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 1452172..cd1557c 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -113,7 +113,7 @@ static Query *pullup_replace_vars_subquery(Query *query,
static reduce_outer_joins_state *reduce_outer_joins_pass1(Node *jtnode);
static void reduce_outer_joins_pass2(Node *jtnode,
reduce_outer_joins_state *state,
- PlannerInfo *root,
+ Query *parse,
Relids nonnullable_rels,
List *nonnullable_vars,
List *forced_null_vars);
@@ -267,6 +267,13 @@ pull_up_sublinks_jointree_recurse(PlannerInfo *root, Node *jtnode,
}
/* Build the replacement FromExpr; no quals yet */
newf = makeFromExpr(newfromlist, NULL);
+ /*
+ * Replace parse->jointree with newf now because we might modify join types
+ * during reduce_outer_joins() in convert_NOT_IN_to_join() which is called
+ * in pull_up_sublinks_qual_recurse() that's coming up next.
+ */
+ newf->quals = f->quals;
+ root->parse->jointree = newf;
/* Set up a link representing the rebuilt jointree */
jtlink = (Node *) newf;
/* Now process qual --- all children are available for use */
@@ -399,7 +406,7 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
/* Is it a convertible ANY or EXISTS clause? */
if (sublink->subLinkType == ANY_SUBLINK)
{
- if ((j = convert_ANY_sublink_to_join(root, sublink,
+ if ((j = convert_ANY_sublink_to_join(root, sublink, false, NULL,
available_rels1)) != NULL)
{
/* Yes; insert the new join node into the join tree */
@@ -425,7 +432,7 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
return NULL;
}
if (available_rels2 != NULL &&
- (j = convert_ANY_sublink_to_join(root, sublink,
+ (j = convert_ANY_sublink_to_join(root, sublink, false, NULL,
available_rels2)) != NULL)
{
/* Yes; insert the new join node into the join tree */
@@ -571,6 +578,68 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
return NULL;
}
}
+ else if (sublink->subLinkType == ANY_SUBLINK)
+ {
+ Node *pullout = NULL;
+
+ if ((j = convert_ANY_sublink_to_join(root, sublink, true, &pullout,
+ available_rels1)) != NULL)
+ {
+ /* Yes; insert the new join node into the join tree */
+ j->larg = *jtlink1;
+ *jtlink1 = (Node *) j;
+ /* Recursively process pulled-up jointree nodes */
+ j->rarg = pull_up_sublinks_jointree_recurse(root,
+ j->rarg,
+ &child_rels);
+
+ /*
+ * Now recursively process the pulled-up quals. Any inserted
+ * joins can get stacked onto either j->larg or j->rarg,
+ * depending on which rels they reference.
+ */
+ j->quals = pull_up_sublinks_qual_recurse(root,
+ j->quals,
+ &j->larg,
+ available_rels1,
+ &j->rarg,
+ child_rels);
+ /*
+ * Return pullout predicate (x is NOT NULL) if it's not null,
+ * otherwise return NULL representing constant TRUE.
+ */
+ return pullout? pullout : NULL;
+ }
+ if (available_rels2 != NULL &&
+ (j = convert_ANY_sublink_to_join(root, sublink, true, &pullout,
+ available_rels2)) != NULL)
+ {
+ /* Yes; insert the new join node into the join tree */
+ j->larg = *jtlink2;
+ *jtlink2 = (Node *) j;
+ /* Recursively process pulled-up jointree nodes */
+ j->rarg = pull_up_sublinks_jointree_recurse(root,
+ j->rarg,
+ &child_rels);
+
+ /*
+ * Now recursively process the pulled-up quals. Any inserted
+ * joins can get stacked onto either j->larg or j->rarg,
+ * depending on which rels they reference.
+ */
+ j->quals = pull_up_sublinks_qual_recurse(root,
+ j->quals,
+ &j->larg,
+ available_rels2,
+ &j->rarg,
+ child_rels);
+ /*
+ * Return pullout predicate (x is NOT NULL) if it's not null,
+ * otherwise return NULL representing constant TRUE.
+ */
+ return pullout? pullout : NULL;
+ }
+ }
}
/* Else return it unmodified */
return node;
@@ -909,7 +978,13 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
subroot->parse = subquery;
subroot->glob = root->glob;
subroot->query_level = root->query_level;
- subroot->parent_root = root->parent_root;
+ /*
+ * Keep a path to the top level root so that we can recursively access top level
+ * CTEs in root->parse->cteList, and CTE plans in root->init_plans. This hack
+ * won't change the original PlannerInfo tree structure because subroot is just
+ * a auxiliary PlannerInfo to help pulling up subquery.
+ */
+ subroot->parent_root = root;
subroot->plan_params = NIL;
subroot->outer_params = NULL;
subroot->planner_cxt = CurrentMemoryContext;
@@ -2558,7 +2633,7 @@ flatten_simple_union_all(PlannerInfo *root)
* alias-var expansion).
*/
void
-reduce_outer_joins(PlannerInfo *root)
+reduce_outer_joins(Query *parse)
{
reduce_outer_joins_state *state;
@@ -2571,14 +2646,14 @@ reduce_outer_joins(PlannerInfo *root)
* join(s) below each side of each join clause. The second pass examines
* qual clauses and changes join types as it descends the tree.
*/
- state = reduce_outer_joins_pass1((Node *) root->parse->jointree);
+ state = reduce_outer_joins_pass1((Node *) parse->jointree);
/* planner.c shouldn't have called me if no outer joins */
if (state == NULL || !state->contains_outer)
elog(ERROR, "so where are the outer joins?");
- reduce_outer_joins_pass2((Node *) root->parse->jointree,
- state, root, NULL, NIL, NIL);
+ reduce_outer_joins_pass2((Node *) parse->jointree,
+ state, parse, NULL, NIL, NIL);
}
/*
@@ -2661,7 +2736,7 @@ reduce_outer_joins_pass1(Node *jtnode)
static void
reduce_outer_joins_pass2(Node *jtnode,
reduce_outer_joins_state *state,
- PlannerInfo *root,
+ Query *parse,
Relids nonnullable_rels,
List *nonnullable_vars,
List *forced_null_vars)
@@ -2700,7 +2775,7 @@ reduce_outer_joins_pass2(Node *jtnode,
reduce_outer_joins_state *sub_state = lfirst(s);
if (sub_state->contains_outer)
- reduce_outer_joins_pass2(lfirst(l), sub_state, root,
+ reduce_outer_joins_pass2(lfirst(l), sub_state, parse,
pass_nonnullable_rels,
pass_nonnullable_vars,
pass_forced_null_vars);
@@ -2812,7 +2887,7 @@ reduce_outer_joins_pass2(Node *jtnode,
/* Apply the jointype change, if any, to both jointree node and RTE */
if (rtindex && jointype != j->jointype)
{
- RangeTblEntry *rte = rt_fetch(rtindex, root->parse->rtable);
+ RangeTblEntry *rte = rt_fetch(rtindex, parse->rtable);
Assert(rte->rtekind == RTE_JOIN);
Assert(rte->jointype == j->jointype);
@@ -2897,7 +2972,7 @@ reduce_outer_joins_pass2(Node *jtnode,
pass_nonnullable_vars = NIL;
pass_forced_null_vars = NIL;
}
- reduce_outer_joins_pass2(j->larg, left_state, root,
+ reduce_outer_joins_pass2(j->larg, left_state, parse,
pass_nonnullable_rels,
pass_nonnullable_vars,
pass_forced_null_vars);
@@ -2919,7 +2994,7 @@ reduce_outer_joins_pass2(Node *jtnode,
pass_nonnullable_vars = NIL;
pass_forced_null_vars = NIL;
}
- reduce_outer_joins_pass2(j->rarg, right_state, root,
+ reduce_outer_joins_pass2(j->rarg, right_state, parse,
pass_nonnullable_rels,
pass_nonnullable_vars,
pass_forced_null_vars);
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 0c6fe01..46a9877 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -38,10 +38,12 @@
#include "optimizer/optimizer.h"
#include "optimizer/plancat.h"
#include "optimizer/planmain.h"
+#include "optimizer/pathnode.h"
#include "parser/analyze.h"
#include "parser/parse_agg.h"
#include "parser/parse_coerce.h"
#include "parser/parse_func.h"
+#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
#include "tcop/tcopprot.h"
#include "utils/acl.h"
@@ -2013,6 +2015,271 @@ find_forced_null_var(Node *node)
}
/*
+ * find_innerjoined_rels
+ * Traverse jointree to locate non-outerjoined-rels and quals above them
+ *
+ * We fill innerjoined_rels with the relids of all rels that are not below
+ * the nullable side of any outer join (which would cause their Vars to be
+ * possibly NULL regardless of what's in the catalogs). In the same scan,
+ * we locate all WHERE and JOIN/ON quals that constrain these rels add them to
+ * the usable_quals list (forming a list with implicit-AND semantics).
+ *
+ * Top-level caller must initialize innerjoined_rels/usable_quals to NULL/NIL.
+ */
+static void
+find_innerjoined_rels(Node *jtnode,
+ Relids *innerjoined_rels, List **usable_quals)
+{
+ if (jtnode == NULL)
+ return;
+ if (IsA(jtnode, RangeTblRef))
+ {
+ int varno = ((RangeTblRef *) jtnode)->rtindex;
+
+ *innerjoined_rels = bms_add_member(*innerjoined_rels, varno);
+ }
+ else if (IsA(jtnode, FromExpr))
+ {
+ FromExpr *f = (FromExpr *) jtnode;
+ ListCell *lc;
+
+ /* All elements of the FROM list are allowable */
+ foreach(lc, f->fromlist)
+ find_innerjoined_rels((Node *) lfirst(lc),
+ innerjoined_rels, usable_quals);
+ /* ... and its WHERE quals are too */
+ if (f->quals)
+ *usable_quals = lappend(*usable_quals, f->quals);
+ }
+ else if (IsA(jtnode, JoinExpr))
+ {
+ JoinExpr *j = (JoinExpr *) jtnode;
+
+ switch (j->jointype)
+ {
+ case JOIN_INNER:
+ /* visit both children */
+ find_innerjoined_rels(j->larg,
+ innerjoined_rels, usable_quals);
+ find_innerjoined_rels(j->rarg,
+ innerjoined_rels, usable_quals);
+ /* and grab the ON quals too */
+ if (j->quals)
+ *usable_quals = lappend(*usable_quals, j->quals);
+ break;
+
+ case JOIN_LEFT:
+ case JOIN_SEMI:
+ case JOIN_ANTI:
+
+ /*
+ * Only the left input is possibly non-nullable; furthermore,
+ * the quals of this join don't constrain the left input.
+ * Note: we probably can't see SEMI or ANTI joins at this
+ * point, but if we do, we can treat them like LEFT joins.
+ */
+ find_innerjoined_rels(j->larg,
+ innerjoined_rels, usable_quals);
+ break;
+
+ case JOIN_RIGHT:
+ /* Reverse of the above case */
+ find_innerjoined_rels(j->rarg,
+ innerjoined_rels, usable_quals);
+ break;
+
+ case JOIN_FULL:
+ /* Neither side is non-nullable, so stop descending */
+ break;
+
+ case JOIN_UNIQUE_OUTER:
+ case JOIN_UNIQUE_INNER:
+ /* Don't think we will see JOIN_UNIQUE_OUTER or
+ * JOIN_UNIQUE_INNER since they are only used internally in
+ * the planner in a much later phase (in standard_join_search).
+ */
+ break;
+
+ default:
+ elog(ERROR, "unrecognized join type: %d",
+ (int) j->jointype);
+ }
+ }
+ else
+ elog(ERROR, "unrecognized node type: %d",
+ (int) nodeTag(jtnode));
+}
+
+/*
+ * Returns true if the Node passed in is nonnullable. Currently handles Var,
+ * TargetEntry, CoaleseExpr and Const.
+ * TODO: Add more supporting cases.
+ * A Var is nonnullable if:
+ * It does not appear on the null-padded side of an outer join and it has NOT NULL constraint,
+ * Or if it's forced non-null by inner join or other strict predicates.
+ * A CoalesceExpr is nonnullable if it has a non-null argument.
+ */
+bool
+is_node_nonnullable(Node * node, Query *parse)
+{
+ AttrNumber attno = InvalidAttrNumber;
+ Oid reloid;
+ Var *var = NULL;
+
+ /*
+ * If the query contains set operations, punt. The set ops themselves
+ * couldn't introduce nulls that weren't in their inputs, but the tlist
+ * present in the top-level query is just dummy and won't give us useful
+ * info. We could get an answer by recursing to examine each leaf query,
+ * but for the moment it doesn't seem worth the extra complication.
+ *
+ * Note that we needn't consider other top-level operators such as
+ * DISTINCT, GROUP BY, etc, as those will not introduce nulls either.
+ */
+ if (parse->setOperations)
+ return false;
+
+ switch (nodeTag(node))
+ {
+ case T_Var:
+ {
+ RangeTblEntry *rte;
+
+ var = (Var *) node;
+ attno = var->varattno;
+ rte = rt_fetch(var->varno, parse->rtable);
+ reloid = rte->relid;
+ break;
+ }
+ case T_TargetEntry:
+ {
+ TargetEntry *te = (TargetEntry *)node;
+ switch(nodeTag(te->expr))
+ {
+ case T_Var:
+ {
+ var = (Var *) te->expr;
+ attno = te->resorigcol;
+ reloid = te->resorigtbl;
+ break;
+ }
+ /* recurse into is_node_nonnullable for other types of Node */
+ default:
+ return is_node_nonnullable((Node *)te->expr, parse);
+ }
+ break;
+ }
+ case T_CoalesceExpr:
+ {
+ ListCell *arg;
+ CoalesceExpr *cexpr = (CoalesceExpr *)node;
+
+ /* handle COALESCE Function by looking for non-null argument */
+ foreach(arg, cexpr->args)
+ {
+ Node *e = lfirst(arg);
+
+ /* recurse into is_node_nonnullable */
+ if (is_node_nonnullable(e, parse))
+ {
+ return true;
+ }
+ }
+ break;
+ }
+ case T_Const:
+ {
+ if(!((Const *) node)->constisnull)
+ {
+ return true;
+ }
+ break;
+ }
+ /*
+ * TODO: handle more cases to make the nullability test more accurate
+ * Assume unhandled cases are nullable.
+ */
+ default:
+ return false;
+ }
+
+ /*
+ * If we have found a Var, it is non-null if:
+ * not on NULL-padded side of an outer join and has NOT NULL constraint
+ * or forced NOT NULL by inner join conditions or by other strict predicates.
+ */
+ if(var &&
+ reloid != InvalidOid)
+ {
+ Relids innerjoined_rels = NULL;
+ List *innerjoined_useful_quals = NIL;
+ List *nonnullable_innerjoined_vars = NIL;
+
+ find_innerjoined_rels((Node *) parse->jointree,
+ &innerjoined_rels,
+ &innerjoined_useful_quals);
+
+ /*
+ * Check if the Var is from an INNER JOINed rel, it's also guaranteed
+ * to not be on the null-padded side of an outer join.
+ */
+ if (bms_is_member(var->varno, innerjoined_rels))
+ {
+ /*
+ * If Var is from a plain relation and its column is marked
+ * NOT NULL according to the catalogs, it can't produce NULL.
+ */
+ if (get_attnotnull(reloid, attno))
+ {
+ return true;
+ }
+
+ /*
+ * Otherwise check for the existance of strict predicates which filter
+ * out NULL values for this Var.
+ */
+ nonnullable_innerjoined_vars =
+ find_nonnullable_vars((Node *) innerjoined_useful_quals);
+
+ if (list_member(nonnullable_innerjoined_vars, var))
+ {
+ return true;
+ }
+ }
+
+ /*
+ * If we get here it means -
+ * The var is on null-padded side of an outer-join, since we've already
+ * tried reduce_outer_joins(), there isn't any strict predicates to turn this
+ * outer join to inner join, then there will be no strict predicates to force
+ * this var non-null as well.
+ */
+ }
+
+ return false;
+}
+
+/*
+ * Returns true if at least one Node in the input list is non-nullable
+ */
+bool
+list_hasnonnullable(List * list, Query *parse)
+{
+ ListCell *lc;
+ Node *node;
+
+ foreach(lc, list)
+ {
+ node = lfirst(lc);
+ if(is_node_nonnullable(node, parse))
+ {
+ return true;
+ }
+ }
+ return false;
+}
+
+/*
* Can we treat a ScalarArrayOpExpr as strict?
*
* If "falseOK" is true, then a "false" result can be considered strict,
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 27bbb58..61ecac7 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -934,6 +934,35 @@ get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok)
return oid;
}
+/*
+ * get_attnotnull
+ * Given the relation id and the attribute number,
+ * return the "attnotnull" field from the attribute relation.
+ */
+bool
+get_attnotnull(Oid relid, AttrNumber attnum)
+{
+ HeapTuple tp;
+ Form_pg_attribute att_tup;
+
+ tp = SearchSysCache2(ATTNUM,
+ ObjectIdGetDatum(relid),
+ Int16GetDatum(attnum));
+
+ if (HeapTupleIsValid(tp))
+ {
+ bool result;
+
+ att_tup = (Form_pg_attribute) GETSTRUCT(tp);
+ result = att_tup->attnotnull;
+ ReleaseSysCache(tp);
+
+ return result;
+ }
+ /* Assume att is nullable if no valid heap tuple is found */
+ return false;
+}
+
/* ---------- COLLATION CACHE ---------- */
/*
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index af876d1..29e3fac 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -1122,6 +1122,15 @@ static struct config_bool ConfigureNamesBool[] =
NULL, NULL, NULL
},
{
+ {"enable_not_in_transform", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("Enables the planner to transform NOT IN subquery to ANTI JOIN when possible."),
+ NULL
+ },
+ &enable_not_in_transform,
+ true,
+ NULL, NULL, NULL
+ },
+ {
{"geqo", PGC_USERSET, QUERY_TUNING_GEQO,
gettext_noop("Enables genetic query optimization."),
gettext_noop("This algorithm attempts to do planning without "
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index d73be2a..12ee3a7 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -657,6 +657,8 @@ typedef struct SubLink
List *operName; /* originally specified operator name */
Node *subselect; /* subselect as Query* or raw parsetree */
int location; /* token location, or -1 if unknown */
+ Node *subroot; /* PlannerInfo for the subquery */
+ Node *subplan; /* best Plan for the subquery */
} SubLink;
/*
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index b7456e3..5078cdd 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -45,6 +45,9 @@ extern List *find_nonnullable_vars(Node *clause);
extern List *find_forced_null_vars(Node *clause);
extern Var *find_forced_null_var(Node *clause);
+extern bool is_node_nonnullable(Node * node, Query *parse);
+extern bool list_hasnonnullable(List * list, Query *parse);
+
extern bool is_pseudo_constant_clause(Node *clause);
extern bool is_pseudo_constant_clause_relids(Node *clause, Relids relids);
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 735ba09..d103415 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -60,6 +60,7 @@ extern PGDLLIMPORT bool enable_nestloop;
extern PGDLLIMPORT bool enable_material;
extern PGDLLIMPORT bool enable_mergejoin;
extern PGDLLIMPORT bool enable_hashjoin;
+extern PGDLLIMPORT bool enable_not_in_transform;
extern PGDLLIMPORT bool enable_gathermerge;
extern PGDLLIMPORT bool enable_partitionwise_join;
extern PGDLLIMPORT bool enable_partitionwise_aggregate;
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.h
index 19c9230..7f807c4 100644
--- a/src/include/optimizer/prep.h
+++ b/src/include/optimizer/prep.h
@@ -26,7 +26,7 @@ extern void pull_up_sublinks(PlannerInfo *root);
extern void preprocess_function_rtes(PlannerInfo *root);
extern void pull_up_subqueries(PlannerInfo *root);
extern void flatten_simple_union_all(PlannerInfo *root);
-extern void reduce_outer_joins(PlannerInfo *root);
+extern void reduce_outer_joins(Query *parse);
extern void remove_useless_result_rtes(PlannerInfo *root);
extern Relids get_relids_in_jointree(Node *jtnode, bool include_joins);
extern Relids get_relids_for_join(Query *query, int joinrelid);
diff --git a/src/include/optimizer/subselect.h b/src/include/optimizer/subselect.h
index d6a872b..a4d309e 100644
--- a/src/include/optimizer/subselect.h
+++ b/src/include/optimizer/subselect.h
@@ -19,6 +19,8 @@
extern void SS_process_ctes(PlannerInfo *root);
extern JoinExpr *convert_ANY_sublink_to_join(PlannerInfo *root,
SubLink *sublink,
+ bool under_not,
+ Node **pullout,
Relids available_rels);
extern JoinExpr *convert_EXISTS_sublink_to_join(PlannerInfo *root,
SubLink *sublink,
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 4e646c5..1e585aa 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -90,6 +90,7 @@ extern char get_attgenerated(Oid relid, AttrNumber attnum);
extern Oid get_atttype(Oid relid, AttrNumber attnum);
extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum,
Oid *typid, int32 *typmod, Oid *collid);
+extern bool get_attnotnull(Oid relid, AttrNumber attnum);
extern Oid get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok);
extern char *get_collation_name(Oid colloid);
extern bool get_collation_isdeterministic(Oid colloid);
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 4c6cd5f..9a54f35 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1614,3 +1614,2789 @@ select * from x for update;
Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3
(2 rows)
+-- test NON IN to ANTI JOIN conversion
+CREATE TABLE s (u INTEGER NOT NULL, n INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
+insert into s (u, n, nn, p)
+ select
+ generate_series(1,3) as u,
+ generate_series(1,3) as n,
+ generate_series(1,3) as nn,
+ 'foo' as p;
+insert into s values(1000002, 1000002, 1000002, 'foofoo');
+UPDATE s set n = NULL WHERE n = 3;
+analyze s;
+CREATE TABLE l (u INTEGER NOT NULL, n INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
+insert into l (u, n, nn, p)
+ select
+ generate_series(1,10000 ) as u,
+ generate_series(1,10000 ) as n,
+ generate_series(1,10000 ) as nn,
+ 'bar' as p;
+UPDATE l set n = NULL WHERE n = 7;
+CREATE UNIQUE INDEX l_u ON l (u);
+CREATE INDEX l_n ON l (n);
+CREATE INDEX l_nn ON l (nn);
+analyze l;
+CREATE TABLE s1 (u INTEGER NOT NULL, n INTEGER NULL, n1 INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
+insert into s1 (u, n, n1, nn, p)
+ select
+ generate_series(1,3) as u,
+ generate_series(1,3) as n,
+ generate_series(1,3) as n1,
+ generate_series(1,3) as nn,
+ 'foo' as p;
+insert into s1 values(1000003, 1000003, 1000003, 1000003, 'foofoo');
+insert into s1 values(1003, 1003, 1003, 1003, 'foofoo');
+UPDATE s1 set n = NULL WHERE n = 3;
+UPDATE s1 set n1 = NULL WHERE n = 2;
+UPDATE s1 set n1 = NULL WHERE n1 = 3;
+analyze s1;
+CREATE TABLE empty (u INTEGER NOT NULL, n INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
+analyze empty;
+-- set work_mem to 64KB so that NOT IN to ANTI JOIN optimization will kick in
+set work_mem = 64;
+-- correctness test 1: inner empty, return every thing from outer including NULL
+explain (costs false) select * from s where n not in (select n from empty);
+ QUERY PLAN
+------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Seq Scan on empty
+(4 rows)
+
+select * from s where n not in (select n from empty);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1 | 1 | 1 | foo
+ 2 | 2 | 2 | foo
+ 1000002 | 1000002 | 1000002 | foofoo
+ 3 | | 3 | foo
+(4 rows)
+
+-- correctness test 2: inner has NULL, return empty result
+explain (costs false) select * from s where n not in (select n from l);
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(12 rows)
+
+select * from s where n not in (select n from l);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+-- correctness test 3: inner non-null, result has no NULL
+explain (costs false) select * from s where n not in (select u from l);
+ QUERY PLAN
+-----------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Index Only Scan using l_u on l
+ Index Cond: (u = s.n)
+(7 rows)
+
+select * from s where n not in (select u from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+-- correctness test 4: inner has predicate
+explain (costs false) select * from s where n not in (select n from l where u > 7);
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ Filter: (u > 7)
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ Filter: (u > 7)
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(14 rows)
+
+select * from s where n not in (select n from l where u > 7);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1 | 1 | 1 | foo
+ 2 | 2 | 2 | foo
+ 1000002 | 1000002 | 1000002 | foofoo
+(3 rows)
+
+-- correctness test 5: multi-expression, (2, 2, null, 2, foo) should be in the result
+explain (costs false) select * from s1 where (n,n1) not in (select u,nn from l where u >= 3);
+ QUERY PLAN
+-----------------------------------------------------------------
+ Nested Loop Anti Join
+ Join Filter: (((s1.n = l.u) AND (s1.n1 = l.nn)) IS NOT FALSE)
+ -> Seq Scan on s1
+ -> Materialize
+ -> Seq Scan on l
+ Filter: (u >= 3)
+(6 rows)
+
+select * from s1 where (n,n1) not in (select u,nn from l where u >= 3);
+ u | n | n1 | nn | p
+---------+---------+---------+---------+--------
+ 1 | 1 | 1 | 1 | foo
+ 1000003 | 1000003 | 1000003 | 1000003 | foofoo
+ 2 | 2 | | 2 | foo
+(3 rows)
+
+-- correctness test 6: multi-expression, (3, null, null, 3, foo) should not be in the result
+explain (costs false) select * from s1 where (n,n1) not in (select u,nn from l where u > 0);
+ QUERY PLAN
+-----------------------------------------------------------------
+ Nested Loop Anti Join
+ Join Filter: (((s1.n = l.u) AND (s1.n1 = l.nn)) IS NOT FALSE)
+ -> Seq Scan on s1
+ -> Materialize
+ -> Seq Scan on l
+ Filter: (u > 0)
+(6 rows)
+
+select * from s1 where (n,n1) not in (select u,nn from l where u > 0);
+ u | n | n1 | nn | p
+---------+---------+---------+---------+--------
+ 1000003 | 1000003 | 1000003 | 1000003 | foofoo
+(1 row)
+
+-- correctness test 6: multi-expression, (3, null, null, 3, foo) should be in the result
+explain (costs false) select * from s1 where (n,n1) not in (select u,nn from l where u < 0);
+ QUERY PLAN
+------------------------------------
+ Seq Scan on s1
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Index Scan using l_u on l
+ Index Cond: (u < 0)
+(5 rows)
+
+select * from s1 where (n,n1) not in (select u,nn from l where u < 0);
+ u | n | n1 | nn | p
+---------+---------+---------+---------+--------
+ 1 | 1 | 1 | 1 | foo
+ 1000003 | 1000003 | 1000003 | 1000003 | foofoo
+ 1003 | 1003 | 1003 | 1003 | foofoo
+ 2 | 2 | | 2 | foo
+ 3 | | | 3 | foo
+(5 rows)
+
+-- test using hashed subplan when inner fits in work_mem
+explain (costs false) select * from l where n not in (select n from s);
+ QUERY PLAN
+------------------------------------
+ Seq Scan on l
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Seq Scan on s
+(4 rows)
+
+select * from l where n not in (select n from s);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+-- test single expression
+explain (costs false) select * from s where n not in (select n from l);
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(12 rows)
+
+select * from s where n not in (select n from l);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where u not in (select u from l);
+ QUERY PLAN
+--------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_u on l
+ Index Cond: (u = s.u)
+(4 rows)
+
+select * from s where u not in (select u from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where 3*n not in (select n from l);
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Seq Scan on s
+ Filter: (((3 * n) IS NOT NULL) OR (NOT $0))
+ -> Bitmap Heap Scan on l
+ Recheck Cond: (((3 * s.n) = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = (3 * s.n))
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(12 rows)
+
+select * from s where 3*n not in (select n from l);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where n not in (select 3*n from l);
+ QUERY PLAN
+-----------------------------------------------------------
+ Nested Loop Anti Join
+ Join Filter: ((s.n = (3 * l.n)) OR ((3 * l.n) IS NULL))
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Materialize
+ -> Seq Scan on l
+(8 rows)
+
+select * from s where n not in (select 3*n from l);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+-- test single expression with predicates
+explain (costs false) select * from s where n not in (select n from l where u > 0);
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ Filter: (u > 0)
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ Filter: (u > 0)
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(14 rows)
+
+select * from s where n not in (select n from l where u > 0);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where n not in (select n from l where u > 100);
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ Filter: (u > 100)
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ Filter: (u > 100)
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(14 rows)
+
+select * from s where n not in (select n from l where u > 100);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1 | 1 | 1 | foo
+ 2 | 2 | 2 | foo
+ 1000002 | 1000002 | 1000002 | foofoo
+(3 rows)
+
+-- test multi expression
+explain (costs false) select * from s where (n,u) not in (select n,u from l);
+ QUERY PLAN
+-------------------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Scan using l_u on l
+ Index Cond: (u = s.u)
+ Filter: ((s.n = n) OR (n IS NULL) OR (s.n IS NULL))
+(5 rows)
+
+select * from s where (n,u) not in (select n,u from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where (u, nn) not in (select u, nn from l);
+ QUERY PLAN
+----------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ Filter: (s.u = u)
+(5 rows)
+
+select * from s where (u, nn) not in (select u, nn from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where (n,u) not in (select u,n from l);
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.u = n) OR (n IS NULL))
+ Filter: ((s.n = u) OR (s.n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.u)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(10 rows)
+
+select * from s where (n,u) not in (select u,n from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where (n,u,nn) not in (select u,n,nn from l);
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ Filter: (((s.n = u) OR (s.n IS NULL)) AND ((s.u = n) OR (n IS NULL)))
+(5 rows)
+
+select * from s where (n,u,nn) not in (select u,n,nn from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where (n,u,nn) not in (select u,n,nn from l where u > 1000);
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ Filter: ((u > 1000) AND ((s.n = u) OR (s.n IS NULL)) AND ((s.u = n) OR (n IS NULL)))
+(5 rows)
+
+select * from s where (n,u,nn) not in (select u,n,nn from l where u > 1000);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1 | 1 | 1 | foo
+ 2 | 2 | 2 | foo
+ 1000002 | 1000002 | 1000002 | foofoo
+ 3 | | 3 | foo
+(4 rows)
+
+explain (costs false) select * from s where (n,u,nn) not in (select u,n,nn from l where u > 0);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ Filter: ((u > 0) AND ((s.n = u) OR (s.n IS NULL)) AND ((s.u = n) OR (n IS NULL)))
+(5 rows)
+
+select * from s where (n,u,nn) not in (select u,n,nn from l where u > 0);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where (n,u,nn) not in (select u,n,nn from l where u > 1);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ Filter: ((u > 1) AND ((s.n = u) OR (s.n IS NULL)) AND ((s.u = n) OR (n IS NULL)))
+(5 rows)
+
+select * from s where (n,u,nn) not in (select u,n,nn from l where u > 1);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1 | 1 | 1 | foo
+ 1000002 | 1000002 | 1000002 | foofoo
+(2 rows)
+
+-- test multi-table
+explain (costs false) select count(*) from s, l where s.n not in (select n from l);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Nested Loop
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Bitmap Heap Scan on l l_1
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+ -> Seq Scan on l
+(15 rows)
+
+select count(*) from s, l where s.n not in (select n from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s, l where s.nn not in (select nn from l);
+ QUERY PLAN
+-------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_nn on l l_1
+ Index Cond: (nn = s.nn)
+ -> Seq Scan on l
+(7 rows)
+
+select count(*) from s, l where s.nn not in (select nn from l);
+ count
+-------
+ 10000
+(1 row)
+
+-- test null padded results from outer join
+explain (costs false) select * from s where n not in (select s.nn from l left join s on l.nn = s.nn);
+ QUERY PLAN
+-----------------------------------------------------
+ Nested Loop Anti Join
+ Join Filter: ((s.n = s_1.nn) OR (s_1.nn IS NULL))
+ InitPlan 1 (returns $0)
+ -> Nested Loop Left Join
+ Join Filter: (l_1.nn = s_2.nn)
+ -> Seq Scan on l l_1
+ -> Materialize
+ -> Seq Scan on s s_2
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Hash Left Join
+ Hash Cond: (l.nn = s_1.nn)
+ -> Seq Scan on l
+ -> Hash
+ -> Seq Scan on s s_1
+(15 rows)
+
+select * from s where n not in (select s.nn from l left join s on l.nn = s.nn);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where n not in (select s.nn from s right join l on s.nn = l.nn);
+ QUERY PLAN
+-----------------------------------------------------
+ Nested Loop Anti Join
+ Join Filter: ((s.n = s_1.nn) OR (s_1.nn IS NULL))
+ InitPlan 1 (returns $0)
+ -> Nested Loop Left Join
+ Join Filter: (s_2.nn = l_1.nn)
+ -> Seq Scan on l l_1
+ -> Materialize
+ -> Seq Scan on s s_2
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Hash Left Join
+ Hash Cond: (l.nn = s_1.nn)
+ -> Seq Scan on l
+ -> Hash
+ -> Seq Scan on s s_1
+(15 rows)
+
+select * from s where n not in (select s.nn from s right join l on s.nn = l.nn);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where l.nn not in (select nn from s);
+ QUERY PLAN
+------------------------------------------------
+ Aggregate
+ -> Hash Left Join
+ Hash Cond: (l.nn = s.nn)
+ -> Seq Scan on l
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Seq Scan on s s_1
+ -> Hash
+ -> Seq Scan on s
+(9 rows)
+
+select count(*) from s right join l on s.nn = l.nn where l.nn not in (select nn from s);
+ count
+-------
+ 9997
+(1 row)
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from s);
+ QUERY PLAN
+------------------------------------------
+ Aggregate
+ -> Hash Left Join
+ Hash Cond: (l.nn = s.nn)
+ Filter: (NOT (hashed SubPlan 1))
+ -> Seq Scan on l
+ -> Hash
+ -> Seq Scan on s
+ SubPlan 1
+ -> Seq Scan on s s_1
+(9 rows)
+
+select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from s);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s right join l on s.nn=l.nn where l.nn not in (select l.nn from l left join
son l.nn = s.nn);
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
+ -> Nested Loop Left Join
+ Join Filter: (s.nn = l.nn)
+ -> Hash Anti Join
+ Hash Cond: (l.nn = l_1.nn)
+ -> Seq Scan on l
+ -> Hash
+ -> Hash Left Join
+ Hash Cond: (l_1.nn = s_1.nn)
+ -> Seq Scan on l l_1
+ -> Hash
+ -> Seq Scan on s s_1
+ -> Seq Scan on s
+(13 rows)
+
+select count(*) from s right join l on s.nn=l.nn where l.nn not in (select l.nn from l left join s on l.nn = s.nn);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s right join l on s.nn=l.nn where s.nn not in (select s.nn from l left join
son l.nn = s.nn);
+ QUERY PLAN
+------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Nested Loop Left Join
+ Join Filter: (l_2.nn = s_2.nn)
+ -> Seq Scan on l l_2
+ -> Materialize
+ -> Seq Scan on s s_2
+ -> Nested Loop Anti Join
+ Join Filter: ((s.nn = s_1.nn) OR (s_1.nn IS NULL))
+ -> Hash Left Join
+ Hash Cond: (l.nn = s.nn)
+ Filter: ((s.nn IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on l
+ -> Hash
+ -> Seq Scan on s
+ -> Materialize
+ -> Hash Left Join
+ Hash Cond: (l_1.nn = s_1.nn)
+ -> Seq Scan on l l_1
+ -> Hash
+ -> Seq Scan on s s_1
+(21 rows)
+
+select count(*) from s right join l on s.nn=l.nn where s.nn not in (select s.nn from l left join s on l.nn = s.nn);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s left join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn
froml);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop Left Join
+ Join Filter: (s.u = s1.u)
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_nn on l l_1
+ Index Cond: (nn = s.nn)
+ -> Seq Scan on s1
+ -> Index Only Scan using l_u on l
+ Index Cond: (u = s.u)
+(11 rows)
+
+select count(*) from s left join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s left join s1 on s.u=s1.u right join l on s.u=l.u where s.nn not in
(selectnn from l);
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Hash Anti Join
+ Hash Cond: (s.nn = l_1.nn)
+ -> Hash Left Join
+ Hash Cond: (l.u = s.u)
+ Filter: ((s.nn IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on l
+ -> Hash
+ -> Hash Right Join
+ Hash Cond: (s1.u = s.u)
+ -> Seq Scan on s1
+ -> Hash
+ -> Seq Scan on s
+ -> Hash
+ -> Seq Scan on l l_1
+(17 rows)
+
+select count(*) from s left join s1 on s.u=s1.u right join l on s.u=l.u where s.nn not in (select nn from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s left join s1 on s.u=s1.u left join l on s.u=l.u where s.nn not in (select
nnfrom l);
+ QUERY PLAN
+---------------------------------------------------
+ Aggregate
+ -> Nested Loop Left Join
+ Join Filter: (s.u = s1.u)
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ -> Seq Scan on s1
+(8 rows)
+
+select count(*) from s left join s1 on s.u=s1.u left join l on s.u=l.u where s.nn not in (select nn from l);
+ count
+-------
+ 1
+(1 row)
+
+explain (costs false) select count(*) from s right join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn
froml);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (s.u = s1.u)
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_nn on l l_1
+ Index Cond: (nn = s.nn)
+ -> Seq Scan on s1
+ -> Index Only Scan using l_u on l
+ Index Cond: (u = s.u)
+(11 rows)
+
+select count(*) from s right join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s join s1 on s.u=s1.u right join l on s.u=l.u where s.nn not in (select nn
froml);
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Hash Anti Join
+ Hash Cond: (s.nn = l_1.nn)
+ -> Hash Left Join
+ Hash Cond: (l.u = s.u)
+ Filter: ((s.nn IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on l
+ -> Hash
+ -> Hash Join
+ Hash Cond: (s1.u = s.u)
+ -> Seq Scan on s1
+ -> Hash
+ -> Seq Scan on s
+ -> Hash
+ -> Seq Scan on l l_1
+(17 rows)
+
+select * from s join s1 on s.u=s1.u right join l on s.u=l.u where s.nn not in (select nn from l);
+ u | n | nn | p | u | n | n1 | nn | p | u | n | nn | p
+---+---+----+---+---+---+----+----+---+---+---+----+---
+(0 rows)
+
+explain (costs false) select count(*) from s full join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn
froml);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop Left Join
+ Join Filter: (s.u = s1.u)
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_nn on l l_1
+ Index Cond: (nn = s.nn)
+ -> Seq Scan on s1
+ -> Index Only Scan using l_u on l
+ Index Cond: (u = s.u)
+(11 rows)
+
+select count(*) from s full join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s join s1 on s.u=s1.u full join l on s.u=l.u where s.nn not in (select nn
froml);
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Hash Anti Join
+ Hash Cond: (s.nn = l_1.nn)
+ -> Hash Full Join
+ Hash Cond: (l.u = s.u)
+ Filter: ((s.nn IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on l
+ -> Hash
+ -> Hash Join
+ Hash Cond: (s1.u = s.u)
+ -> Seq Scan on s1
+ -> Hash
+ -> Seq Scan on s
+ -> Hash
+ -> Seq Scan on l l_1
+(17 rows)
+
+select count(*) from s join s1 on s.u=s1.u full join l on s.u=l.u where s.nn not in (select nn from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select * from s where s.nn not in (select l.nn from l left join s on l.nn=s.nn left join s1 on
l.nn=s1.nn);
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Nested Loop Left Join
+ Join Filter: (l.nn = s1.nn)
+ -> Nested Loop Left Join
+ Join Filter: (l.nn = s_1.nn)
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ -> Seq Scan on s s_1
+ -> Seq Scan on s1
+(10 rows)
+
+select * from s where s.nn not in (select l.nn from l left join s on l.nn=s.nn left join s1 on l.nn=s1.nn);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where s.nn not in (select l.nn from l left join s on l.nn=s.nn right join s1 on
l.nn=s1.nn);
+ QUERY PLAN
+-----------------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop Left Join
+ Join Filter: (l.nn = s_1.nn)
+ -> Nested Loop Left Join
+ -> Seq Scan on s1
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s1.nn)
+ -> Materialize
+ -> Seq Scan on s s_1
+(11 rows)
+
+select * from s where s.nn not in (select l.nn from l left join s on l.nn=s.nn right join s1 on l.nn=s1.nn);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where (n,u,nn) not in (select l.n,l.u,l.nn from l left join s on l.nn = s.nn);
+ QUERY PLAN
+-----------------------------------------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Nested Loop Left Join
+ Join Filter: (l.nn = s_1.nn)
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ Filter: (((s.n = n) OR (n IS NULL) OR (s.n IS NULL)) AND (s.u = u))
+ -> Seq Scan on s s_1
+(8 rows)
+
+select * from s where (n,u,nn) not in (select l.n,l.u,l.nn from l left join s on l.nn = s.nn);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where (n,u,nn) not in (select l.n,l.u,l.nn from l right join s on l.nn = s.nn);
+ QUERY PLAN
+-------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop Left Join
+ -> Seq Scan on s s_1
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s_1.nn)
+(7 rows)
+
+select * from s where (n,u,nn) not in (select l.n,l.u,l.nn from l left join s on l.nn = s.nn);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+--test reduce outer joins from outer query
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from l);
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Hash Anti Join
+ Hash Cond: (s.nn = l_1.nn)
+ -> Hash Left Join
+ Hash Cond: (l.nn = s.nn)
+ Filter: ((s.nn IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on l
+ -> Hash
+ -> Seq Scan on s
+ -> Hash
+ -> Seq Scan on l l_1
+(13 rows)
+
+select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from l) and
s.u>0;
+ QUERY PLAN
+-------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: (u > 0)
+ -> Index Only Scan using l_nn on l l_1
+ Index Cond: (nn = s.nn)
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+(9 rows)
+
+select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from l) and s.u>0;
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn join s1 on s.u = s1.u where s.nn not in
(selectnn from l);
+ QUERY PLAN
+-------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (s.u = s1.u)
+ -> Nested Loop
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_nn on l l_1
+ Index Cond: (nn = s.nn)
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ -> Seq Scan on s1
+(11 rows)
+
+select count(*) from s right join l on s.nn = l.nn join s1 on s.u = s1.u where s.nn not in (select nn from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn right join s1 on s.u = s1.u where s.nn not in
(selectnn from l);
+ QUERY PLAN
+---------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Nested Loop Anti Join
+ -> Nested Loop Left Join
+ Join Filter: (s.u = s1.u)
+ Filter: ((s.nn IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on s1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ -> Index Only Scan using l_nn on l l_1
+ Index Cond: (nn = s.nn)
+(15 rows)
+
+select count(*) from s right join l on s.nn = l.nn right join s1 on s.u = s1.u where s.nn not in (select nn from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn left join s1 on s.u = s1.u where s.nn not in
(selectnn from l);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Nested Loop Left Join
+ Join Filter: (s.u = s1.u)
+ -> Hash Anti Join
+ Hash Cond: (s.nn = l_1.nn)
+ -> Hash Left Join
+ Hash Cond: (l.nn = s.nn)
+ Filter: ((s.nn IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on l
+ -> Hash
+ -> Seq Scan on s
+ -> Hash
+ -> Seq Scan on l l_1
+ -> Seq Scan on s1
+(16 rows)
+
+select count(*) from s right join l on s.nn = l.nn left join s1 on s.u = s1.u where s.nn not in (select nn from l);
+ count
+-------
+ 0
+(1 row)
+
+--test reduce outer joins from subquery
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn);
+ QUERY PLAN
+-----------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop Left Join
+ -> Seq Scan on s s_1
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s_1.nn)
+(7 rows)
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn where l.u > 9);
+ QUERY PLAN
+-------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s_1.nn)
+ Filter: (u > 9)
+(8 rows)
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn where l.u > 9);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1 | 1 | 1 | foo
+ 2 | 2 | 2 | foo
+ 1000002 | 1000002 | 1000002 | foofoo
+ 3 | | 3 | foo
+(4 rows)
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn where s.u > 9);
+ QUERY PLAN
+-----------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop Left Join
+ -> Seq Scan on s s_1
+ Filter: (u > 9)
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s_1.nn)
+(8 rows)
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn where s.u > 9);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn join s1 on l.n =
s1.n);
+ QUERY PLAN
+-------------------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop
+ Join Filter: (l.n = s1.n)
+ -> Seq Scan on s1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s_1.nn)
+(11 rows)
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn join s1 on l.n = s1.n);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+ 3 | | 3 | foo
+(2 rows)
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn right join s1 on
l.n= s1.n);
+ QUERY PLAN
+-------------------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop Left Join
+ Join Filter: (l.n = s1.n)
+ -> Seq Scan on s1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s_1.nn)
+(11 rows)
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn right join s1 on l.n = s1.n);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn left join s1 on
l.n= s1.n);
+ QUERY PLAN
+-------------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop Left Join
+ Join Filter: (l.n = s1.n)
+ -> Nested Loop Left Join
+ -> Seq Scan on s s_1
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s_1.nn)
+ -> Materialize
+ -> Seq Scan on s1
+(11 rows)
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn left join s1 on l.n = s1.n);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+--test reduce outer join on outer and sub-query
+explain (costs false) select count(*) from s right join l on s.nn = l.nn join s1 on s.u = s1.u where s.nn not in
(selectl.nn from l right join s on l.nn = s.nn join s1 on l.n = s1.n);
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (s.u = s1.u)
+ -> Seq Scan on s1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop
+ Join Filter: (l_1.n = s1_1.n)
+ -> Seq Scan on s1 s1_1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Scan using l_nn on l l_1
+ Index Cond: (nn = s_1.nn)
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+(19 rows)
+
+select count(*) from s right join l on s.nn = l.nn join s1 on s.u = s1.u where s.nn not in (select l.nn from l right
joins on l.nn = s.nn join s1 on l.n = s1.n);
+ count
+-------
+ 1
+(1 row)
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn left join s1 on s.u = s1.u where s.nn not in
(selectl.nn from l right join s on l.nn = s.nn left join s1 on l.n = s1.n);
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Hash Left Join
+ Hash Cond: (l.nn = s.nn)
+ Filter: (NOT (hashed SubPlan 1))
+ -> Seq Scan on l
+ -> Hash
+ -> Hash Right Join
+ Hash Cond: (s1.u = s.u)
+ -> Seq Scan on s1
+ -> Hash
+ -> Seq Scan on s
+ SubPlan 1
+ -> Nested Loop Left Join
+ Join Filter: (l_1.n = s1_1.n)
+ -> Nested Loop Left Join
+ -> Seq Scan on s s_1
+ -> Index Scan using l_nn on l l_1
+ Index Cond: (nn = s_1.nn)
+ -> Materialize
+ -> Seq Scan on s1 s1_1
+(20 rows)
+
+select count(*) from s right join l on s.nn = l.nn left join s1 on s.u = s1.u where s.nn not in (select l.nn from l
rightjoin s on l.nn = s.nn left join s1 on l.n = s1.n);
+ count
+-------
+ 0
+(1 row)
+
+-- test union all
+explain (costs false) select * from s as t where not exists
+(select 1 from (select n as y from l union all
+ select u as y from s union all
+ select nn as y from s) as v where t.n=v.y or v.y is null) and n is not null;
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s t
+ Filter: (n IS NOT NULL)
+ -> Append
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((t.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = t.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+ -> Seq Scan on s
+ Filter: ((t.n = u) OR (u IS NULL))
+ -> Seq Scan on s s_1
+ Filter: ((t.n = nn) OR (nn IS NULL))
+(15 rows)
+
+select * from s as t where not exists
+(select 1 from (select n as y from l union all
+ select u as y from s union all
+ select nn as y from s) as v where t.n=v.y or v.y is null) and n is not null;
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where n not in
+(select n as y from l union all
+ select u as y from s union all
+ select nn as y from s);
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Result
+ -> Append
+ -> Seq Scan on l l_1
+ -> Seq Scan on s s_3
+ -> Seq Scan on s s_4
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Append
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+ -> Seq Scan on s s_1
+ Filter: ((s.n = u) OR (u IS NULL))
+ -> Seq Scan on s s_2
+ Filter: ((s.n = nn) OR (nn IS NULL))
+(21 rows)
+
+select * from s where n not in
+(select n as y from l union all
+ select u as y from s union all
+ select nn as y from s);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select count(*) from
+(select n as x from s union all select u as x from l) t where t.x not in
+(select nn from l);
+ QUERY PLAN
+-----------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Hash Anti Join
+ Hash Cond: (s.n = l_1.nn)
+ -> Append
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on l
+ Filter: ((u IS NOT NULL) OR (NOT $0))
+ -> Hash
+ -> Seq Scan on l l_1
+(12 rows)
+
+select count(*) from
+(select n as x from s union all select u as x from l) t where t.x not in
+(select nn from l);
+ count
+-------
+ 1
+(1 row)
+
+explain (costs false) select count(*) from
+(select n as x from s union all select n as x from l) t where t.x not in
+(select nn from empty);
+ QUERY PLAN
+------------------------------------------------
+ Aggregate
+ -> Append
+ -> Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Seq Scan on empty
+ -> Seq Scan on l
+ Filter: (NOT (hashed SubPlan 1))
+(8 rows)
+
+select count(*) from
+(select n as x from s union all select n as x from l) t where t.x not in
+(select nn from empty);
+ count
+-------
+ 10004
+(1 row)
+
+explain (costs false) select count(*) from
+(select n as x from s union all select u as x from l) t where t.x not in
+(select n as y from l union all
+ select u as y from s union all
+ select nn as y from s);
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Finalize Aggregate
+ InitPlan 1 (returns $0)
+ -> Result
+ -> Append
+ -> Seq Scan on l l_2
+ -> Seq Scan on s s_3
+ -> Seq Scan on s s_4
+ -> Gather
+ Workers Planned: 2
+ Params Evaluated: $0
+ -> Partial Aggregate
+ -> Nested Loop Anti Join
+ -> Parallel Append
+ -> Parallel Seq Scan on l
+ Filter: ((u IS NOT NULL) OR (NOT $0))
+ -> Parallel Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Append
+ -> Bitmap Heap Scan on l l_1
+ Recheck Cond: ((l.u = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = l.u)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+ -> Seq Scan on s s_1
+ Filter: ((l.u = u) OR (u IS NULL))
+ -> Seq Scan on s s_2
+ Filter: ((l.u = nn) OR (nn IS NULL))
+(29 rows)
+
+select count(*) from
+(select n as x from s union all select u as x from l) t where t.x not in
+(select n as y from l union all
+ select u as y from s union all
+ select nn as y from s);
+ count
+-------
+ 0
+(1 row)
+
+-- test multi-levels of NOT IN
+explain (costs false) select * from s where n not in (select n from s where n not in (select n from l));
+ QUERY PLAN
+------------------------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 2))
+ SubPlan 2
+ -> Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l
+ -> Seq Scan on s s_1
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Bitmap Heap Scan on l l_1
+ Recheck Cond: ((s_1.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s_1.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(15 rows)
+
+select * from s where n not in (select n from s where n not in (select n from l));
+ u | n | nn | p
+---------+---------+---------+--------
+ 1 | 1 | 1 | foo
+ 2 | 2 | 2 | foo
+ 1000002 | 1000002 | 1000002 | foofoo
+ 3 | | 3 | foo
+(4 rows)
+
+explain (costs false) select * from s where n not in (select n from s where n not in (select u from l));
+ QUERY PLAN
+-------------------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 2))
+ SubPlan 2
+ -> Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l
+ -> Seq Scan on s s_1
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Index Only Scan using l_u on l l_1
+ Index Cond: (u = s_1.n)
+(10 rows)
+
+select * from s where n not in (select n from s where n not in (select u from l));
+ u | n | nn | p
+---+---+----+-----
+ 1 | 1 | 1 | foo
+ 2 | 2 | 2 | foo
+(2 rows)
+
+explain (costs false) select count(*) from s where u not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n));
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on s
+ Filter: (NOT (SubPlan 2))
+ SubPlan 2
+ -> Result
+ One-Time Filter: (NOT $2)
+ InitPlan 1 (returns $2)
+ -> Nested Loop Anti Join
+ -> Seq Scan on s1
+ Filter: (n = s.n)
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s1.u = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s1.u)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+ -> Seq Scan on s1 s1_1
+(18 rows)
+
+select count(*) from s where u not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n));
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select * from s where n not in (select n from s1) and u not in (select u from s1) and nn not in
(selectnn from s1);
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Seq Scan on s
+ Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)) AND (NOT (hashed SubPlan 3)))
+ SubPlan 1
+ -> Seq Scan on s1
+ SubPlan 2
+ -> Seq Scan on s1 s1_1
+ SubPlan 3
+ -> Seq Scan on s1 s1_2
+(8 rows)
+
+select * from s where n not in (select n from s1) and u not in (select u from s1) and nn not in (select nn from s1);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where n not in (select n from s1) and u not in (select u from s1) and nn not in
(selectnn from l);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: ((NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)))
+ SubPlan 1
+ -> Seq Scan on s1
+ SubPlan 2
+ -> Seq Scan on s1 s1_1
+ -> Index Only Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+(9 rows)
+
+select * from s where n not in (select n from s1) and u not in (select u from s1) and nn not in (select nn from l);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select count(*) from s where u not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n))
+and nn not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n));
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on s
+ Filter: ((NOT (SubPlan 2)) AND (NOT (SubPlan 4)))
+ SubPlan 2
+ -> Result
+ One-Time Filter: (NOT $2)
+ InitPlan 1 (returns $2)
+ -> Nested Loop Anti Join
+ -> Seq Scan on s1
+ Filter: (n = s.n)
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s1.u = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s1.u)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+ -> Seq Scan on s1 s1_1
+ SubPlan 4
+ -> Result
+ One-Time Filter: (NOT $6)
+ InitPlan 3 (returns $6)
+ -> Nested Loop Anti Join
+ -> Seq Scan on s1 s1_2
+ Filter: (n = s.n)
+ -> Bitmap Heap Scan on l l_1
+ Recheck Cond: ((s1_2.u = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s1_2.u)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+ -> Seq Scan on s1 s1_3
+(33 rows)
+
+select count(*) from s where u not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n))
+and nn not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n));
+ count
+-------
+ 0
+(1 row)
+
+--test COALESCE
+explain (costs false) select * from s where COALESCE(n, -1) not in (select COALESCE(n, -1) from l);
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Hash Anti Join
+ Hash Cond: (COALESCE(s.n, '-1'::integer) = COALESCE(l.n, '-1'::integer))
+ -> Seq Scan on s
+ -> Hash
+ -> Seq Scan on l
+(5 rows)
+
+select * from s where COALESCE(n, -1) not in (select COALESCE(n, -1) from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where COALESCE(n, NULL, -1) not in (select COALESCE(n, NULL, -1) from l);
+ QUERY PLAN
+----------------------------------------------------------------------------
+ Hash Anti Join
+ Hash Cond: (COALESCE(s.n, '-1'::integer) = COALESCE(l.n, '-1'::integer))
+ -> Seq Scan on s
+ -> Hash
+ -> Seq Scan on l
+(5 rows)
+
+select * from s where COALESCE(n, NULL, -1) not in (select COALESCE(n, NULL, -1) from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where COALESCE(n, NULL, NULL) not in (select COALESCE(n, NULL, NULL) from l);
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Nested Loop Anti Join
+ Join Filter: ((COALESCE(s.n) = COALESCE(l.n)) OR (COALESCE(l.n) IS NULL))
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Seq Scan on s
+ Filter: ((COALESCE(n) IS NOT NULL) OR (NOT $0))
+ -> Materialize
+ -> Seq Scan on l
+(8 rows)
+
+select * from s where COALESCE(n, NULL, NULL) not in (select COALESCE(n, NULL, NULL) from l);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where COALESCE(n, nn) not in (select COALESCE(n, nn) from l);
+ QUERY PLAN
+----------------------------------------------------------
+ Hash Anti Join
+ Hash Cond: (COALESCE(s.n, s.nn) = COALESCE(l.n, l.nn))
+ -> Seq Scan on s
+ -> Hash
+ -> Seq Scan on l
+(5 rows)
+
+select * from s where COALESCE(n, nn) not in (select COALESCE(n, nn) from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where COALESCE(nn, NULL) not in (select COALESCE(nn, NULL) from l);
+ QUERY PLAN
+------------------------------------------------
+ Hash Anti Join
+ Hash Cond: (COALESCE(s.nn) = COALESCE(l.nn))
+ -> Seq Scan on s
+ -> Hash
+ -> Seq Scan on l
+(5 rows)
+
+select * from s where COALESCE(nn, NULL) not in (select COALESCE(nn, NULL) from l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where (COALESCE(n, -1), nn, COALESCE(n, u)) not in (select COALESCE(n, -1), nn,
COALESCE(n,u) from l);
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Scan using l_nn on l
+ Index Cond: (nn = s.nn)
+ Filter: ((COALESCE(s.n, '-1'::integer) = COALESCE(n, '-1'::integer)) AND (COALESCE(s.n, s.u) = COALESCE(n,
u)))
+(5 rows)
+
+select * from s where (COALESCE(n, -1), nn, COALESCE(n, u)) not in (select COALESCE(n, -1), nn, COALESCE(n, u) from
l);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+ 3 | | 3 | foo
+(2 rows)
+
+-- test miscellaneous outer nullable cases
+explain (costs false) select * from s where (n,n) not in (select n,n from l);
+ QUERY PLAN
+-------------------------------------------------------------
+ Nested Loop Anti Join
+ Join Filter: (((s.n = l.n) AND (s.n = l.n)) IS NOT FALSE)
+ -> Seq Scan on s
+ -> Materialize
+ -> Seq Scan on l
+(5 rows)
+
+select * from s where (n,n) not in (select n,n from l);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s right join l on s.nn = l.nn where (s.n,s.u,s.nn) not in (select n,u,nn from l);
+ QUERY PLAN
+-------------------------------------------------------------------------------------
+ Nested Loop Anti Join
+ Join Filter: (((s.n = l_1.n) AND (s.u = l_1.u) AND (s.nn = l_1.nn)) IS NOT FALSE)
+ -> Hash Left Join
+ Hash Cond: (l.nn = s.nn)
+ -> Seq Scan on l
+ -> Hash
+ -> Seq Scan on s
+ -> Materialize
+ -> Seq Scan on l l_1
+(9 rows)
+
+select * from s right join l on s.nn = l.nn where (s.n,s.u,s.nn) not in (select n,u,nn from l);
+ u | n | nn | p | u | n | nn | p
+---+---+----+---+---+---+----+---
+(0 rows)
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where (s.n,s.u,s.nn) not in (select n,u,nn
froml where u < 0);
+ QUERY PLAN
+---------------------------------------------
+ Aggregate
+ -> Hash Left Join
+ Hash Cond: (l.nn = s.nn)
+ Filter: (NOT (hashed SubPlan 1))
+ -> Seq Scan on l
+ -> Hash
+ -> Seq Scan on s
+ SubPlan 1
+ -> Index Scan using l_u on l l_1
+ Index Cond: (u < 0)
+(10 rows)
+
+select count(*) from s right join l on s.nn = l.nn where (s.n,s.u,s.nn) not in (select n,u,nn from l where u < 0);
+ count
+-------
+ 10000
+(1 row)
+
+explain (costs false) select * from s where (n,n,n) not in (select distinct n,n,n from l where u > 0 limit 3) order by
n;
+ QUERY PLAN
+-----------------------------------------------------
+ Sort
+ Sort Key: s.n
+ -> Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Limit
+ -> Unique
+ -> Index Scan using l_n on l
+ Filter: (u > 0)
+(9 rows)
+
+select * from s where (n,n,n) not in (select distinct n,n,n from l where u > 0 limit 3) order by n;
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+--test outer has strict predicate or inner join
+explain (costs false) select * from s where n not in (select n from l) and n > 0;
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: (n > 0)
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(10 rows)
+
+select * from s where n not in (select n from l) and n > 0;
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where n not in (select n from l) and u > 0;
+ QUERY PLAN
+-------------------------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Seq Scan on s
+ Filter: (((n IS NOT NULL) OR (NOT $0)) AND (u > 0))
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(12 rows)
+
+select * from s where n not in (select n from l) and u > 0;
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where n not in (select n from l) and n is not null;
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: (n IS NOT NULL)
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(10 rows)
+
+select * from s where n not in (select n from l) and n is not null;
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s join l on s.n = l.n where s.n not in (select n from l);
+ QUERY PLAN
+--------------------------------------------------------
+ Nested Loop
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Bitmap Heap Scan on l l_1
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+ -> Index Scan using l_n on l
+ Index Cond: (n = s.n)
+(12 rows)
+
+select * from s join l on s.n = l.n where s.n not in (select n from l);
+ u | n | nn | p | u | n | nn | p
+---+---+----+---+---+---+----+---
+(0 rows)
+
+explain (costs false) select count(*) from s right join l on s.n = l.n where s.n not in (select n from l);
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Nested Loop Anti Join
+ -> Hash Left Join
+ Hash Cond: (l.n = s.n)
+ Filter: ((s.n IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on l
+ -> Hash
+ -> Seq Scan on s
+ -> Bitmap Heap Scan on l l_1
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(17 rows)
+
+select count(*) from s right join l on s.n = l.n where s.n not in (select n from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select
nfrom l);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ Join Filter: (s.u = s1.u)
+ -> Nested Loop
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Bitmap Heap Scan on l l_1
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+ -> Index Only Scan using l_n on l
+ Index Cond: (n = s.n)
+ -> Seq Scan on s1
+(16 rows)
+
+select count(*) from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select n from l);
+ count
+-------
+ 0
+(1 row)
+
+explain (costs false) select count(*) from s join l on s.n = l.n right join s1 on s.u = s1.u where s.n not in (select
nfrom l);
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_2
+ -> Nested Loop Anti Join
+ -> Nested Loop Left Join
+ Join Filter: (s.u = s1.u)
+ Filter: ((s.n IS NOT NULL) OR (NOT $0))
+ -> Seq Scan on s1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s
+ -> Index Only Scan using l_n on l
+ Index Cond: (n = s.n)
+ -> Bitmap Heap Scan on l l_1
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(20 rows)
+
+select count(*) from s join l on s.n = l.n right join s1 on s.u = s1.u where s.n not in (select n from l);
+ count
+-------
+ 0
+(1 row)
+
+--test inner has strict predicate or inner join
+explain (costs false) select * from s where u not in (select n from l where n > 0);
+ QUERY PLAN
+---------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_n on l
+ Index Cond: ((n = s.u) AND (n > 0))
+(4 rows)
+
+select * from s where u not in (select n from l where n > 0);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where u not in (select n from l where u > 0);
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.u = n) OR (n IS NULL))
+ Filter: (u > 0)
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.u)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(10 rows)
+
+select * from s where u not in (select n from l where u > 0);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where u not in (select n from l where n is not null);
+ QUERY PLAN
+-----------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_n on l
+ Index Cond: ((n = s.u) AND (n IS NOT NULL))
+(4 rows)
+
+select * from s where u not in (select n from l where n is not null);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where u not in (select l.n from l join s on l.n=s.n);
+ QUERY PLAN
+----------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Only Scan using l_n on l
+ Index Cond: (n = s_1.n)
+(7 rows)
+
+select * from s where u not in (select l.n from l join s on l.n=s.n);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+ 3 | | 3 | foo
+(2 rows)
+
+explain (costs false) select * from s where u not in (select l.n from l join s on l.u=s.u);
+ QUERY PLAN
+-----------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Scan using l_u on l
+ Index Cond: (u = s_1.u)
+(7 rows)
+
+select * from s where u not in (select l.n from l join s on l.u=s.u);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where u not in (select l.n from l join s on l.n = s.n);
+ QUERY PLAN
+----------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Only Scan using l_n on l
+ Index Cond: (n = s_1.n)
+(7 rows)
+
+select * from s where u not in (select l.n from l join s on l.n = s.n);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+ 3 | | 3 | foo
+(2 rows)
+
+explain (costs false) select * from s where u not in (select l.n from l right join s on l.n = s.n);
+ QUERY PLAN
+----------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop Left Join
+ -> Seq Scan on s s_1
+ -> Index Only Scan using l_n on l
+ Index Cond: (n = s_1.n)
+(7 rows)
+
+select * from s where u not in (select l.n from l right join s on l.n = s.n);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where u not in (select l.n from l right join s on l.n=s.n join s1 on l.n=s1.n);
+ QUERY PLAN
+----------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (s_1.n = s1.n)
+ -> Seq Scan on s1
+ -> Materialize
+ -> Seq Scan on s s_1
+ -> Index Only Scan using l_n on l
+ Index Cond: (n = s_1.n)
+(11 rows)
+
+select * from s where u not in (select l.n from l right join s on l.n=s.n join s1 on l.n=s1.n);
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+ 3 | | 3 | foo
+(2 rows)
+
+explain (costs false) select * from s where u not in (select l.n from l join s on l.n=s.n right join s1 on l.n=s1.n);
+ QUERY PLAN
+----------------------------------------------------------
+ Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop Left Join
+ Join Filter: (l.n = s1.n)
+ -> Seq Scan on s1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Only Scan using l_n on l
+ Index Cond: (n = s_1.n)
+(11 rows)
+
+select * from s where u not in (select l.n from l join s on l.n=s.n right join s1 on l.n=s1.n);
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+--test both sides have strict predicate or inner join
+explain (costs false) select * from s where n not in (select n from l where n > 0) and n > 0;
+ QUERY PLAN
+---------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: (n > 0)
+ -> Index Only Scan using l_n on l
+ Index Cond: ((n = s.n) AND (n > 0))
+(5 rows)
+
+select * from s where n not in (select n from l where n > 0) and n > 0;
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s where n not in (select n from l where u > 0) and n > 0;
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: (n > 0)
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ Filter: (u > 0)
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(11 rows)
+
+select * from s where n not in (select n from l where u > 0) and n > 0;
+ u | n | nn | p
+---+---+----+---
+(0 rows)
+
+explain (costs false) select * from s where n not in (select n from l where n > 0) and u > 0;
+ QUERY PLAN
+-------------------------------------------------------------
+ Nested Loop Anti Join
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ Filter: (n > 0)
+ -> Seq Scan on s
+ Filter: (((n IS NOT NULL) OR (NOT $0)) AND (u > 0))
+ -> Index Only Scan using l_n on l
+ Index Cond: ((n = s.n) AND (n > 0))
+(8 rows)
+
+select * from s where n not in (select n from l where n > 0) and u > 0;
+ u | n | nn | p
+---------+---------+---------+--------
+ 1000002 | 1000002 | 1000002 | foofoo
+(1 row)
+
+explain (costs false) select * from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select l.n
froml right join s on l.n=s.n join s s1 on l.n=s1.n);
+ QUERY PLAN
+--------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (s.u = s1.u)
+ -> Seq Scan on s1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop
+ -> Nested Loop
+ Join Filter: (s_1.n = s1_1.n)
+ -> Seq Scan on s s_1
+ -> Materialize
+ -> Seq Scan on s s1_1
+ -> Index Only Scan using l_n on l l_1
+ Index Cond: (n = s_1.n)
+ -> Index Scan using l_n on l
+ Index Cond: (n = s.n)
+(18 rows)
+
+select * from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select l.n from l right join s on
l.n=s.njoin s s1 on l.n=s1.n);
+ u | n | nn | p | u | n | nn | p | u | n | n1 | nn | p
+---+---+----+---+---+---+----+---+---+---+----+----+---
+(0 rows)
+
+explain (costs false) select * from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select l.n
froml join s on l.n=s.n right join s s1 on l.n=s1.n);
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (s.u = s1.u)
+ -> Seq Scan on s1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Nested Loop Left Join
+ Join Filter: (l_1.n = s1_1.n)
+ -> Seq Scan on s s1_1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Only Scan using l_n on l l_1
+ Index Cond: (n = s_1.n)
+ -> Index Scan using l_n on l
+ Index Cond: (n = s.n)
+(18 rows)
+
+select * from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select l.n from l join s on l.n=s.n
rightjoin s s1 on l.n=s1.n);
+ u | n | nn | p | u | n | nn | p | u | n | n1 | nn | p
+---+---+----+---+---+---+----+---+---+---+----+----+---
+(0 rows)
+
+explain (costs false) select * from s join l on s.n = l.n right join s1 on s.u = s1.u where s.n not in (select l.n
froml join s on l.n=s.n right join s s1 on l.n=s1.n);
+ QUERY PLAN
+--------------------------------------------------------------
+ Nested Loop Left Join
+ Join Filter: (s.u = s1.u)
+ Filter: (NOT (hashed SubPlan 1))
+ -> Seq Scan on s1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s
+ -> Index Scan using l_n on l
+ Index Cond: (n = s.n)
+ SubPlan 1
+ -> Nested Loop Left Join
+ Join Filter: (l_1.n = s1_1.n)
+ -> Seq Scan on s s1_1
+ -> Materialize
+ -> Nested Loop
+ -> Seq Scan on s s_1
+ -> Index Only Scan using l_n on l l_1
+ Index Cond: (n = s_1.n)
+(18 rows)
+
+select * from s join l on s.n = l.n right join s1 on s.u = s1.u where s.n not in (select l.n from l join s on l.n=s.n
rightjoin s s1 on l.n=s1.n);
+ u | n | nn | p | u | n | nn | p | u | n | n1 | nn | p
+---+---+----+---+---+---+----+---+---+---+----+----+---
+(0 rows)
+
+--JIRA-7279 CTE with NOT IN
+create table public.testing
+(
+a integer,
+b integer,
+c integer
+);
+explain (costs false) with
+selected(a,b,c) as (values(1,2,3)),
+updated(d,e,f) as (values(4,5,6))
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated);
+ QUERY PLAN
+---------------------------------------------------
+ Insert on testing
+ -> Result
+ One-Time Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Result
+(5 rows)
+
+with
+selected(a,b,c) as (values(1,2,3)),
+updated(d,e,f) as (values(4,5,6))
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated);
+select * from public.testing;
+ a | b | c
+---+---+---
+ 1 | 2 | 3
+(1 row)
+
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated);
+ QUERY PLAN
+-------------------------------------------------------------------------------------
+ Insert on testing
+ -> Nested Loop Anti Join
+ Join Filter: (((s.u = l.u) AND (s.n = l.n) AND (s.nn = l.nn)) IS NOT FALSE)
+ -> Seq Scan on s
+ -> Materialize
+ -> Seq Scan on l
+(6 rows)
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated);
+select * from public.testing;
+ a | b | c
+---------+---------+---------
+ 1 | 2 | 3
+ 1000002 | 1000002 | 1000002
+(2 rows)
+
+-- expect to get Hash Anti Join
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where a not in (select d from updated);
+ QUERY PLAN
+-----------------------------------------------------
+ Insert on testing
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: ((u IS NOT NULL) OR (NOT $0))
+ -> Index Only Scan using l_u on l
+ Index Cond: (u = s.u)
+(8 rows)
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where a not in (select d from updated);
+select * from public.testing;
+ a | b | c
+---------+---------+---------
+ 1 | 2 | 3
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+(3 rows)
+
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where b not in (select e from updated);
+ QUERY PLAN
+--------------------------------------------------------
+ Insert on testing
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Bitmap Heap Scan on l
+ Recheck Cond: ((s.n = n) OR (n IS NULL))
+ -> BitmapOr
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n = s.n)
+ -> Bitmap Index Scan on l_n
+ Index Cond: (n IS NULL)
+(13 rows)
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where b not in (select e from updated);
+select * from public.testing;
+ a | b | c
+---------+---------+---------
+ 1 | 2 | 3
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+(3 rows)
+
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where b not in (select d from updated);
+ QUERY PLAN
+-----------------------------------------------------
+ Insert on testing
+ InitPlan 1 (returns $0)
+ -> Seq Scan on l l_1
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ Filter: ((n IS NOT NULL) OR (NOT $0))
+ -> Index Only Scan using l_u on l
+ Index Cond: (u = s.n)
+(8 rows)
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where b not in (select d from updated);
+select * from public.testing;
+ a | b | c
+---------+---------+---------
+ 1 | 2 | 3
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+(4 rows)
+
+-- two levels of NOT IN with CTE, 2nd NOT IN
+-- subquery access CTE two levels above
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated
+ where d not in (select a from selected));
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Insert on testing
+ CTE selected
+ -> Seq Scan on s
+ -> Nested Loop Anti Join
+ Join Filter: (((selected.a = l.u) AND (selected.b = l.n) AND (selected.c = l.nn)) IS NOT FALSE)
+ -> CTE Scan on selected
+ -> Materialize
+ -> Seq Scan on l
+ Filter: (NOT (hashed SubPlan 3))
+ SubPlan 3
+ -> CTE Scan on selected selected_1
+(11 rows)
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated
+ where d not in (select a from selected));
+select * from public.testing;
+ a | b | c
+---------+---------+---------
+ 1 | 2 | 3
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+ 1 | 1 | 1
+ 2 | 2 | 2
+ 1000002 | 1000002 | 1000002
+ 3 | | 3
+(8 rows)
+
+-- With clause inside a query block
+explain select count(distinct t.a) from
+(with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+select * from selected where (a,b,c) not in
+(select d,e,f from updated
+ where d not in (select a from selected))) as t;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------
+ Aggregate (cost=693.75..693.76 rows=1 width=8)
+ -> Nested Loop Anti Join (cost=1.13..693.71 rows=3 width=12)
+ Join Filter: (((selected.a = l.u) AND (selected.b = l.n) AND (selected.c = l.nn)) IS NOT FALSE)
+ CTE selected
+ -> Seq Scan on s (cost=0.00..1.04 rows=4 width=12)
+ -> CTE Scan on selected (cost=0.00..0.08 rows=4 width=12)
+ -> Materialize (cost=0.09..230.09 rows=5000 width=12)
+ -> Seq Scan on l (cost=0.09..180.09 rows=5000 width=12)
+ Filter: (NOT (hashed SubPlan 3))
+ SubPlan 3
+ -> CTE Scan on selected selected_1 (cost=0.00..0.08 rows=4 width=4)
+(11 rows)
+
+select count(distinct t.a) from
+(with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+select * from selected where (a,b,c) not in
+(select d,e,f from updated
+ where d not in (select a from selected))) as t;
+ count
+-------
+ 4
+(1 row)
+
+-- With clause in subquery, can't flatten subquery to anti join
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s)
+insert into public.testing
+select * from selected where (a,b,c) not in
+(with updated (d,e,f) as (select u, n, nn from l)
+select d,e,f from updated);
+ QUERY PLAN
+-----------------------------------
+ Insert on testing
+ -> Seq Scan on s
+ Filter: (NOT (SubPlan 1))
+ SubPlan 1
+ -> Materialize
+ -> Seq Scan on l
+(6 rows)
+
+with
+selected(a,b,c) as (select u, n, nn from s)
+insert into public.testing
+select * from selected where (a,b,c) not in
+(with updated (d,e,f) as (select u, n, nn from l)
+select d,e,f from updated);
+select * from public.testing;
+ a | b | c
+---------+---------+---------
+ 1 | 2 | 3
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+ 1 | 1 | 1
+ 2 | 2 | 2
+ 1000002 | 1000002 | 1000002
+ 3 | | 3
+ 1000002 | 1000002 | 1000002
+(9 rows)
+
+-- With clause in subquery, subsubquery access CTE in subquery
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s)
+insert into public.testing
+select * from selected where (a,b,c) not in
+(
+with updated(d,e,f) as (select u, n, nn from l)
+select d,e,f from updated where d not in (select d from updated)
+);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Insert on testing
+ -> Seq Scan on s
+ Filter: (NOT (SubPlan 3))
+ SubPlan 3
+ -> Materialize
+ CTE updated
+ -> Seq Scan on l
+ InitPlan 2 (returns $1)
+ -> CTE Scan on updated
+ -> Hash Anti Join
+ Hash Cond: (updated_1.d = updated_2.d)
+ -> CTE Scan on updated updated_1
+ Filter: ((d IS NOT NULL) OR (NOT $1))
+ -> Hash
+ -> CTE Scan on updated updated_2
+(15 rows)
+
+with
+selected(a,b,c) as (select u, n, nn from s)
+insert into public.testing
+select * from selected where (a,b,c) not in
+(
+with updated(d,e,f) as (select u, n, nn from l)
+select d,e,f from updated where d not in (select d from updated)
+);
+select * from public.testing;
+ a | b | c
+---------+---------+---------
+ 1 | 2 | 3
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+ 1000002 | 1000002 | 1000002
+ 1 | 1 | 1
+ 2 | 2 | 2
+ 1000002 | 1000002 | 1000002
+ 3 | | 3
+ 1000002 | 1000002 | 1000002
+ 1 | 1 | 1
+ 2 | 2 | 2
+ 1000002 | 1000002 | 1000002
+ 3 | | 3
+(13 rows)
+
+-- Recursive CTE
+CREATE TABLE employees (
+ id serial,
+ name varchar(255),
+ manager_id int
+);
+INSERT INTO employees VALUES (1, 'Mark', null);
+INSERT INTO employees VALUES (2, 'John', 1);
+INSERT INTO employees VALUES (3, 'Dan', 2);
+INSERT INTO employees VALUES (4, 'Clark', 1);
+INSERT INTO employees VALUES (5, 'Linda', 2);
+INSERT INTO employees VALUES (6, 'Willy', 2);
+INSERT INTO employees VALUES (7, 'Barack', 2);
+INSERT INTO employees VALUES (8, 'Elen', 2);
+INSERT INTO employees VALUES (9, 'Kate', 3);
+INSERT INTO employees VALUES (10, 'Terry', 4);
+WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON mtree.id = e.manager_id
+)
+SELECT *
+FROM managertree;
+ id | name | manager_id
+----+--------+------------
+ 2 | John | 1
+ 3 | Dan | 2
+ 5 | Linda | 2
+ 6 | Willy | 2
+ 7 | Barack | 2
+ 8 | Elen | 2
+ 9 | Kate | 3
+(7 rows)
+
+-- NOT IN subquery access Recursive CTE
+EXPLAIN (COSTS FALSE) WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON mtree.id = e.manager_id
+)
+SELECT *
+FROM managertree mt WHERE mt.manager_id NOT IN (SELECT id FROM managertree);
+ QUERY PLAN
+---------------------------------------------------------------
+ CTE Scan on managertree mt
+ Filter: (NOT (hashed SubPlan 2))
+ CTE managertree
+ -> Recursive Union
+ -> Seq Scan on employees
+ Filter: (id = 2)
+ -> Hash Join
+ Hash Cond: (e.manager_id = mtree.id)
+ -> Seq Scan on employees e
+ -> Hash
+ -> WorkTable Scan on managertree mtree
+ SubPlan 2
+ -> CTE Scan on managertree
+(13 rows)
+
+WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON mtree.id = e.manager_id
+)
+SELECT *
+FROM managertree mt WHERE mt.manager_id NOT IN (SELECT id FROM managertree);
+ id | name | manager_id
+----+------+------------
+ 2 | John | 1
+(1 row)
+
+-- NOT IN under UNION ALL inside Recursive CTE
+EXPLAIN (COSTS FALSE) WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON
+ (mtree.id = e.manager_id AND
+ mtree.manager_id NOT IN (SELECT manager_id FROM employees)
+ )
+)
+SELECT *
+FROM managertree;
+ QUERY PLAN
+---------------------------------------------------------------
+ CTE Scan on managertree
+ CTE managertree
+ -> Recursive Union
+ -> Seq Scan on employees employees_1
+ Filter: (id = 2)
+ -> Hash Join
+ Hash Cond: (e.manager_id = mtree.id)
+ -> Seq Scan on employees e
+ -> Hash
+ -> WorkTable Scan on managertree mtree
+ Filter: (NOT (hashed SubPlan 1))
+ SubPlan 1
+ -> Seq Scan on employees
+(13 rows)
+
+WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON
+ (mtree.id = e.manager_id AND
+ mtree.manager_id NOT IN (SELECT manager_id FROM employees)
+ )
+)
+SELECT *
+FROM managertree;
+ id | name | manager_id
+----+------+------------
+ 2 | John | 1
+(1 row)
+
+--Manfred-7613 CTE NOT IN with Union All
+create table cocotero as (
+ select * from(
+ values(1,2,3)) as data(a,b,c)
+);
+explain (costs off) with selected as (
+ select *
+ from cocotero
+),
+updated as (
+ update cocotero
+ set a = 3
+ from selected
+ where cocotero.a = selected.a
+ returning selected.a,selected.b,selected.c
+),
+inserted as (
+ insert into cocotero
+ select *
+ from selected
+ where a not in (select a from updated)
+ returning *
+)
+select 'updated' as action, count(*) as lines from updated
+union all
+select 'inserted' as action, count(*) as lines from inserted;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Append
+ CTE selected
+ -> Seq Scan on cocotero
+ CTE updated
+ -> Update on cocotero cocotero_1
+ -> Merge Join
+ Merge Cond: (cocotero_1.a = selected.a)
+ -> Sort
+ Sort Key: cocotero_1.a
+ -> Seq Scan on cocotero cocotero_1
+ -> Materialize
+ -> Sort
+ Sort Key: selected.a
+ -> CTE Scan on selected
+ CTE inserted
+ -> Insert on cocotero cocotero_2
+ InitPlan 3 (returns $3)
+ -> CTE Scan on updated updated_1
+ -> Nested Loop Anti Join
+ Join Filter: ((selected_1.a = updated_2.a) OR (updated_2.a IS NULL))
+ -> CTE Scan on selected selected_1
+ Filter: ((a IS NOT NULL) OR (NOT $3))
+ -> CTE Scan on updated updated_2
+ -> Aggregate
+ -> CTE Scan on updated
+ -> Aggregate
+ -> CTE Scan on inserted
+(27 rows)
+
+with selected as (
+ select *
+ from cocotero
+),
+updated as (
+ update cocotero
+ set a = 3
+ from selected
+ where cocotero.a = selected.a
+ returning selected.a,selected.b,selected.c
+),
+inserted as (
+ insert into cocotero
+ select *
+ from selected
+ where a not in (select a from updated)
+ returning *
+)
+select 'updated' as action, count(*) as lines from updated
+union all
+select 'inserted' as action, count(*) as lines from inserted;
+ action | lines
+----------+-------
+ updated | 1
+ inserted | 0
+(2 rows)
+
+--test enable_not_in_transform
+explain (costs off) select count(*) from s where s.u not in (select l.u from l);
+ QUERY PLAN
+--------------------------------------------
+ Aggregate
+ -> Nested Loop Anti Join
+ -> Seq Scan on s
+ -> Index Only Scan using l_u on l
+ Index Cond: (u = s.u)
+(5 rows)
+
+set enable_not_in_transform = off;
+explain (costs off) select count(*) from s where s.u not in (select l.u from l);
+ QUERY PLAN
+-----------------------------------
+ Aggregate
+ -> Seq Scan on s
+ Filter: (NOT (SubPlan 1))
+ SubPlan 1
+ -> Materialize
+ -> Seq Scan on l
+(6 rows)
+
+-- clean up
+reset work_mem;
+reset enable_not_in_transform;
+drop table s;
+drop table s1;
+drop table l;
+drop table empty;
+drop table public.testing;
+drop table employees;
+drop table cocotero;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 715842b..6e3aee8 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -83,6 +83,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_material | on
enable_mergejoin | on
enable_nestloop | on
+ enable_not_in_transform | on
enable_parallel_append | on
enable_parallel_hash | on
enable_partition_pruning | on
@@ -91,7 +92,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(19 rows)
+(20 rows)
-- Test that the pg_timezone_names and pg_timezone_abbrevs views are
-- more-or-less working. We can't test their contents in any great detail
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 893d8d0..4553a9f 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -831,3 +831,818 @@ select * from (with x as (select 2 as y) select * from x) ss;
explain (verbose, costs off)
with x as (select * from subselect_tbl)
select * from x for update;
+
+-- test NON IN to ANTI JOIN conversion
+CREATE TABLE s (u INTEGER NOT NULL, n INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
+insert into s (u, n, nn, p)
+ select
+ generate_series(1,3) as u,
+ generate_series(1,3) as n,
+ generate_series(1,3) as nn,
+ 'foo' as p;
+insert into s values(1000002, 1000002, 1000002, 'foofoo');
+UPDATE s set n = NULL WHERE n = 3;
+analyze s;
+
+CREATE TABLE l (u INTEGER NOT NULL, n INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
+insert into l (u, n, nn, p)
+ select
+ generate_series(1,10000 ) as u,
+ generate_series(1,10000 ) as n,
+ generate_series(1,10000 ) as nn,
+ 'bar' as p;
+UPDATE l set n = NULL WHERE n = 7;
+
+CREATE UNIQUE INDEX l_u ON l (u);
+CREATE INDEX l_n ON l (n);
+CREATE INDEX l_nn ON l (nn);
+analyze l;
+
+CREATE TABLE s1 (u INTEGER NOT NULL, n INTEGER NULL, n1 INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
+insert into s1 (u, n, n1, nn, p)
+ select
+ generate_series(1,3) as u,
+ generate_series(1,3) as n,
+ generate_series(1,3) as n1,
+ generate_series(1,3) as nn,
+ 'foo' as p;
+insert into s1 values(1000003, 1000003, 1000003, 1000003, 'foofoo');
+insert into s1 values(1003, 1003, 1003, 1003, 'foofoo');
+UPDATE s1 set n = NULL WHERE n = 3;
+UPDATE s1 set n1 = NULL WHERE n = 2;
+UPDATE s1 set n1 = NULL WHERE n1 = 3;
+analyze s1;
+
+CREATE TABLE empty (u INTEGER NOT NULL, n INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
+analyze empty;
+
+-- set work_mem to 64KB so that NOT IN to ANTI JOIN optimization will kick in
+set work_mem = 64;
+
+-- correctness test 1: inner empty, return every thing from outer including NULL
+explain (costs false) select * from s where n not in (select n from empty);
+
+select * from s where n not in (select n from empty);
+
+-- correctness test 2: inner has NULL, return empty result
+explain (costs false) select * from s where n not in (select n from l);
+
+select * from s where n not in (select n from l);
+
+-- correctness test 3: inner non-null, result has no NULL
+explain (costs false) select * from s where n not in (select u from l);
+
+select * from s where n not in (select u from l);
+
+-- correctness test 4: inner has predicate
+explain (costs false) select * from s where n not in (select n from l where u > 7);
+
+select * from s where n not in (select n from l where u > 7);
+
+-- correctness test 5: multi-expression, (2, 2, null, 2, foo) should be in the result
+explain (costs false) select * from s1 where (n,n1) not in (select u,nn from l where u >= 3);
+
+select * from s1 where (n,n1) not in (select u,nn from l where u >= 3);
+
+-- correctness test 6: multi-expression, (3, null, null, 3, foo) should not be in the result
+explain (costs false) select * from s1 where (n,n1) not in (select u,nn from l where u > 0);
+
+select * from s1 where (n,n1) not in (select u,nn from l where u > 0);
+
+-- correctness test 6: multi-expression, (3, null, null, 3, foo) should be in the result
+explain (costs false) select * from s1 where (n,n1) not in (select u,nn from l where u < 0);
+
+select * from s1 where (n,n1) not in (select u,nn from l where u < 0);
+
+-- test using hashed subplan when inner fits in work_mem
+explain (costs false) select * from l where n not in (select n from s);
+
+select * from l where n not in (select n from s);
+
+-- test single expression
+explain (costs false) select * from s where n not in (select n from l);
+
+select * from s where n not in (select n from l);
+
+explain (costs false) select * from s where u not in (select u from l);
+
+select * from s where u not in (select u from l);
+
+explain (costs false) select * from s where 3*n not in (select n from l);
+
+select * from s where 3*n not in (select n from l);
+
+explain (costs false) select * from s where n not in (select 3*n from l);
+
+select * from s where n not in (select 3*n from l);
+
+-- test single expression with predicates
+explain (costs false) select * from s where n not in (select n from l where u > 0);
+
+select * from s where n not in (select n from l where u > 0);
+
+explain (costs false) select * from s where n not in (select n from l where u > 100);
+
+select * from s where n not in (select n from l where u > 100);
+
+-- test multi expression
+explain (costs false) select * from s where (n,u) not in (select n,u from l);
+
+select * from s where (n,u) not in (select n,u from l);
+
+explain (costs false) select * from s where (u, nn) not in (select u, nn from l);
+
+select * from s where (u, nn) not in (select u, nn from l);
+
+explain (costs false) select * from s where (n,u) not in (select u,n from l);
+
+select * from s where (n,u) not in (select u,n from l);
+
+explain (costs false) select * from s where (n,u,nn) not in (select u,n,nn from l);
+
+select * from s where (n,u,nn) not in (select u,n,nn from l);
+
+explain (costs false) select * from s where (n,u,nn) not in (select u,n,nn from l where u > 1000);
+
+select * from s where (n,u,nn) not in (select u,n,nn from l where u > 1000);
+
+explain (costs false) select * from s where (n,u,nn) not in (select u,n,nn from l where u > 0);
+
+select * from s where (n,u,nn) not in (select u,n,nn from l where u > 0);
+
+explain (costs false) select * from s where (n,u,nn) not in (select u,n,nn from l where u > 1);
+
+select * from s where (n,u,nn) not in (select u,n,nn from l where u > 1);
+
+-- test multi-table
+explain (costs false) select count(*) from s, l where s.n not in (select n from l);
+
+select count(*) from s, l where s.n not in (select n from l);
+
+explain (costs false) select count(*) from s, l where s.nn not in (select nn from l);
+
+select count(*) from s, l where s.nn not in (select nn from l);
+
+-- test null padded results from outer join
+explain (costs false) select * from s where n not in (select s.nn from l left join s on l.nn = s.nn);
+
+select * from s where n not in (select s.nn from l left join s on l.nn = s.nn);
+
+explain (costs false) select * from s where n not in (select s.nn from s right join l on s.nn = l.nn);
+
+select * from s where n not in (select s.nn from s right join l on s.nn = l.nn);
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where l.nn not in (select nn from s);
+
+select count(*) from s right join l on s.nn = l.nn where l.nn not in (select nn from s);
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from s);
+
+select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from s);
+
+explain (costs false) select count(*) from s right join l on s.nn=l.nn where l.nn not in (select l.nn from l left join
son l.nn = s.nn);
+
+select count(*) from s right join l on s.nn=l.nn where l.nn not in (select l.nn from l left join s on l.nn = s.nn);
+
+explain (costs false) select count(*) from s right join l on s.nn=l.nn where s.nn not in (select s.nn from l left join
son l.nn = s.nn);
+
+select count(*) from s right join l on s.nn=l.nn where s.nn not in (select s.nn from l left join s on l.nn = s.nn);
+
+explain (costs false) select count(*) from s left join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn
froml);
+
+select count(*) from s left join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn from l);
+
+explain (costs false) select count(*) from s left join s1 on s.u=s1.u right join l on s.u=l.u where s.nn not in
(selectnn from l);
+
+select count(*) from s left join s1 on s.u=s1.u right join l on s.u=l.u where s.nn not in (select nn from l);
+
+explain (costs false) select count(*) from s left join s1 on s.u=s1.u left join l on s.u=l.u where s.nn not in (select
nnfrom l);
+
+select count(*) from s left join s1 on s.u=s1.u left join l on s.u=l.u where s.nn not in (select nn from l);
+
+explain (costs false) select count(*) from s right join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn
froml);
+
+select count(*) from s right join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn from l);
+
+explain (costs false) select count(*) from s join s1 on s.u=s1.u right join l on s.u=l.u where s.nn not in (select nn
froml);
+
+select * from s join s1 on s.u=s1.u right join l on s.u=l.u where s.nn not in (select nn from l);
+
+explain (costs false) select count(*) from s full join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn
froml);
+
+select count(*) from s full join s1 on s.u=s1.u join l on s.u=l.u where s.nn not in (select nn from l);
+
+explain (costs false) select count(*) from s join s1 on s.u=s1.u full join l on s.u=l.u where s.nn not in (select nn
froml);
+
+select count(*) from s join s1 on s.u=s1.u full join l on s.u=l.u where s.nn not in (select nn from l);
+
+explain (costs false) select * from s where s.nn not in (select l.nn from l left join s on l.nn=s.nn left join s1 on
l.nn=s1.nn);
+
+select * from s where s.nn not in (select l.nn from l left join s on l.nn=s.nn left join s1 on l.nn=s1.nn);
+
+explain (costs false) select * from s where s.nn not in (select l.nn from l left join s on l.nn=s.nn right join s1 on
l.nn=s1.nn);
+
+select * from s where s.nn not in (select l.nn from l left join s on l.nn=s.nn right join s1 on l.nn=s1.nn);
+
+explain (costs false) select * from s where (n,u,nn) not in (select l.n,l.u,l.nn from l left join s on l.nn = s.nn);
+
+select * from s where (n,u,nn) not in (select l.n,l.u,l.nn from l left join s on l.nn = s.nn);
+
+explain (costs false) select * from s where (n,u,nn) not in (select l.n,l.u,l.nn from l right join s on l.nn = s.nn);
+
+select * from s where (n,u,nn) not in (select l.n,l.u,l.nn from l left join s on l.nn = s.nn);
+
+--test reduce outer joins from outer query
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from l);
+
+select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from l);
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from l) and
s.u>0;
+
+select count(*) from s right join l on s.nn = l.nn where s.nn not in (select nn from l) and s.u>0;
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn join s1 on s.u = s1.u where s.nn not in
(selectnn from l);
+
+select count(*) from s right join l on s.nn = l.nn join s1 on s.u = s1.u where s.nn not in (select nn from l);
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn right join s1 on s.u = s1.u where s.nn not in
(selectnn from l);
+
+select count(*) from s right join l on s.nn = l.nn right join s1 on s.u = s1.u where s.nn not in (select nn from l);
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn left join s1 on s.u = s1.u where s.nn not in
(selectnn from l);
+
+select count(*) from s right join l on s.nn = l.nn left join s1 on s.u = s1.u where s.nn not in (select nn from l);
+
+--test reduce outer joins from subquery
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn);
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn);
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn where l.u > 9);
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn where l.u > 9);
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn where s.u > 9);
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn where s.u > 9);
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn join s1 on l.n =
s1.n);
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn join s1 on l.n = s1.n);
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn right join s1 on
l.n= s1.n);
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn right join s1 on l.n = s1.n);
+
+explain (costs false) select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn left join s1 on
l.n= s1.n);
+
+select * from s where nn not in (select l.nn from l right join s on l.nn = s.nn left join s1 on l.n = s1.n);
+
+--test reduce outer join on outer and sub-query
+explain (costs false) select count(*) from s right join l on s.nn = l.nn join s1 on s.u = s1.u where s.nn not in
(selectl.nn from l right join s on l.nn = s.nn join s1 on l.n = s1.n);
+
+select count(*) from s right join l on s.nn = l.nn join s1 on s.u = s1.u where s.nn not in (select l.nn from l right
joins on l.nn = s.nn join s1 on l.n = s1.n);
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn left join s1 on s.u = s1.u where s.nn not in
(selectl.nn from l right join s on l.nn = s.nn left join s1 on l.n = s1.n);
+
+select count(*) from s right join l on s.nn = l.nn left join s1 on s.u = s1.u where s.nn not in (select l.nn from l
rightjoin s on l.nn = s.nn left join s1 on l.n = s1.n);
+
+-- test union all
+explain (costs false) select * from s as t where not exists
+(select 1 from (select n as y from l union all
+ select u as y from s union all
+ select nn as y from s) as v where t.n=v.y or v.y is null) and n is not null;
+
+select * from s as t where not exists
+(select 1 from (select n as y from l union all
+ select u as y from s union all
+ select nn as y from s) as v where t.n=v.y or v.y is null) and n is not null;
+
+explain (costs false) select * from s where n not in
+(select n as y from l union all
+ select u as y from s union all
+ select nn as y from s);
+
+select * from s where n not in
+(select n as y from l union all
+ select u as y from s union all
+ select nn as y from s);
+
+explain (costs false) select count(*) from
+(select n as x from s union all select u as x from l) t where t.x not in
+(select nn from l);
+
+select count(*) from
+(select n as x from s union all select u as x from l) t where t.x not in
+(select nn from l);
+
+explain (costs false) select count(*) from
+(select n as x from s union all select n as x from l) t where t.x not in
+(select nn from empty);
+
+select count(*) from
+(select n as x from s union all select n as x from l) t where t.x not in
+(select nn from empty);
+
+explain (costs false) select count(*) from
+(select n as x from s union all select u as x from l) t where t.x not in
+(select n as y from l union all
+ select u as y from s union all
+ select nn as y from s);
+
+select count(*) from
+(select n as x from s union all select u as x from l) t where t.x not in
+(select n as y from l union all
+ select u as y from s union all
+ select nn as y from s);
+
+-- test multi-levels of NOT IN
+explain (costs false) select * from s where n not in (select n from s where n not in (select n from l));
+
+select * from s where n not in (select n from s where n not in (select n from l));
+
+explain (costs false) select * from s where n not in (select n from s where n not in (select u from l));
+
+select * from s where n not in (select n from s where n not in (select u from l));
+
+explain (costs false) select count(*) from s where u not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n));
+
+select count(*) from s where u not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n));
+
+explain (costs false) select * from s where n not in (select n from s1) and u not in (select u from s1) and nn not in
(selectnn from s1);
+
+select * from s where n not in (select n from s1) and u not in (select u from s1) and nn not in (select nn from s1);
+
+explain (costs false) select * from s where n not in (select n from s1) and u not in (select u from s1) and nn not in
(selectnn from l);
+
+select * from s where n not in (select n from s1) and u not in (select u from s1) and nn not in (select nn from l);
+
+explain (costs false) select count(*) from s where u not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n))
+and nn not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n));
+
+select count(*) from s where u not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n))
+and nn not in
+(select n from s1 where not exists
+ (select 1 from (select n from s1 where u not in (select n from l)) t where t.n = s.n));
+
+--test COALESCE
+explain (costs false) select * from s where COALESCE(n, -1) not in (select COALESCE(n, -1) from l);
+
+select * from s where COALESCE(n, -1) not in (select COALESCE(n, -1) from l);
+
+explain (costs false) select * from s where COALESCE(n, NULL, -1) not in (select COALESCE(n, NULL, -1) from l);
+
+select * from s where COALESCE(n, NULL, -1) not in (select COALESCE(n, NULL, -1) from l);
+
+explain (costs false) select * from s where COALESCE(n, NULL, NULL) not in (select COALESCE(n, NULL, NULL) from l);
+
+select * from s where COALESCE(n, NULL, NULL) not in (select COALESCE(n, NULL, NULL) from l);
+
+explain (costs false) select * from s where COALESCE(n, nn) not in (select COALESCE(n, nn) from l);
+
+select * from s where COALESCE(n, nn) not in (select COALESCE(n, nn) from l);
+
+explain (costs false) select * from s where COALESCE(nn, NULL) not in (select COALESCE(nn, NULL) from l);
+
+select * from s where COALESCE(nn, NULL) not in (select COALESCE(nn, NULL) from l);
+
+explain (costs false) select * from s where (COALESCE(n, -1), nn, COALESCE(n, u)) not in (select COALESCE(n, -1), nn,
COALESCE(n,u) from l);
+
+select * from s where (COALESCE(n, -1), nn, COALESCE(n, u)) not in (select COALESCE(n, -1), nn, COALESCE(n, u) from
l);
+
+-- test miscellaneous outer nullable cases
+
+explain (costs false) select * from s where (n,n) not in (select n,n from l);
+
+select * from s where (n,n) not in (select n,n from l);
+
+explain (costs false) select * from s right join l on s.nn = l.nn where (s.n,s.u,s.nn) not in (select n,u,nn from l);
+
+select * from s right join l on s.nn = l.nn where (s.n,s.u,s.nn) not in (select n,u,nn from l);
+
+explain (costs false) select count(*) from s right join l on s.nn = l.nn where (s.n,s.u,s.nn) not in (select n,u,nn
froml where u < 0);
+
+select count(*) from s right join l on s.nn = l.nn where (s.n,s.u,s.nn) not in (select n,u,nn from l where u < 0);
+
+explain (costs false) select * from s where (n,n,n) not in (select distinct n,n,n from l where u > 0 limit 3) order by
n;
+
+select * from s where (n,n,n) not in (select distinct n,n,n from l where u > 0 limit 3) order by n;
+
+--test outer has strict predicate or inner join
+explain (costs false) select * from s where n not in (select n from l) and n > 0;
+
+select * from s where n not in (select n from l) and n > 0;
+
+explain (costs false) select * from s where n not in (select n from l) and u > 0;
+
+select * from s where n not in (select n from l) and u > 0;
+
+explain (costs false) select * from s where n not in (select n from l) and n is not null;
+
+select * from s where n not in (select n from l) and n is not null;
+
+explain (costs false) select * from s join l on s.n = l.n where s.n not in (select n from l);
+
+select * from s join l on s.n = l.n where s.n not in (select n from l);
+
+explain (costs false) select count(*) from s right join l on s.n = l.n where s.n not in (select n from l);
+
+select count(*) from s right join l on s.n = l.n where s.n not in (select n from l);
+
+explain (costs false) select count(*) from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select
nfrom l);
+
+select count(*) from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select n from l);
+
+explain (costs false) select count(*) from s join l on s.n = l.n right join s1 on s.u = s1.u where s.n not in (select
nfrom l);
+
+select count(*) from s join l on s.n = l.n right join s1 on s.u = s1.u where s.n not in (select n from l);
+
+--test inner has strict predicate or inner join
+explain (costs false) select * from s where u not in (select n from l where n > 0);
+
+select * from s where u not in (select n from l where n > 0);
+
+explain (costs false) select * from s where u not in (select n from l where u > 0);
+
+select * from s where u not in (select n from l where u > 0);
+
+explain (costs false) select * from s where u not in (select n from l where n is not null);
+
+select * from s where u not in (select n from l where n is not null);
+
+explain (costs false) select * from s where u not in (select l.n from l join s on l.n=s.n);
+
+select * from s where u not in (select l.n from l join s on l.n=s.n);
+
+explain (costs false) select * from s where u not in (select l.n from l join s on l.u=s.u);
+
+select * from s where u not in (select l.n from l join s on l.u=s.u);
+
+explain (costs false) select * from s where u not in (select l.n from l join s on l.n = s.n);
+
+select * from s where u not in (select l.n from l join s on l.n = s.n);
+
+explain (costs false) select * from s where u not in (select l.n from l right join s on l.n = s.n);
+
+select * from s where u not in (select l.n from l right join s on l.n = s.n);
+
+explain (costs false) select * from s where u not in (select l.n from l right join s on l.n=s.n join s1 on l.n=s1.n);
+
+select * from s where u not in (select l.n from l right join s on l.n=s.n join s1 on l.n=s1.n);
+
+explain (costs false) select * from s where u not in (select l.n from l join s on l.n=s.n right join s1 on l.n=s1.n);
+
+select * from s where u not in (select l.n from l join s on l.n=s.n right join s1 on l.n=s1.n);
+
+--test both sides have strict predicate or inner join
+explain (costs false) select * from s where n not in (select n from l where n > 0) and n > 0;
+
+select * from s where n not in (select n from l where n > 0) and n > 0;
+
+explain (costs false) select * from s where n not in (select n from l where u > 0) and n > 0;
+
+select * from s where n not in (select n from l where u > 0) and n > 0;
+
+explain (costs false) select * from s where n not in (select n from l where n > 0) and u > 0;
+
+select * from s where n not in (select n from l where n > 0) and u > 0;
+
+explain (costs false) select * from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select l.n
froml right join s on l.n=s.n join s s1 on l.n=s1.n);
+
+select * from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select l.n from l right join s on
l.n=s.njoin s s1 on l.n=s1.n);
+
+explain (costs false) select * from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select l.n
froml join s on l.n=s.n right join s s1 on l.n=s1.n);
+
+select * from s right join l on s.n = l.n join s1 on s.u = s1.u where s.n not in (select l.n from l join s on l.n=s.n
rightjoin s s1 on l.n=s1.n);
+
+explain (costs false) select * from s join l on s.n = l.n right join s1 on s.u = s1.u where s.n not in (select l.n
froml join s on l.n=s.n right join s s1 on l.n=s1.n);
+
+select * from s join l on s.n = l.n right join s1 on s.u = s1.u where s.n not in (select l.n from l join s on l.n=s.n
rightjoin s s1 on l.n=s1.n);
+
+--JIRA-7279 CTE with NOT IN
+create table public.testing
+(
+a integer,
+b integer,
+c integer
+);
+
+explain (costs false) with
+selected(a,b,c) as (values(1,2,3)),
+updated(d,e,f) as (values(4,5,6))
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated);
+
+with
+selected(a,b,c) as (values(1,2,3)),
+updated(d,e,f) as (values(4,5,6))
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated);
+
+select * from public.testing;
+
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated);
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated);
+
+select * from public.testing;
+
+-- expect to get Hash Anti Join
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where a not in (select d from updated);
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where a not in (select d from updated);
+
+select * from public.testing;
+
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where b not in (select e from updated);
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where b not in (select e from updated);
+
+select * from public.testing;
+
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where b not in (select d from updated);
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where b not in (select d from updated);
+
+select * from public.testing;
+
+-- two levels of NOT IN with CTE, 2nd NOT IN
+-- subquery access CTE two levels above
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated
+ where d not in (select a from selected));
+
+with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+insert into public.testing
+select * from selected
+where (a,b,c) not in (select d,e,f from updated
+ where d not in (select a from selected));
+
+select * from public.testing;
+
+-- With clause inside a query block
+explain select count(distinct t.a) from
+(with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+select * from selected where (a,b,c) not in
+(select d,e,f from updated
+ where d not in (select a from selected))) as t;
+
+select count(distinct t.a) from
+(with
+selected(a,b,c) as (select u, n, nn from s),
+updated(d,e,f) as (select u, n, nn from l)
+select * from selected where (a,b,c) not in
+(select d,e,f from updated
+ where d not in (select a from selected))) as t;
+
+-- With clause in subquery, can't flatten subquery to anti join
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s)
+insert into public.testing
+select * from selected where (a,b,c) not in
+(with updated (d,e,f) as (select u, n, nn from l)
+select d,e,f from updated);
+
+with
+selected(a,b,c) as (select u, n, nn from s)
+insert into public.testing
+select * from selected where (a,b,c) not in
+(with updated (d,e,f) as (select u, n, nn from l)
+select d,e,f from updated);
+
+select * from public.testing;
+
+-- With clause in subquery, subsubquery access CTE in subquery
+explain (costs false) with
+selected(a,b,c) as (select u, n, nn from s)
+insert into public.testing
+select * from selected where (a,b,c) not in
+(
+with updated(d,e,f) as (select u, n, nn from l)
+select d,e,f from updated where d not in (select d from updated)
+);
+
+with
+selected(a,b,c) as (select u, n, nn from s)
+insert into public.testing
+select * from selected where (a,b,c) not in
+(
+with updated(d,e,f) as (select u, n, nn from l)
+select d,e,f from updated where d not in (select d from updated)
+);
+
+select * from public.testing;
+
+-- Recursive CTE
+CREATE TABLE employees (
+ id serial,
+ name varchar(255),
+ manager_id int
+);
+
+INSERT INTO employees VALUES (1, 'Mark', null);
+INSERT INTO employees VALUES (2, 'John', 1);
+INSERT INTO employees VALUES (3, 'Dan', 2);
+INSERT INTO employees VALUES (4, 'Clark', 1);
+INSERT INTO employees VALUES (5, 'Linda', 2);
+INSERT INTO employees VALUES (6, 'Willy', 2);
+INSERT INTO employees VALUES (7, 'Barack', 2);
+INSERT INTO employees VALUES (8, 'Elen', 2);
+INSERT INTO employees VALUES (9, 'Kate', 3);
+INSERT INTO employees VALUES (10, 'Terry', 4);
+
+WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON mtree.id = e.manager_id
+)
+SELECT *
+FROM managertree;
+
+-- NOT IN subquery access Recursive CTE
+EXPLAIN (COSTS FALSE) WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON mtree.id = e.manager_id
+)
+SELECT *
+FROM managertree mt WHERE mt.manager_id NOT IN (SELECT id FROM managertree);
+
+WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON mtree.id = e.manager_id
+)
+SELECT *
+FROM managertree mt WHERE mt.manager_id NOT IN (SELECT id FROM managertree);
+
+-- NOT IN under UNION ALL inside Recursive CTE
+EXPLAIN (COSTS FALSE) WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON
+ (mtree.id = e.manager_id AND
+ mtree.manager_id NOT IN (SELECT manager_id FROM employees)
+ )
+)
+SELECT *
+FROM managertree;
+
+WITH RECURSIVE managertree AS (
+ SELECT id, name, manager_id
+ FROM employees
+ WHERE id = 2
+ UNION ALL
+ SELECT e.id, e.name, e.manager_id
+ FROM employees e
+ INNER JOIN managertree mtree ON
+ (mtree.id = e.manager_id AND
+ mtree.manager_id NOT IN (SELECT manager_id FROM employees)
+ )
+)
+SELECT *
+FROM managertree;
+
+--Manfred-7613 CTE NOT IN with Union All
+create table cocotero as (
+ select * from(
+ values(1,2,3)) as data(a,b,c)
+);
+
+explain (costs off) with selected as (
+ select *
+ from cocotero
+),
+updated as (
+ update cocotero
+ set a = 3
+ from selected
+ where cocotero.a = selected.a
+ returning selected.a,selected.b,selected.c
+),
+inserted as (
+ insert into cocotero
+ select *
+ from selected
+ where a not in (select a from updated)
+ returning *
+)
+select 'updated' as action, count(*) as lines from updated
+union all
+select 'inserted' as action, count(*) as lines from inserted;
+
+with selected as (
+ select *
+ from cocotero
+),
+updated as (
+ update cocotero
+ set a = 3
+ from selected
+ where cocotero.a = selected.a
+ returning selected.a,selected.b,selected.c
+),
+inserted as (
+ insert into cocotero
+ select *
+ from selected
+ where a not in (select a from updated)
+ returning *
+)
+select 'updated' as action, count(*) as lines from updated
+union all
+select 'inserted' as action, count(*) as lines from inserted;
+
+--test enable_not_in_transform
+explain (costs off) select count(*) from s where s.u not in (select l.u from l);
+
+set enable_not_in_transform = off;
+
+explain (costs off) select count(*) from s where s.u not in (select l.u from l);
+
+-- clean up
+reset work_mem;
+reset enable_not_in_transform;
+drop table s;
+drop table s1;
+drop table l;
+drop table empty;
+drop table public.testing;
+drop table employees;
+drop table cocotero;
pgsql-hackers by date: