On 2016/10/27 18:06, Ashutosh Bapat wrote:
On Thu, Oct 27, 2016 at 12:46 PM, Etsuro Fujita
<fujita.ets...@lab.ntt.co.jp> wrote:
On 2016/10/26 19:53, Ashutosh Bapat wrote:
On Wed, Oct 26, 2016 at 3:35 PM, Etsuro Fujita
My concern about your proposal is: it might not be worth complicating the
code to solve a problem that is actually not a problem in practice.
To me the current code looks complicated esp. because of the recursion
involved and usage of out parameters to isSubqueryExpr().
I don't think so. isSubqueryExpr is prety small, written in less than 50
lines, and the code looks rather simple to me.
My
suggestion goes inline with the current method of deparsing a Var.
Yeah, I think your approach makes it easy to search for the alias to a given
Var from the array you proposed. I think the complexity of your approach
would be in extra work for building and maintaining the array while
deparsing the query. I think that would probably need more invasive and
much larger changes to the existing code than what I proposed.
I think this issue is optional, so I'd like to propose to leave this for the
committer's judge.
Fine with me.
OK
Here is the updated version, which includes the restructuring you
proposed. Other than the above issue and the alias issue we discussed,
I addressed all your comments except one on testing; I tried to add test
cases where the remote query is deparsed as nested subqueries, but I
couldn't because IIUC, reduce_outer_joins reduced full joins to inner
joins or left joins. So, I added two test cases: (1) the joining
relations are both base relations (actually, we already have that) and
(2) one of the joining relations is a base relation and the other is a
join relation. I rebased the patch to HEAD, so I added a test case with
aggregate pushdown also.
Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***************
*** 109,114 **** typedef struct deparse_expr_cxt
--- 109,116 ----
/* 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
***************
*** 159,172 **** 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,
- 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);
! static void deparseFromExpr(List *quals, deparse_expr_cxt *context);
static void deparseAggref(Aggref *node, deparse_expr_cxt *context);
static void appendGroupByClause(List *tlist, deparse_expr_cxt *context);
static void appendAggOrderBy(List *orderList, List *targetList,
--- 161,177 ----
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
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);
! static void deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
! bool make_subquery, List **params_list);
! static void appendSubselectAlias(StringInfo buf, int tabno, int ncols);
! static void getSubselectAliasInfo(Var *node, RelOptInfo *foreignrel,
! int *tabno, int *colno);
! static bool isSubqueryExpr(Var *node, RelOptInfo *foreignrel, int *tabno, int *colno);
static void deparseAggref(Aggref *node, deparse_expr_cxt *context);
static void appendGroupByClause(List *tlist, deparse_expr_cxt *context);
static void appendAggOrderBy(List *orderList, List *targetList,
***************
*** 899,1000 **** build_tlist_to_deparse(RelOptInfo *foreignrel)
* List of columns selected is returned in retrieved_attrs.
*/
extern void
! deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
! List *tlist, List *remote_conds, List *pathkeys,
List **retrieved_attrs, List **params_list)
{
deparse_expr_cxt context;
! PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
List *quals;
/*
* We handle relations for foreign tables, joins between those and upper
* relations.
*/
! Assert(rel->reloptkind == RELOPT_JOINREL ||
! rel->reloptkind == RELOPT_BASEREL ||
! rel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
! rel->reloptkind == RELOPT_UPPER_REL);
/* Fill portions of context common to upper, join and base relation */
context.buf = buf;
context.root = root;
! context.foreignrel = rel;
! context.scanrel = (rel->reloptkind == RELOPT_UPPER_REL) ?
! fpinfo->outerrel : rel;
context.params_list = params_list;
- /* Construct SELECT clause */
- deparseSelectSql(tlist, retrieved_attrs, &context);
-
- /*
- * For upper relations, the WHERE clause is built from the remote
- * conditions of the underlying scan relation; otherwise, we can use the
- * supplied list of remote conditions directly.
- */
- if (rel->reloptkind == RELOPT_UPPER_REL)
- {
- PgFdwRelationInfo *ofpinfo;
-
- ofpinfo = (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private;
- quals = ofpinfo->remote_conds;
- }
- else
- quals = remote_conds;
-
- /* Construct FROM and WHERE clauses */
- deparseFromExpr(quals, &context);
-
- if (rel->reloptkind == RELOPT_UPPER_REL)
- {
- /* Append GROUP BY clause */
- appendGroupByClause(tlist, &context);
-
- /* Append HAVING clause */
- if (remote_conds)
- {
- appendStringInfo(buf, " HAVING ");
- appendConditions(remote_conds, &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 ... ".
- *
- * 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)
- {
- 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 ||
! foreignrel->reloptkind == RELOPT_UPPER_REL)
{
! /* For a join relation use the input tlist */
! deparseExplicitTargetList(tlist, retrieved_attrs, context);
}
else
{
--- 904,959 ----
* List of columns selected is returned in retrieved_attrs.
*/
extern void
! deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root,
! RelOptInfo *foreignrel, List *tlist,
! List *remote_conds, List *pathkeys,
List **retrieved_attrs, List **params_list)
{
deparse_expr_cxt context;
! PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
List *quals;
+ RelOptInfo *scanrel = (foreignrel->reloptkind == RELOPT_UPPER_REL) ?
+ fpinfo->outerrel : foreignrel;
/*
* We handle relations for foreign tables, joins between those and upper
* relations.
*/
! Assert(foreignrel->reloptkind == RELOPT_JOINREL ||
! foreignrel->reloptkind == RELOPT_BASEREL ||
! foreignrel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
! foreignrel->reloptkind == RELOPT_UPPER_REL);
!
! /* For upper relations, scanrel must be either a joinrel or a baserel */
! Assert(foreignrel->reloptkind != RELOPT_UPPER_REL ||
! scanrel->reloptkind == RELOPT_JOINREL ||
! scanrel->reloptkind == RELOPT_BASEREL);
/* Fill portions of context common to upper, join and base relation */
context.buf = buf;
context.root = root;
! context.foreignrel = foreignrel;
! context.scanrel = scanrel;
context.params_list = params_list;
/*
* Construct SELECT list
*/
appendStringInfoString(buf, "SELECT ");
+ /*
+ * Note: non-NIL tlist might be supplied for a baserel; if the baserel is
+ * full-outer-joined to any other foreign table (or join), and has any
+ * remote conditions, then non-NIL tlist will be supplied for the baserel,
+ * to deparse it as a subquery (see deparseRangeTblRef). In that case,
+ * use deparseExplicitTargetList.
+ */
if (foreignrel->reloptkind == RELOPT_JOINREL ||
! foreignrel->reloptkind == RELOPT_UPPER_REL ||
! tlist != NIL)
{
! /* Use the input tlist */
! deparseExplicitTargetList(tlist, retrieved_attrs, &context);
}
else
{
***************
*** 1014,1050 **** deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
fpinfo->attrs_used, false, retrieved_attrs);
heap_close(rel, NoLock);
}
- }
! /*
! * Construct a FROM clause and, if needed, a WHERE clause, and append those to
! * "buf".
! *
! * quals is the list of clauses to be included in the WHERE clause.
! */
! static void
! deparseFromExpr(List *quals, deparse_expr_cxt *context)
! {
! StringInfo buf = context->buf;
! RelOptInfo *scanrel = context->scanrel;
! /* For upper relations, scanrel must be either a joinrel or a baserel */
! Assert(context->foreignrel->reloptkind != RELOPT_UPPER_REL ||
! scanrel->reloptkind == RELOPT_JOINREL ||
! scanrel->reloptkind == RELOPT_BASEREL);
! /* Construct FROM clause */
! appendStringInfoString(buf, " FROM ");
! deparseFromExprForRel(buf, context->root, scanrel,
! (bms_num_members(scanrel->relids) > 1),
! context->params_list);
! /* Construct WHERE clause */
if (quals != NIL)
{
appendStringInfo(buf, " WHERE ");
! appendConditions(quals, context);
}
}
/*
--- 973,1030 ----
fpinfo->attrs_used, false, retrieved_attrs);
heap_close(rel, NoLock);
}
! /*
! * Construct FROM clause
! */
! appendStringInfoString(buf, " FROM ");
! deparseFromExprForRel(buf, root, scanrel,
! (scanrel->reloptkind == RELOPT_JOINREL),
! params_list);
! /*
! * For upper relations, the WHERE clause is built from the remote
! * conditions of the underlying scan relation; otherwise, we can use the
! * supplied list of remote conditions directly.
! */
! if (foreignrel->reloptkind == RELOPT_UPPER_REL)
! {
! PgFdwRelationInfo *ofpinfo;
! ofpinfo = (PgFdwRelationInfo *) fpinfo->outerrel->fdw_private;
! quals = ofpinfo->remote_conds;
! }
! else
! quals = remote_conds;
! /*
! * Construct WHERE clause
! */
if (quals != NIL)
{
appendStringInfo(buf, " WHERE ");
! appendConditions(quals, &context);
! }
!
! if (foreignrel->reloptkind == RELOPT_UPPER_REL)
! {
! /* Append GROUP BY clause */
! appendGroupByClause(tlist, &context);
!
! /* Append HAVING clause */
! if (remote_conds)
! {
! appendStringInfo(buf, " HAVING ");
! appendConditions(remote_conds, &context);
! }
}
+
+ /* Add ORDER BY clause if we found any useful pathkeys */
+ if (pathkeys)
+ appendOrderByClause(pathkeys, &context);
+
+ /* Add any necessary FOR UPDATE/SHARE. */
+ deparseLockingClause(&context);
}
/*
***************
*** 1155,1165 **** deparseLockingClause(deparse_expr_cxt *context)
--- 1135,1154 ----
StringInfo buf = context->buf;
PlannerInfo *root = context->root;
RelOptInfo *rel = context->scanrel;
+ 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 the relation
+ * 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
***************
*** 1347,1364 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
if (foreignrel->reloptkind == RELOPT_JOINREL)
{
- RelOptInfo *rel_o = fpinfo->outerrel;
- RelOptInfo *rel_i = fpinfo->innerrel;
StringInfoData join_sql_o;
StringInfoData join_sql_i;
/* Deparse outer relation */
initStringInfo(&join_sql_o);
! deparseFromExprForRel(&join_sql_o, root, rel_o, true, params_list);
/* Deparse inner relation */
initStringInfo(&join_sql_i);
! deparseFromExprForRel(&join_sql_i, root, rel_i, true, params_list);
/*
* For a join relation FROM clause entry is deparsed as
--- 1336,1357 ----
if (foreignrel->reloptkind == RELOPT_JOINREL)
{
StringInfoData join_sql_o;
StringInfoData join_sql_i;
/* Deparse outer relation */
initStringInfo(&join_sql_o);
! deparseRangeTblRef(&join_sql_o, root,
! fpinfo->outerrel,
! fpinfo->make_outerrel_subquery,
! params_list);
/* Deparse inner relation */
initStringInfo(&join_sql_i);
! deparseRangeTblRef(&join_sql_i, root,
! fpinfo->innerrel,
! fpinfo->make_innerrel_subquery,
! params_list);
/*
* For a join relation FROM clause entry is deparsed as
***************
*** 1414,1419 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
--- 1407,1557 ----
}
/*
+ * 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(foreignrel->reloptkind == RELOPT_BASEREL ||
+ foreignrel->reloptkind == RELOPT_JOINREL);
+ Assert(fpinfo->local_conds == NIL);
+
+ if (make_subquery)
+ {
+ List *tlist;
+ List *retrieved_attrs;
+
+ tlist = make_tlist_from_pathtarget(foreignrel->reltarget);
+ Assert(tlist != NIL);
+ appendStringInfoChar(buf, '(');
+ deparseSelectStmtForRel(buf, root, foreignrel, tlist,
+ fpinfo->remote_conds, NIL,
+ &retrieved_attrs, params_list);
+ appendStringInfoChar(buf, ')');
+ appendSubselectAlias(buf, fpinfo->relation_index,
+ list_length(foreignrel->reltarget->exprs));
+ }
+ else
+ deparseFromExprForRel(buf, root, foreignrel, true, params_list);
+ }
+
+ /*
+ * Add a subselect alias to a subquery-in-FROM.
+ *
+ * The table alias and column aliases are appended to buf. The index for the
+ * table alias is tabno. The number of the column aliases added is ncols and
+ * the indexes for the column aliases are indexed 1..ncols.
+ */
+ static void
+ appendSubselectAlias(StringInfo buf, int tabno, int ncols)
+ {
+ int i;
+
+ /* Append the table alias */
+ appendStringInfo(buf, " %s%d", SS_TAB_ALIAS_PREFIX, tabno);
+
+ /* Append the column aliases */
+ appendStringInfoChar(buf, '(');
+ for (i = 1; i <= ncols; 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(Var *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(Var *node, RelOptInfo *foreignrel, int *tabno, int *colno)
+ {
+ 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;
+
+ if (bms_is_member(node->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(node->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
***************
*** 2057,2066 **** static void
--- 2195,2220 ----
deparseVar(Var *node, deparse_expr_cxt *context)
{
Relids relids = context->scanrel->relids;
+ RelOptInfo *rel = (context->foreignrel->reloptkind == RELOPT_UPPER_REL) ?
+ context->scanrel : context->foreignrel;
+ int tabno;
+ int colno;
/* Qualify columns when multiple relations are involved. */
bool qualify_col = (bms_num_members(relids) > 1);
+ /*
+ * If the given Var is an output column of a subquery-in-FROM, deparse
+ * the alias to the given Var instead.
+ */
+ if (isSubqueryExpr(node, rel, &tabno, &colno))
+ {
+ appendStringInfo(context->buf, "%s%d.%s%d",
+ SS_TAB_ALIAS_PREFIX, tabno,
+ SS_COL_ALIAS_PREFIX, colno);
+ return;
+ }
+
if (bms_is_member(node->varno, relids) && node->varlevelsup == 0)
deparseColumnRef(context->buf, node->varno, node->varattno,
context->root, qualify_col);
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 1217,1241 **** SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.
(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
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)
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
--- 1217,1232 ----
(10 rows)
-- full outer join with restrictions on the joining relations
+ -- a. the joining relations are both base 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
! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan
Output: ft4.c1, ft5.c1
! 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
***************
*** 1250,1281 **** SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL
| 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
! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
(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)
-- full outer join three tables
--- 1241,1295 ----
| 57
(8 rows)
+ -- b. one of the joining relations is a base relation and the other is a join
+ -- relation
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM ft4 t2 LEFT JOIN ft5 t3 ON (t2.c1 = t3.c1) WHERE (t2.c1 between 50 and 60)) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b;
+ QUERY PLAN
+ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft4.c1, t2.c1, t3.c1
+ Relations: (public.ft4) FULL JOIN ((public.ft4 t2) LEFT JOIN (public.ft5 t3))
+ Remote SQL: SELECT s4.c1, s8.c1, s8.c2 FROM ((SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s4(c1) FULL JOIN (SELECT r5.c1, r6.c1 FROM ("S 1"."T 3" r5 LEFT JOIN "S 1"."T 4" r6 ON (((r5.c1 = r6.c1)))) WHERE ((r5.c1 >= 50)) AND ((r5.c1 <= 60))) s8(c1, c2) ON (((s4.c1 = s8.c1)))) ORDER BY s4.c1 ASC NULLS LAST, s8.c1 ASC NULLS LAST, s8.c2 ASC NULLS LAST
+ (4 rows)
+
+ SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM ft4 t2 LEFT JOIN ft5 t3 ON (t2.c1 = t3.c1) WHERE (t2.c1 between 50 and 60)) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b;
+ c1 | a | b
+ ----+----+----
+ 50 | 50 |
+ 52 | 52 |
+ 54 | 54 | 54
+ 56 | 56 |
+ 58 | 58 |
+ 60 | 60 | 60
+ (6 rows)
+
-- full outer join + inner join
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
-> Foreign Scan
! Output: t1.*, t1.c1, t2.c1, t3.c1
Relations: ((public.ft4 t1) INNER JOIN (public.ft5 t2)) FULL JOIN (public.ft4 t3)
! Remote SQL: SELECT CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1.c1, r1.c2, r1.c3) END, 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
(6 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
***************
*** 3062,3067 **** select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) gr
--- 3076,3099 ----
| 9
(3 rows)
+ -- Aggregate over FULL join needing to deparse the joining relations as
+ -- subqueries.
+ explain (verbose, costs off)
+ select count(*), sum(t1.c1), avg(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);
+ QUERY PLAN
+ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: (count(*)), (sum(ft4.c1)), (avg(ft5.c1))
+ Relations: Aggregate on ((public.ft4) FULL JOIN (public.ft5))
+ Remote SQL: SELECT count(*), sum(s4.c1), avg(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))))
+ (4 rows)
+
+ select count(*), sum(t1.c1), avg(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);
+ count | sum | avg
+ -------+-----+---------------------
+ 8 | 330 | 55.5000000000000000
+ (1 row)
+
-- ORDER BY expression is part of the target list but not pushed down to
-- foreign server.
explain (verbose, costs off)
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 668,673 **** postgresGetForeignRelSize(PlannerInfo *root,
--- 668,681 ----
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;
}
/*
***************
*** 4147,4156 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
fpinfo->jointype = jointype;
/*
* 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.
*
* 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
--- 4155,4176 ----
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, so save the
+ * relids of those relations for later use of deparser.
+ */
+ fpinfo->make_outerrel_subquery = false;
+ fpinfo->make_innerrel_subquery = false;
+ Assert(bms_is_subset(fpinfo_i->subquery_rels, innerrel->relids));
+ Assert(bms_is_subset(fpinfo_o->subquery_rels, outerrel->relids));
+ 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.
*
* 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
***************
*** 4161,4168 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
*
* 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.
*
* The joining sides can not have local conditions, thus no need to test
* shippability of the clauses being pulled up.
--- 4181,4187 ----
*
* 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.
*
* The joining sides can not have local conditions, thus no need to test
* shippability of the clauses being pulled up.
***************
*** 4191,4198 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
break;
case JOIN_FULL:
! if (fpinfo_i->remote_conds || fpinfo_o->remote_conds)
! return false;
break;
default:
--- 4210,4236 ----
break;
case JOIN_FULL:
!
! /*
! * In this case, if any of the joining relations has conditions,
! * we need to deparse that relation as a subquery so that
! * conditions can be evaluated before the join. Remember it in
! * the fpinfo so that deparser can take appropriate action. We
! * also save the relids of that relation that is covered by the
! * subquery for later use of 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:
***************
*** 4273,4278 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4311,4326 ----
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.
+ */
+ Assert(fpinfo->relation_index == 0);
+ fpinfo->relation_index =
+ list_length(root->parse->rtable) + list_length(root->join_rel_list);
+
return true;
}
*** a/contrib/postgres_fdw/postgres_fdw.h
--- b/contrib/postgres_fdw/postgres_fdw.h
***************
*** 95,100 **** typedef struct PgFdwRelationInfo
--- 95,113 ----
/* Grouping information */
List *grouped_tlist;
+
+ /* 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 */
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 391,403 **** 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;
-- 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;
--- 391,409 ----
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
+ -- a. the joining relations are both base 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;
+ -- b. one of the joining relations is a base relation and the other is a join
+ -- relation
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM ft4 t2 LEFT JOIN ft5 t3 ON (t2.c1 = t3.c1) WHERE (t2.c1 between 50 and 60)) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b;
+ SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT t2.c1, t3.c1 FROM ft4 t2 LEFT JOIN ft5 t3 ON (t2.c1 = t3.c1) WHERE (t2.c1 between 50 and 60)) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b;
-- full outer join + inner join
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;
***************
*** 793,798 **** explain (verbose, costs off)
--- 799,810 ----
select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2;
select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) group by t2.c1 having (avg(t1.c1) is null and sum(t2.c1) < 10) or sum(t2.c1) is null order by 1 nulls last, 2;
+ -- Aggregate over FULL join needing to deparse the joining relations as
+ -- subqueries.
+ explain (verbose, costs off)
+ select count(*), sum(t1.c1), avg(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);
+ select count(*), sum(t1.c1), avg(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 expression is part of the target list but not pushed down to
-- foreign server.
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
