public inbox for [email protected]
help / color / mirror / Atom feedAdd semi-join pushdown to postgres_fdw
19+ messages / 7 participants
[nested] [flat]
* Add semi-join pushdown to postgres_fdw
@ 2022-08-24 07:25 Alexander Pyhalov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Pyhalov @ 2022-08-24 07:25 UTC (permalink / raw)
To: pgsql-hackers
Hi.
It's possible to extend deparsing in postgres_fdw, so that we can push
down semi-joins, which doesn't refer to inner reltarget. This allows
us to push down joins in queries like
SELECT * FROM ft1 t1 WHERE t1.c1 < 10 AND t1.c3 IN (SELECT c3 FROM ft2
t2 WHERE date(c5) = '1970-01-17'::date);
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 < 10 AND
t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE date(c5) = '1970-01-17'::date);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
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" < 10)) AND (EXISTS
(SELECT NULL FROM "S 1"."T 1" r3 WHERE ((date(r3.c5) =
'1970-01-17'::date)) AND ((r1.c3 = r3.c3))))
Deparsing semi-joins leads to generating (text) conditions like 'EXISTS
(SELECT NULL FROM inner_rel WHERE join_conds) . Such conditions are
generated in deparseFromExprForRel() and distributed to nearest WHERE,
where they are added to the list of and clauses.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachments:
[text/x-diff] 0001-postgres_fdw-add-support-for-deparsing-semi-joins.patch (48.8K, ../../[email protected]/2-0001-postgres_fdw-add-support-for-deparsing-semi-joins.patch)
download | inline diff:
From 7833d67f69287648c4594a5508feed376427f95d Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <[email protected]>
Date: Fri, 12 Aug 2022 15:02:24 +0300
Subject: [PATCH] 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 | 198 +++++++++---
.../postgres_fdw/expected/postgres_fdw.out | 297 ++++++++++++++++--
contrib/postgres_fdw/postgres_fdw.c | 78 ++++-
contrib/postgres_fdw/postgres_fdw.h | 3 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 119 ++++++-
5 files changed, 613 insertions(+), 82 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index a9766f97346..fcdc679d51f 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,
@@ -1372,23 +1373,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);
}
/*
@@ -1600,6 +1598,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)
@@ -1618,6 +1643,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);
@@ -1717,7 +1745,7 @@ deparseSubqueryTargetList(deparse_expr_cxt *context)
*/
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;
@@ -1730,6 +1758,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))
{
@@ -1763,10 +1794,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.
@@ -1782,6 +1814,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;
}
}
@@ -1789,10 +1827,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.
@@ -1803,6 +1864,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;
}
}
@@ -1811,33 +1878,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
{
@@ -1869,7 +1972,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;
@@ -1927,7 +2030,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);
}
/*
@@ -2150,6 +2253,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;
@@ -2187,21 +2291,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,
@@ -2257,6 +2361,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;
@@ -2270,21 +2375,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 7bf35602b02..763b2781cd5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2001,23 +2001,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 (((r1."C 1" = r2."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 ((r1."C 1" = r2."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
@@ -3919,23 +3912,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: (t1.c3 = t2.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 ((r1.c3 = r3.c3)))) ORDER BY r1."C 1" ASC NULLS LAST
+(4 rows)
EXECUTE st3(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
@@ -4479,6 +4462,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 ((r6.c2 = r7.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 16320170cee..13596fe341f 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;
}
@@ -5508,6 +5509,37 @@ 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))
+ {
+ /* We deparse semi-join as exists() subquery, and so can't deparse references to inner rel in join 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
@@ -5525,12 +5557,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;
/*
@@ -5642,6 +5681,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
@@ -5685,6 +5726,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:
/*
@@ -5727,6 +5778,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;
@@ -7414,6 +7483,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);
@@ -7424,6 +7495,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 21f2b20ce8d..ae2d977f15e 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 42735ae78a9..d23362aa643 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;
@@ -1259,6 +1259,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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2022-08-29 14:12 Ashutosh Bapat <[email protected]>
parent: Alexander Pyhalov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Ashutosh Bapat @ 2022-08-29 14:12 UTC (permalink / raw)
To: Alexander Pyhalov <[email protected]>; +Cc: pgsql-hackers
Hi Alexander,
Thanks for working on this. It's great to see FDW join pushdown scope
being expanded to more complex cases.
I am still figuring out the implementation. It's been a while I have
looked at join push down code.
But following change strikes me odd
-- 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: (t1.c3 = t2.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 ((r1.c3 = r3.c3)))) ORDER BY
r1."C 1" ASC NULLS LAST
+(4 rows)
date_in | s | 1 | [0:0]={cstring}
date_in which will be used to cast a test to date is not immutable. So
the query should't be pushed down. May not be a problem with your
patch. Can you please check?
On Wed, Aug 24, 2022 at 12:55 PM Alexander Pyhalov
<[email protected]> wrote:
>
> Hi.
>
> It's possible to extend deparsing in postgres_fdw, so that we can push
> down semi-joins, which doesn't refer to inner reltarget. This allows
> us to push down joins in queries like
>
> SELECT * FROM ft1 t1 WHERE t1.c1 < 10 AND t1.c3 IN (SELECT c3 FROM ft2
> t2 WHERE date(c5) = '1970-01-17'::date);
>
>
> EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 < 10 AND
> t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE date(c5) = '1970-01-17'::date);
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Foreign Scan
> Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
> 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" < 10)) AND (EXISTS
> (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((date(r3.c5) =
> '1970-01-17'::date)) AND ((r1.c3 = r3.c3))))
>
Thanks for working on this. It's great to see FDW join pushdown scope
being expanded to more complex cases.
I am still figuring out the implementation. It's been a while I have
looked at join push down code.
But following change strikes me odd
-- 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: (t1.c3 = t2.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 ((r1.c3 = r3.c3)))) ORDER BY
r1."C 1" ASC NULLS LAST
+(4 rows)
date_in | s | 1 | [0:0]={cstring}
date_in which will be used to cast a test to date is not immutable. So
the query should't be pushed down. May not be a problem with your
patch. Can you please check?
--
Best Wishes,
Ashutosh Bapat
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2022-08-30 06:58 Alexander Pyhalov <[email protected]>
parent: Ashutosh Bapat <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Pyhalov @ 2022-08-30 06:58 UTC (permalink / raw)
To: Ashutosh Bapat <[email protected]>; +Cc: pgsql-hackers
Ashutosh Bapat писал 2022-08-29 17:12:
> Hi Alexander,
> Thanks for working on this. It's great to see FDW join pushdown scope
> being expanded to more complex cases.
>
> I am still figuring out the implementation. It's been a while I have
> looked at join push down code.
>
> But following change strikes me odd
> -- 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: (t1.c3 = t2.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 ((r1.c3 = r3.c3)))) ORDER BY
> r1."C 1" ASC NULLS LAST
> +(4 rows)
>
> date_in | s | 1 | [0:0]={cstring}
> date_in which will be used to cast a test to date is not immutable. So
> the query should't be pushed down. May not be a problem with your
> patch. Can you please check?
Hi.
It is not related to my change and works as expected. As I see, we have
expression FuncExprdate(oid = 2029, args=Var ) = Const(type date)
(date(r3.c5) = '1970-01-17'::date).
Function is
# select proname, provolatile from pg_proc where oid=2029;
proname | provolatile
---------+-------------
date | i
So it's shippable.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2022-11-03 23:21 Ian Lawrence Barwick <[email protected]>
parent: Alexander Pyhalov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Ian Lawrence Barwick @ 2022-11-03 23:21 UTC (permalink / raw)
To: Alexander Pyhalov <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; pgsql-hackers
2022年8月30日(火) 15:58 Alexander Pyhalov <[email protected]>:
>
> Ashutosh Bapat писал 2022-08-29 17:12:
> > Hi Alexander,
> > Thanks for working on this. It's great to see FDW join pushdown scope
> > being expanded to more complex cases.
> >
> > I am still figuring out the implementation. It's been a while I have
> > looked at join push down code.
> >
> > But following change strikes me odd
> > -- 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: (t1.c3 = t2.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 ((r1.c3 = r3.c3)))) ORDER BY
> > r1."C 1" ASC NULLS LAST
> > +(4 rows)
> >
> > date_in | s | 1 | [0:0]={cstring}
> > date_in which will be used to cast a test to date is not immutable. So
> > the query should't be pushed down. May not be a problem with your
> > patch. Can you please check?
>
> Hi.
>
> It is not related to my change and works as expected. As I see, we have
> expression FuncExprdate(oid = 2029, args=Var ) = Const(type date)
> (date(r3.c5) = '1970-01-17'::date).
> Function is
>
> # select proname, provolatile from pg_proc where oid=2029;
> proname | provolatile
> ---------+-------------
> date | i
>
> So it's shippable.
This entry was marked as "Needs review" in the CommitFest app but cfbot
reports the patch no longer applies.
We've marked it as "Waiting on Author". As CommitFest 2022-11 is
currently underway, this would be an excellent time update the patch.
Once you think the patchset is ready for review again, you (or any
interested party) can move the patch entry forward by visiting
https://commitfest.postgresql.org/40/3838/
and changing the status to "Needs review".
Thanks
Ian Barwick
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2022-11-07 07:52 Alexander Pyhalov <[email protected]>
parent: Ian Lawrence Barwick <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Pyhalov @ 2022-11-07 07:52 UTC (permalink / raw)
To: Ian Lawrence Barwick <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; pgsql-hackers
Ian Lawrence Barwick писал 2022-11-04 02:21:
>
> This entry was marked as "Needs review" in the CommitFest app but cfbot
> reports the patch no longer applies.
>
> We've marked it as "Waiting on Author". As CommitFest 2022-11 is
> currently underway, this would be an excellent time update the patch.
>
> Once you think the patchset is ready for review again, you (or any
> interested party) can move the patch entry forward by visiting
>
> https://commitfest.postgresql.org/40/3838/
>
> and changing the status to "Needs review".
>
Hi. I've rebased the patch.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachments:
[text/x-diff] v2-0001-postgres_fdw-add-support-for-deparsing-semi-joins.patch (48.8K, ../../[email protected]/2-v2-0001-postgres_fdw-add-support-for-deparsing-semi-joins.patch)
download | inline diff:
From 1ac2a9e3611f716da688c04a4ec36888f62078ce Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <[email protected]>
Date: Mon, 7 Nov 2022 10:23:32 +0300
Subject: [PATCH] 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 | 198 +++++++++---
.../postgres_fdw/expected/postgres_fdw.out | 297 ++++++++++++++++--
contrib/postgres_fdw/postgres_fdw.c | 78 ++++-
contrib/postgres_fdw/postgres_fdw.h | 3 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 119 ++++++-
5 files changed, 613 insertions(+), 82 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 95247656504..45885442418 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);
@@ -1715,7 +1743,7 @@ deparseSubqueryTargetList(deparse_expr_cxt *context)
*/
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 +1756,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 +1792,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 +1812,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 +1825,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 +1862,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 +1876,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 +1970,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 +2028,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 +2251,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 +2289,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 +2359,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 +2373,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 558e94b8450..bab8155c092 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 8d7500abfbd..d0f5466c629 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;
}
@@ -5515,6 +5516,37 @@ 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))
+ {
+ /* We deparse semi-join as exists() subquery, and so can't deparse references to inner rel in join 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
@@ -5532,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;
/*
@@ -5649,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
@@ -5692,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:
/*
@@ -5734,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;
@@ -7467,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);
@@ -7477,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 b0dbb41fb55..bb73ca2009a 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
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Add semi-join pushdown to postgres_fdw
@ 2022-12-03 03:02 [email protected] <[email protected]>
parent: Alexander Pyhalov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: [email protected] @ 2022-12-03 03:02 UTC (permalink / raw)
To: Alexander Pyhalov <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected] <[email protected]>
Hi Mr.Pyhalov.
Thank you for work on this useful patch.
I'm starting to review v2 patch.
I have cheked we can apply v2 patch to commit ec386948948c1708c0c28c48ef08b9c4dd9d47cc
(Date:Thu Dec 1 12:56:21 2022 +0100).
I briefly looked at this whole thing and did step execute this
by running simple queries such as the followings.
query1) select * from f_t1 a1 where a1.c1 in (select c1 from f_t2);
query2) select * from f_t1 a1 join f_t3 a2 on a1.c1 = a2.c1 where a1.c1 in (select c1 from f_t3) ;
query3) update f_t2 set c1 = 1 from f_t1 a1 where a1.c2 = f_t2.c2 and exists (select null from f_t2 where c1 = a1.c1);
Although I haven't seen all of v2 patch, for now I have the following questions.
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?
I think if there are comments for the intent of this condition and the restrictions imposed by this condition
then they help PostgreSQL developper. What do you think?
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?
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"?
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?
Later, I'm going to look at part of your patch which is used when running more complex query.
Sincerely yours,
Yuuki Fujii
--
Yuuki Fujii
Information Technology R&D Center Mitsubishi Electric Corporation
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2022-12-06 09:28 Alexander Pyhalov <[email protected]>
parent: [email protected] <[email protected]>
0 siblings, 2 replies; 19+ messages in thread
From: Alexander Pyhalov @ 2022-12-06 09:28 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>
Hi, Yuki.
Thanks for looking at this patch.
[email protected] писал 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
Attachments:
[text/x-diff] v3-0001-postgres_fdw-add-support-for-deparsing-semi-joins.patch (49.3K, ../../[email protected]/2-v3-0001-postgres_fdw-add-support-for-deparsing-semi-joins.patch)
download | inline diff:
From 0f26b0841cb095b4e114984deac2b1b001368c15 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <[email protected]>
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* RE: Add semi-join pushdown to postgres_fdw
@ 2022-12-06 10:25 [email protected] <[email protected]>
parent: Alexander Pyhalov <[email protected]>
1 sibling, 0 replies; 19+ messages in thread
From: [email protected] @ 2022-12-06 10:25 UTC (permalink / raw)
To: Alexander Pyhalov <[email protected]>; +Cc: Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected] <[email protected]>
Hi Mr.Pyhalov.
Thank you for fixing it and giving more explanation.
> 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.
Thank you for expanding your comment and giving examples.
Thanks to the above examples, I understood in what case planner wolud create semi-join,
which targetlist references Vars from inner join relation.
> > 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.
I understand the situation.
> 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.
Sure.
> > 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?
Thank you.
Later, I'm going to look at other part of your patch.
Sincerely yours,
Yuuki Fujii
--
Yuuki Fujii
Information Technology R&D Center Mitsubishi Electric Corporation
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-18 00:20 Robins Tharakan <[email protected]>
parent: Alexander Pyhalov <[email protected]>
1 sibling, 1 reply; 19+ messages in thread
From: Robins Tharakan @ 2025-03-18 00:20 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Alexander Pyhalov <[email protected]>; Tomas Vondra <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected] <[email protected]>
Hi Alexander,
On Mon, 4 Dec 2023 at 07:22, Alexander Korotkov <[email protected]>
wrote:
>
>
> Now, I think this looks good. I'm going to push this if no objections.
After this commit, I began seeing an unexpected ERROR - see this bug-report.
https://www.postgresql.org/message-id/18852-fb75b88160678f78%40postgresql.org
-
robins
https://robins.in
Ref:
1.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=824dbea3e41efa3b35094163c834988de...
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-18 00:27 Alexander Korotkov <[email protected]>
parent: Robins Tharakan <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Korotkov @ 2025-03-18 00:27 UTC (permalink / raw)
To: Robins Tharakan <[email protected]>; +Cc: Alexander Pyhalov <[email protected]>; Tomas Vondra <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected] <[email protected]>
Hi, Robins!
On Tue, Mar 18, 2025 at 2:20 AM Robins Tharakan <[email protected]> wrote:
> On Mon, 4 Dec 2023 at 07:22, Alexander Korotkov <[email protected]> wrote:
> >
> >
> > Now, I think this looks good. I'm going to push this if no objections.
>
> After this commit, I began seeing an unexpected ERROR - see this bug-report.
> https://www.postgresql.org/message-id/18852-fb75b88160678f78%40postgresql.org
Thank you for pointing.
I'll check this in the next couple of days.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-18 11:13 Alexander Pyhalov <[email protected]>
parent: Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Pyhalov @ 2025-03-18 11:13 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Robins Tharakan <[email protected]>; Tomas Vondra <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected]
Alexander Korotkov писал(а) 2025-03-18 03:27:
> Hi, Robins!
>
> On Tue, Mar 18, 2025 at 2:20 AM Robins Tharakan <[email protected]>
> wrote:
>> On Mon, 4 Dec 2023 at 07:22, Alexander Korotkov <[email protected]>
>> wrote:
>> >
>> >
>> > Now, I think this looks good. I'm going to push this if no objections.
>>
>> After this commit, I began seeing an unexpected ERROR - see this
>> bug-report.
>> https://www.postgresql.org/message-id/18852-fb75b88160678f78%40postgresql.org
>
> Thank you for pointing.
> I'll check this in the next couple of days.
>
Hi.
It seems conditions, coming from semi-joins, are handled incorrectly
under left and right join. When deparsing left/right joins and there are
semi-joins in inner or outer part of the query, the corresponding part
is deparsed as subquery. And we can't refer subquery vars from above, so
just should not pull up the restrictinfos. The attached patch does
exactly this.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachments:
[text/x-diff] 0001-Avoid-pulling-up-restrict-infos-from-subqueries.patch (5.4K, ../../[email protected]/2-0001-Avoid-pulling-up-restrict-infos-from-subqueries.patch)
download | inline diff:
From 04c89be4b2802a0b51b10ca4828108a338c77e71 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <[email protected]>
Date: Tue, 18 Mar 2025 13:46:08 +0300
Subject: [PATCH] Avoid pulling up restrict infos from subqueries
Semi-join joins below left/right join are deparsed as
subqueries. We can't refer to subqueries vars from upper relations,
so avoid pulling conditions from them.
---
.../postgres_fdw/expected/postgres_fdw.out | 39 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 22 +++++++++--
contrib/postgres_fdw/sql/postgres_fdw.sql | 18 +++++++++
3 files changed, 75 insertions(+), 4 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bb4ed3059c4..88385e06f31 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4936,6 +4936,45 @@ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
----+----+----+----+----+----+----+----+----+----+----
(0 rows)
+-- semi-join conditions shouldn't pop up as
+-- left/right join join clause
+set enable_material to off;
+EXPLAIN (verbose, costs off)
+ SELECT NULL FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ ON (TRUE)
+LIMIT 10;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: NULL::text
+ Relations: ((public.ft2 ft2_1) SEMI JOIN (public.ft4 ft4_1)) LEFT JOIN ((public.ft2) SEMI JOIN (public.ft4))
+ Remote SQL: SELECT NULL FROM ((SELECT NULL FROM "S 1"."T 1" r6 WHERE ((r6."C 1" < 10)) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r7)) s8 LEFT JOIN (SELECT NULL FROM "S 1"."T 1" r4 WHERE ((r4."C 1" < 10)) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r5)) s9 ON (TRUE)) LIMIT 10::bigint
+(4 rows)
+
+SELECT NULL FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ ON (TRUE)
+LIMIT 10;
+ ?column?
+----------
+
+
+
+
+
+
+
+
+
+
+(10 rows)
+
+reset enable_material;
-- Can't push down semi-join with inner rel vars in targetlist
EXPLAIN (verbose, costs off)
SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 1131a8bf77e..8af8b802a24 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5964,16 +5964,30 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
break;
case JOIN_LEFT:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ /*
+ * When semi-join is involved in inner or outer part of left join,
+ * it's deparsed as a subquery and we can't refer to its vars
+ * on upper level
+ */
+ if (bms_is_empty(fpinfo_i->hidden_subquery_rels))
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
fpinfo_i->remote_conds);
- fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
+ if (bms_is_empty(fpinfo_o->hidden_subquery_rels))
+ fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
fpinfo_o->remote_conds);
break;
case JOIN_RIGHT:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ /*
+ * When semi-join is involved in inner or outer part of right join,
+ * it's deparsed as a subquery and we can't refer to its vars
+ * on upper level
+ */
+ if (bms_is_empty(fpinfo_o->hidden_subquery_rels))
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
fpinfo_o->remote_conds);
- fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
+ if (bms_is_empty(fpinfo_i->hidden_subquery_rels))
+ fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
fpinfo_i->remote_conds);
break;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index d45e9f8ab52..faedeab9d41 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1455,6 +1455,24 @@ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
WHERE ft2.c1 > 900
ORDER BY ft2.c1 LIMIT 10;
+-- semi-join conditions shouldn't pop up as
+-- left/right join join clause
+set enable_material to off;
+EXPLAIN (verbose, costs off)
+ SELECT NULL FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ ON (TRUE)
+LIMIT 10;
+SELECT NULL FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft2.c1 < 10))
+ ON (TRUE)
+LIMIT 10;
+reset enable_material;
+
-- Can't push down semi-join with inner rel vars in targetlist
EXPLAIN (verbose, costs off)
SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
--
2.43.0
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-18 11:19 Alexander Korotkov <[email protected]>
parent: Alexander Pyhalov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Korotkov @ 2025-03-18 11:19 UTC (permalink / raw)
To: Alexander Pyhalov <[email protected]>; +Cc: Robins Tharakan <[email protected]>; Tomas Vondra <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected]
Hi, Alexander!
On Tue, Mar 18, 2025 at 1:13 PM Alexander Pyhalov
<[email protected]> wrote:
> Alexander Korotkov писал(а) 2025-03-18 03:27:
> > Hi, Robins!
> >
> > On Tue, Mar 18, 2025 at 2:20 AM Robins Tharakan <[email protected]>
> > wrote:
> >> On Mon, 4 Dec 2023 at 07:22, Alexander Korotkov <[email protected]>
> >> wrote:
> >> >
> >> >
> >> > Now, I think this looks good. I'm going to push this if no objections.
> >>
> >> After this commit, I began seeing an unexpected ERROR - see this
> >> bug-report.
> >> https://www.postgresql.org/message-id/18852-fb75b88160678f78%40postgresql.org
> >
> > Thank you for pointing.
> > I'll check this in the next couple of days.
> >
>
> It seems conditions, coming from semi-joins, are handled incorrectly
> under left and right join. When deparsing left/right joins and there are
> semi-joins in inner or outer part of the query, the corresponding part
> is deparsed as subquery. And we can't refer subquery vars from above, so
> just should not pull up the restrictinfos. The attached patch does
> exactly this.
Thank you for the fix. It looks like this fix generally affects
left/right joins, not just semi-joins. Can you confirm this doesn't
cause regression for other cases?
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-18 16:04 Alexander Pyhalov <[email protected]>
parent: Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Pyhalov @ 2025-03-18 16:04 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Robins Tharakan <[email protected]>; Tomas Vondra <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected]
Alexander Korotkov писал(а) 2025-03-18 14:19:
> Hi, Alexander!
>
> On Tue, Mar 18, 2025 at 1:13 PM Alexander Pyhalov
> <[email protected]> wrote:
>> Alexander Korotkov писал(а) 2025-03-18 03:27:
>> > Hi, Robins!
>> >
>> > On Tue, Mar 18, 2025 at 2:20 AM Robins Tharakan <[email protected]>
>> > wrote:
>> >> On Mon, 4 Dec 2023 at 07:22, Alexander Korotkov <[email protected]>
>> >> wrote:
>> >> >
>> >> >
>> >> > Now, I think this looks good. I'm going to push this if no objections.
>> >>
>> >> After this commit, I began seeing an unexpected ERROR - see this
>> >> bug-report.
>> >> https://www.postgresql.org/message-id/18852-fb75b88160678f78%40postgresql.org
>> >
>> > Thank you for pointing.
>> > I'll check this in the next couple of days.
>> >
>>
>> It seems conditions, coming from semi-joins, are handled incorrectly
>> under left and right join. When deparsing left/right joins and there
>> are
>> semi-joins in inner or outer part of the query, the corresponding part
>> is deparsed as subquery. And we can't refer subquery vars from above,
>> so
>> just should not pull up the restrictinfos. The attached patch does
>> exactly this.
>
> Thank you for the fix. It looks like this fix generally affects
> left/right joins, not just semi-joins. Can you confirm this doesn't
> cause regression for other cases?
>
Hi.
This shouldn't. When semi-join is found below left/right join, it's
deparsed as subquery.
Interesting enough, this mechanics (deparsing as subquery) is used
1) for semi-joins under left/right join,
2) for full outer joins when inner or outer part has some remote_conds.
The issue here is that after subquery is deparsed, we don't consider if
its target attributes are available to the upper level
join . As for semi-join itself, all conditions are still deparsed on
left/right join boundary, they are just not propagated further.
This shouldn't be a problem, as they are evaluated in subquery. As for
left/right join without semi-join beneath it - its behavior is not
affected
(as hidden_subquery_rels is empty).
--
Best regards,
Alexander Pyhalov,
Postgres Professional
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-24 01:21 Alexander Korotkov <[email protected]>
parent: Alexander Pyhalov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Korotkov @ 2025-03-24 01:21 UTC (permalink / raw)
To: Alexander Pyhalov <[email protected]>; +Cc: Robins Tharakan <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected]
Hi, Alexander!
On Tue, Mar 18, 2025 at 6:04 PM Alexander Pyhalov
<[email protected]> wrote:
> This shouldn't. When semi-join is found below left/right join, it's
> deparsed as subquery.
> Interesting enough, this mechanics (deparsing as subquery) is used
> 1) for semi-joins under left/right join,
> 2) for full outer joins when inner or outer part has some remote_conds.
>
> The issue here is that after subquery is deparsed, we don't consider if
> its target attributes are available to the upper level
> join . As for semi-join itself, all conditions are still deparsed on
> left/right join boundary, they are just not propagated further.
> This shouldn't be a problem, as they are evaluated in subquery. As for
> left/right join without semi-join beneath it - its behavior is not
> affected
> (as hidden_subquery_rels is empty).
Thank you for the explanation. But I have another question. Aren't
the checks you've proposed too strict? hidden_subquery_rels are
propagated all the way to the join tree. So, pulling conditions would
be disables all the way to the join tree too. Is it enough to just
disable pulling conditions directly from semi-joins, then their
further pulls will be disabled automatically? See the attached patch.
It also contains other (mostly cosmetic improvements).
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v2-0001-Avoid-pulling-up-restrict-infos-from-subqueries.patch (6.2K, ../../CAPpHfdu0+Zyvy6acCeW2YKaaTbH=PggyNJbrKMu2__KbEGVEFA@mail.gmail.com/2-v2-0001-Avoid-pulling-up-restrict-infos-from-subqueries.patch)
download | inline diff:
From 510031bfa9eba3726cc93ddf05c1d9217b4e1585 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <[email protected]>
Date: Tue, 18 Mar 2025 13:46:08 +0300
Subject: [PATCH v2] Avoid pulling up restrict infos from subqueries
Semi-join joins below left/right join are deparsed as subqueries. Thus,
we can't refer to subqueries vars from upper relations. This commit avoids
pulling conditions from them.
Reported-by: Robins Tharakan <[email protected]>
Bug: #18852
Discussion: https://postgr.es/m/CAEP4nAzryLd3gwcUpFBAG9MWyDfMRX8ZjuyY2XXjyC_C6k%2B_Zw%40mail.gmail.com
Author: Alexander Pyhalov <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Backpatch-through: 17
---
.../postgres_fdw/expected/postgres_fdw.out | 38 +++++++++++++++++++
contrib/postgres_fdw/postgres_fdw.c | 22 +++++++++--
contrib/postgres_fdw/sql/postgres_fdw.sql | 17 +++++++++
3 files changed, 73 insertions(+), 4 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bb4ed3059c4..d1acee5a5fa 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4936,6 +4936,44 @@ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
----+----+----+----+----+----+----+----+----+----+----
(0 rows)
+-- Semi-join conditions shouldn't pop up as left/right join clauses.
+SET enable_material TO off;
+EXPLAIN (verbose, costs off)
+SELECT x1.c1 FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
+ ON (x1.c1 = x2.c1)
+ORDER BY x1.c1 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1
+ Relations: ((public.ft2 ft2_1) LEFT JOIN ((public.ft2) SEMI JOIN (public.ft4))) SEMI JOIN (public.ft4 ft4_1)
+ Remote SQL: SELECT s9.c1 FROM ("S 1"."T 1" r6 LEFT JOIN (SELECT r4."C 1" FROM "S 1"."T 1" r4 WHERE ((r4.c2 < 10)) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r5 WHERE ((r4."C 1" = r5.c1)))) s9(c1) ON (((s9.c1 = r6."C 1")))) WHERE ((r6.c2 < 10)) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r7 WHERE ((r6."C 1" = r7.c1))) ORDER BY s9.c1 ASC NULLS LAST LIMIT 10::bigint
+(4 rows)
+
+SELECT x1.c1 FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
+ ON (x1.c1 = x2.c1)
+ORDER BY x1.c1 LIMIT 10;
+ c1
+----
+ 2
+ 4
+ 6
+ 8
+ 10
+ 12
+ 14
+ 16
+ 18
+ 20
+(10 rows)
+
+RESET enable_material;
-- Can't push down semi-join with inner rel vars in targetlist
EXPLAIN (verbose, costs off)
SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 6beae0fa37f..f9bc7b60939 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5963,16 +5963,30 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
break;
case JOIN_LEFT:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ /*
+ * When semi-join is involved in the inner or outer part of the
+ * left join, it's deparsed as a subquery, and we can't refer to
+ * its vars on the upper level.
+ */
+ if (fpinfo_i->jointype != JOIN_SEMI)
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
fpinfo_i->remote_conds);
- fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
+ if (fpinfo_o->jointype != JOIN_SEMI)
+ fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
fpinfo_o->remote_conds);
break;
case JOIN_RIGHT:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ /*
+ * When semi-join is involved in the inner or outer part of the
+ * right join, it's deparsed as a subquery, and we can't refer to
+ * its vars on the upper level.
+ */
+ if (fpinfo_o->jointype != JOIN_SEMI)
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
fpinfo_o->remote_conds);
- fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
+ if (fpinfo_i->jointype != JOIN_SEMI)
+ fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
fpinfo_i->remote_conds);
break;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index d45e9f8ab52..ea6287b03fd 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1455,6 +1455,23 @@ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
WHERE ft2.c1 > 900
ORDER BY ft2.c1 LIMIT 10;
+-- Semi-join conditions shouldn't pop up as left/right join clauses.
+SET enable_material TO off;
+EXPLAIN (verbose, costs off)
+SELECT x1.c1 FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
+ ON (x1.c1 = x2.c1)
+ORDER BY x1.c1 LIMIT 10;
+SELECT x1.c1 FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
+ ON (x1.c1 = x2.c1)
+ORDER BY x1.c1 LIMIT 10;
+RESET enable_material;
+
-- Can't push down semi-join with inner rel vars in targetlist
EXPLAIN (verbose, costs off)
SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-24 07:07 Alexander Pyhalov <[email protected]>
parent: Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Pyhalov @ 2025-03-24 07:07 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Robins Tharakan <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected]
Alexander Korotkov писал(а) 2025-03-24 04:21:
> Hi, Alexander!
>
> On Tue, Mar 18, 2025 at 6:04 PM Alexander Pyhalov
> <[email protected]> wrote:
>> This shouldn't. When semi-join is found below left/right join, it's
>> deparsed as subquery.
>> Interesting enough, this mechanics (deparsing as subquery) is used
>> 1) for semi-joins under left/right join,
>> 2) for full outer joins when inner or outer part has some
>> remote_conds.
>>
>> The issue here is that after subquery is deparsed, we don't consider
>> if
>> its target attributes are available to the upper level
>> join . As for semi-join itself, all conditions are still deparsed on
>> left/right join boundary, they are just not propagated further.
>> This shouldn't be a problem, as they are evaluated in subquery. As for
>> left/right join without semi-join beneath it - its behavior is not
>> affected
>> (as hidden_subquery_rels is empty).
>
> Thank you for the explanation. But I have another question. Aren't
> the checks you've proposed too strict? hidden_subquery_rels are
> propagated all the way to the join tree. So, pulling conditions would
> be disables all the way to the join tree too. Is it enough to just
> disable pulling conditions directly from semi-joins, then their
> further pulls will be disabled automatically? See the attached patch.
> It also contains other (mostly cosmetic improvements).
>
> ------
> Regards,
> Alexander Korotkov
> Supabase
Hi. No, they are not too strict. Look at the following example
EXPLAIN (verbose, costs off)
SELECT x1.c1 FROM
(SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE
ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
RIGHT JOIN
(SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE
ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
ON (x1.c1 = x2.c1)
LEFT JOIN
(SELECT * FROM ft2 WHERE c2 < 11) x3
ON (x1.c1 = x3.c1)
ORDER BY x1.c1 LIMIT 10;
With patch which you suggest, we'll deparse left part of left join as
subquery, but will try to pop c2 < 10 condition from
(8) LEFT JOIN ((6) SEMI JOIN (7)) subquery. When we look at left join of
this subquery and ft2, we still deparse left part as
subquery, so can't pop up conditions from it.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-24 08:49 Alexander Korotkov <[email protected]>
parent: Alexander Pyhalov <[email protected]>
0 siblings, 1 reply; 19+ messages in thread
From: Alexander Korotkov @ 2025-03-24 08:49 UTC (permalink / raw)
To: Alexander Pyhalov <[email protected]>; +Cc: Robins Tharakan <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected]
On Mon, Mar 24, 2025 at 9:07 AM Alexander Pyhalov
<[email protected]> wrote:
> Alexander Korotkov писал(а) 2025-03-24 04:21:
> > Hi, Alexander!
> >
> > On Tue, Mar 18, 2025 at 6:04 PM Alexander Pyhalov
> > <[email protected]> wrote:
> >> This shouldn't. When semi-join is found below left/right join, it's
> >> deparsed as subquery.
> >> Interesting enough, this mechanics (deparsing as subquery) is used
> >> 1) for semi-joins under left/right join,
> >> 2) for full outer joins when inner or outer part has some
> >> remote_conds.
> >>
> >> The issue here is that after subquery is deparsed, we don't consider
> >> if
> >> its target attributes are available to the upper level
> >> join . As for semi-join itself, all conditions are still deparsed on
> >> left/right join boundary, they are just not propagated further.
> >> This shouldn't be a problem, as they are evaluated in subquery. As for
> >> left/right join without semi-join beneath it - its behavior is not
> >> affected
> >> (as hidden_subquery_rels is empty).
> >
> > Thank you for the explanation. But I have another question. Aren't
> > the checks you've proposed too strict? hidden_subquery_rels are
> > propagated all the way to the join tree. So, pulling conditions would
> > be disables all the way to the join tree too. Is it enough to just
> > disable pulling conditions directly from semi-joins, then their
> > further pulls will be disabled automatically? See the attached patch.
> > It also contains other (mostly cosmetic improvements).
> >
> > ------
> > Regards,
> > Alexander Korotkov
> > Supabase
>
> Hi. No, they are not too strict. Look at the following example
>
> EXPLAIN (verbose, costs off)
> SELECT x1.c1 FROM
> (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE
> ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
> RIGHT JOIN
> (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE
> ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
> ON (x1.c1 = x2.c1)
> LEFT JOIN
> (SELECT * FROM ft2 WHERE c2 < 11) x3
> ON (x1.c1 = x3.c1)
> ORDER BY x1.c1 LIMIT 10;
>
> With patch which you suggest, we'll deparse left part of left join as
> subquery, but will try to pop c2 < 10 condition from
> (8) LEFT JOIN ((6) SEMI JOIN (7)) subquery. When we look at left join of
> this subquery and ft2, we still deparse left part as
> subquery, so can't pop up conditions from it.
I've checked, this query seems to result in the exactly same remote
SQLs with your and mine patches. Could you elaborate more on the
difference? Do you think foreign_join_ok() can give different results
on this query?
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-24 16:56 Alexander Pyhalov <[email protected]>
parent: Alexander Korotkov <[email protected]>
0 siblings, 2 replies; 19+ messages in thread
From: Alexander Pyhalov @ 2025-03-24 16:56 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Robins Tharakan <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected]
Alexander Korotkov писал(а) 2025-03-24 11:49:
> On Mon, Mar 24, 2025 at 9:07 AM Alexander Pyhalov
> <[email protected]> wrote:
>> Alexander Korotkov писал(а) 2025-03-24 04:21:
>> > Hi, Alexander!
>> >
>> > On Tue, Mar 18, 2025 at 6:04 PM Alexander Pyhalov
>> > <[email protected]> wrote:
>> >> This shouldn't. When semi-join is found below left/right join, it's
>> >> deparsed as subquery.
>> >> Interesting enough, this mechanics (deparsing as subquery) is used
>> >> 1) for semi-joins under left/right join,
>> >> 2) for full outer joins when inner or outer part has some
>> >> remote_conds.
>> >>
>> >> The issue here is that after subquery is deparsed, we don't consider
>> >> if
>> >> its target attributes are available to the upper level
>> >> join . As for semi-join itself, all conditions are still deparsed on
>> >> left/right join boundary, they are just not propagated further.
>> >> This shouldn't be a problem, as they are evaluated in subquery. As for
>> >> left/right join without semi-join beneath it - its behavior is not
>> >> affected
>> >> (as hidden_subquery_rels is empty).
>> >
>> > Thank you for the explanation. But I have another question. Aren't
>> > the checks you've proposed too strict? hidden_subquery_rels are
>> > propagated all the way to the join tree. So, pulling conditions would
>> > be disables all the way to the join tree too. Is it enough to just
>> > disable pulling conditions directly from semi-joins, then their
>> > further pulls will be disabled automatically? See the attached patch.
>> > It also contains other (mostly cosmetic improvements).
>> >
>> > ------
>> > Regards,
>> > Alexander Korotkov
>> > Supabase
>>
>> Hi. No, they are not too strict. Look at the following example
>>
>> EXPLAIN (verbose, costs off)
>> SELECT x1.c1 FROM
>> (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4
>> WHERE
>> ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
>> RIGHT JOIN
>> (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4
>> WHERE
>> ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
>> ON (x1.c1 = x2.c1)
>> LEFT JOIN
>> (SELECT * FROM ft2 WHERE c2 < 11) x3
>> ON (x1.c1 = x3.c1)
>> ORDER BY x1.c1 LIMIT 10;
>>
>> With patch which you suggest, we'll deparse left part of left join as
>> subquery, but will try to pop c2 < 10 condition from
>> (8) LEFT JOIN ((6) SEMI JOIN (7)) subquery. When we look at left join
>> of
>> this subquery and ft2, we still deparse left part as
>> subquery, so can't pop up conditions from it.
>
> I've checked, this query seems to result in the exactly same remote
> SQLs with your and mine patches. Could you elaborate more on the
> difference? Do you think foreign_join_ok() can give different results
> on this query?
Hi.
With your patch this example gives the same
ERROR: unexpected expression in subquery output
This happens, because we don't keep knowledge that we have deparsed all
semi-joins below this left join. As long as left/right join has
semi-join in its left or right part, this part will be deparsed as
subquery (look at the following lines in foreign_join_ok()):
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->hidden_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->hidden_subquery_rels))
{
fpinfo->make_innerrel_subquery = true;
fpinfo->lower_subquery_rels =
bms_add_members(fpinfo->lower_subquery_rels, innerrel->relids);
}
}
So, we still can't refer to its remote_conds from upper level queries
(as not all Vars are available from subquery after subquery is created
in one part of left or right join). It's not necessary to have semi-join
for this as immediate left/right join inner or outer for inner/outer to
be deparsed as subquery. But it shouldn't be an issue - we've already
used remote_conds when created this subquery.
What I'm trying to say - logic of 'making subquery' and extracting
conditions should match (or we need more sophisticated way of forming
subquery targetlist, so that extracted conditions could be used above
subqueries).
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachments:
[text/x-diff] example.diff (5.6K, ../../[email protected]/2-example.diff)
download | inline diff:
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bb4ed3059c4..d1acee5a5fa 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4936,6 +4936,44 @@ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
----+----+----+----+----+----+----+----+----+----+----
(0 rows)
+-- Semi-join conditions shouldn't pop up as left/right join clauses.
+SET enable_material TO off;
+EXPLAIN (verbose, costs off)
+SELECT x1.c1 FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
+ ON (x1.c1 = x2.c1)
+ORDER BY x1.c1 LIMIT 10;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1
+ Relations: ((public.ft2 ft2_1) LEFT JOIN ((public.ft2) SEMI JOIN (public.ft4))) SEMI JOIN (public.ft4 ft4_1)
+ Remote SQL: SELECT s9.c1 FROM ("S 1"."T 1" r6 LEFT JOIN (SELECT r4."C 1" FROM "S 1"."T 1" r4 WHERE ((r4.c2 < 10)) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r5 WHERE ((r4."C 1" = r5.c1)))) s9(c1) ON (((s9.c1 = r6."C 1")))) WHERE ((r6.c2 < 10)) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r7 WHERE ((r6."C 1" = r7.c1))) ORDER BY s9.c1 ASC NULLS LAST LIMIT 10::bigint
+(4 rows)
+
+SELECT x1.c1 FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
+ ON (x1.c1 = x2.c1)
+ORDER BY x1.c1 LIMIT 10;
+ c1
+----
+ 2
+ 4
+ 6
+ 8
+ 10
+ 12
+ 14
+ 16
+ 18
+ 20
+(10 rows)
+
+RESET enable_material;
-- Can't push down semi-join with inner rel vars in targetlist
EXPLAIN (verbose, costs off)
SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 1131a8bf77e..455ddf101b4 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5964,16 +5964,30 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
break;
case JOIN_LEFT:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ /*
+ * When semi-join is involved in the inner or outer part of the
+ * left join, it's deparsed as a subquery, and we can't refer to
+ * its vars on the upper level.
+ */
+ if (fpinfo_i->jointype != JOIN_SEMI)
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
fpinfo_i->remote_conds);
- fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
+ if (fpinfo_o->jointype != JOIN_SEMI)
+ fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
fpinfo_o->remote_conds);
break;
case JOIN_RIGHT:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
+ /*
+ * When semi-join is involved in the inner or outer part of the
+ * right join, it's deparsed as a subquery, and we can't refer to
+ * its vars on the upper level.
+ */
+ if (fpinfo_o->jointype != JOIN_SEMI)
+ fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
fpinfo_o->remote_conds);
- fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
+ if (fpinfo_i->jointype != JOIN_SEMI)
+ fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
fpinfo_i->remote_conds);
break;
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index d45e9f8ab52..c58e8fc9912 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1455,6 +1455,34 @@ SELECT ft2.*, ft4.* FROM ft2 INNER JOIN
WHERE ft2.c1 > 900
ORDER BY ft2.c1 LIMIT 10;
+-- Semi-join conditions shouldn't pop up as left/right join clauses.
+SET enable_material TO off;
+EXPLAIN (verbose, costs off)
+SELECT x1.c1 FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
+ ON (x1.c1 = x2.c1)
+ORDER BY x1.c1 LIMIT 10;
+SELECT x1.c1 FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
+ ON (x1.c1 = x2.c1)
+ORDER BY x1.c1 LIMIT 10;
+
+EXPLAIN (verbose, costs off)
+SELECT x1.c1 FROM
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
+ RIGHT JOIN
+ (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
+ ON (x1.c1 = x2.c1)
+ LEFT JOIN
+ (SELECT * FROM ft2 WHERE c2 < 11) x3
+ ON (x1.c1 = x3.c1)
+ORDER BY x1.c1 LIMIT 10;
+RESET enable_material;
+
-- Can't push down semi-join with inner rel vars in targetlist
EXPLAIN (verbose, costs off)
SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-25 07:04 Tender Wang <[email protected]>
parent: Alexander Pyhalov <[email protected]>
1 sibling, 0 replies; 19+ messages in thread
From: Tender Wang @ 2025-03-25 07:04 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Alexander Pyhalov <[email protected]>; Robins Tharakan <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>
Hi Alexander Korotkov,
I saw the Bug: #18852 fix had been pushed. While I debug the sem- join
pushed down codes,
I wrote a patch to remove an unnecessary check on semijoin_target_ok() in
[1]. The patch
has been reviewed by Alexander Pyhalov. I think it's ready to commit.
Any thoughts?
[1]
https://www.postgresql.org/message-id/flat/CAHewXN=8aW4hd_W71F7Ua4+_w0=bppuvvTEBFBF6G0NuSXLwUw@mail....
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Add semi-join pushdown to postgres_fdw
@ 2025-03-25 10:55 Alexander Korotkov <[email protected]>
parent: Alexander Pyhalov <[email protected]>
1 sibling, 0 replies; 19+ messages in thread
From: Alexander Korotkov @ 2025-03-25 10:55 UTC (permalink / raw)
To: Alexander Pyhalov <[email protected]>; +Cc: Robins Tharakan <[email protected]>; [email protected] <[email protected]>; Ashutosh Bapat <[email protected]>; pgsql-hackers; Ian Lawrence Barwick <[email protected]>; [email protected]
On Mon, Mar 24, 2025 at 6:56 PM Alexander Pyhalov
<[email protected]> wrote:
> Alexander Korotkov писал(а) 2025-03-24 11:49:
> > On Mon, Mar 24, 2025 at 9:07 AM Alexander Pyhalov
> > <[email protected]> wrote:
> >> Alexander Korotkov писал(а) 2025-03-24 04:21:
> >> > Hi, Alexander!
> >> >
> >> > On Tue, Mar 18, 2025 at 6:04 PM Alexander Pyhalov
> >> > <[email protected]> wrote:
> >> >> This shouldn't. When semi-join is found below left/right join, it's
> >> >> deparsed as subquery.
> >> >> Interesting enough, this mechanics (deparsing as subquery) is used
> >> >> 1) for semi-joins under left/right join,
> >> >> 2) for full outer joins when inner or outer part has some
> >> >> remote_conds.
> >> >>
> >> >> The issue here is that after subquery is deparsed, we don't consider
> >> >> if
> >> >> its target attributes are available to the upper level
> >> >> join . As for semi-join itself, all conditions are still deparsed on
> >> >> left/right join boundary, they are just not propagated further.
> >> >> This shouldn't be a problem, as they are evaluated in subquery. As for
> >> >> left/right join without semi-join beneath it - its behavior is not
> >> >> affected
> >> >> (as hidden_subquery_rels is empty).
> >> >
> >> > Thank you for the explanation. But I have another question. Aren't
> >> > the checks you've proposed too strict? hidden_subquery_rels are
> >> > propagated all the way to the join tree. So, pulling conditions would
> >> > be disables all the way to the join tree too. Is it enough to just
> >> > disable pulling conditions directly from semi-joins, then their
> >> > further pulls will be disabled automatically? See the attached patch.
> >> > It also contains other (mostly cosmetic improvements).
> >> >
> >> > ------
> >> > Regards,
> >> > Alexander Korotkov
> >> > Supabase
> >>
> >> Hi. No, they are not too strict. Look at the following example
> >>
> >> EXPLAIN (verbose, costs off)
> >> SELECT x1.c1 FROM
> >> (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4
> >> WHERE
> >> ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x1
> >> RIGHT JOIN
> >> (SELECT * FROM ft2 WHERE EXISTS (SELECT 1 FROM ft4
> >> WHERE
> >> ft4.c1 = ft2.c1 AND ft2.c2 < 10)) x2
> >> ON (x1.c1 = x2.c1)
> >> LEFT JOIN
> >> (SELECT * FROM ft2 WHERE c2 < 11) x3
> >> ON (x1.c1 = x3.c1)
> >> ORDER BY x1.c1 LIMIT 10;
> >>
> >> With patch which you suggest, we'll deparse left part of left join as
> >> subquery, but will try to pop c2 < 10 condition from
> >> (8) LEFT JOIN ((6) SEMI JOIN (7)) subquery. When we look at left join
> >> of
> >> this subquery and ft2, we still deparse left part as
> >> subquery, so can't pop up conditions from it.
> >
> > I've checked, this query seems to result in the exactly same remote
> > SQLs with your and mine patches. Could you elaborate more on the
> > difference? Do you think foreign_join_ok() can give different results
> > on this query?
>
> Hi.
> With your patch this example gives the same
> ERROR: unexpected expression in subquery output
>
> This happens, because we don't keep knowledge that we have deparsed all
> semi-joins below this left join. As long as left/right join has
> semi-join in its left or right part, this part will be deparsed as
> subquery (look at the following lines in foreign_join_ok()):
>
> 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->hidden_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->hidden_subquery_rels))
> {
> fpinfo->make_innerrel_subquery = true;
> fpinfo->lower_subquery_rels =
> bms_add_members(fpinfo->lower_subquery_rels, innerrel->relids);
> }
> }
>
>
> So, we still can't refer to its remote_conds from upper level queries
> (as not all Vars are available from subquery after subquery is created
> in one part of left or right join). It's not necessary to have semi-join
> for this as immediate left/right join inner or outer for inner/outer to
> be deparsed as subquery. But it shouldn't be an issue - we've already
> used remote_conds when created this subquery.
> What I'm trying to say - logic of 'making subquery' and extracting
> conditions should match (or we need more sophisticated way of forming
> subquery targetlist, so that extracted conditions could be used above
> subqueries).
Thank you for the explanation. Pushed. However, it would be nice in
future to rework this in a way that semi-joins on lower levels of join
tree don't have negative impact on optimization of upper levels of
join tree.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2025-03-25 10:55 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-24 07:25 Add semi-join pushdown to postgres_fdw Alexander Pyhalov <[email protected]>
2022-08-29 14:12 ` Ashutosh Bapat <[email protected]>
2022-08-30 06:58 ` Alexander Pyhalov <[email protected]>
2022-11-03 23:21 ` Ian Lawrence Barwick <[email protected]>
2022-11-07 07:52 ` Alexander Pyhalov <[email protected]>
2022-12-03 03:02 ` [email protected] <[email protected]>
2022-12-06 09:28 ` Alexander Pyhalov <[email protected]>
2022-12-06 10:25 ` [email protected] <[email protected]>
2025-03-18 00:20 ` Robins Tharakan <[email protected]>
2025-03-18 00:27 ` Alexander Korotkov <[email protected]>
2025-03-18 11:13 ` Alexander Pyhalov <[email protected]>
2025-03-18 11:19 ` Alexander Korotkov <[email protected]>
2025-03-18 16:04 ` Alexander Pyhalov <[email protected]>
2025-03-24 01:21 ` Alexander Korotkov <[email protected]>
2025-03-24 07:07 ` Alexander Pyhalov <[email protected]>
2025-03-24 08:49 ` Alexander Korotkov <[email protected]>
2025-03-24 16:56 ` Alexander Pyhalov <[email protected]>
2025-03-25 07:04 ` Tender Wang <[email protected]>
2025-03-25 10:55 ` Alexander Korotkov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox