> 11. I have reworded following comment and restructured the code that follows
> it
> in the attached patch.
> + /*
> + * Set the subquery information. If the relation performs a full outer
> + * join and if the input relations have non-NIL remote_conds, the input
> + * relations need to be deparsed as a subquery.
> + */
> The code esp. the if .. else .. block followed by another one, made it a bit
> unreadable. Hence I restructured it so that it's readable and doesn't require
> variable "relids" from earlier code, which was being reused. Let me know if
> this change looks good.
>
Sorry, forgot to attach the patch. Here it is.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 691658f..aa1f111 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -95,20 +95,22 @@ typedef struct deparse_expr_cxt
PlannerInfo *root; /* global planner state */
RelOptInfo *foreignrel; /* the foreign relation we are planning for */
StringInfo buf; /* output buffer to append to */
List **params_list; /* exprs that will become remote Params */
} deparse_expr_cxt;
#define REL_ALIAS_PREFIX "r"
/* Handy macro to add relation name qualification */
#define ADD_REL_QUALIFIER(buf, varno) \
appendStringInfo((buf), "%s%d.", REL_ALIAS_PREFIX, (varno))
+#define SS_TAB_ALIAS_PREFIX "s"
+#define SS_COL_ALIAS_PREFIX "c"
/*
* Functions to determine whether an expression can be evaluated safely on
* remote server.
*/
static bool foreign_expr_walker(Node *node,
foreign_glob_cxt *glob_cxt,
foreign_loc_cxt *outer_cxt);
static char *deparse_type_name(Oid type_oid, int32 typemod);
@@ -145,27 +147,36 @@ static void deparseDistinctExpr(DistinctExpr *node, deparse_expr_cxt *context);
static void deparseScalarArrayOpExpr(ScalarArrayOpExpr *node,
deparse_expr_cxt *context);
static void deparseRelabelType(RelabelType *node, deparse_expr_cxt *context);
static void deparseBoolExpr(BoolExpr *node, deparse_expr_cxt *context);
static void deparseNullTest(NullTest *node, deparse_expr_cxt *context);
static void deparseArrayExpr(ArrayExpr *node, deparse_expr_cxt *context);
static void printRemoteParam(int paramindex, Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
-static void deparseSelectSql(List *tlist, List **retrieved_attrs,
+static void deparseSelectSql(List *tlist,
+ List *remote_conds,
+ List **retrieved_attrs,
deparse_expr_cxt *context);
static void deparseLockingClause(deparse_expr_cxt *context);
static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context);
static void appendConditions(List *exprs, deparse_expr_cxt *context);
static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root,
- RelOptInfo *joinrel, bool use_alias, List **params_list);
+ RelOptInfo *foreignrel, bool add_rel_alias,
+ List **params_list);
+static void deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
+ bool make_subquery, List **params_list);
+static void appendSubselectAlias(deparse_expr_cxt *context);
+static void getSubselectAliasInfo(Expr *node, RelOptInfo *foreignrel,
+ int *tabno, int *colno);
+static bool isSubqueryExpr(Expr *node, RelOptInfo *foreignrel, int *tabno, int *colno);
/*
* Examine each qual clause in input_conds, and classify them into two groups,
* which are returned as two lists:
* - remote_conds contains expressions that can be evaluated remotely
* - local_conds contains expressions that can't be evaluated remotely
*/
void
classifyConditions(PlannerInfo *root,
@@ -773,95 +784,105 @@ deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
Assert(rel->reloptkind == RELOPT_JOINREL ||
rel->reloptkind == RELOPT_BASEREL ||
rel->reloptkind == RELOPT_OTHER_MEMBER_REL);
/* Fill portions of context common to join and base relation */
context.buf = buf;
context.root = root;
context.foreignrel = rel;
context.params_list = params_list;
- /* Construct SELECT clause and FROM clause */
- deparseSelectSql(tlist, retrieved_attrs, &context);
-
- /*
- * Construct WHERE clause
- */
- if (remote_conds)
- {
- appendStringInfo(buf, " WHERE ");
- appendConditions(remote_conds, &context);
- }
+ /* Construct SELECT clause, FROM clause, and WHERE clause */
+ deparseSelectSql(tlist, remote_conds, retrieved_attrs, &context);
/* Add ORDER BY clause if we found any useful pathkeys */
if (pathkeys)
appendOrderByClause(pathkeys, &context);
/* Add any necessary FOR UPDATE/SHARE. */
deparseLockingClause(&context);
}
/*
* Construct a simple SELECT statement that retrieves desired columns
* of the specified foreign table, and append it to "buf". The output
- * contains just "SELECT ... FROM ....".
+ * contains just "SELECT ... FROM ... WHERE ...".
*
* We also create an integer List of the columns being retrieved, which is
* returned to *retrieved_attrs.
*
* tlist is the list of desired columns. Read prologue of
* deparseSelectStmtForRel() for details.
*/
static void
-deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
+deparseSelectSql(List *tlist,
+ List *remote_conds,
+ List **retrieved_attrs,
+ deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
RelOptInfo *foreignrel = context->foreignrel;
PlannerInfo *root = context->root;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
/*
* Construct SELECT list
*/
appendStringInfoString(buf, "SELECT ");
- if (foreignrel->reloptkind == RELOPT_JOINREL)
+ /*
+ * Note: tlist for a base relation might be non-NIL. For example, if the
+ * base relation is an operand of a foreign join performing a full outer
+ * join and has non-NIL remote_conds, the base relation will be deparsed
+ * as a subquery, so the tlist for the base relation could be non-NIL.
+ */
+ if (tlist != NIL)
{
- /* For a join relation use the input tlist */
deparseExplicitTargetList(tlist, retrieved_attrs, context);
}
else
{
/*
* For a base relation fpinfo->attrs_used gives the list of columns
* required to be fetched from the foreign server.
*/
RangeTblEntry *rte = planner_rt_fetch(foreignrel->relid, root);
/*
* Core code already has some lock on each rel being planned, so we
* can use NoLock here.
*/
Relation rel = heap_open(rte->relid, NoLock);
+ Assert(foreignrel->reloptkind != RELOPT_JOINREL);
+
deparseTargetList(buf, root, foreignrel->relid, rel, false,
fpinfo->attrs_used, false, retrieved_attrs);
heap_close(rel, NoLock);
}
/*
* Construct FROM clause
*/
appendStringInfoString(buf, " FROM ");
deparseFromExprForRel(buf, root, foreignrel,
(foreignrel->reloptkind == RELOPT_JOINREL),
context->params_list);
+
+ /*
+ * Construct WHERE clause
+ */
+ if (remote_conds)
+ {
+ appendStringInfoString(buf, " WHERE ");
+ appendConditions(remote_conds, context);
+ }
}
/*
* Emit a target list that retrieves the columns specified in attrs_used.
* This is used for both SELECT and RETURNING targetlists; the is_returning
* parameter is true only for a RETURNING targetlist.
*
* The tlist text is appended to buf, and we also create an integer List
* of the columns being retrieved, which is returned to *retrieved_attrs.
*
@@ -958,25 +979,34 @@ deparseTargetList(StringInfo buf,
/*
* Deparse the appropriate locking clause (FOR UPDATE or FOR SHARE) for a
* given relation (context->foreignrel).
*/
static void
deparseLockingClause(deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
PlannerInfo *root = context->root;
RelOptInfo *rel = context->foreignrel;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
int relid = -1;
while ((relid = bms_next_member(rel->relids, relid)) >= 0)
{
/*
+ * Ignore relation if it appears in a lower subquery, because in that
+ * case we would have already considered locking for it while
+ * deparsing the lower subquery.
+ */
+ if (bms_is_member(relid, fpinfo->subquery_rels))
+ continue;
+
+ /*
* Add FOR UPDATE/SHARE if appropriate. We apply locking during the
* initial row fetch, rather than later on as is done for local
* tables. The extra roundtrips involved in trying to duplicate the
* local semantics exactly don't seem worthwhile (see also comments
* for RowMarkType).
*
* Note: because we actually run the query as a cursor, this assumes
* that DECLARE CURSOR ... FOR UPDATE is supported, which it isn't
* before 8.3.
*/
@@ -1131,112 +1161,268 @@ deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
/* Extract expression if TargetEntry node */
Assert(IsA(tle, TargetEntry));
var = (Var *) tle->expr;
/* We expect only Var nodes here */
if (!IsA(var, Var))
elog(ERROR, "non-Var not expected in target list");
if (i > 0)
appendStringInfoString(buf, ", ");
- deparseVar(var, context);
+ deparseExpr((Expr *) var, context);
*retrieved_attrs = lappend_int(*retrieved_attrs, i + 1);
i++;
}
+ /* Don't generate bad syntax if no columns */
if (i == 0)
appendStringInfoString(buf, "NULL");
}
/*
* Construct FROM clause for given relation
*
- * The function constructs ... JOIN ... ON ... for join relation. For a base
- * relation it just returns schema-qualified tablename, with the appropriate
- * alias if so requested.
+ * For a join relation the clause of the following form is appended to buf:
+ * ((outer relation) <join type> (inner relation) ON (joinclauses))
+ * For a base relation the function just adds the schema-qualified tablename,
+ * with the appropriate alias if so requested.
*/
static void
-deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
- bool use_alias, List **params_list)
+deparseFromExprForRel(StringInfo buf, PlannerInfo *root,
+ RelOptInfo *foreignrel, bool add_rel_alias,
+ List **params_list)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
if (foreignrel->reloptkind == RELOPT_JOINREL)
{
- RelOptInfo *rel_o = fpinfo->outerrel;
- RelOptInfo *rel_i = fpinfo->innerrel;
- StringInfoData join_sql_o;
- StringInfoData join_sql_i;
+ /* Begin the FROM clause entry */
+ appendStringInfoChar(buf, '(');
/* Deparse outer relation */
- initStringInfo(&join_sql_o);
- deparseFromExprForRel(&join_sql_o, root, rel_o, true, params_list);
+ deparseRangeTblRef(buf, root,
+ fpinfo->outerrel,
+ fpinfo->make_outerrel_subquery,
+ params_list);
- /* Deparse inner relation */
- initStringInfo(&join_sql_i);
- deparseFromExprForRel(&join_sql_i, root, rel_i, true, params_list);
+ /* Append join type */
+ appendStringInfo(buf, " %s JOIN ",
+ get_jointype_name(fpinfo->jointype));
- /*
- * 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);
+ /* Deparse inner relation */
+ deparseRangeTblRef(buf, root,
+ fpinfo->innerrel,
+ fpinfo->make_innerrel_subquery,
+ params_list);
- /* Append join clause; (TRUE) if no join clause */
+ /* Append join conditions */
+ appendStringInfoString(buf, " ON ");
if (fpinfo->joinclauses)
{
deparse_expr_cxt context;
context.buf = buf;
context.foreignrel = foreignrel;
context.root = root;
context.params_list = params_list;
- appendStringInfo(buf, "(");
+ appendStringInfoChar(buf, '(');
appendConditions(fpinfo->joinclauses, &context);
- appendStringInfo(buf, ")");
+ appendStringInfoChar(buf, ')');
}
else
+ {
+ /* No join conditions; add "(TRUE)" */
appendStringInfoString(buf, "(TRUE)");
+ }
- /* End the FROM clause entry. */
- appendStringInfo(buf, ")");
+ /* End the FROM clause entry */
+ appendStringInfoChar(buf, ')');
}
else
{
RangeTblEntry *rte = planner_rt_fetch(foreignrel->relid, root);
/*
* Core code already has some lock on each rel being planned, so we
* can use NoLock here.
*/
Relation rel = heap_open(rte->relid, NoLock);
deparseRelation(buf, rel);
/*
* Add a unique alias to avoid any conflict in relation names due to
* pulled up subqueries in the query being built for a pushed down
* join.
*/
- if (use_alias)
+ if (add_rel_alias)
appendStringInfo(buf, " %s%d", REL_ALIAS_PREFIX, foreignrel->relid);
heap_close(rel, NoLock);
}
}
/*
+ * Append operand relation of foreign join to buf.
+ */
+static void
+deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
+ bool make_subquery, List **params_list)
+{
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+
+ Assert(fpinfo->local_conds == NIL);
+
+ if (make_subquery)
+ {
+ List *tlist;
+ List *retrieved_attrs;
+ deparse_expr_cxt context;
+
+ context.buf = buf;
+ context.root = root;
+ context.foreignrel = foreignrel;
+ context.params_list = params_list;
+
+ tlist = build_tlist_to_deparse(foreignrel);
+ appendStringInfoChar(buf, '(');
+ deparseSelectSql(tlist,
+ fpinfo->remote_conds,
+ &retrieved_attrs,
+ &context);
+ deparseLockingClause(&context);
+ appendStringInfoChar(buf, ')');
+ appendSubselectAlias(&context);
+ }
+ else
+ deparseFromExprForRel(buf, root, foreignrel, true, params_list);
+}
+
+/*
+ * Add a subselect alias to a subquery-in-FROM.
+ */
+static void
+appendSubselectAlias(deparse_expr_cxt *context)
+{
+ StringInfo buf = context->buf;
+ RelOptInfo *foreignrel = context->foreignrel;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ int num_columns = list_length(foreignrel->reltarget->exprs);
+ int i;
+
+ /* Append the table alias */
+ appendStringInfo(buf, " %s%d", SS_TAB_ALIAS_PREFIX, fpinfo->relation_index);
+
+ /* Append the column aliases */
+ appendStringInfoChar(buf, '(');
+ for (i = 1; i <= num_columns; i++)
+ {
+ if (i > 1)
+ appendStringInfoString(buf, ", ");
+
+ appendStringInfo(buf, "%s%d", SS_COL_ALIAS_PREFIX, i);
+ }
+ appendStringInfoChar(buf, ')');
+}
+
+/*
+ * Get info about the subselect alias to given expression.
+ *
+ * The subselect table and column numbers are returned to *tabno and *colno,
+ * respectively.
+ */
+static void
+getSubselectAliasInfo(Expr *node, RelOptInfo *foreignrel,
+ int *tabno, int *colno)
+{
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ int i;
+ ListCell *lc;
+
+ /* Get the table number */
+ *tabno = fpinfo->relation_index;
+
+ /* Get the column number */
+ i = 1;
+ foreach(lc, foreignrel->reltarget->exprs)
+ {
+ if (equal(lfirst(lc), (Node *) node))
+ {
+ *colno = i;
+ return;
+ }
+ i++;
+ }
+ /* shouldn't get here */
+ elog(ERROR, "unexpected expression in subquery output");
+}
+
+/*
+ * Returns true if given expression is an output column of a subquery-in-FROM.
+ *
+ * The subselect table and column numbers are returned to *tabno and *colno,
+ * respectively, in that case.
+ */
+static bool
+isSubqueryExpr(Expr *node, RelOptInfo *foreignrel, int *tabno, int *colno)
+{
+ Var *var = (Var *) node;
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ RelOptInfo *outerrel = fpinfo->outerrel;
+ RelOptInfo *innerrel = fpinfo->innerrel;
+
+ if (foreignrel->reloptkind != RELOPT_JOINREL)
+ return false;
+
+ if (!fpinfo->subquery_rels)
+ return false;
+
+ Assert(IsA(var, Var));
+
+ if (bms_is_member(var->varno, outerrel->relids))
+ {
+ /*
+ * If outer relation is deparsed as a subqeury, the given expression
+ * will be an output column of the subquery; get the subselect alias
+ * info for the given expression.
+ */
+ if (fpinfo->make_outerrel_subquery)
+ {
+ getSubselectAliasInfo(node, outerrel, tabno, colno);
+ return true;
+ }
+ /* Otherwise, recurse into outer relation */
+ if (isSubqueryExpr(node, outerrel, tabno, colno))
+ return true;
+ }
+ else
+ {
+ Assert(bms_is_member(var->varno, innerrel->relids));
+
+ /*
+ * Likewise for inner relation
+ */
+ if (fpinfo->make_innerrel_subquery)
+ {
+ getSubselectAliasInfo(node, innerrel, tabno, colno);
+ return true;
+ }
+ if (isSubqueryExpr(node, innerrel, tabno, colno))
+ return true;
+ }
+ return false;
+}
+
+/*
* deparse remote INSERT statement
*
* The statement text is appended to buf, and we also create an integer List
* of the columns being retrieved by RETURNING (if any), which is returned
* to *retrieved_attrs.
*/
void
deparseInsertSql(StringInfo buf, PlannerInfo *root,
Index rtindex, Relation rel,
List *targetAttrs, bool doNothing,
@@ -1804,20 +1990,38 @@ deparseStringLiteral(StringInfo buf, const char *val)
* Note: unlike ruleutils.c, we just use a simple hard-wired parenthesization
* scheme: anything more complex than a Var, Const, function call or cast
* should be self-parenthesized.
*/
static void
deparseExpr(Expr *node, deparse_expr_cxt *context)
{
if (node == NULL)
return;
+ /*
+ * If the given expression is an output column of a subquery-in-FROM,
+ * deparse the alias to the expression instead.
+ */
+ if (IsA(node, Var))
+ {
+ int tabno;
+ int colno;
+
+ if (isSubqueryExpr(node, context->foreignrel, &tabno, &colno))
+ {
+ appendStringInfo(context->buf, "%s%d.%s%d",
+ SS_TAB_ALIAS_PREFIX, tabno,
+ SS_COL_ALIAS_PREFIX, colno);
+ return;
+ }
+ }
+
switch (nodeTag(node))
{
case T_Var:
deparseVar((Var *) node, context);
break;
case T_Const:
deparseConst((Const *) node, context);
break;
case T_Param:
deparseParam((Param *) node, context);
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index d97e694..65465c3 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -449,31 +449,31 @@ SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1"
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- A join between local table and foreign join. ORDER BY clause is added to the
-- foreign join so that the local table can be joined using merge join strategy.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1" FROM "S 1"."T 1" t1 left join ft1 t2 join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1."C 1"
-> Merge Right Join
Output: t1."C 1"
Merge Cond: (t3.c1 = t1."C 1")
-> Foreign Scan
Output: t3.c1
Relations: (public.ft1 t2) INNER JOIN (public.ft2 t3)
- Remote SQL: SELECT r3."C 1" FROM ("S 1"."T 1" r2 INNER JOIN "S 1"."T 1" r3 ON (((r2."C 1" = r3."C 1")))) ORDER BY r2."C 1" ASC NULLS LAST
+ Remote SQL: SELECT r3."C 1" FROM ("S 1"."T 1" r2 INNER JOIN "S 1"."T 1" r3 ON (TRUE)) WHERE ((r2."C 1" = r3."C 1")) ORDER BY r2."C 1" ASC NULLS LAST
-> Index Only Scan using t1_pkey on "S 1"."T 1" t1
Output: t1."C 1"
(11 rows)
SELECT t1."C 1" FROM "S 1"."T 1" t1 left join ft1 t2 join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
C 1
-----
101
102
103
@@ -972,59 +972,59 @@ SELECT count(c3) FROM ft1 t1 WHERE t1.c1 === t1.c2;
-- ===================================================================
-- JOIN queries
-- ===================================================================
-- Analyze ft4 and ft5 so that we have better statistics. These tables do not
-- have use_remote_estimate set.
ANALYZE ft4;
ANALYZE ft5;
-- join two tables
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" 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
+ Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST
(6 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
c1 | c1
-----+-----
101 | 101
102 | 102
103 | 103
104 | 104
105 | 105
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- join three tables
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c2, t3.c3, t1.c3
-> Sort
Output: t1.c1, t2.c2, t3.c3, t1.c3
Sort Key: t1.c3, t1.c1
-> Foreign Scan
Output: t1.c1, t2.c2, t3.c3, t1.c3
Relations: ((public.ft1 t1) INNER JOIN (public.ft2 t2)) INNER JOIN (public.ft4 t3)
- Remote SQL: SELECT r1."C 1", r1.c3, r2.c2, r4.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) INNER JOIN "S 1"."T 3" r4 ON (((r1."C 1" = r4.c1))))
+ Remote SQL: SELECT r1."C 1", r1.c3, r2.c2, r4.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) INNER JOIN "S 1"."T 3" r4 ON (TRUE)) WHERE ((r1."C 1" = r4.c1)) AND ((r1."C 1" = r2."C 1"))
(9 rows)
SELECT t1.c1, t2.c2, t3.c3 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) JOIN ft4 t3 ON (t3.c1 = t1.c1) ORDER BY t1.c3, t1.c1 OFFSET 10 LIMIT 10;
c1 | c2 | c3
----+----+--------
22 | 2 | AAA022
24 | 4 | AAA024
26 | 6 | AAA026
28 | 8 | AAA028
30 | 0 | AAA030
@@ -1216,79 +1216,107 @@ SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.
| 3
| 9
| 15
| 21
| 27
(10 rows)
-- full outer join with restrictions on the joining relations
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
- QUERY PLAN
-------------------------------------------------------------------------------------------------
- Sort
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
Output: ft4.c1, ft5.c1
- Sort Key: ft4.c1, ft5.c1
- -> Hash Full Join
- Output: ft4.c1, ft5.c1
- Hash Cond: (ft4.c1 = ft5.c1)
- -> Foreign Scan on public.ft4
- Output: ft4.c1, ft4.c2, ft4.c3
- Remote SQL: SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))
- -> Hash
- Output: ft5.c1
- -> Foreign Scan on public.ft5
- Output: ft5.c1
- Remote SQL: SELECT c1 FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))
-(14 rows)
+ Relations: (public.ft4) FULL JOIN (public.ft5)
+ Remote SQL: SELECT s4.c1, s5.c1 FROM ((SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s4(c1) FULL JOIN (SELECT c1 FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s5(c1) ON (((s4.c1 = s5.c1)))) ORDER BY s4.c1 ASC NULLS LAST, s5.c1 ASC NULLS LAST
+(4 rows)
SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
c1 | c1
----+----
50 |
52 |
54 | 54
56 |
58 |
60 | 60
| 51
| 57
(8 rows)
-- full outer join + inner join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t3.c1
-> Foreign Scan
Output: t1.c1, t2.c1, t3.c1
Relations: ((public.ft4 t1) INNER JOIN (public.ft5 t2)) FULL JOIN (public.ft4 t3)
- Remote SQL: SELECT r1.c1, r2.c1, r4.c1 FROM (("S 1"."T 3" r1 INNER JOIN "S 1"."T 4" r2 ON (((r1.c1 = (r2.c1 + 1))) AND ((r1.c1 >= 50)) AND ((r1.c1 <= 60)))) FULL JOIN "S 1"."T 3" r4 ON (((r2.c1 = r4.c1)))) ORDER BY r1.c1 ASC NULLS LAST, r2.c1 ASC NULLS LAST, r4.c1 ASC NULLS LAST
+ Remote SQL: SELECT s6.c1, s6.c2, r4.c1 FROM ((SELECT r1.c1, r2.c1 FROM ("S 1"."T 3" r1 INNER JOIN "S 1"."T 4" r2 ON (TRUE)) WHERE ((r1.c1 = (r2.c1 + 1))) AND ((r1.c1 >= 50)) AND ((r1.c1 <= 60))) s6(c1, c2) FULL JOIN "S 1"."T 3" r4 ON (((s6.c2 = r4.c1)))) ORDER BY s6.c1 ASC NULLS LAST, s6.c2 ASC NULLS LAST, r4.c1 ASC NULLS LAST
(6 rows)
SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
c1 | c1 | c1
----+----+----
52 | 51 |
58 | 57 |
| | 2
| | 4
| | 6
| | 8
| | 10
| | 12
| | 14
| | 16
(10 rows)
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1, t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.*, t1.c1, t2.c1, t3.c1
+ -> Sort
+ Output: t1.*, t1.c1, t2.c1, t3.c1
+ Sort Key: t1.c1, t2.c1, t3.c1
+ -> Hash Full Join
+ Output: t1.*, t1.c1, t2.c1, t3.c1
+ Hash Cond: (t2.c1 = t3.c1)
+ -> Foreign Scan
+ Output: t1.*, t1.c1, t2.c1
+ Relations: (public.ft4 t1) INNER JOIN (public.ft5 t2)
+ Remote SQL: SELECT CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1.c1, r1.c2, r1.c3) END, r1.c1, r2.c1 FROM ("S 1"."T 3" r1 INNER JOIN "S 1"."T 4" r2 ON (TRUE)) WHERE ((r1.c1 = (r2.c1 + 1))) AND ((r1.c1 >= 50)) AND ((r1.c1 <= 60))
+ -> Hash
+ Output: t3.c1
+ -> Foreign Scan on public.ft4 t3
+ Output: t3.c1
+ Remote SQL: SELECT c1 FROM "S 1"."T 3"
+(17 rows)
+
+SELECT t1, t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ t1 | c1 | c1 | c1
+----------------+----+----+----
+ (52,53,AAA052) | 52 | 51 |
+ (58,59,AAA058) | 58 | 57 |
+ | | | 2
+ | | | 4
+ | | | 6
+ | | | 8
+ | | | 10
+ | | | 12
+ | | | 14
+ | | | 16
+(10 rows)
+
-- full outer join three tables
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c2, t3.c3
-> Foreign Scan
Output: t1.c1, t2.c2, t3.c3
Relations: ((public.ft2 t1) FULL JOIN (public.ft2 t2)) FULL JOIN (public.ft4 t3)
@@ -1446,28 +1474,28 @@ SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) LEFT
16 | 6 | AAA016
17 | 7 |
18 | 8 | AAA018
19 | 9 |
20 | 0 | AAA020
(10 rows)
-- left outer join + right outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c2, t3.c3
-> Foreign Scan
Output: t1.c1, t2.c2, t3.c3
Relations: (public.ft4 t3) LEFT JOIN ((public.ft2 t1) INNER JOIN (public.ft2 t2))
- Remote SQL: SELECT r4.c3, r1."C 1", r2.c2 FROM ("S 1"."T 3" r4 LEFT JOIN ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ON (((r2."C 1" = r4.c1))))
+ Remote SQL: SELECT r4.c3, r1."C 1", r2.c2 FROM ("S 1"."T 3" r4 LEFT JOIN ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) ON (((r2."C 1" = r4.c1)) AND ((r1."C 1" = r2."C 1"))))
(6 rows)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
c1 | c2 | c3
----+----+--------
22 | 2 | AAA022
24 | 4 | AAA024
26 | 6 | AAA026
28 | 8 | AAA028
30 | 0 | AAA030
@@ -1506,30 +1534,30 @@ SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1
| 3
| 9
| 15
| 21
(10 rows)
-- join two tables with FOR UPDATE clause
-- tests whole-row reference for row marks
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, 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, r2."C 1", 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 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 FOR UPDATE OF r1
+ Remote SQL: SELECT r1."C 1", r1.c3, 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, r2."C 1", 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 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
-> Sort
Output: t1.c1, t1.c3, t1.*
Sort Key: t1.c1
-> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
-> Sort
@@ -1550,30 +1578,30 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
105 | 105
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, 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, r2."C 1", 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 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 FOR UPDATE OF r1 FOR UPDATE OF r2
+ Remote SQL: SELECT r1."C 1", r1.c3, 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, r2."C 1", 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 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1 FOR UPDATE OF r2
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
-> Sort
Output: t1.c1, t1.c3, t1.*
Sort Key: t1.c1
-> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR UPDATE
-> Sort
@@ -1595,30 +1623,30 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- join two tables with FOR SHARE clause
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, 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, r2."C 1", 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 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 FOR SHARE OF r1
+ Remote SQL: SELECT r1."C 1", r1.c3, 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, r2."C 1", 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 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
-> Sort
Output: t1.c1, t1.c3, t1.*
Sort Key: t1.c1
-> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
-> Sort
@@ -1639,30 +1667,30 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
105 | 105
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, 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, r2."C 1", 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 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 FOR SHARE OF r1 FOR SHARE OF r2
+ Remote SQL: SELECT r1."C 1", r1.c3, 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, r2."C 1", 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 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1 FOR SHARE OF r2
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
-> Sort
Output: t1.c1, t1.c3, t1.*
Sort Key: t1.c1
-> Foreign Scan on public.ft1 t1
Output: t1.c1, t1.c3, t1.*
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" FOR SHARE
-> Sort
@@ -1684,29 +1712,29 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- join in CTE
EXPLAIN (VERBOSE, COSTS OFF)
WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t.c1_1, t.c2_1, t.c1_3
CTE t
-> Foreign Scan
Output: t1.c1, t1.c3, t2.c1
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
+ Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
-> Sort
Output: t.c1_1, t.c2_1, t.c1_3
Sort Key: t.c1_3, t.c1_1
-> CTE Scan on t
Output: t.c1_1, t.c2_1, t.c1_3
(12 rows)
WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1 OFFSET 100 LIMIT 10;
c1_1 | c2_1
------+------
@@ -1718,28 +1746,28 @@ WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- 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;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
-> Foreign Scan
Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- 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, r1."C 1", r1.c3, 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 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
+ 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, r1."C 1", r1.c3, 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 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST
(6 rows)
-- SEMI JOIN, not pushed down
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
Output: t1.c1
-> Merge Semi Join
@@ -1948,109 +1976,109 @@ SELECT t1.c1, t2.c1 FROM ft1 t1 LEFT JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8
109 | 109
110 | 110
(10 rows)
-- join where unsafe to pushdown condition in WHERE clause has a column not
-- in the SELECT clause. In this test unsafe clause needs to have column
-- references from both joining sides so that the clause is not pushed down
-- into one of the joining sides.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3
-> Sort
Output: t1.c1, t2.c1, t1.c3
Sort Key: t1.c3, t1.c1
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3
Filter: (t1.c8 = t2.c8)
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1", r1.c8, r2.c8 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
+ Remote SQL: SELECT r1."C 1", r1.c3, r2."C 1", r1.c8, r2.c8 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
(10 rows)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) WHERE t1.c8 = t2.c8 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
c1 | c1
-----+-----
101 | 101
102 | 102
103 | 103
104 | 104
105 | 105
106 | 106
107 | 107
108 | 108
109 | 109
110 | 110
(10 rows)
-- Aggregate after UNION, for testing setrefs
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, (avg((t1.c1 + t2.c1)))
-> Sort
Output: t1.c1, (avg((t1.c1 + t2.c1)))
Sort Key: t1.c1
-> HashAggregate
Output: t1.c1, avg((t1.c1 + t2.c1))
Group Key: t1.c1
-> HashAggregate
Output: t1.c1, t2.c1
Group Key: t1.c1, t2.c1
-> Append
-> Foreign Scan
Output: t1.c1, t2.c1
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
-> Foreign Scan
Output: t1_1.c1, t2_1.c1
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1"))))
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1"))
(20 rows)
SELECT t1c1, avg(t1c1 + t2c1) FROM (SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) UNION SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1)) AS t (t1c1, t2c1) GROUP BY t1c1 ORDER BY t1c1 OFFSET 100 LIMIT 10;
t1c1 | avg
------+----------------------
101 | 202.0000000000000000
102 | 204.0000000000000000
103 | 206.0000000000000000
104 | 208.0000000000000000
105 | 210.0000000000000000
106 | 212.0000000000000000
107 | 214.0000000000000000
108 | 216.0000000000000000
109 | 218.0000000000000000
110 | 220.0000000000000000
(10 rows)
-- join with lateral reference
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1."C 1"
-> Nested Loop
Output: t1."C 1"
-> Index Scan using t1_pkey on "S 1"."T 1" t1
Output: t1."C 1", t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
-> HashAggregate
Output: t2.c1, t3.c1
Group Key: t2.c1, t3.c1
-> Foreign Scan
Output: t2.c1, t3.c1
Relations: (public.ft1 t2) INNER JOIN (public.ft2 t3)
- Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")) AND ((r1.c2 = $1::integer))))
+ Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1."C 1" = r2."C 1")) AND ((r1.c2 = $1::integer))
(13 rows)
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
C 1
-----
1
1
1
1
1
@@ -2088,54 +2116,54 @@ SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 O
| 11
| 12
13 | 13
| 14
| 15
(6 rows)
-- ok to push {ft1, ft2} but not {ft1, ft2, ft4}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join
Output: ft4.c1, (13), ft1.c1, ft2.c1
Join Filter: (ft4.c1 = ft1.c1)
-> Foreign Scan on public.ft4
Output: ft4.c1, ft4.c2, ft4.c3
Remote SQL: SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 10)) AND ((c1 <= 15))
-> Materialize
Output: ft1.c1, ft2.c1, (13)
-> Foreign Scan
Output: ft1.c1, ft2.c1, 13
Relations: (public.ft1) INNER JOIN (public.ft2)
- Remote SQL: SELECT r4."C 1", r5."C 1" FROM ("S 1"."T 1" r4 INNER JOIN "S 1"."T 1" r5 ON (((r5."C 1" = 12)) AND ((r4."C 1" = 12)))) ORDER BY r4."C 1" ASC NULLS LAST
+ Remote SQL: SELECT r4."C 1", r5."C 1" FROM ("S 1"."T 1" r4 INNER JOIN "S 1"."T 1" r5 ON (TRUE)) WHERE ((r5."C 1" = 12)) AND ((r4."C 1" = 12)) ORDER BY r4."C 1" ASC NULLS LAST
(12 rows)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
c1 | a | b | c
----+----+----+----
10 | | |
12 | 13 | 12 | 12
14 | | |
(3 rows)
-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: ft5.*, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2
Relations: (public.ft5) INNER JOIN (public.ft4)
- Remote SQL: SELECT CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1.c1, r1.c2, r1.c3) END, r1.c1, r1.c2, r1.c3, r2.c1, r2.c2 FROM ("S 1"."T 4" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c1 = r2.c1)) AND ((r2.c1 >= 10)) AND ((r2.c1 <= 30)))) ORDER BY r1.c1 ASC NULLS LAST
+ Remote SQL: SELECT CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1.c1, r1.c2, r1.c3) END, r1.c1, r1.c2, r1.c3, r2.c1, r2.c2 FROM ("S 1"."T 4" r1 INNER JOIN "S 1"."T 3" r2 ON (TRUE)) WHERE ((r1.c1 = r2.c1)) AND ((r2.c1 >= 10)) AND ((r2.c1 <= 30)) ORDER BY r1.c1 ASC NULLS LAST
(4 rows)
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
ft5 | c1 | c2 | c3 | c1 | c2
----------------+----+----+--------+----+----
(12,13,AAA012) | 12 | 13 | AAA012 | 12 | 13
(18,19,) | 18 | 19 | | 18 | 19
(24,25,AAA024) | 24 | 25 | AAA024 | 24 | 25
(30,31,AAA030) | 30 | 31 | AAA030 | 30 | 31
(4 rows)
@@ -2281,26 +2309,26 @@ SELECT t1.c1, t2.c2 FROM v4 t1 LEFT JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c
ALTER VIEW v4 OWNER TO regress_view_owner;
-- cleanup
DROP OWNED BY regress_view_owner;
DROP ROLE regress_view_owner;
-- ===================================================================
-- parameterized queries
-- ===================================================================
-- simple join
PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2);
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: t1.c3, t2.c3
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
- Remote SQL: SELECT r1.c3, r2.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = 2)) AND ((r1."C 1" = 1))))
+ Remote SQL: SELECT r1.c3, r2.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r2."C 1" = 2)) AND ((r1."C 1" = 1))
(4 rows)
EXECUTE st1(1, 1);
c3 | c3
-------+-------
00001 | 00001
(1 row)
EXECUTE st1(101, 101);
c3 | c3
@@ -2910,28 +2938,28 @@ UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING
977 | 407 | 00977_update7 | Thu Mar 19 00:00:00 1970 PST | Thu Mar 19 00:00:00 1970 | 7 | 7 | foo
987 | 407 | 00987_update7 | Sun Mar 29 00:00:00 1970 PST | Sun Mar 29 00:00:00 1970 | 7 | 7 | foo
997 | 407 | 00997_update7 | Wed Apr 08 00:00:00 1970 PST | Wed Apr 08 00:00:00 1970 | 7 | 7 | foo
1007 | 507 | 0000700007_update7 | | | | ft2 |
1017 | 507 | 0001700017_update7 | | | | ft2 |
(102 rows)
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down
- QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c3 = $3, c7 = $4 WHERE ctid = $1
-> Foreign Scan
Output: ft2.c1, (ft2.c2 + 500), NULL::integer, (ft2.c3 || '_update9'::text), ft2.c4, ft2.c5, ft2.c6, 'ft2 '::character(10), ft2.c8, ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
- Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c8, r1.ctid, 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 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9)))) FOR UPDATE OF r1
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c8, r1.ctid, 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 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9)) FOR UPDATE OF r1
-> Hash Join
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid, ft1.*
Hash Cond: (ft2.c2 = ft1.c1)
-> Foreign Scan on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c8, ctid FROM "S 1"."T 1" FOR UPDATE
-> Hash
Output: ft1.*, ft1.c1
-> Foreign Scan on public.ft1
Output: ft1.*, ft1.c1
@@ -3053,28 +3081,28 @@ DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
975 | Tue Mar 17 00:00:00 1970 PST
985 | Fri Mar 27 00:00:00 1970 PST
995 | Mon Apr 06 00:00:00 1970 PST
1005 |
1015 |
1105 |
(103 rows)
EXPLAIN (verbose, costs off)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1
-> Foreign Scan
Output: ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
- Remote SQL: SELECT r1.ctid, 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 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 2)))) FOR UPDATE OF r1
+ Remote SQL: SELECT r1.ctid, 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 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (TRUE)) WHERE ((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 2)) FOR UPDATE OF r1
-> Hash Join
Output: ft2.ctid, ft1.*
Hash Cond: (ft2.c2 = ft1.c1)
-> Foreign Scan on public.ft2
Output: ft2.ctid, ft2.c2
Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" FOR UPDATE
-> Hash
Output: ft1.*, ft1.c1
-> Foreign Scan on public.ft1
Output: ft1.*, ft1.c1
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index daf0438..fb4b6af 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -396,20 +396,21 @@ static HeapTuple make_tuple_from_result_row(PGresult *res,
List *retrieved_attrs,
ForeignScanState *fsstate,
MemoryContext temp_context);
static void conversion_error_callback(void *arg);
static bool foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel,
JoinType jointype, RelOptInfo *outerrel, RelOptInfo *innerrel,
JoinPathExtraData *extra);
static List *get_useful_pathkeys_for_relation(PlannerInfo *root,
RelOptInfo *rel);
static List *get_useful_ecs_for_relation(PlannerInfo *root, RelOptInfo *rel);
+static bool reltarget_has_non_vars(RelOptInfo *foreignrel);
static void add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
Path *epq_path);
/*
* Foreign-data wrapper handler function: return a struct with pointers
* to my callback routines.
*/
Datum
postgres_fdw_handler(PG_FUNCTION_ARGS)
@@ -648,20 +649,28 @@ postgresGetForeignRelSize(PlannerInfo *root,
fpinfo->relation_name = makeStringInfo();
namespace = get_namespace_name(get_rel_namespace(foreigntableid));
relname = get_rel_name(foreigntableid);
refname = rte->eref->aliasname;
appendStringInfo(fpinfo->relation_name, "%s.%s",
quote_identifier(namespace),
quote_identifier(relname));
if (*refname && strcmp(refname, relname) != 0)
appendStringInfo(fpinfo->relation_name, " %s",
quote_identifier(rte->eref->aliasname));
+
+ /* Set the subquery information */
+ fpinfo->make_outerrel_subquery = false;
+ fpinfo->make_innerrel_subquery = false;
+ fpinfo->subquery_rels = NULL;
+
+ /* Set the relation index */
+ fpinfo->relation_index = baserel->relid;
}
/*
* get_useful_ecs_for_relation
* Determine which EquivalenceClasses might be involved in useful
* orderings of this relation.
*
* This function is in some respects a mirror image of the core function
* pathkeys_useful_for_merging: for a regular table, we know what indexes
* we have and want to test whether any of them are useful. For a foreign
@@ -3940,20 +3949,21 @@ static bool
foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
RelOptInfo *outerrel, RelOptInfo *innerrel,
JoinPathExtraData *extra)
{
PgFdwRelationInfo *fpinfo;
PgFdwRelationInfo *fpinfo_o;
PgFdwRelationInfo *fpinfo_i;
ListCell *lc;
List *joinclauses;
List *otherclauses;
+ Relids relids;
/*
* We support pushing down INNER, LEFT, RIGHT and FULL OUTER joins.
* Constructing queries representing SEMI and ANTI joins is hard, hence
* not considered right now.
*/
if (jointype != JOIN_INNER && jointype != JOIN_LEFT &&
jointype != JOIN_RIGHT && jointype != JOIN_FULL)
return false;
@@ -4004,24 +4014,24 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
/*
* deparseExplicitTargetList() isn't smart enough to handle anything other
* than a Var. In particular, if there's some PlaceHolderVar that would
* need to be evaluated within this join tree (because there's an upper
* reference to a quantity that may go to NULL as a result of an outer
* join), then we can't try to push the join down because we'll fail when
* we get to deparseExplicitTargetList(). However, a PlaceHolderVar that
* needs to be evaluated *at the top* of this join tree is OK, because we
* can do that locally after fetching the results from the remote side.
*/
+ relids = joinrel->relids;
foreach(lc, root->placeholder_list)
{
PlaceHolderInfo *phinfo = lfirst(lc);
- Relids relids = joinrel->relids;
if (bms_is_subset(phinfo->ph_eval_at, relids) &&
bms_nonempty_difference(relids, phinfo->ph_eval_at))
return false;
}
/* Save the join clauses, for later use. */
fpinfo->joinclauses = joinclauses;
/*
@@ -4040,36 +4050,48 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
fpinfo->local_conds = lappend(fpinfo->local_conds, expr);
else
fpinfo->remote_conds = lappend(fpinfo->remote_conds, expr);
}
fpinfo->outerrel = outerrel;
fpinfo->innerrel = innerrel;
fpinfo->jointype = jointype;
/*
+ * By default both the joining relations are not required to be deparsed as
+ * subqueries. But there might be some relations covered by the joining
+ * relations that are required to be deparsed as subqueries. Those need to
+ * be bubbled up to the topmost join that can be pushed down.
+ */
+ Assert(bms_is_subset(fpinfo_i->subquery_rels, innerrel->relids));
+ Assert(bms_is_subset(fpinfo_o->subquery_rels, outerrel->relids));
+
+ fpinfo->make_outerrel_subquery = false;
+ fpinfo->make_innerrel_subquery = false;
+ fpinfo->subquery_rels = bms_union(fpinfo_i->subquery_rels,
+ fpinfo_o->subquery_rels);
+
+ /*
* Pull the other remote conditions from the joining relations into join
* clauses or other remote clauses (remote_conds) of this relation
- * wherever possible. This avoids building subqueries at every join step,
- * which is not currently supported by the deparser logic.
+ * wherever possible. This avoids building subqueries at every join step.
*
* For an inner join, clauses from both the relations are added to the
* other remote clauses. For LEFT and RIGHT OUTER join, the clauses from
* the outer side are added to remote_conds since those can be evaluated
* after the join is evaluated. The clauses from inner side are added to
* the joinclauses, since they need to evaluated while constructing the
* join.
*
* For a FULL OUTER JOIN, the other clauses from either relation can not
* be added to the joinclauses or remote_conds, since each relation acts
- * as an outer relation for the other. Consider such full outer join as
- * unshippable because of the reasons mentioned above in this comment.
+ * as an outer relation for the other.
*
* The joining sides can not have local conditions, thus no need to test
* shippability of the clauses being pulled up.
*/
switch (jointype)
{
case JOIN_INNER:
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
@@ -4084,41 +4106,57 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
break;
case JOIN_RIGHT:
fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_o->remote_conds));
fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
break;
case JOIN_FULL:
- if (fpinfo_i->remote_conds || fpinfo_o->remote_conds)
+ /*
+ * We can't do anything here, and if there are any non-Vars in the
+ * outerrel/innerrel's reltarget, give up pushing down this join,
+ * because the deparsing logic can't support such a case currently.
+ */
+ if (reltarget_has_non_vars(outerrel))
+ return false;
+ if (reltarget_has_non_vars(innerrel))
return false;
+
+ /*
+ * If any of the joining relations have conditions and this is a
+ * FULL OUTER join, we need to deparse that relation as a subquery
+ * so that conditions can be evaluated before join. Remember it in
+ * the fpinfo so that deparser can take appropriate action. We also
+ * save the relids of the relations that are covered by subqueries
+ * for deparser.
+ */
+ if (fpinfo_o->remote_conds)
+ {
+ fpinfo->make_outerrel_subquery = true;
+ fpinfo->subquery_rels = bms_add_members(fpinfo->subquery_rels,
+ outerrel->relids);
+ }
+ if (fpinfo_i->remote_conds)
+ {
+ fpinfo->make_innerrel_subquery = true;
+ fpinfo->subquery_rels = bms_add_members(fpinfo->subquery_rels,
+ innerrel->relids);
+ }
break;
default:
/* Should not happen, we have just check this above */
elog(ERROR, "unsupported join type %d", jointype);
}
- /*
- * For an inner join, all restrictions can be treated alike. Treating the
- * pushed down conditions as join conditions allows a top level full outer
- * join to be deparsed without requiring subqueries.
- */
- if (jointype == JOIN_INNER)
- {
- Assert(!fpinfo->joinclauses);
- fpinfo->joinclauses = fpinfo->remote_conds;
- fpinfo->remote_conds = NIL;
- }
-
/* Mark that this join can be pushed down safely */
fpinfo->pushdown_safe = true;
/*
* If user is willing to estimate cost for a scan of either of the joining
* relations using EXPLAIN, he intends to estimate scans on that relation
* more accurately. Then, it makes sense to estimate the cost the join
* with that relation more accurately using EXPLAIN.
*/
fpinfo->use_remote_estimate = fpinfo_o->use_remote_estimate ||
@@ -4166,23 +4204,56 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
/*
* Set the string describing this join relation to be used in EXPLAIN
* output of corresponding ForeignScan.
*/
fpinfo->relation_name = makeStringInfo();
appendStringInfo(fpinfo->relation_name, "(%s) %s JOIN (%s)",
fpinfo_o->relation_name->data,
get_jointype_name(fpinfo->jointype),
fpinfo_i->relation_name->data);
+ /*
+ * Set the relation index. This is defined as the position of this
+ * joinrel in the join_rel_list list plus the length of the rtable list.
+ * Note that since this joinrel is at the end of the list when we are
+ * called, we can get the position by list_length.
+ */
+ fpinfo->relation_index =
+ list_length(root->parse->rtable) + list_length(root->join_rel_list);
+
return true;
}
+/*
+ * Detect whether there are whole-row Vars or system columns other than ctid
+ * and oid in the given relation's reltarget.
+ *
+ * Note: currently deparseExplicitTargetList can't properly handle such Vars.
+ */
+static bool
+reltarget_has_non_vars(RelOptInfo *foreignrel)
+{
+ ListCell *lc;
+
+ foreach(lc, foreignrel->reltarget->exprs)
+ {
+ Var *var = (Var *) lfirst(lc);
+
+ Assert(IsA(var, Var));
+ if (var->varattno <= 0 &&
+ var->varattno != SelfItemPointerAttributeNumber &&
+ var->varattno != ObjectIdAttributeNumber)
+ return true;
+ }
+ return false;
+}
+
static void
add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
Path *epq_path)
{
List *useful_pathkeys_list = NIL; /* List of all pathkeys */
ListCell *lc;
useful_pathkeys_list = get_useful_pathkeys_for_relation(root, rel);
/* Create one path for each set of pathkeys we found above. */
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index 67126bc..bcf7ed8 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -85,20 +85,33 @@ typedef struct PgFdwRelationInfo
* relations but is set for all relations. For join relation, the name
* indicates which foreign tables are being joined and the join type used.
*/
StringInfo relation_name;
/* Join information */
RelOptInfo *outerrel;
RelOptInfo *innerrel;
JoinType jointype;
List *joinclauses;
+
+ /* Subquery information */
+ bool make_outerrel_subquery; /* do we deparse outerrel as a
+ * subquery? */
+ bool make_innerrel_subquery; /* do we deparse innerrel as a
+ * subquery? */
+ Relids subquery_rels; /* all relids appearing in lower subqueries */
+
+ /*
+ * Index of the relation. It is used for creating a subselect alias when
+ * deparsing the relation as a subquery.
+ */
+ int relation_index;
} PgFdwRelationInfo;
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
/* in connection.c */
extern PGconn *GetConnection(UserMapping *user, bool will_prep_stmt);
extern void ReleaseConnection(PGconn *conn);
extern unsigned int GetCursorNumber(PGconn *conn);
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 4f68e89..53181fe 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -389,22 +389,25 @@ SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGH
-- full outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
-- full outer join with restrictions on the joining relations
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
-- full outer join + inner join
EXPLAIN (VERBOSE, COSTS OFF)
-SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
-SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+SELECT t1, t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+SELECT t1, t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1, t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+SELECT t1, t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
-- full outer join three tables
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
-- full outer join + right outer join
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
-- right outer join + full outer join
EXPLAIN (VERBOSE, COSTS OFF)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
