On 2016/12/07 20:23, Etsuro Fujita wrote:
On 2016/12/07 15:39, Ashutosh Bapat wrote:
On 2016/11/22 18:28, Ashutosh Bapat wrote:
I guess, the reason why you are doing it this way, is SELECT clause for
the
outermost query gets deparsed before FROM clause. For later we call
deparseRangeTblRef(), which builds the tlist. So, while deparsing
SELECT
clause, we do not have tlist to build from. In that case, I guess,
we have
to
build the tlist in get_subselect_alias_id() if it's not available and
stick it
in fpinfo. Subsequent calls to get_subselect_alias_id() should find
tlist
there. Then in deparseRangeTblRef() assert that there's a tlist in
fpinfo
and use it to build the SELECT clause of subquery. That way, we don't
build
tlist unless it's needed and also use the same tlist for all searches.
Please
use tlist_member() to search into the tlist.
I wrote:
This would probably work, but seems to me a bit complicated.
Instead, I'd
like to propose that we build the tlist for each relation being
deparsed as
a subquery in a given join tree, right before deparsing the SELECT
clause in
deparseSelectStmtForRel, if is_subquery is false and lower_subquery_rels
isn't NULL, and store the tlist into the relation's fpinfo. That would
allow us to build the tlist only when we need it, and to use
tlist_member
for the exact comparison. I think it would be much easier to implement
that.
IIRC, this is inline with my original proposal of creating tlists
before deparsing anything. Along-with that I also proposed to bubble
this array of tlist up the join hierarchy to avoid recursion [1] point
#15, further elaborated in [2]
[1]
https://www.postgresql.org/message-id/ad449b25-66ee-4c06-568c-0eb2e1bef9f9%40lab.ntt.co.jp
[2]
https://www.postgresql.org/message-id/CAFjFpRcn7%3DDNOXm-PJ_jVDqAmghKVf6tApQC%2B_RgMZ8tOPExcA%40mail.gmail.com
My proposal here would be a bit different from what you proposed; right
before deparseSelectSql in deparseSelectStmtForRel, build the tlists for
relations present in the given jointree that will be deparsed as
subqueries, by (1) traversing the given jointree and (2) applying
build_tlist_to_deparse to each relation to be deparsed as a subquery and
saving the result in that relation's fpinfo. I think that would be
implemented easily, and the overhead would be small.
I implemented that to address your concern:
* deparseRangeTblRef uses the tlist created as above, to build the
SELECT clause of the subquery. (You said "Then in deparseRangeTblRef()
assert that there's a tlist in fpinfo", but the tlist may be empty, so I
didn't add any assertion to that function.)
* get_relation_column_alias_ids uses tlist_member with the tlist.
I also addressed the comments #1, #2 and #3 in [1]. For #1, I added
"LIMIT 10" to the query. Attached is the new version of the patch.
Other changes:
* As discussed before, renamed grouped_tlist in fpinfo to "tlist" and
used it to store the tlist created as above.
* Also, renamed SS_REL_ALIAS_PREFIX to SUBQUERY_REL_ALIAS_PREFIX
(Likewise for SS_COL_ALIAS_PREFIX).
* Relocated some functions.
* Revised comments a little bit.
Best regards,
Etsuro Fujita
[1]
https://www.postgresql.org/message-id/CAFjFpRfU4-gxqZ8ahoKM1ZtDJEThe3K8Lb_6beRKa8mmP%3Dv%3DfA%40mail.gmail.com
*** 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 SUBQUERY_REL_ALIAS_PREFIX "s"
+ #define SUBQUERY_COL_ALIAS_PREFIX "c"
/*
* Functions to determine whether an expression can be evaluated safely on
***************
*** 159,165 **** 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);
--- 161,167 ----
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
! static void deparseSelectSql(List *tlist, bool is_subquery, List **retrieved_attrs,
deparse_expr_cxt *context);
static void deparseLockingClause(deparse_expr_cxt *context);
static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context);
***************
*** 167,172 **** static void appendConditions(List *exprs, deparse_expr_cxt *context);
--- 169,177 ----
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, List **params_list);
+ static void appendSubqueryAlias(StringInfo buf, int relno, int ncols);
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,
***************
*** 175,180 **** static void appendFunctionName(Oid funcid, deparse_expr_cxt *context);
--- 180,194 ----
static Node *deparseSortGroupClause(Index ref, List *tlist,
deparse_expr_cxt *context);
+ /*
+ * Helper functions
+ */
+ static void build_subquery_rel_tlists(RelOptInfo *foreignrel);
+ static bool is_subquery_var(Var *node, RelOptInfo *foreignrel,
+ int *relno, int *colno);
+ static void get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
+ int *relno, int *colno);
+
/*
* Examine each qual clause in input_conds, and classify them into two groups,
***************
*** 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
--- 875,881 ----
* 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
***************
*** 881,888 **** build_tlist_to_deparse(RelOptInfo *foreignrel)
* Deparse SELECT statement for given relation into buf.
*
* tlist contains the list of desired columns to be fetched from foreign server.
! * For a base relation fpinfo->attrs_used is used to construct SELECT clause,
! * hence the tlist is ignored for a base relation.
*
* remote_conds is the list of conditions to be deparsed into the WHERE clause
* (or, in the case of upper relations, into the HAVING clause).
--- 895,905 ----
* Deparse SELECT statement for given relation into buf.
*
* tlist contains the list of desired columns to be fetched from foreign server.
! * We use the tlist to construct the SELECT clause, if the relation is a join
! * or upper relation or if is_subquery is true. (In the latter case the
! * relation is deparsed as a subquery.) Otherwise, we use the relation's
! * fpinfo->attrs_used to do that, in which case the tlist is ignored for the
! * relation.
*
* remote_conds is the list of conditions to be deparsed into the WHERE clause
* (or, in the case of upper relations, into the HAVING clause).
***************
*** 901,907 **** build_tlist_to_deparse(RelOptInfo *foreignrel)
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;
--- 918,925 ----
extern void
deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
List *tlist, List *remote_conds, List *pathkeys,
! bool is_subquery, List **retrieved_attrs,
! List **params_list)
{
deparse_expr_cxt context;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
***************
*** 924,931 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *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
--- 942,956 ----
fpinfo->outerrel : rel;
context.params_list = params_list;
+ /*
+ * If we are creating a whole query, build the tlists for relations
+ * in the jointree that will be deparsed as subqueries.
+ */
+ if (!is_subquery)
+ build_subquery_rel_tlists(context.scanrel);
+
/* Construct SELECT clause */
! deparseSelectSql(tlist, is_subquery, retrieved_attrs, &context);
/*
* For upper relations, the WHERE clause is built from the remote
***************
*** 974,984 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
* 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;
--- 999,1011 ----
* We also create an integer List of the columns being retrieved, which is
* returned to *retrieved_attrs.
*
! * tlist is the list of desired columns. is_subquery is a flag to indicate
! * whether to deparse the relation as a subquery. Read prologue of
* deparseSelectStmtForRel() for details.
*/
static void
! deparseSelectSql(List *tlist, bool is_subquery, List **retrieved_attrs,
! deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
RelOptInfo *foreignrel = context->foreignrel;
***************
*** 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
{
/*
--- 1017,1031 ----
*/
appendStringInfoString(buf, "SELECT ");
+ /*
+ * For a join relation or an upper relation, use deparseExplicitTargetList.
+ * Likewise, for a relation that is being deparsed as a subquery, use that
+ * function. Otherwise, use deparseTargetList.
+ */
if (foreignrel->reloptkind == RELOPT_JOINREL ||
! foreignrel->reloptkind == RELOPT_UPPER_REL ||
! is_subquery)
deparseExplicitTargetList(tlist, retrieved_attrs, context);
else
{
/*
***************
*** 1155,1165 **** deparseLockingClause(deparse_expr_cxt *context)
--- 1185,1203 ----
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. Locking clause
+ * for such a relation, if needed, is included in the subquery.
+ */
+ if (bms_is_member(relid, fpinfo->lower_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
--- 1385,1400 ----
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, params_list);
/* Deparse inner relation */
initStringInfo(&join_sql_i);
! deparseRangeTblRef(&join_sql_i, root, fpinfo->innerrel, params_list);
/*
* For a join relation FROM clause entry is deparsed as
***************
*** 1414,1419 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
--- 1450,1517 ----
}
/*
+ * Append FROM clause entry for the given relation to buf.
+ */
+ static void
+ deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
+ List **params_list)
+ {
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+
+ Assert(foreignrel->reloptkind == RELOPT_BASEREL ||
+ foreignrel->reloptkind == RELOPT_JOINREL);
+ Assert(fpinfo->local_conds == NIL);
+
+ /* If is_subquery_rel is true, deparse the relation as a subquery. */
+ if (fpinfo->is_subquery_rel)
+ {
+ List *tlist = fpinfo->tlist;
+ List *retrieved_attrs;
+
+ /* Deparse the subquery representing the relation. */
+ appendStringInfoChar(buf, '(');
+ deparseSelectStmtForRel(buf, root, foreignrel, tlist,
+ fpinfo->remote_conds, NIL, true,
+ &retrieved_attrs, params_list);
+ appendStringInfoChar(buf, ')');
+
+ /* Append the alias to the subquery. */
+ appendSubqueryAlias(buf, fpinfo->relation_index, list_length(tlist));
+ }
+ else
+ deparseFromExprForRel(buf, root, foreignrel, true, params_list);
+ }
+
+ /*
+ * Append the relation and column aliases to a subquery.
+ *
+ * 'relno' is the relation alias ID.
+ * 'ncols' is the number of the column aliases to add.
+ */
+ static void
+ appendSubqueryAlias(StringInfo buf, int relno, int ncols)
+ {
+ int i;
+
+ /* Append the relation alias */
+ appendStringInfo(buf, " %s%d", SUBQUERY_REL_ALIAS_PREFIX, relno);
+
+ /* Append the column aliases, if needed */
+ if (ncols > 0)
+ {
+ appendStringInfoChar(buf, '(');
+ for (i = 1; i <= ncols; i++)
+ {
+ if (i > 1)
+ appendStringInfoString(buf, ", ");
+
+ appendStringInfo(buf, "%s%d", SUBQUERY_COL_ALIAS_PREFIX, i);
+ }
+ appendStringInfoChar(buf, ')');
+ }
+ }
+
+ /*
* deparse remote INSERT statement
*
* The statement text is appended to buf, and we also create an integer List
***************
*** 2057,2066 **** static void
--- 2155,2179 ----
deparseVar(Var *node, deparse_expr_cxt *context)
{
Relids relids = context->scanrel->relids;
+ int relno;
+ int colno;
/* Qualify columns when multiple relations are involved. */
bool qualify_col = (bms_num_members(relids) > 1);
+ /*
+ * If the given Var belongs to a foreign relation deparsed as a subquery,
+ * use the relation and column alias provided by the subquery, instead of
+ * the remote name.
+ */
+ if (is_subquery_var(node, context->scanrel, &relno, &colno))
+ {
+ appendStringInfo(context->buf, "%s%d.%s%d",
+ SUBQUERY_REL_ALIAS_PREFIX, relno,
+ SUBQUERY_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);
***************
*** 2938,2940 **** deparseSortGroupClause(Index ref, List *tlist, deparse_expr_cxt *context)
--- 3051,3173 ----
return (Node *) expr;
}
+
+
+ /*
+ * Build the tlists for relations in a given jointree that will be deparsed as
+ * subqueries, if any.
+ */
+ static void
+ build_subquery_rel_tlists(RelOptInfo *foreignrel)
+ {
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ RelOptInfo *outerrel = fpinfo->outerrel;
+ RelOptInfo *innerrel = fpinfo->innerrel;
+
+ if (foreignrel->reloptkind != RELOPT_JOINREL)
+ return;
+ if (fpinfo->lower_subquery_rels == NULL)
+ return;
+
+ /*
+ * If the outer relation is deparsed as a subquery, build the tlist for
+ * the relation and save it into the relation's fpinfo.
+ */
+ if (bms_is_subset(outerrel->relids, fpinfo->lower_subquery_rels))
+ {
+ PgFdwRelationInfo *fpinfo2 = (PgFdwRelationInfo *) outerrel->fdw_private;
+
+ if (fpinfo2->is_subquery_rel)
+ fpinfo2->tlist = build_tlist_to_deparse(outerrel);
+ }
+ /* Likewise for the inner relation. */
+ if (bms_is_subset(innerrel->relids, fpinfo->lower_subquery_rels))
+ {
+ PgFdwRelationInfo *fpinfo2 = (PgFdwRelationInfo *) innerrel->fdw_private;
+
+ if (fpinfo2->is_subquery_rel)
+ fpinfo2->tlist = build_tlist_to_deparse(innerrel);
+ }
+
+ /* Recurse into the outer relation. */
+ build_subquery_rel_tlists(outerrel);
+ /* Likewise for the inner relation. */
+ build_subquery_rel_tlists(innerrel);
+ }
+
+ /*
+ * Returns true if the Var belongs to a foreign relation deparsed as a subquery.
+ * Returns false otherwise. When returning true, this function sets *relno and
+ * *colno to the IDs for the relation and column alias provided by the subquery,
+ * respectively.
+ */
+ static bool
+ is_subquery_var(Var *node, RelOptInfo *foreignrel, int *relno, 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->lower_subquery_rels))
+ return false;
+
+ if (bms_is_member(node->varno, outerrel->relids))
+ {
+ PgFdwRelationInfo *fpinfo2 = (PgFdwRelationInfo *) outerrel->fdw_private;
+
+ /*
+ * If the outer relation is deparsed as a subquery, get the IDs for the
+ * relation and column alias to the Var.
+ */
+ if (fpinfo2->is_subquery_rel)
+ {
+ get_relation_column_alias_ids(node, outerrel, relno, colno);
+ return true;
+ }
+
+ /* Otherwise, recurse into the outer relation. */
+ return is_subquery_var(node, outerrel, relno, colno);
+ }
+ else
+ {
+ PgFdwRelationInfo *fpinfo2 = (PgFdwRelationInfo *) innerrel->fdw_private;
+
+ Assert(bms_is_member(node->varno, innerrel->relids));
+
+ /*
+ * If the inner relation is deparsed as a subquery, get the IDs for the
+ * relation and column alias to the Var.
+ */
+ if (fpinfo2->is_subquery_rel)
+ {
+ get_relation_column_alias_ids(node, innerrel, relno, colno);
+ return true;
+ }
+
+ /* Otherwise, recurse into the inner relation. */
+ return is_subquery_var(node, innerrel, relno, colno);
+ }
+ }
+
+ /*
+ * Get the IDs for the relation and column alias to the Var belonging to the
+ * given foreignrel. They are returned into *relno and *colno, respectively.
+ */
+ static void
+ get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
+ int *relno, int *colno)
+ {
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ TargetEntry *tle;
+
+ /* Get the relation alias ID */
+ *relno = fpinfo->relation_index;
+
+ /* Get the column alias ID */
+ tle = tlist_member((Node *) node, fpinfo->tlist);
+ if (tle == NULL)
+ elog(ERROR, "unexpected expression in subquery output");
+ *colno = tle->resno;
+ }
*** 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,1365 ----
| 57
(8 rows)
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT 1 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 (TRUE) OFFSET 10 LIMIT 10;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: 1
+ -> Foreign Scan
+ Output: 1
+ Relations: (public.ft4) FULL JOIN (public.ft5)
+ Remote SQL: SELECT NULL FROM ((SELECT NULL FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s4 FULL JOIN (SELECT NULL FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s5 ON (TRUE))
+ (6 rows)
+
+ SELECT 1 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 (TRUE) OFFSET 10 LIMIT 10;
+ ?column?
+ ----------
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ (10 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)
+
+ -- d. test deparsing rowmarked relations as subqueries
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1 INNER 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 (TRUE) ORDER BY t1.c1, ss.a, ss.b FOR UPDATE OF t1;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ Output: "T 3".c1, ft4.c1, ft5.c1, "T 3".ctid, ft4.*, ft5.*
+ -> Nested Loop
+ Output: "T 3".c1, ft4.c1, ft5.c1, "T 3".ctid, ft4.*, ft5.*
+ -> Foreign Scan
+ Output: ft4.c1, ft4.*, ft5.c1, ft5.*
+ Relations: (public.ft4) FULL JOIN (public.ft5)
+ Remote SQL: SELECT s8.c1, s8.c2, s9.c1, s9.c2 FROM ((SELECT c1, ROW(c1, c2, c3) FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s8(c1, c2) FULL JOIN (SELECT c1, ROW(c1, c2, c3) FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))) s9(c1, c2) ON (((s8.c1 = s9.c1)))) WHERE (((s8.c1 IS NULL) OR (s8.c1 IS NOT NULL))) ORDER BY s8.c1 ASC NULLS LAST, s9.c1 ASC NULLS LAST
+ -> Hash Full Join
+ Output: ft4.c1, ft4.*, ft5.c1, ft5.*
+ Hash Cond: (ft4.c1 = ft5.c1)
+ Filter: ((ft4.c1 IS NULL) OR (ft4.c1 IS NOT NULL))
+ -> Foreign Scan on public.ft4
+ Output: ft4.c1, ft4.*
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))
+ -> Hash
+ Output: ft5.c1, ft5.*
+ -> Foreign Scan on public.ft5
+ Output: ft5.c1, ft5.*
+ Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))
+ -> Materialize
+ Output: "T 3".c1, "T 3".ctid
+ -> Seq Scan on "S 1"."T 3"
+ Output: "T 3".c1, "T 3".ctid
+ Filter: ("T 3".c1 = 50)
+ (25 rows)
+
+ SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1 INNER 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 (TRUE) ORDER BY t1.c1, ss.a, ss.b FOR UPDATE OF t1;
+ c1 | a | b
+ ----+----+----
+ 50 | 50 |
+ 50 | 52 |
+ 50 | 54 | 54
+ 50 | 56 |
+ 50 | 58 |
+ 50 | 60 | 60
+ 50 | | 51
+ 50 | | 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
--- 3172,3195 ----
| 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,682 ----
if (*refname && strcmp(refname, relname) != 0)
appendStringInfo(fpinfo->relation_name, " %s",
quote_identifier(rte->eref->aliasname));
+
+ /* Initialize info about whether to deparse the relation as a subquery. */
+ fpinfo->is_subquery_rel = false;
+ /* Initialize info about lower subqueries. */
+ fpinfo->lower_subquery_rels = NULL;
+ /* Set the relation index. */
+ fpinfo->relation_index = baserel->relid;
+ /* Initialize the tlist. */
+ fpinfo->tlist = NIL;
}
/*
***************
*** 1239,1245 **** postgresGetForeignPlan(PlannerInfo *root,
initStringInfo(&sql);
deparseSelectStmtForRel(&sql, root, foreignrel, fdw_scan_tlist,
remote_conds, best_path->path.pathkeys,
! &retrieved_attrs, ¶ms_list);
/*
* Build the fdw_private list that will be available to the executor.
--- 1248,1254 ----
initStringInfo(&sql);
deparseSelectStmtForRel(&sql, root, foreignrel, fdw_scan_tlist,
remote_conds, best_path->path.pathkeys,
! false, &retrieved_attrs, ¶ms_list);
/*
* Build the fdw_private list that will be available to the executor.
***************
*** 2551,2558 **** estimate_path_cost_size(PlannerInfo *root,
initStringInfo(&sql);
appendStringInfoString(&sql, "EXPLAIN ");
deparseSelectStmtForRel(&sql, root, foreignrel, fdw_scan_tlist,
! remote_conds, pathkeys, &retrieved_attrs,
! NULL);
/* Get the remote estimate */
conn = GetConnection(fpinfo->user, false);
--- 2560,2567 ----
initStringInfo(&sql);
appendStringInfoString(&sql, "EXPLAIN ");
deparseSelectStmtForRel(&sql, root, foreignrel, fdw_scan_tlist,
! remote_conds, pathkeys, false,
! &retrieved_attrs, NULL);
/* Get the remote estimate */
conn = GetConnection(fpinfo->user, false);
***************
*** 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);
--- 2717,2723 ----
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);
/*
--- 2727,2733 ----
numGroupCols = list_length(root->parse->groupClause);
numGroups = estimate_num_groups(root,
get_sortgrouplist_exprs(root->parse->groupClause,
! fpinfo->tlist),
input_rows, NULL);
/*
***************
*** 4146,4156 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
fpinfo->innerrel = innerrel;
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,4178 ----
fpinfo->innerrel = innerrel;
fpinfo->jointype = jointype;
+ /* Initialize info about whether to deparse the relation as a subquery. */
+ fpinfo->is_subquery_rel = false;
+
+ /*
+ * 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.
+ */
+ Assert(bms_is_subset(fpinfo_o->lower_subquery_rels, outerrel->relids));
+ Assert(bms_is_subset(fpinfo_i->lower_subquery_rels, innerrel->relids));
+ fpinfo->lower_subquery_rels = bms_union(fpinfo_o->lower_subquery_rels,
+ fpinfo_i->lower_subquery_rels);
+
/*
* 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.
--- 4183,4189 ----
*
* 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:
--- 4212,4241 ----
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 the
! * conditions can be evaluated before the join. Remember it in
! * the fpinfo of the joining relation so that the deparser can
! * take appropriate action. Also, save the relids of base
! * relations covered by the joining relation into the fpinfo of
! * this relation.
! */
! if (fpinfo_o->remote_conds)
! {
! fpinfo_o->is_subquery_rel = true;
! fpinfo->lower_subquery_rels =
! bms_add_members(fpinfo->lower_subquery_rels,
! outerrel->relids);
! }
! if (fpinfo_i->remote_conds)
! {
! fpinfo_i->is_subquery_rel = true;
! fpinfo->lower_subquery_rels =
! bms_add_members(fpinfo->lower_subquery_rels,
! innerrel->relids);
! }
break;
default:
***************
*** 4273,4278 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4316,4334 ----
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 join_rel_list 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);
+
+ /* Initialize the tlist. */
+ fpinfo->tlist = NIL;
+
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;
--- 4669,4675 ----
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
***************
*** 93,100 **** typedef struct PgFdwRelationInfo
JoinType jointype;
List *joinclauses;
! /* Grouping information */
! List *grouped_tlist;
} PgFdwRelationInfo;
/* in postgres_fdw.c */
--- 93,114 ----
JoinType jointype;
List *joinclauses;
! /* Subquery information */
! bool is_subquery_rel; /* do we deparse this as a subquery? */
! Relids lower_subquery_rels; /* all relids appearing in lower
! * subqueries */
!
! /*
! * Index of the relation. It is used for creating an alias to a subquery
! * representing the relation if is_subquery_rel is true.
! */
! int relation_index;
!
! /*
! * Optional tlist describing the contents of the scan tuple from the
! * relation.
! */
! List *tlist;
} PgFdwRelationInfo;
/* in postgres_fdw.c */
***************
*** 161,167 **** extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
extern List *build_tlist_to_deparse(RelOptInfo *foreignrel);
extern void deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root,
RelOptInfo *foreignrel, List *tlist,
! List *remote_conds, List *pathkeys,
List **retrieved_attrs, List **params_list);
/* in shippable.c */
--- 175,181 ----
extern List *build_tlist_to_deparse(RelOptInfo *foreignrel);
extern void deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root,
RelOptInfo *foreignrel, List *tlist,
! List *remote_conds, List *pathkeys, bool is_subquery,
List **retrieved_attrs, List **params_list);
/* in shippable.c */
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 391,399 **** EXPLAIN (VERBOSE, COSTS OFF)
--- 391,416 ----
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;
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT 1 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 (TRUE) OFFSET 10 LIMIT 10;
+ SELECT 1 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 (TRUE) OFFSET 10 LIMIT 10;
+ -- 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;
+ -- d. test deparsing rowmarked relations as subqueries
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1 INNER 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 (TRUE) ORDER BY t1.c1, ss.a, ss.b FOR UPDATE OF t1;
+ SELECT t1.c1, ss.a, ss.b FROM (SELECT c1 FROM "S 1"."T 3" WHERE c1 = 50) t1 INNER 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 (TRUE) ORDER BY t1.c1, ss.a, ss.b FOR UPDATE OF t1;
-- 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)
--- 810,821 ----
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
