On 2016/11/16 18:14, Ashutosh Bapat wrote:
(1) You added the
following comments to deparseSelectSql:
+ /*
+ * For a non-base relation, use the input tlist. If a base relation
is
+ * being deparsed as a subquery, use input tlist, if the caller has
passed
+ * one. The column aliases of the subquery are crafted based on the
input
+ * tlist. If tlist is NIL, there will not be any expression
referring to
+ * any of the columns of the base relation being deparsed. Hence it
doesn't
+ * matter whether the base relation is being deparsed as subquery or
not.
+ */
The last sentence seems confusing to me. My understanding is: if a base
relation has tlist=NIL, then the base relation *must* be deparsed as
ordinary, not as a subquery. Maybe I'm missing something, though. (I left
that as-is, but I think we need to reword that to be more clear, at least.)
Hmm, I agree. I think the comment should just say, "Use tlist to
create the SELECT clause if one has been provided. For a base relation
with tlist = NIL, check attrs_needed information.". Does that sound
good?
I don't think that is 100% correct, because (1) tlist can be NIL for a
join relation, you pointed out upthread, but we need to use
deparseExplicitTargetList, so the first sentence is not completely
correct, and (2) we need to check attrs_needed information not only for
a baserel but for an otherrel, so the second sentence is not completely
correct, either. How about this, instead?:
/*
* For a join relation or an upper relation, use
deparseExplicitTargetList.
* Likewise, for a base relation that is being deparsed as a
subquery, in
* which case the caller would have passed tlist that is non-NIL,
use that
* function. Otherwise, use deparseTargetList.
*/
(3) I don't think we need this in isSubqueryExpr, so I removed it from the
patch:
+ /* Keep compiler happy. */
+ return false;
Doesn't that cause compiler warning, saying "non-void function
returning nothing" or something like that. Actually, the "if
(bms_is_member(node->varno, outerrel->relids))" ends with a "return"
always. Hence we don't need to encapsulate the code in "else" block in
else { }. It could be taken outside.
Yeah, I think so too, but I like the "if () { } else { }" coding. That
coding can be found in other places in core, eg,
operator_same_subexprs_lookup.
OK, I changed isSubqueryExpr to is_subquery_expr; I kept to refer to expr
because I think we would soon extend that function so that it can handle
PHVs, as I said upthread. For getSubselectAliasInfo, I changed the name to
get_subselect_alias_id, because (1) the word "alias" seems general and (2)
the "for_var" part would make the name a bit long.
is_subquery_expr(Var *node -- that looks odd. Either it should
is_subquery_var(Var * ... OR it should be is_subquery_expr(Expr * ...
. I would prefer the first one, since that's what current patch is
doing. When we introduce PHVs, we may change it, if required.
OK, I used is_subquery_var().
Done. I modified the patch as proposed; create the tlist by
build_tlist_to_deparse in foreign_join_ok, if needed, and search the tlist
by tlist_member. I also added a new member "tlist" to PgFdwRelationInfo to
save the tlist created in foreign_join_ok.
Instead of adding a new member, you might want to reuse grouped_tlist
by renaming it.
Done.
Another idea on the "tlist" member would be to save a tlist created for
EXPLAIN into that member in estimate_patch_cost_size, so that we don't need
to generate the tlist again in postgresGetForeignPlan, when
use_remote_estimate=true. But I'd like to leave that for another patch.
I think that will happen automatically, while deparsing, whether for
EXPLAIN or for actual execution.
Really? Anyway, I'd like to leave that as-is.
Please find attached a new version of the patch.
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
***************
*** 167,172 **** static void appendConditions(List *exprs, deparse_expr_cxt *context);
--- 169,180 ----
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 deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
+ bool make_subquery, List **params_list);
+ static void appendSubselectAlias(StringInfo buf, int tabno, int ncols);
+ static void get_subselect_alias_id(Var *node, RelOptInfo *foreignrel,
+ int *tabno, int *colno);
+ static bool is_subquery_var(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,
***************
*** 861,867 **** build_tlist_to_deparse(RelOptInfo *foreignrel)
* checking shippability, so just return that.
*/
if (foreignrel->reloptkind == RELOPT_UPPER_REL)
! return fpinfo->grouped_tlist;
/*
* We require columns specified in foreignrel->reltarget->exprs and those
--- 869,875 ----
* checking shippability, so just return that.
*/
if (foreignrel->reloptkind == RELOPT_UPPER_REL)
! return fpinfo->tlist;
/*
* We require columns specified in foreignrel->reltarget->exprs and those
***************
*** 990,1001 **** deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
*/
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
{
/*
--- 998,1013 ----
*/
appendStringInfoString(buf, "SELECT ");
+ /*
+ * For a join relation or an upper relation, use deparseExplicitTargetList.
+ * Likewise, for a base relation that is being deparsed as a subquery, in
+ * which case the caller would have passed tlist that is non-NIL, use that
+ * function. Otherwise, use deparseTargetList.
+ */
if (foreignrel->reloptkind == RELOPT_JOINREL ||
! foreignrel->reloptkind == RELOPT_UPPER_REL ||
! tlist != NIL)
deparseExplicitTargetList(tlist, retrieved_attrs, context);
else
{
/*
***************
*** 1155,1165 **** deparseLockingClause(deparse_expr_cxt *context)
--- 1167,1186 ----
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
--- 1368,1385 ----
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,
--- 1435,1578 ----
}
/*
+ * Append FROM clause entry for the given relation to buf.
+ *
+ * If make_subquery is true, deparse the relation as a subquery. Otherwise,
+ * deparse it as ordinary.
+ */
+ 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 *retrieved_attrs;
+
+ /* Append the subquery representing the given relation. */
+ appendStringInfoChar(buf, '(');
+ deparseSelectStmtForRel(buf, root, foreignrel, fpinfo->tlist,
+ fpinfo->remote_conds, NIL,
+ &retrieved_attrs, params_list);
+ appendStringInfoChar(buf, ')');
+
+ /*
+ * Append the relation and column aliases, so that it becomes easy to
+ * refer to this relation in the rest of the query.
+ */
+ appendSubselectAlias(buf, fpinfo->relation_index,
+ list_length(fpinfo->tlist));
+ }
+ else
+ deparseFromExprForRel(buf, root, foreignrel, true, params_list);
+ }
+
+ /*
+ * Append the relation and column aliases to the subquery.
+ *
+ * 'tabno' is an integer which uniquely identifies the subquery.
+ * 'ncols' is the number of the column aliases to add.
+ */
+ 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 the relation and column alias for a given Var node, which belongs to
+ * input foreignrel. They are returned in *tabno and *colno respectively.
+ */
+ static void
+ get_subselect_alias_id(Var *node, RelOptInfo *foreignrel,
+ int *tabno, int *colno)
+ {
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ TargetEntry *tle;
+
+ /* Get the table number */
+ *tabno = fpinfo->relation_index;
+
+ /* Get the column number */
+ tle = tlist_member((Node *) node, fpinfo->tlist);
+ if (!tle)
+ elog(ERROR, "unexpected expression in subquery output");
+ *colno = tle->resno;
+ }
+
+ /*
+ * Returns true if a given Var node belongs to a relation being deparsed as a
+ * subquery. Returns false otherwise. When returning true, it sets tabno and
+ * colno to unique indexes identifying the relation and column resp. to which
+ * the given Var will be deparsed.
+ */
+ static bool
+ is_subquery_var(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 (!bms_is_member(node->varno, fpinfo->subquery_rels))
+ return false;
+
+ if (bms_is_member(node->varno, outerrel->relids))
+ {
+ /*
+ * If outer relation is deparsed as a subquery, get the identifiers for
+ * the relation and column corresponding to the given Var node.
+ */
+ if (fpinfo->make_outerrel_subquery)
+ {
+ get_subselect_alias_id(node, outerrel, tabno, colno);
+ return true;
+ }
+
+ /* Otherwise, recurse into the outer relation. */
+ return is_subquery_var(node, outerrel, tabno, colno);
+ }
+ else
+ {
+ Assert(bms_is_member(node->varno, innerrel->relids));
+
+ /*
+ * If inner relation is deparsed as a subquery, get the identifiers for
+ * the relation and column corresponding to the given Var node.
+ */
+ if (fpinfo->make_innerrel_subquery)
+ {
+ get_subselect_alias_id(node, innerrel, tabno, colno);
+ return true;
+ }
+
+ /* Otherwise, recurse into the inner relation. */
+ return is_subquery_var(node, innerrel, tabno, colno);
+ }
+ }
+
+ /*
* deparse remote INSERT statement
*
* The statement text is appended to buf, and we also create an integer List
***************
*** 2057,2066 **** static void
--- 2216,2242 ----
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 belongs to a relation deparsed as a subquery, use the
+ * relation and column alias provided by the subquery, instead of the
+ * actual column name.
+ */
+ if (is_subquery_var(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,1255 **** SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL
--- 1241,1293 ----
| 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)
+
+ -- c. test deparsing the remote query as nested subqueries
+ 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 (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) ss(a, b) ON (t1.c1 = ss.a) ORDER BY t1.c1, ss.a, ss.b;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft4.c1, ft4_1.c1, ft5.c1
+ Relations: (public.ft4) FULL JOIN ((public.ft4) FULL JOIN (public.ft5))
+ Remote SQL: SELECT s4.c1, s10.c1, s10.c2 FROM ((SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s4(c1) FULL JOIN (SELECT s8.c1, s9.c1 FROM ((SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s8(c1) FULL JOIN (SELECT c1 FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s9(c1) ON (((s8.c1 = s9.c1)))) WHERE (((s8.c1 IS NULL) OR (s8.c1 IS NOT NULL)))) s10(c1, c2) ON (((s4.c1 = s10.c1)))) ORDER BY s4.c1 ASC NULLS LAST, s10.c1 ASC NULLS LAST, s10.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 (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) 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
+ | | 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;
***************
*** 3062,3067 **** select avg(t1.c1), sum(t2.c1) from ft4 t1 full join ft5 t2 on (t1.c1 = t2.c1) gr
--- 3100,3123 ----
| 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));
+
+ /* No outer and inner relation for a base relation. */
+ fpinfo->make_outerrel_subquery = false;
+ fpinfo->make_innerrel_subquery = false;
+ fpinfo->subquery_rels = NULL;
+
+ /* Set the relation index */
+ fpinfo->relation_index = baserel->relid;
}
/*
***************
*** 2708,2714 **** estimate_path_cost_size(PlannerInfo *root,
MemSet(&aggcosts, 0, sizeof(AggClauseCosts));
if (root->parse->hasAggs)
{
! get_agg_clause_costs(root, (Node *) fpinfo->grouped_tlist,
AGGSPLIT_SIMPLE, &aggcosts);
get_agg_clause_costs(root, (Node *) root->parse->havingQual,
AGGSPLIT_SIMPLE, &aggcosts);
--- 2716,2722 ----
MemSet(&aggcosts, 0, sizeof(AggClauseCosts));
if (root->parse->hasAggs)
{
! get_agg_clause_costs(root, (Node *) fpinfo->tlist,
AGGSPLIT_SIMPLE, &aggcosts);
get_agg_clause_costs(root, (Node *) root->parse->havingQual,
AGGSPLIT_SIMPLE, &aggcosts);
***************
*** 2718,2724 **** estimate_path_cost_size(PlannerInfo *root,
numGroupCols = list_length(root->parse->groupClause);
numGroups = estimate_num_groups(root,
get_sortgrouplist_exprs(root->parse->groupClause,
! fpinfo->grouped_tlist),
input_rows, NULL);
/*
--- 2726,2732 ----
numGroupCols = list_length(root->parse->groupClause);
numGroups = estimate_num_groups(root,
get_sortgrouplist_exprs(root->parse->groupClause,
! fpinfo->tlist),
input_rows, NULL);
/*
***************
*** 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 by the 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,4247 ----
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 the deparser can take appropriate action.
! * We also save the relids of the base relations covered by the
! * joining relation. We also create the tlist for the subquery.
! *
! * Note: the tlist would have one-to-one correspondence with the
! * joining relation's reltarget->exprs because (1) the above test
! * on PHVs guarantees that the reltarget->exprs doesn't contain
! * any PHVs and (2) the joining relation's local_conds is NIL.
! * This allows us to search the targetlist entry matching a given
! * Var node from the tlist in get_subselect_alias_id.
! */
! if (fpinfo_o->remote_conds)
! {
! fpinfo->make_outerrel_subquery = true;
! fpinfo->subquery_rels = bms_add_members(fpinfo->subquery_rels,
! outerrel->relids);
! fpinfo_o->tlist = build_tlist_to_deparse(outerrel);
! Assert(fpinfo_o->tlist != NIL);
! }
! if (fpinfo_i->remote_conds)
! {
! fpinfo->make_innerrel_subquery = true;
! fpinfo->subquery_rels = bms_add_members(fpinfo->subquery_rels,
! innerrel->relids);
! fpinfo_i->tlist = build_tlist_to_deparse(innerrel);
! Assert(fpinfo_i->tlist != NIL);
! }
break;
default:
***************
*** 4273,4278 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4322,4337 ----
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;
}
***************
*** 4613,4619 **** foreign_grouping_ok(PlannerInfo *root, RelOptInfo *grouped_rel)
apply_pathtarget_labeling_to_tlist(tlist, grouping_target);
/* Store generated targetlist */
! fpinfo->grouped_tlist = tlist;
/* Safe to pushdown */
fpinfo->pushdown_safe = true;
--- 4672,4678 ----
apply_pathtarget_labeling_to_tlist(tlist, grouping_target);
/* Store generated targetlist */
! fpinfo->tlist = tlist;
/* Safe to pushdown */
fpinfo->pushdown_safe = true;
*** a/contrib/postgres_fdw/postgres_fdw.h
--- b/contrib/postgres_fdw/postgres_fdw.h
***************
*** 51,56 **** typedef struct PgFdwRelationInfo
--- 51,59 ----
/* Bitmap of attr numbers we need to fetch from the remote server. */
Bitmapset *attrs_used;
+ /* Optional tlist describing the contents of the scan tuple */
+ List *tlist;
+
/* Cost and selectivity of local_conds. */
QualCost local_conds_cost;
Selectivity local_conds_sel;
***************
*** 93,100 **** typedef struct PgFdwRelationInfo
JoinType jointype;
List *joinclauses;
! /* Grouping information */
! List *grouped_tlist;
} PgFdwRelationInfo;
/* in postgres_fdw.c */
--- 96,113 ----
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 */
!
! /*
! * Unique integer identifying this 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,399 **** EXPLAIN (VERBOSE, COSTS OFF)
--- 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;
+ -- c. test deparsing the remote query as nested subqueries
+ 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 (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) 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 (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t2 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t3 ON (t2.c1 = t3.c1) WHERE t2.c1 IS NULL OR t2.c1 IS NOT NULL) 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.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;
***************
*** 793,798 **** explain (verbose, costs off)
--- 803,814 ----
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
