Hi, Yuki.
Thanks for looking at this patch.
fujii.y...@df.mitsubishielectric.co.jp писал 2022-12-03 06:02:
question1)
> + if (jointype == JOIN_SEMI && bms_is_member(var->varno,
innerrel->relids) && !bms_is_member(var->varno, outerrel->relids))
It takes time for me to find in what case this condition is true.
There is cases in which this condition is true for semi-join of two
baserels
when running query which joins more than two relations such as
query2 and query3.
Running queries such as query2, you maybe want to pushdown of only
semi-join path of
joinrel(outerrel) defined by (f_t1 a1 join f_t3 a2 on a1.c1 = a2.c1)
and baserel(innerrel) f_t3
because of safety deparse. So you add this condition.
Becouase of this limitation, your patch can't push down subquery
expression
"exists (select null from f_t2 where c1 = a1.c1)" in query3.
I think, it is one of difficulty points for semi-join pushdown.
This is my understanding of the intent of this condition and the
restrictions imposed by this condition.
Is my understanding right?
IIRC, planner can create semi-join, which targetlist references Vars
from inner join relation. However, it's deparsed as exists and so we
can't reference it from SQL. So, there's this check - if Var is
referenced in semi-join target list, it can't be pushed down.
You can see this if comment out this check.
EXPLAIN (verbose, costs off)
SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
(SELECT * FROM ft4 WHERE EXISTS (
SELECT 1 FROM ft2 WHERE ft2.c2=ft4.c2)) ft4
ON ft2.c2 = ft4.c1
INNER JOIN
(SELECT * FROM ft2 WHERE EXISTS (
SELECT 1 FROM ft4 WHERE ft2.c2=ft4.c2)) ft21
ON ft2.c2 = ft21.c2
WHERE ft2.c1 > 900
ORDER BY ft2.c1 LIMIT 10;
will fail with
EXPLAIN SELECT r8.c2, r9.c2 FROM "S 1"."T 1" r8 WHERE (EXISTS (SELECT
NULL FROM "S 1"."T 3" r9 WHERE ((r8.c2 = r9.c2))))
Here you can see that
SELECT * FROM ft2 WHERE EXISTS (
SELECT 1 FROM ft4 WHERE ft2.c2=ft4.c2)
was transformed to
SELECT r8.c2, r9.c2 FROM "S 1"."T 1" r8 WHERE (EXISTS (SELECT NULL FROM
"S 1"."T 3" r9 WHERE ((r8.c2 = r9.c2))))
where our exists subquery is referenced from tlist. It's fine for plan
(relations, participating in semi-join, can be referenced in tlist),
but is not going to work with EXISTS subquery.
BTW, there's a comment in joinrel_target_ok(). It tells exactly that -
5535 if (jointype == JOIN_SEMI && bms_is_member(var->varno,
innerrel->relids) && !bms_is_member(var->varno, outerrel->relids))
5536 {
5537 /* We deparse semi-join as exists() subquery, and
so can't deparse references to inner rel in join target list. */
5538 ok = false;
5539 break;
5540 }
Expanded comment.
question2) In foreign_join_ok
> * Constructing queries representing ANTI joins is hard, hence
Is this true? Is it hard to expand your approach to ANTI join
pushdown?
I haven't tried, so don't know.
question3) You use variables whose name is "addl_condXXX" in the
following code.
> appendStringInfo(addl_conds, "EXISTS (SELECT NULL FROM %s",
join_sql_i.data);
Does this naming mean additional literal?
Is there more complehensive naming, such as "subquery_exprXXX"?
The naming means additional conditions (for WHERE clause, by analogy
with ignore_conds and remote_conds). Not sure if subquery_expr sounds
better, but if you come with better idea, I'm fine with renaming them.
question4) Although really detail, there is expression making space
such as
"ft4.c2 = ft2.c2" and one making no space such as "c1=ftupper.c1".
Is there reason for this difference? If not, need we use same policy
for making space?
Fixed.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
From 0f26b0841cb095b4e114984deac2b1b001368c15 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyha...@postgrespro.ru>
Date: Mon, 7 Nov 2022 10:23:32 +0300
Subject: [PATCH v3] postgres_fdw: add support for deparsing semi joins
We deparse semi-joins as EXISTS subqueries. So, deparsing
semi-join leads to generating addl_conds condition,
which is then added to the uppermost JOIN's WHERE clause.
---
contrib/postgres_fdw/deparse.c | 201 +++++++++---
.../postgres_fdw/expected/postgres_fdw.out | 297 ++++++++++++++++--
contrib/postgres_fdw/postgres_fdw.c | 82 ++++-
contrib/postgres_fdw/postgres_fdw.h | 3 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 119 ++++++-
5 files changed, 620 insertions(+), 82 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 95247656504..10d82d9f2ab 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -179,12 +179,13 @@ static void appendLimitClause(deparse_expr_cxt *context);
static void appendConditions(List *exprs, deparse_expr_cxt *context);
static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root,
RelOptInfo *foreignrel, bool use_alias,
- Index ignore_rel, List **ignore_conds,
+ Index ignore_rel, List **ignore_conds, StringInfo addl_conds,
List **params_list);
+static void appendWhereClause(List *exprs, StringInfo addl_conds, deparse_expr_cxt *context);
static void deparseFromExpr(List *quals, deparse_expr_cxt *context);
static void deparseRangeTblRef(StringInfo buf, PlannerInfo *root,
RelOptInfo *foreignrel, bool make_subquery,
- Index ignore_rel, List **ignore_conds, List **params_list);
+ Index ignore_rel, List **ignore_conds, StringInfo addl_conds, List **params_list);
static void deparseAggref(Aggref *node, deparse_expr_cxt *context);
static void appendGroupByClause(List *tlist, deparse_expr_cxt *context);
static void appendOrderBySuffix(Oid sortop, Oid sortcoltype, bool nulls_first,
@@ -1370,23 +1371,20 @@ deparseFromExpr(List *quals, deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
RelOptInfo *scanrel = context->scanrel;
+ StringInfoData addl_conds;
/* For upper relations, scanrel must be either a joinrel or a baserel */
Assert(!IS_UPPER_REL(context->foreignrel) ||
IS_JOIN_REL(scanrel) || IS_SIMPLE_REL(scanrel));
+ initStringInfo(&addl_conds);
/* Construct FROM clause */
appendStringInfoString(buf, " FROM ");
deparseFromExprForRel(buf, context->root, scanrel,
(bms_membership(scanrel->relids) == BMS_MULTIPLE),
- (Index) 0, NULL, context->params_list);
-
- /* Construct WHERE clause */
- if (quals != NIL)
- {
- appendStringInfoString(buf, " WHERE ");
- appendConditions(quals, context);
- }
+ (Index) 0, NULL, &addl_conds, context->params_list);
+ appendWhereClause(quals, &addl_conds, context);
+ pfree(addl_conds.data);
}
/*
@@ -1598,6 +1596,33 @@ appendConditions(List *exprs, deparse_expr_cxt *context)
reset_transmission_modes(nestlevel);
}
+/*
+ * Append WHERE clause, containing conditions
+ * from exprs and addl_conds, to context->buf.
+ */
+static void
+appendWhereClause(List *exprs, StringInfo addl_conds, deparse_expr_cxt *context)
+{
+ StringInfo buf = context->buf;
+ bool need_and = false;
+
+ if (exprs != NIL || addl_conds->len > 0)
+ appendStringInfoString(buf, " WHERE ");
+
+ if (exprs != NIL)
+ {
+ appendConditions(exprs, context);
+ need_and = true;
+ }
+
+ if (addl_conds->len > 0)
+ {
+ if (need_and)
+ appendStringInfoString(buf, " AND ");
+ appendStringInfo(buf, "(%s)", addl_conds->data);
+ }
+}
+
/* Output join name for given join type */
const char *
get_jointype_name(JoinType jointype)
@@ -1616,6 +1641,9 @@ get_jointype_name(JoinType jointype)
case JOIN_FULL:
return "FULL";
+ case JOIN_SEMI:
+ return "SEMI";
+
default:
/* Shouldn't come here, but protect from buggy code. */
elog(ERROR, "unsupported join type %d", jointype);
@@ -1712,10 +1740,13 @@ deparseSubqueryTargetList(deparse_expr_cxt *context)
* of DELETE; it deparses the join relation as if the relation never contained
* the target relation, and creates a List of conditions to be deparsed into
* the top-level WHERE clause, which is returned to *ignore_conds.
+ *
+ * 'addl_conds' is a initialized StringInfo. If some additional conditions
+ * should be appended to WHERE clause, they are returned as addl_conds.
*/
static void
deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
- bool use_alias, Index ignore_rel, List **ignore_conds,
+ bool use_alias, Index ignore_rel, List **ignore_conds, StringInfo addl_conds,
List **params_list)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
@@ -1728,6 +1759,9 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
RelOptInfo *innerrel = fpinfo->innerrel;
bool outerrel_is_target = false;
bool innerrel_is_target = false;
+ StringInfoData addl_conds_i;
+ StringInfoData addl_conds_o;
+ bool need_and;
if (ignore_rel > 0 && bms_is_member(ignore_rel, foreignrel->relids))
{
@@ -1761,10 +1795,11 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
/* Deparse outer relation if not the target relation. */
if (!outerrel_is_target)
{
+ initStringInfo(&addl_conds_o);
initStringInfo(&join_sql_o);
deparseRangeTblRef(&join_sql_o, root, outerrel,
fpinfo->make_outerrel_subquery,
- ignore_rel, ignore_conds, params_list);
+ ignore_rel, ignore_conds, &addl_conds_o, params_list);
/*
* If inner relation is the target relation, skip deparsing it.
@@ -1780,6 +1815,12 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
Assert(fpinfo->jointype == JOIN_INNER);
Assert(fpinfo->joinclauses == NIL);
appendBinaryStringInfo(buf, join_sql_o.data, join_sql_o.len);
+ if (addl_conds_o.len > 0)
+ {
+ Assert(addl_conds->len == 0);
+ appendStringInfoString(addl_conds, addl_conds_o.data);
+ }
+ pfree(addl_conds_o.data);
return;
}
}
@@ -1787,10 +1828,33 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
/* Deparse inner relation if not the target relation. */
if (!innerrel_is_target)
{
+ initStringInfo(&addl_conds_i);
+
initStringInfo(&join_sql_i);
deparseRangeTblRef(&join_sql_i, root, innerrel,
fpinfo->make_innerrel_subquery,
- ignore_rel, ignore_conds, params_list);
+ ignore_rel, ignore_conds, &addl_conds_i, params_list);
+
+ if (fpinfo->jointype == JOIN_SEMI)
+ {
+ deparse_expr_cxt context;
+
+ appendStringInfo(addl_conds, "EXISTS (SELECT NULL FROM %s", join_sql_i.data);
+
+ context.buf = addl_conds;
+ context.foreignrel = foreignrel;
+ context.scanrel = foreignrel;
+ context.root = root;
+ context.params_list = params_list;
+
+ appendWhereClause(fpinfo->joinclauses, &addl_conds_i, &context);
+
+ if (addl_conds_i.len > 0)
+ resetStringInfo(&addl_conds_i);
+
+ /* Close parentheses for EXISTS subquery */
+ appendStringInfo(addl_conds, ")");
+ }
/*
* If outer relation is the target relation, skip deparsing it.
@@ -1801,6 +1865,12 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
Assert(fpinfo->jointype == JOIN_INNER);
Assert(fpinfo->joinclauses == NIL);
appendBinaryStringInfo(buf, join_sql_i.data, join_sql_i.len);
+ if (addl_conds_i.len > 0)
+ {
+ Assert(addl_conds->len == 0);
+ appendStringInfoString(addl_conds, addl_conds_i.data);
+ }
+ pfree(addl_conds_i.data);
return;
}
}
@@ -1809,33 +1879,69 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
Assert(!outerrel_is_target && !innerrel_is_target);
/*
- * For a join relation FROM clause entry is deparsed as
- *
- * ((outer relation) <join type> (inner relation) ON (joinclauses))
+ * For semijoin FROM clause is deparsed as an outer relation. An inner
+ * relation and join clauses are converted to EXISTS condition and
+ * passed to the upper level.
*/
- appendStringInfo(buf, "(%s %s JOIN %s ON ", join_sql_o.data,
- get_jointype_name(fpinfo->jointype), join_sql_i.data);
-
- /* Append join clause; (TRUE) if no join clause */
- if (fpinfo->joinclauses)
+ if (fpinfo->jointype == JOIN_SEMI)
{
- deparse_expr_cxt context;
+ appendStringInfo(buf, "%s", join_sql_o.data);
+ }
+ else
+ {
+ /*
+ * For a join relation FROM clause entry is deparsed as
+ *
+ * ((outer relation) <join type> (inner relation) ON
+ * (joinclauses))
+ */
+ appendStringInfo(buf, "(%s %s JOIN %s ON ", join_sql_o.data,
+ get_jointype_name(fpinfo->jointype), join_sql_i.data);
+
+ /* Append join clause; (TRUE) if no join clause */
+ if (fpinfo->joinclauses)
+ {
+ deparse_expr_cxt context;
- context.buf = buf;
- context.foreignrel = foreignrel;
- context.scanrel = foreignrel;
- context.root = root;
- context.params_list = params_list;
+ context.buf = buf;
+ context.foreignrel = foreignrel;
+ context.scanrel = foreignrel;
+ context.root = root;
+ context.params_list = params_list;
- appendStringInfoChar(buf, '(');
- appendConditions(fpinfo->joinclauses, &context);
+ appendStringInfoChar(buf, '(');
+ appendConditions(fpinfo->joinclauses, &context);
+ appendStringInfoChar(buf, ')');
+ }
+ else
+ appendStringInfoString(buf, "(TRUE)");
+
+ /* End the FROM clause entry. */
appendStringInfoChar(buf, ')');
}
- else
- appendStringInfoString(buf, "(TRUE)");
- /* End the FROM clause entry. */
- appendStringInfoChar(buf, ')');
+ /*
+ * Construct addl_conds from current level addl_conds and addl_conds,
+ * coming from inner and outer rels.
+ */
+ need_and = false;
+ if (addl_conds->len > 0)
+ need_and = true;
+ if (addl_conds_o.len > 0)
+ {
+ if (need_and)
+ appendStringInfoString(addl_conds, " AND ");
+ appendStringInfoString(addl_conds, addl_conds_o.data);
+ need_and = true;
+ }
+ if (addl_conds_i.len > 0)
+ {
+ if (need_and)
+ appendStringInfoString(addl_conds, " AND ");
+ appendStringInfoString(addl_conds, addl_conds_i.data);
+ }
+ pfree(addl_conds_i.data);
+ pfree(addl_conds_o.data);
}
else
{
@@ -1867,7 +1973,7 @@ deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
static void
deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
bool make_subquery, Index ignore_rel, List **ignore_conds,
- List **params_list)
+ StringInfo addl_conds, List **params_list)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
@@ -1925,7 +2031,7 @@ deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
}
else
deparseFromExprForRel(buf, root, foreignrel, true, ignore_rel,
- ignore_conds, params_list);
+ ignore_conds, addl_conds, params_list);
}
/*
@@ -2148,6 +2254,7 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
RangeTblEntry *rte = planner_rt_fetch(rtindex, root);
ListCell *lc,
*lc2;
+ StringInfoData addl_conds;
/* Set up context struct for recursion */
context.root = root;
@@ -2185,21 +2292,21 @@ deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
reset_transmission_modes(nestlevel);
+ initStringInfo(&addl_conds);
+
if (foreignrel->reloptkind == RELOPT_JOINREL)
{
List *ignore_conds = NIL;
+
appendStringInfoString(buf, " FROM ");
deparseFromExprForRel(buf, root, foreignrel, true, rtindex,
- &ignore_conds, params_list);
+ &ignore_conds, &addl_conds, params_list);
remote_conds = list_concat(remote_conds, ignore_conds);
}
- if (remote_conds)
- {
- appendStringInfoString(buf, " WHERE ");
- appendConditions(remote_conds, &context);
- }
+ appendWhereClause(remote_conds, &addl_conds, &context);
+ pfree(addl_conds.data);
if (foreignrel->reloptkind == RELOPT_JOINREL)
deparseExplicitTargetList(returningList, true, retrieved_attrs,
@@ -2255,6 +2362,7 @@ deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
List **retrieved_attrs)
{
deparse_expr_cxt context;
+ StringInfoData addl_conds;
/* Set up context struct for recursion */
context.root = root;
@@ -2268,21 +2376,20 @@ deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
if (foreignrel->reloptkind == RELOPT_JOINREL)
appendStringInfo(buf, " %s%d", REL_ALIAS_PREFIX, rtindex);
+ initStringInfo(&addl_conds);
+
if (foreignrel->reloptkind == RELOPT_JOINREL)
{
List *ignore_conds = NIL;
appendStringInfoString(buf, " USING ");
deparseFromExprForRel(buf, root, foreignrel, true, rtindex,
- &ignore_conds, params_list);
+ &ignore_conds, &addl_conds, params_list);
remote_conds = list_concat(remote_conds, ignore_conds);
}
- if (remote_conds)
- {
- appendStringInfoString(buf, " WHERE ");
- appendConditions(remote_conds, &context);
- }
+ appendWhereClause(remote_conds, &addl_conds, &context);
+ pfree(addl_conds.data);
if (foreignrel->reloptkind == RELOPT_JOINREL)
deparseExplicitTargetList(returningList, true, retrieved_attrs,
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2ab3f1efaa0..340a1efcd44 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2000,23 +2000,16 @@ SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER B
Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END, r1."C 1", r1.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST LIMIT 10::bigint OFFSET 100::bigint
(4 rows)
--- SEMI JOIN, not pushed down
+-- SEMI JOIN
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
----------------------------------------------------------------------------------------
- Limit
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
Output: t1.c1
- -> Merge Semi Join
- Output: t1.c1
- Merge Cond: (t1.c1 = t2.c1)
- -> Foreign Scan on public.ft1 t1
- Output: t1.c1
- Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
- -> Foreign Scan on public.ft2 t2
- Output: t2.c1
- Remote SQL: SELECT "C 1" FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
-(11 rows)
+ Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1" FROM "S 1"."T 1" r1 WHERE (EXISTS (SELECT NULL FROM "S 1"."T 1" r2 WHERE ((r2."C 1" = r1."C 1")))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint OFFSET 100::bigint
+(4 rows)
SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
c1
@@ -4000,23 +3993,13 @@ EXECUTE st2(101, 121);
-- subquery using immutable function (can be sent to remote)
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c5) = '1970-01-17'::date) ORDER BY c1;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
- Sort Key: t1.c1
- -> Nested Loop Semi Join
- Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
- Join Filter: (t2.c3 = t1.c3)
- -> Foreign Scan on public.ft1 t1
- Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 20))
- -> Materialize
- Output: t2.c3
- -> Foreign Scan on public.ft2 t2
- Output: t2.c3
- Remote SQL: SELECT c3 FROM "S 1"."T 1" WHERE (("C 1" > 10)) AND ((date(c5) = '1970-01-17'::date))
-(14 rows)
+ Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" < 20)) AND (EXISTS (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r3."C 1" > 10)) AND ((date(r3.c5) = '1970-01-17'::date)) AND ((r3.c3 = r1.c3)))) ORDER BY r1."C 1" ASC NULLS LAST
+(4 rows)
EXECUTE st3(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
@@ -4560,6 +4543,260 @@ explain (verbose, costs off) select * from ft3 f, loct3 l
Index Cond: (l.f1 = 'foo'::text)
(12 rows)
+-- ===================================================================
+-- test SEMI-JOIN pushdown
+-- ===================================================================
+EXPLAIN (verbose, costs off)
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN ft4 ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ AND EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)
+ ORDER BY ft2.c1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3
+ Relations: ((public.ft2) INNER JOIN (public.ft4)) SEMI JOIN (public.ft5)
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r2.c1, r2.c2, r2.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 900)))) WHERE (EXISTS (SELECT NULL FROM "S 1"."T 4" r4 WHERE ((r1.c2 = r4.c1)))) ORDER BY r1."C 1" ASC NULLS LAST
+(4 rows)
+
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN ft4 ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ AND EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)
+ ORDER BY ft2.c1;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3
+-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+--------
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 916 | 6 | 00916 | Sat Jan 17 00:00:00 1970 PST | Sat Jan 17 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 926 | 6 | 00926 | Tue Jan 27 00:00:00 1970 PST | Tue Jan 27 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 936 | 6 | 00936 | Fri Feb 06 00:00:00 1970 PST | Fri Feb 06 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 946 | 6 | 00946 | Mon Feb 16 00:00:00 1970 PST | Mon Feb 16 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 956 | 6 | 00956 | Thu Feb 26 00:00:00 1970 PST | Thu Feb 26 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 966 | 6 | 00966 | Sun Mar 08 00:00:00 1970 PST | Sun Mar 08 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 976 | 6 | 00976 | Wed Mar 18 00:00:00 1970 PST | Wed Mar 18 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 986 | 6 | 00986 | Sat Mar 28 00:00:00 1970 PST | Sat Mar 28 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 996 | 6 | 00996 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+(10 rows)
+
+-- The same query, different join order
+EXPLAIN (verbose, costs off)
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3
+ Relations: ((public.ft2) INNER JOIN (public.ft4)) SEMI JOIN (public.ft5)
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r4.c1, r4.c2, r4.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r4 ON (((r1.c2 = r4.c1)) AND ((r1."C 1" > 900)))) WHERE (EXISTS (SELECT NULL FROM "S 1"."T 4" r5 WHERE ((r4.c1 = r5.c1)))) ORDER BY r1."C 1" ASC NULLS LAST
+(4 rows)
+
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3
+-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+--------
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 916 | 6 | 00916 | Sat Jan 17 00:00:00 1970 PST | Sat Jan 17 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 926 | 6 | 00926 | Tue Jan 27 00:00:00 1970 PST | Tue Jan 27 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 936 | 6 | 00936 | Fri Feb 06 00:00:00 1970 PST | Fri Feb 06 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 946 | 6 | 00946 | Mon Feb 16 00:00:00 1970 PST | Mon Feb 16 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 956 | 6 | 00956 | Thu Feb 26 00:00:00 1970 PST | Thu Feb 26 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 966 | 6 | 00966 | Sun Mar 08 00:00:00 1970 PST | Sun Mar 08 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 976 | 6 | 00976 | Wed Mar 18 00:00:00 1970 PST | Wed Mar 18 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 986 | 6 | 00986 | Sat Mar 28 00:00:00 1970 PST | Sat Mar 28 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 996 | 6 | 00996 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+(10 rows)
+
+-- Left join
+EXPLAIN (verbose, costs off)
+SELECT ft2.*, ft4.* FROM ft2 LEFT JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3
+ Relations: (public.ft2) LEFT JOIN ((public.ft4) SEMI JOIN (public.ft5))
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, s6.c1, s6.c2, s6.c3 FROM ("S 1"."T 1" r1 LEFT JOIN (SELECT r4.c1, r4.c2, r4.c3 FROM "S 1"."T 3" r4 WHERE (EXISTS (SELECT NULL FROM "S 1"."T 4" r5 WHERE ((r4.c1 = r5.c1))))) s6(c1, c2, c3) ON (((r1.c2 = s6.c1)))) WHERE ((r1."C 1" > 900)) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint
+(4 rows)
+
+SELECT ft2.*, ft4.* FROM ft2 LEFT JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3
+-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+--------
+ 901 | 1 | 00901 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1 | 1 | foo | | |
+ 902 | 2 | 00902 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2 | 2 | foo | | |
+ 903 | 3 | 00903 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3 | foo | | |
+ 904 | 4 | 00904 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4 | 4 | foo | | |
+ 905 | 5 | 00905 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo | | |
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 907 | 7 | 00907 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo | | |
+ 908 | 8 | 00908 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8 | 8 | foo | | |
+ 909 | 9 | 00909 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9 | foo | | |
+ 910 | 0 | 00910 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0 | 0 | foo | | |
+(10 rows)
+
+-- Several semi-joins per upper level join
+EXPLAIN (verbose, costs off)
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ INNER JOIN (SELECT * FROM ft5 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft5.c1)) ft5
+ ON ft2.c2 <= ft5.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3
+ Relations: ((((public.ft2) INNER JOIN (public.ft4)) SEMI JOIN (public.ft5)) INNER JOIN (public.ft5 ft5_1)) SEMI JOIN (public.ft4 ft4_1)
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r6.c1, r6.c2, r6.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r6 ON (((r1.c2 = r6.c1)) AND ((r1."C 1" > 900)))) INNER JOIN "S 1"."T 4" r8 ON (((r1.c2 <= r8.c1)))) WHERE (EXISTS (SELECT NULL FROM "S 1"."T 3" r9 WHERE ((r8.c1 = r9.c1))) AND EXISTS (SELECT NULL FROM "S 1"."T 4" r7 WHERE ((r6.c1 = r7.c1)))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint
+(4 rows)
+
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ INNER JOIN (SELECT * FROM ft5 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft5.c1)) ft5
+ ON ft2.c2 <= ft5.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3
+-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+--------
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+ 906 | 6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006
+(10 rows)
+
+-- Semi-join below Semi-join
+EXPLAIN (verbose, costs off)
+SELECT ft2.* FROM ft2 WHERE
+ c1 = ANY (
+ SELECT c1 FROM ft2 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2))
+ AND ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8
+ Relations: (public.ft2) SEMI JOIN ((public.ft2 ft2_1) SEMI JOIN (public.ft4))
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" > 900)) AND (EXISTS (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r1."C 1" = r3."C 1")) AND (EXISTS (SELECT NULL FROM "S 1"."T 3" r4 WHERE ((r3.c2 = r4.c2)))))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint
+(4 rows)
+
+SELECT ft2.* FROM ft2 WHERE
+ c1 = ANY (
+ SELECT c1 FROM ft2 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2))
+ AND ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+-----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 903 | 3 | 00903 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3 | foo
+ 905 | 5 | 00905 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo
+ 907 | 7 | 00907 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
+ 909 | 9 | 00909 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9 | foo
+ 913 | 3 | 00913 | Wed Jan 14 00:00:00 1970 PST | Wed Jan 14 00:00:00 1970 | 3 | 3 | foo
+ 915 | 5 | 00915 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo
+ 917 | 7 | 00917 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo
+ 919 | 9 | 00919 | Tue Jan 20 00:00:00 1970 PST | Tue Jan 20 00:00:00 1970 | 9 | 9 | foo
+ 923 | 3 | 00923 | Sat Jan 24 00:00:00 1970 PST | Sat Jan 24 00:00:00 1970 | 3 | 3 | foo
+ 925 | 5 | 00925 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo
+(10 rows)
+
+-- Upper level relations shouldn't refer EXISTS() subqueries
+EXPLAIN (verbose, costs off)
+SELECT * FROM ft2 ftupper WHERE
+ EXISTS (
+ SELECT c1 FROM ft2 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2) AND c1 = ftupper.c1 )
+ AND ftupper.c1 > 900
+ ORDER BY ftupper.c1 LIMIT 10;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ftupper.c1, ftupper.c2, ftupper.c3, ftupper.c4, ftupper.c5, ftupper.c6, ftupper.c7, ftupper.c8
+ Relations: (public.ft2 ftupper) SEMI JOIN ((public.ft2) SEMI JOIN (public.ft4))
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" > 900)) AND (EXISTS (SELECT NULL FROM "S 1"."T 1" r2 WHERE ((r1."C 1" = r2."C 1")) AND (EXISTS (SELECT NULL FROM "S 1"."T 3" r3 WHERE ((r2.c2 = r3.c2)))))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint
+(4 rows)
+
+SELECT * FROM ft2 ftupper WHERE
+ EXISTS (
+ SELECT c1 FROM ft2 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2) AND c1 = ftupper.c1 )
+ AND ftupper.c1 > 900
+ ORDER BY ftupper.c1 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
+-----+----+-------+------------------------------+--------------------------+----+------------+-----
+ 903 | 3 | 00903 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3 | 3 | foo
+ 905 | 5 | 00905 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5 | 5 | foo
+ 907 | 7 | 00907 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7 | 7 | foo
+ 909 | 9 | 00909 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9 | 9 | foo
+ 913 | 3 | 00913 | Wed Jan 14 00:00:00 1970 PST | Wed Jan 14 00:00:00 1970 | 3 | 3 | foo
+ 915 | 5 | 00915 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5 | 5 | foo
+ 917 | 7 | 00917 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7 | 7 | foo
+ 919 | 9 | 00919 | Tue Jan 20 00:00:00 1970 PST | Tue Jan 20 00:00:00 1970 | 9 | 9 | foo
+ 923 | 3 | 00923 | Sat Jan 24 00:00:00 1970 PST | Sat Jan 24 00:00:00 1970 | 3 | 3 | foo
+ 925 | 5 | 00925 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5 | 5 | foo
+(10 rows)
+
+-- EXISTS should be propogated to the highest upper inner join
+EXPLAIN (verbose, costs off)
+ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE EXISTS (
+ SELECT 1 FROM ft2 WHERE ft2.c2 = ft4.c2)) ft4
+ ON ft2.c2 = ft4.c1
+ INNER JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (
+ SELECT 1 FROM ft4 WHERE ft2.c2 = ft4.c2)) ft21
+ ON ft2.c2 = ft21.c2
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3
+ Relations: ((((public.ft2) INNER JOIN (public.ft4)) SEMI JOIN (public.ft2 ft2_1)) INNER JOIN (public.ft2 ft2_2)) SEMI JOIN (public.ft4 ft4_1)
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r6.c1, r6.c2, r6.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r6 ON (((r1.c2 = r6.c1)) AND ((r1."C 1" > 900)))) INNER JOIN "S 1"."T 1" r8 ON (((r1.c2 = r8.c2)))) WHERE (EXISTS (SELECT NULL FROM "S 1"."T 3" r9 WHERE ((r1.c2 = r9.c2))) AND EXISTS (SELECT NULL FROM "S 1"."T 1" r7 WHERE ((r7.c2 = r6.c2)))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint
+(4 rows)
+
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE EXISTS (
+ SELECT 1 FROM ft2 WHERE ft2.c2 = ft4.c2)) ft4
+ ON ft2.c2 = ft4.c1
+ INNER JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (
+ SELECT 1 FROM ft4 WHERE ft2.c2 = ft4.c2)) ft21
+ ON ft2.c2 = ft21.c2
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3
+----+----+----+----+----+----+----+----+----+----+----
+(0 rows)
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 20c7b1ad05a..29f725bbae1 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -778,6 +778,7 @@ postgresGetForeignRelSize(PlannerInfo *root,
fpinfo->make_outerrel_subquery = false;
fpinfo->make_innerrel_subquery = false;
fpinfo->lower_subquery_rels = NULL;
+ fpinfo->unknown_subquery_rels = NULL;
/* Set the relation index. */
fpinfo->relation_index = baserel->relid;
}
@@ -5511,6 +5512,41 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
return commands;
}
+/*
+ * Check if reltarget is safe enough to push down such join
+ */
+static bool
+joinrel_target_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype, RelOptInfo *outerrel, RelOptInfo *innerrel)
+{
+ List *vars;
+ ListCell *lc;
+ bool ok = true;
+
+ Assert(joinrel->reltarget);
+
+ vars = pull_var_clause((Node *) joinrel->reltarget->exprs, PVC_INCLUDE_PLACEHOLDERS);
+
+ foreach(lc, vars)
+ {
+ Var *var = (Var *) lfirst(lc);
+
+ if (IsA(var, Var))
+ {
+ if (jointype == JOIN_SEMI && bms_is_member(var->varno, innerrel->relids) && !bms_is_member(var->varno, outerrel->relids))
+ {
+ /*
+ * Planner can create semi-joins, which refer to inner rel vars in its
+ * target list. However, we deparse semi-join as exists() subquery,
+ * so can't handle references to inner rel in target list.
+ */
+ ok = false;
+ break;
+ }
+ }
+ }
+ return ok;
+}
+
/*
* Assess whether the join between inner and outer relations can be pushed down
* to the foreign server. As a side effect, save information we obtain in this
@@ -5528,12 +5564,19 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
List *joinclauses;
/*
- * We support pushing down INNER, LEFT, RIGHT and FULL OUTER joins.
- * Constructing queries representing SEMI and ANTI joins is hard, hence
+ * We support pushing down INNER, LEFT, RIGHT, FULL OUTER and SEMI joins.
+ * Constructing queries representing ANTI joins is hard, hence
* not considered right now.
*/
if (jointype != JOIN_INNER && jointype != JOIN_LEFT &&
- jointype != JOIN_RIGHT && jointype != JOIN_FULL)
+ jointype != JOIN_RIGHT && jointype != JOIN_FULL &&
+ jointype != JOIN_SEMI)
+ return false;
+
+ /*
+ * We can't push down join if its reltarget is not safe
+ */
+ if (!joinrel_target_ok(root, joinrel, jointype, outerrel, innerrel))
return false;
/*
@@ -5645,6 +5688,8 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
Assert(bms_is_subset(fpinfo_i->lower_subquery_rels, innerrel->relids));
fpinfo->lower_subquery_rels = bms_union(fpinfo_o->lower_subquery_rels,
fpinfo_i->lower_subquery_rels);
+ fpinfo->unknown_subquery_rels = bms_union(fpinfo_o->unknown_subquery_rels,
+ fpinfo_i->unknown_subquery_rels);
/*
* Pull the other remote conditions from the joining relations into join
@@ -5688,6 +5733,16 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
fpinfo_i->remote_conds);
break;
+ case JOIN_SEMI:
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ fpinfo_i->remote_conds);
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ fpinfo->remote_conds);
+ fpinfo->remote_conds = list_copy(fpinfo_o->remote_conds);
+ fpinfo->unknown_subquery_rels = bms_union(fpinfo->unknown_subquery_rels,
+ innerrel->relids);
+ break;
+
case JOIN_FULL:
/*
@@ -5730,6 +5785,24 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
fpinfo->joinclauses = fpinfo->remote_conds;
fpinfo->remote_conds = NIL;
}
+ else if (jointype == JOIN_LEFT || jointype == JOIN_RIGHT || jointype == JOIN_FULL)
+ {
+ /*
+ * Conditions, generated from semi-joins, should
+ * be evaluated before LEFT/RIGHT/FULL join.
+ */
+ if (!bms_is_empty(fpinfo_o->unknown_subquery_rels))
+ {
+ fpinfo->make_outerrel_subquery = true;
+ fpinfo->lower_subquery_rels = bms_add_members(fpinfo->lower_subquery_rels, outerrel->relids);
+ }
+
+ if (!bms_is_empty(fpinfo_i->unknown_subquery_rels))
+ {
+ fpinfo->make_innerrel_subquery = true;
+ fpinfo->lower_subquery_rels = bms_add_members(fpinfo->lower_subquery_rels, innerrel->relids);
+ }
+ }
/* Mark that this join can be pushed down safely */
fpinfo->pushdown_safe = true;
@@ -7463,6 +7536,8 @@ find_em_for_rel(PlannerInfo *root, EquivalenceClass *ec, RelOptInfo *rel)
{
ListCell *lc;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
+
foreach(lc, ec->ec_members)
{
EquivalenceMember *em = (EquivalenceMember *) lfirst(lc);
@@ -7473,6 +7548,7 @@ find_em_for_rel(PlannerInfo *root, EquivalenceClass *ec, RelOptInfo *rel)
*/
if (bms_is_subset(em->em_relids, rel->relids) &&
!bms_is_empty(em->em_relids) &&
+ bms_is_empty(bms_intersect(em->em_relids, fpinfo->unknown_subquery_rels)) &&
is_foreign_expr(root, rel, em->em_expr))
return em;
}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index a11d45bedfc..dc5835a5bad 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -118,7 +118,8 @@ typedef struct PgFdwRelationInfo
* subquery? */
Relids lower_subquery_rels; /* all relids appearing in lower
* subqueries */
-
+ Relids unknown_subquery_rels; /* relids, which can't be referred to
+ * from upper relations */
/*
* Index of the relation. It is used to create an alias to a subquery
* representing the relation.
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 51560429e06..18672968de3 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -599,7 +599,7 @@ WITH t (c1_1, c1_3, c2_1) AS MATERIALIZED (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t
-- ctid with whole-row reference
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
--- SEMI JOIN, not pushed down
+-- SEMI JOIN
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
SELECT t1.c1 FROM ft1 t1 WHERE EXISTS (SELECT 1 FROM ft2 t2 WHERE t1.c1 = t2.c1) ORDER BY t1.c1 OFFSET 100 LIMIT 10;
@@ -1272,6 +1272,123 @@ explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
explain (verbose, costs off) select * from ft3 f, loct3 l
where f.f3 = l.f3 COLLATE "POSIX" and l.f1 = 'foo';
+-- ===================================================================
+-- test SEMI-JOIN pushdown
+-- ===================================================================
+EXPLAIN (verbose, costs off)
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN ft4 ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ AND EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)
+ ORDER BY ft2.c1;
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN ft4 ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ AND EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)
+ ORDER BY ft2.c1;
+
+-- The same query, different join order
+EXPLAIN (verbose, costs off)
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1;
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1;
+
+-- Left join
+EXPLAIN (verbose, costs off)
+SELECT ft2.*, ft4.* FROM ft2 LEFT JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+SELECT ft2.*, ft4.* FROM ft2 LEFT JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+
+-- Several semi-joins per upper level join
+EXPLAIN (verbose, costs off)
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ INNER JOIN (SELECT * FROM ft5 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft5.c1)) ft5
+ ON ft2.c2 <= ft5.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE
+ EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4
+ ON ft2.c2 = ft4.c1
+ INNER JOIN (SELECT * FROM ft5 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft5.c1)) ft5
+ ON ft2.c2 <= ft5.c1
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+
+-- Semi-join below Semi-join
+EXPLAIN (verbose, costs off)
+SELECT ft2.* FROM ft2 WHERE
+ c1 = ANY (
+ SELECT c1 FROM ft2 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2))
+ AND ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+SELECT ft2.* FROM ft2 WHERE
+ c1 = ANY (
+ SELECT c1 FROM ft2 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2))
+ AND ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+
+-- Upper level relations shouldn't refer EXISTS() subqueries
+EXPLAIN (verbose, costs off)
+SELECT * FROM ft2 ftupper WHERE
+ EXISTS (
+ SELECT c1 FROM ft2 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2) AND c1 = ftupper.c1 )
+ AND ftupper.c1 > 900
+ ORDER BY ftupper.c1 LIMIT 10;
+SELECT * FROM ft2 ftupper WHERE
+ EXISTS (
+ SELECT c1 FROM ft2 WHERE
+ EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2) AND c1 = ftupper.c1 )
+ AND ftupper.c1 > 900
+ ORDER BY ftupper.c1 LIMIT 10;
+
+-- EXISTS should be propogated to the highest upper inner join
+EXPLAIN (verbose, costs off)
+ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE EXISTS (
+ SELECT 1 FROM ft2 WHERE ft2.c2 = ft4.c2)) ft4
+ ON ft2.c2 = ft4.c1
+ INNER JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (
+ SELECT 1 FROM ft4 WHERE ft2.c2 = ft4.c2)) ft21
+ ON ft2.c2 = ft21.c2
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
+ (SELECT * FROM ft4 WHERE EXISTS (
+ SELECT 1 FROM ft2 WHERE ft2.c2 = ft4.c2)) ft4
+ ON ft2.c2 = ft4.c1
+ INNER JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (
+ SELECT 1 FROM ft4 WHERE ft2.c2 = ft4.c2)) ft21
+ ON ft2.c2 = ft21.c2
+ WHERE ft2.c1 > 900
+ ORDER BY ft2.c1 LIMIT 10;
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
--
2.34.1
