On 2016/11/24 21:45, Etsuro Fujita wrote:
On 2016/11/22 18:28, Ashutosh Bapat wrote:
The comments should explain why is the assertion true.
+ /* Shouldn't be NIL */
+ Assert(tlist != NIL);
I noticed that I was wrong; in the Assertion the tlist can be empty. An
example for such a case is:
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);
In this example any columns of the relations ft4 and ft5 wouldn't be
needed for the join or the final output, so both the tlists for the
relations created in deparseRangeTblRef would be empty. Attached is an
updated version fixing this bug. Changes are:
* I removed make_outerrel_subquery/make_innerrel_subquery from fpinfo
and added a new member "is_subquery_rel" to fpinfo, as a flag on whether
to deparse the relation as a subquery.
* I modified deparseRangeTblRef, deparseSelectSql, and is_subquery_var
to see the is_subquery_rel, not
make_outerrel_subquery/make_innerrel_subquery.
* I modified appendSubselectAlias to handle the case where ncols = 0
properly.
* I renamed subquery_rels in fpinfo to lower_subquery_rels, to make it
easy to distinguish this from is_subquery_rel clearly.
* I added regression tests for that.
I noticed that the above changes are not adequate; the updated patch
breaks EXPLAIN outputs for EvalPlanQual subplans of foreign joins in
which foreign tables are full joined and in the remote join query the
foreign tables are deparsed as subqueries. Consider:
postgres=# explain verbose select * from test, ((select * from ft1 where
b between 1 and 3) t1 full join (select * from ft2 where b between 1 and
3) t2
on (t1.a = t2.a)) ss for update of test;
QUERY PLA
N
-------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
LockRows (cost=100.00..287.33 rows=6780 width=94)
Output: test.a, test.b, ft1.a, ft1.b, ft2.a, ft2.b, test.ctid,
ft1.*, ft2.*
-> Nested Loop (cost=100.00..219.53 rows=6780 width=94)
Output: test.a, test.b, ft1.a, ft1.b, ft2.a, ft2.b, test.ctid,
ft1.*, ft2.*
-> Seq Scan on public.test (cost=0.00..32.60 rows=2260 width=14)
Output: test.a, test.b, test.ctid
-> Materialize (cost=100.00..102.19 rows=3 width=80)
Output: ft1.a, ft1.b, ft1.*, ft2.a, ft2.b, ft2.*
-> Foreign Scan (cost=100.00..102.17 rows=3 width=80)
Output: ft1.a, ft1.b, ft1.*, ft2.a, ft2.b, ft2.*
Relations: (public.ft1) FULL JOIN (public.ft2)
Remote SQL: SELECT s5.c1, s5.c2, s5.c3, s6.c1,
s6.c2, s6.c3 FROM ((SELECT a, b, ROW(a, b) FROM public.t1 WHERE ((b >=
1)) AND ((b
<= 3))) s5(c1, c2, c3) FULL JOIN (SELECT a, b, ROW(a, b) FROM public.t2
WHERE ((b >= 1)) AND ((b <= 3))) s6(c1, c2, c3) ON (((s5.c1 = s6.c1))))
-> Hash Full Join (cost=201.14..202.29 rows=3
width=80)
Output: ft1.a, ft1.b, ft1.*, ft2.a, ft2.b, ft2.*
Hash Cond: (ft1.a = ft2.a)
-> Foreign Scan on public.ft1
(cost=100.00..101.11 rows=3 width=40)
Output: ft1.a, ft1.b, ft1.*
Remote SQL: SELECT NULL FROM public.t1
WHERE ((b >= 1)) AND ((b <= 3))
-> Hash (cost=101.11..101.11 rows=3 width=40)
Output: ft2.a, ft2.b, ft2.*
-> Foreign Scan on public.ft2
(cost=100.00..101.11 rows=3 width=40)
Output: ft2.a, ft2.b, ft2.*
Remote SQL: SELECT NULL FROM
public.t2 WHERE ((b >= 1)) AND ((b <= 3))
(23 rows)
The SELECT clauses of the remote queries for the Foreign Scans in the
EPQ subplan are deparsed incorrectly into "SELECT NULL". The reason for
that is that since the foreign tables' fpinfo->is_subquery_rel has been
set true at the time those clauses are deparsed (due to that the foreign
tables have been deparsed as subqueries in the main remote join query),
deparseSelectSql calls deparseExplicitTargetList with an empty tlist, to
construct the clauses, so it deparses the tlist into "NULL". This is
harmless because we don't use the remote queries for the Foreign Scans
during an EPQ recheck, but that would be confusing for users, so I
modified the patch a bit further to make the EXPLAIN output as-is.
Attached is a new version of the patch.
I also revised code and comments a bit, just for consistency.
Also, I renamed appendSubselectAlias to appendSubqueryAlias, because we
use the term "subquery" for other places.
I will update another patch for PHVs on top of the attached one.
Done. I also revised some code and comments. I'm also attaching the
updated version of the patch for PHVs.
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_REL_ALIAS_PREFIX "s"
+ #define SS_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,181 ----
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 get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
+ int *relno, int *colno);
+ static bool is_subquery_var(Var *node, RelOptInfo *foreignrel, int *relno,
+ 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,
***************
*** 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).
--- 890,899 ----
* 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 which case the relation is
! * deparsed as a subquery. Otherwise, we use the relation's fpinfo->attrs_used,
! * 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;
--- 912,919 ----
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;
***************
*** 925,931 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *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
--- 937,943 ----
context.params_list = params_list;
/* 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;
--- 986,998 ----
* 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
{
/*
--- 1004,1018 ----
*/
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)
--- 1172,1190 ----
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
--- 1372,1387 ----
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,
--- 1437,1606 ----
}
/*
+ * 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;
+ List *retrieved_attrs;
+
+ /* Build a tlist from the subquery. */
+ tlist = build_tlist_to_deparse(foreignrel);
+
+ /*
+ * Since (1) the expressions in foreignrel's reltarget doesn't contain
+ * any PHVs and (2) foreignrel's local_conds is empty, the tlist
+ * created by build_tlist_to_deparse must be one-to-one with the
+ * expressions.
+ */
+ Assert(list_length(tlist) == list_length(foreignrel->reltarget->exprs));
+
+ /* Append the subquery representing the given relation. */
+ appendStringInfoChar(buf, '(');
+ deparseSelectStmtForRel(buf, root, foreignrel, tlist,
+ fpinfo->remote_conds, NIL, true,
+ &retrieved_attrs, params_list);
+ appendStringInfoChar(buf, ')');
+
+ /*
+ * Append the subquery alias for references to this relation in the
+ * rest of the query.
+ */
+ 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", SS_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", SS_COL_ALIAS_PREFIX, i);
+ }
+ appendStringInfoChar(buf, ')');
+ }
+ }
+
+ /*
+ * Get the relation and column alias IDs of a given Var node, which belongs
+ * to input foreignrel. They are returned in *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;
+ int i;
+ ListCell *lc;
+
+ /* Get the relation alias ID */
+ *relno = fpinfo->relation_index;
+
+ /* Get the column alias ID */
+ 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 a given Var node belongs to a relation being deparsed as a
+ * subquery. Returns false otherwise. When returning true, it sets *relno and
+ * *colno to the relation and column alias IDs of the given Var node,
+ * 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 outer relation is deparsed as a subquery, get the identifiers for
+ * the relation and column alias to the given Var node.
+ */
+ 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 inner relation is deparsed as a subquery, get the identifiers for
+ * the relation and column alias to the given Var node.
+ */
+ 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);
+ }
+ }
+
+ /*
* deparse remote INSERT statement
*
* The statement text is appended to buf, and we also create an integer List
***************
*** 2057,2066 **** static void
--- 2244,2268 ----
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 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, context->scanrel, &relno, &colno))
+ {
+ appendStringInfo(context->buf, "%s%d.%s%d",
+ SS_REL_ALIAS_PREFIX, relno,
+ 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,1380 ----
| 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);
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 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))
+ (4 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);
+ ?column?
+ ----------
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ 1
+ (24 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 the remote queries for simple foreign table scans in
+ -- an EPQ subplan of a foreign join in which the foreign tables are full
+ -- joined and in the remote join query the foreign tables are deparsed 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
--- 3187,3210 ----
| 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,680 ----
if (*refname && strcmp(refname, relname) != 0)
appendStringInfo(fpinfo->relation_name, " %s",
quote_identifier(rte->eref->aliasname));
+
+ /* Initialize info about whether to deparse this rel 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;
}
/*
***************
*** 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.
--- 1246,1252 ----
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);
--- 2558,2565 ----
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);
***************
*** 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
--- 4153,4176 ----
fpinfo->innerrel = innerrel;
fpinfo->jointype = jointype;
+ /* Initialize info about whether to deparse this rel 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_i->lower_subquery_rels, innerrel->relids));
+ Assert(bms_is_subset(fpinfo_o->lower_subquery_rels, outerrel->relids));
+ fpinfo->lower_subquery_rels = bms_union(fpinfo_i->lower_subquery_rels,
+ fpinfo_o->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.
--- 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,4238 ----
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 so that the deparser can take appropriate action.
! * We also save the relids of the base relations covered by the
! * joining 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,
--- 4313,4328 ----
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,110 ----
/* Grouping information */
List *grouped_tlist;
+
+ /* 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 a subquery alias when
+ * the is_subquery_rel is true.
+ */
+ int relation_index;
} PgFdwRelationInfo;
/* in postgres_fdw.c */
***************
*** 159,168 **** extern void deparseAnalyzeSql(StringInfo buf, Relation rel,
extern void deparseStringLiteral(StringInfo buf, const char *val);
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 */
extern bool is_builtin(Oid objectId);
--- 169,178 ----
extern void deparseStringLiteral(StringInfo buf, const char *val);
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 *rel,
! List *tlist, List *remote_conds, List *pathkeys,
! bool is_subquery, List **retrieved_attrs,
! List **params_list);
/* in shippable.c */
extern bool is_builtin(Oid objectId);
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 391,399 **** EXPLAIN (VERBOSE, COSTS OFF)
--- 391,419 ----
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);
+ 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);
+ -- 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 the remote queries for simple foreign table scans in
+ -- an EPQ subplan of a foreign join in which the foreign tables are full
+ -- joined and in the remote join query the foreign tables are deparsed 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)
--- 813,824 ----
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)
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***************
*** 49,54 ****
--- 49,55 ----
#include "nodes/nodeFuncs.h"
#include "nodes/plannodes.h"
#include "optimizer/clauses.h"
+ #include "optimizer/placeholder.h"
#include "optimizer/prep.h"
#include "optimizer/tlist.h"
#include "optimizer/var.h"
***************
*** 157,162 **** static void deparseRelabelType(RelabelType *node, deparse_expr_cxt *context);
--- 158,164 ----
static void deparseBoolExpr(BoolExpr *node, deparse_expr_cxt *context);
static void deparseNullTest(NullTest *node, deparse_expr_cxt *context);
static void deparseArrayExpr(ArrayExpr *node, deparse_expr_cxt *context);
+ static void deparseExprInPlaceHolderVar(PlaceHolderVar *node, deparse_expr_cxt *context);
static void printRemoteParam(int paramindex, Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
***************
*** 172,181 **** 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 get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
int *relno, int *colno);
! static bool is_subquery_var(Var *node, RelOptInfo *foreignrel, int *relno,
! 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,
--- 174,184 ----
static void deparseRangeTblRef(StringInfo buf, PlannerInfo *root,
RelOptInfo *foreignrel, List **params_list);
static void appendSubqueryAlias(StringInfo buf, int relno, int ncols);
! static void get_relation_column_alias_ids(Expr *node, RelOptInfo *foreignrel,
int *relno, int *colno);
! static bool is_subquery_expr(Expr *node, PlannerInfo *root, RelOptInfo *foreignrel,
! int *relno, 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,
***************
*** 766,771 **** foreign_expr_walker(Node *node,
--- 769,793 ----
state = FDW_COLLATE_UNSAFE;
}
break;
+ case T_PlaceHolderVar:
+ {
+ PlaceHolderVar *phv = (PlaceHolderVar *) node;
+ PlaceHolderInfo *phinfo = find_placeholder_info(glob_cxt->root,
+ phv, false);
+
+ /*
+ * If the PHV's contained expression is computable on the
+ * remote server, we consider the PHV safe to send.
+ */
+ if (phv->phlevelsup == 0 &&
+ bms_is_subset(phinfo->ph_eval_at,
+ glob_cxt->foreignrel->relids))
+ return foreign_expr_walker((Node *) phv->phexpr,
+ glob_cxt, outer_cxt);
+ else
+ return false;
+ }
+ break;
default:
/*
***************
*** 860,866 **** deparse_type_name(Oid type_oid, int32 typemod)
* foreign server.
*/
List *
! build_tlist_to_deparse(RelOptInfo *foreignrel)
{
List *tlist = NIL;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
--- 882,888 ----
* foreign server.
*/
List *
! build_tlist_to_deparse(PlannerInfo *root, RelOptInfo *foreignrel)
{
List *tlist = NIL;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
***************
*** 873,887 **** build_tlist_to_deparse(RelOptInfo *foreignrel)
return fpinfo->grouped_tlist;
/*
! * We require columns specified in foreignrel->reltarget->exprs and those
! * required for evaluating the local conditions.
*/
! tlist = add_to_flat_tlist(tlist,
! pull_var_clause((Node *) foreignrel->reltarget->exprs,
! PVC_RECURSE_PLACEHOLDERS));
! tlist = add_to_flat_tlist(tlist,
! pull_var_clause((Node *) fpinfo->local_conds,
! PVC_RECURSE_PLACEHOLDERS));
return tlist;
}
--- 895,967 ----
return fpinfo->grouped_tlist;
/*
! * Fetch all expressions in foreignrel's reltarget if the
! * reltarget_is_shippable flag is set TRUE. Otherwise, fetch shipplable
! * expressions in the reltarget plus expressions required for evaluating
! * non-shippable expressions in the reltarget.
*/
! if (fpinfo->reltarget_is_shippable)
! tlist = add_to_flat_tlist(tlist, foreignrel->reltarget->exprs);
! else
! {
! List *exprs = NIL;
! ListCell *lc;
!
! /* Note: we have at least one non-shippable PHV in the reltarget. */
! foreach(lc, foreignrel->reltarget->exprs)
! {
! Node *node = (Node *) lfirst(lc);
!
! if (IsA(node, Var))
! exprs = lappend(exprs, node);
! else if (IsA(node, PlaceHolderVar))
! {
! PlaceHolderVar *phv = (PlaceHolderVar *) node;
! PlaceHolderInfo *phinfo = find_placeholder_info(root, phv,
! false);
!
! if (bms_is_subset(phinfo->ph_eval_at,
! fpinfo->outerrel->relids) ||
! bms_is_subset(phinfo->ph_eval_at,
! fpinfo->innerrel->relids))
! {
! /*
! * The PHV coming from an either input should be shippable.
! */
! exprs = lappend(exprs, node);
! }
! else
! {
! /*
! * The PHV might be shippable, but in any case just fetch
! * expressions required for evaluating the PHV.
! */
! List *exprs2 = pull_var_clause(node,
! PVC_INCLUDE_PLACEHOLDERS);
! ListCell *lc2;
!
! foreach(lc2, exprs2)
! {
! Node *node2 = (Node *) lfirst(lc2);
!
! exprs = lappend(exprs, node2);
! }
! }
! }
! else
! elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
! }
!
! tlist = add_to_flat_tlist(tlist, exprs);
! }
!
! /*
! * Fetch expressions required for evaluating local conditions, if any.
! */
! if (fpinfo->local_conds)
! tlist = add_to_flat_tlist(tlist,
! pull_var_clause((Node *) fpinfo->local_conds,
! PVC_INCLUDE_PLACEHOLDERS));
return tlist;
}
***************
*** 1447,1452 **** deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
--- 1527,1533 ----
Assert(foreignrel->reloptkind == RELOPT_BASEREL ||
foreignrel->reloptkind == RELOPT_JOINREL);
+ Assert(fpinfo->reltarget_is_shippable);
Assert(fpinfo->local_conds == NIL);
/* If is_subquery_rel is true, deparse the relation as a subquery. */
***************
*** 1456,1468 **** deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
List *retrieved_attrs;
/* Build a tlist from the subquery. */
! tlist = build_tlist_to_deparse(foreignrel);
/*
! * Since (1) the expressions in foreignrel's reltarget doesn't contain
! * any PHVs and (2) foreignrel's local_conds is empty, the tlist
! * created by build_tlist_to_deparse must be one-to-one with the
! * expressions.
*/
Assert(list_length(tlist) == list_length(foreignrel->reltarget->exprs));
--- 1537,1549 ----
List *retrieved_attrs;
/* Build a tlist from the subquery. */
! tlist = build_tlist_to_deparse(root, foreignrel);
/*
! * Since (1) the relation's reltarget_is_shippable is true and (2) the
! * relation's local_conds is empty, the tlist created by
! * build_tlist_to_deparse must be one-to-one with the relation's
! * reltarget.
*/
Assert(list_length(tlist) == list_length(foreignrel->reltarget->exprs));
***************
*** 1513,1529 **** appendSubqueryAlias(StringInfo buf, int relno, int ncols)
}
/*
! * Get the relation and column alias IDs of a given Var node, which belongs
* to input foreignrel. They are returned in *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;
int i;
ListCell *lc;
/* Get the relation alias ID */
*relno = fpinfo->relation_index;
--- 1594,1612 ----
}
/*
! * Get the relation and column alias IDs of a given Var or PHV, which belongs
* to input foreignrel. They are returned in *relno and *colno respectively.
*/
static void
! get_relation_column_alias_ids(Expr *node, RelOptInfo *foreignrel,
int *relno, int *colno)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
int i;
ListCell *lc;
+ Assert(IsA(node, Var) || IsA(node, PlaceHolderVar));
+
/* Get the relation alias ID */
*relno = fpinfo->relation_index;
***************
*** 1544,1568 **** get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
}
/*
! * Returns true if a given Var node belongs to a relation being deparsed as a
! * subquery. Returns false otherwise. When returning true, it sets *relno and
! * *colno to the relation and column alias IDs of the given Var node,
* 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;
--- 1627,1689 ----
}
/*
! * Returns true if a given Var or PHV belongs to a relation being deparsed as
! * a subquery. Returns false otherwise. When returning true, it sets *relno
! * and *colno to the relation and column alias IDs of the given Var or PHV,
* respectively.
*/
static bool
! is_subquery_expr(Expr *node, PlannerInfo *root, RelOptInfo *foreignrel,
! int *relno, int *colno)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
RelOptInfo *outerrel = fpinfo->outerrel;
RelOptInfo *innerrel = fpinfo->innerrel;
+ bool is_outer_var;
if (foreignrel->reloptkind != RELOPT_JOINREL)
return false;
! if (IsA(node, Var))
! {
! Var *var = (Var *) node;
! /*
! * The Var can't be a subquery output column if it doesn't belong to
! * any relation being deparsed as a subquery.
! */
! if (!bms_is_member(var->varno, fpinfo->lower_subquery_rels))
! return false;
!
! is_outer_var = bms_is_member(var->varno, outerrel->relids);
! }
! else if (IsA(node, PlaceHolderVar))
! {
! PlaceHolderVar *phv = (PlaceHolderVar *) node;
! PlaceHolderInfo *phinfo = find_placeholder_info(root, phv, false);
! bool is_inner_var;
!
! /*
! * The PHV can't be a subquery output column if it doesn't belong to
! * any relation being deparsed as a subquery.
! */
! if (!bms_is_subset(phinfo->ph_eval_at, fpinfo->lower_subquery_rels))
! return false;
!
! is_outer_var = bms_is_subset(phinfo->ph_eval_at, outerrel->relids);
! is_inner_var = bms_is_subset(phinfo->ph_eval_at, innerrel->relids);
!
! /*
! * The PHV computed here but not in either input isn't a subquery
! * output column.
! */
! if (!is_outer_var && !is_inner_var)
! return false;
! }
! else
! elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
!
! if (is_outer_var)
{
PgFdwRelationInfo *fpinfo2 = (PgFdwRelationInfo *) outerrel->fdw_private;
***************
*** 1577,1590 **** is_subquery_var(Var *node, RelOptInfo *foreignrel, int *relno, int *colno)
}
/* 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 inner relation is deparsed as a subquery, get the identifiers for
* the relation and column alias to the given Var node.
--- 1698,1709 ----
}
/* Otherwise, recurse into the outer relation. */
! return is_subquery_expr(node, root, outerrel, relno, colno);
}
else
{
PgFdwRelationInfo *fpinfo2 = (PgFdwRelationInfo *) innerrel->fdw_private;
/*
* If inner relation is deparsed as a subquery, get the identifiers for
* the relation and column alias to the given Var node.
***************
*** 1596,1602 **** is_subquery_var(Var *node, RelOptInfo *foreignrel, int *relno, int *colno)
}
/* Otherwise, recurse into the inner relation. */
! return is_subquery_var(node, innerrel, relno, colno);
}
}
--- 1715,1721 ----
}
/* Otherwise, recurse into the inner relation. */
! return is_subquery_expr(node, root, innerrel, relno, colno);
}
}
***************
*** 1981,1989 **** deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root,
{
/*
* All other system attributes are fetched as 0, except for table OID,
! * which is fetched as the local table OID. However, we must be
! * careful; the table could be beneath an outer join, in which case it
! * must go to NULL whenever the rest of the row does.
*/
Oid fetchval = 0;
--- 2100,2106 ----
{
/*
* All other system attributes are fetched as 0, except for table OID,
! * which is fetched as the local table OID.
*/
Oid fetchval = 0;
***************
*** 1993,2006 **** deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root,
fetchval = rte->relid;
}
! if (qualify_col)
! {
! appendStringInfoString(buf, "CASE WHEN (");
! ADD_REL_QUALIFIER(buf, varno);
! appendStringInfo(buf, "*)::text IS NOT NULL THEN %u END", fetchval);
! }
! else
! appendStringInfo(buf, "%u", fetchval);
}
else if (varattno == 0)
{
--- 2110,2116 ----
fetchval = rte->relid;
}
! appendStringInfo(buf, "%u", fetchval);
}
else if (varattno == 0)
{
***************
*** 2031,2058 **** deparseColumnRef(StringInfo buf, int varno, int varattno, PlannerInfo *root,
attrs_used = bms_add_member(NULL,
0 - FirstLowInvalidHeapAttributeNumber);
- /*
- * In case the whole-row reference is under an outer join then it has
- * to go NULL whenever the rest of the row goes NULL. Deparsing a join
- * query would always involve multiple relations, thus qualify_col
- * would be true.
- */
- if (qualify_col)
- {
- appendStringInfoString(buf, "CASE WHEN (");
- ADD_REL_QUALIFIER(buf, varno);
- appendStringInfo(buf, "*)::text IS NOT NULL THEN ");
- }
-
appendStringInfoString(buf, "ROW(");
deparseTargetList(buf, root, varno, rel, false, attrs_used, qualify_col,
&retrieved_attrs);
appendStringInfoString(buf, ")");
- /* Complete the CASE WHEN statement started above. */
- if (qualify_col)
- appendStringInfo(buf, " END");
-
heap_close(rel, NoLock);
bms_free(attrs_used);
}
--- 2141,2151 ----
***************
*** 2225,2230 **** deparseExpr(Expr *node, deparse_expr_cxt *context)
--- 2318,2326 ----
case T_Aggref:
deparseAggref((Aggref *) node, context);
break;
+ case T_PlaceHolderVar:
+ deparseExprInPlaceHolderVar((PlaceHolderVar *) node, context);
+ break;
default:
elog(ERROR, "unsupported expression type for deparse: %d",
(int) nodeTag(node));
***************
*** 2255,2261 **** deparseVar(Var *node, deparse_expr_cxt *context)
* relation and column alias provided by the subquery, instead of the
* actual column name.
*/
! if (is_subquery_var(node, context->scanrel, &relno, &colno))
{
appendStringInfo(context->buf, "%s%d.%s%d",
SS_REL_ALIAS_PREFIX, relno,
--- 2351,2358 ----
* relation and column alias provided by the subquery, instead of the
* actual column name.
*/
! if (is_subquery_expr((Expr *) node, context->root, context->scanrel,
! &relno, &colno))
{
appendStringInfo(context->buf, "%s%d.%s%d",
SS_REL_ALIAS_PREFIX, relno,
***************
*** 2949,2954 **** appendAggOrderBy(List *orderList, List *targetList, deparse_expr_cxt *context)
--- 3046,3076 ----
}
/*
+ * Deparse a PlaceHolderVar's contained expression.
+ */
+ static void
+ deparseExprInPlaceHolderVar(PlaceHolderVar *node, deparse_expr_cxt *context)
+ {
+ int relno;
+ int colno;
+
+ /*
+ * If the given PHV belongs to a relation deparsed as a subquery, use the
+ * relation and column alias provided by the subquery instead.
+ */
+ if (is_subquery_expr((Expr *) node, context->root, context->scanrel,
+ &relno, &colno))
+ {
+ appendStringInfo(context->buf, "%s%d.%s%d",
+ SS_REL_ALIAS_PREFIX, relno,
+ SS_COL_ALIAS_PREFIX, colno);
+ return;
+ }
+
+ deparseExpr(node->phexpr, context);
+ }
+
+ /*
* Print the representation of a parameter to be sent to the remote side.
*
* Note: we always label the Param's type explicitly rather than relying on
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 1634,1641 **** SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) WHERE (t1.c1
-- tests whole-row reference for row marks
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
--- 1634,1641 ----
-- tests whole-row reference for row marks
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE OF t1;
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
***************
*** 1643,1649 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
--- 1643,1649 ----
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT s1.c1, s1.c2, s1.c3, s2.c1, s2.c2 FROM ((SELECT "C 1", c3, ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR UPDATE) s1(c1, c2, c3) INNER JOIN (SELECT "C 1", ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1") s2(c1, c2) ON (((s1.c1 = s2.c1)))) ORDER BY s1.c2 ASC NULLS LAST, s1.c1 ASC NULLS LAST
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
***************
*** 1678,1685 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
--- 1678,1685 ----
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR UPDATE;
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
***************
*** 1687,1693 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR UPDATE OF r1 FOR UPDATE OF r2
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
--- 1687,1693 ----
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT s1.c1, s1.c2, s1.c3, s2.c1, s2.c2 FROM ((SELECT "C 1", c3, ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR UPDATE) s1(c1, c2, c3) INNER JOIN (SELECT "C 1", ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR UPDATE) s2(c1, c2) ON (((s1.c1 = s2.c1)))) ORDER BY s1.c2 ASC NULLS LAST, s1.c1 ASC NULLS LAST
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
***************
*** 1723,1730 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
-- join two tables with FOR SHARE clause
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
--- 1723,1730 ----
-- join two tables with FOR SHARE clause
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE OF t1;
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
***************
*** 1732,1738 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
--- 1732,1738 ----
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT s1.c1, s1.c2, s1.c3, s2.c1, s2.c2 FROM ((SELECT "C 1", c3, ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR SHARE) s1(c1, c2, c3) INNER JOIN (SELECT "C 1", ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1") s2(c1, c2) ON (((s1.c1 = s2.c1)))) ORDER BY s1.c2 ASC NULLS LAST, s1.c1 ASC NULLS LAST
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
***************
*** 1767,1774 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
--- 1767,1774 ----
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10 FOR SHARE;
! QUERY PLAN
! -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
-> LockRows
***************
*** 1776,1782 **** SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT r1."C 1", r1.c3, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, r2."C 1", CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST FOR SHARE OF r1 FOR SHARE OF r2
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
--- 1776,1782 ----
-> Foreign Scan
Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT s1.c1, s1.c2, s1.c3, s2.c1, s2.c2 FROM ((SELECT "C 1", c3, ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR SHARE) s1(c1, c2, c3) INNER JOIN (SELECT "C 1", ROW("C 1", c2, c3, c4, c5, c6, c7, c8) FROM "S 1"."T 1" FOR SHARE) s2(c1, c2) ON (((s1.c1 = s2.c1)))) ORDER BY s1.c2 ASC NULLS LAST, s1.c1 ASC NULLS LAST
-> Merge Join
Output: t1.c1, t1.c3, t1.*, t2.c1, t2.*
Merge Cond: (t1.c1 = t2.c1)
***************
*** 1846,1859 **** WITH t (c1_1, c1_3, c2_1) AS (SELECT t1.c1, t1.c3, t2.c1 FROM ft1 t1 JOIN ft2 t2
-- ctid with whole-row reference
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
-> Foreign Scan
Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, r1."C 1", r1.c3, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1."C 1" = r2."C 1")))) ORDER BY r1.c3 ASC NULLS LAST, r1."C 1" ASC NULLS LAST
(6 rows)
-- SEMI JOIN, not pushed down
--- 1846,1859 ----
-- ctid with whole-row reference
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit
Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
-> Foreign Scan
Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
! Remote SQL: SELECT s1.c1, s1.c2, s1.c3, s1.c4, s2.c1 FROM ((SELECT ctid, ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1", c3 FROM "S 1"."T 1") s1(c1, c2, c3, c4) INNER JOIN (SELECT ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1" FROM "S 1"."T 1") s2(c1, c2) ON (((s1.c3 = s2.c2)))) ORDER BY s1.c4 ASC NULLS LAST, s1.c3 ASC NULLS LAST
(6 rows)
-- SEMI JOIN, not pushed down
***************
*** 2182,2206 **** SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM
1
(10 rows)
! -- non-Var items in targelist of the nullable rel of a join preventing
! -- push-down in some cases
! -- unable to push {ft1, ft2}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------
! Nested Loop Left Join
Output: (13), ft2.c1
! Join Filter: (13 = ft2.c1)
! -> Foreign Scan on public.ft2
! Output: ft2.c1
! Remote SQL: SELECT "C 1" FROM "S 1"."T 1" WHERE (("C 1" >= 10)) AND (("C 1" <= 15)) ORDER BY "C 1" ASC NULLS LAST
! -> Materialize
! Output: (13)
! -> Foreign Scan on public.ft1
! Output: 13
! Remote SQL: SELECT NULL FROM "S 1"."T 1" WHERE (("C 1" = 13))
! (11 rows)
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
a | c1
--- 2182,2197 ----
1
(10 rows)
! -- check join pushdown in situations where PHVs are involved
EXPLAIN (VERBOSE, COSTS OFF)
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
! QUERY PLAN
! ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan
Output: (13), ft2.c1
! Relations: (public.ft2) LEFT JOIN (public.ft1)
! Remote SQL: SELECT r2."C 1", s4.c1 FROM ("S 1"."T 1" r2 LEFT JOIN (SELECT 13 FROM "S 1"."T 1" WHERE (("C 1" = 13))) s4(c1) ON (((13 = r2."C 1")))) WHERE ((r2."C 1" >= 10)) AND ((r2."C 1" <= 15))
! (4 rows)
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
a | c1
***************
*** 2213,2236 **** SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 O
| 15
(6 rows)
- -- ok to push {ft1, ft2} but not {ft1, ft2, ft4}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! Nested Loop Left Join
Output: ft4.c1, (13), ft1.c1, ft2.c1
! Join Filter: (ft4.c1 = ft1.c1)
! -> Foreign Scan on public.ft4
! Output: ft4.c1, ft4.c2, ft4.c3
! Remote SQL: SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 10)) AND ((c1 <= 15))
! -> Materialize
! Output: ft1.c1, ft2.c1, (13)
! -> Foreign Scan
! Output: ft1.c1, ft2.c1, 13
! Relations: (public.ft1) INNER JOIN (public.ft2)
! Remote SQL: SELECT r4."C 1", r5."C 1" FROM ("S 1"."T 1" r4 INNER JOIN "S 1"."T 1" r5 ON (((r5."C 1" = 12)) AND ((r4."C 1" = 12)))) ORDER BY r4."C 1" ASC NULLS LAST
! (12 rows)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
c1 | a | b | c
--- 2204,2218 ----
| 15
(6 rows)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
! Foreign Scan
Output: ft4.c1, (13), ft1.c1, ft2.c1
! Relations: (public.ft4) LEFT JOIN ((public.ft1) INNER JOIN (public.ft2))
! Remote SQL: SELECT r1.c1, s7.c1, s7.c2, s7.c3 FROM ("S 1"."T 3" r1 LEFT JOIN (SELECT r4."C 1", r5."C 1", 13 FROM ("S 1"."T 1" r4 INNER JOIN "S 1"."T 1" r5 ON (((r5."C 1" = 12)) AND ((r4."C 1" = 12))))) s7(c1, c2, c3) ON (((r1.c1 = s7.c1)))) WHERE ((r1.c1 >= 10)) AND ((r1.c1 <= 15))
! (4 rows)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
c1 | a | b | c
***************
*** 2240,2255 **** SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT
14 | | |
(3 rows)
-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: ft5.*, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2
Relations: (public.ft5) INNER JOIN (public.ft4)
! Remote SQL: SELECT CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1.c1, r1.c2, r1.c3) END, r1.c1, r1.c2, r1.c3, r2.c1, r2.c2 FROM ("S 1"."T 4" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c1 = r2.c1)) AND ((r2.c1 >= 10)) AND ((r2.c1 <= 30)))) ORDER BY r1.c1 ASC NULLS LAST
(4 rows)
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
--- 2222,2302 ----
14 | | |
(3 rows)
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT ft2.c1, q2.* FROM ft2 LEFT JOIN (SELECT 13, q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15) q2(a2, b2, c2) ON (ft2.c1 = q2.a2) WHERE ft2.c1 BETWEEN 10 AND 15;
+ QUERY PLAN
+ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1, (13), (13), ft2_1.c1
+ Relations: (public.ft2) LEFT JOIN ((public.ft2) LEFT JOIN (public.ft1))
+ Remote SQL: SELECT r1."C 1", s8.c1, s8.c2, s8.c3 FROM ("S 1"."T 1" r1 LEFT JOIN (SELECT r5."C 1", 13, s7.c1 FROM ("S 1"."T 1" r5 LEFT JOIN (SELECT 13 FROM "S 1"."T 1" WHERE (("C 1" = 13))) s7(c1) ON (((13 = r5."C 1")))) WHERE ((r5."C 1" >= 10)) AND ((r5."C 1" <= 15))) s8(c1, c2, c3) ON (((r1."C 1" = 13)))) WHERE ((r1."C 1" >= 10)) AND ((r1."C 1" <= 15))
+ (4 rows)
+
+ SELECT ft2.c1, q2.* FROM ft2 LEFT JOIN (SELECT 13, q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15) q2(a2, b2, c2) ON (ft2.c1 = q2.a2) WHERE ft2.c1 BETWEEN 10 AND 15;
+ c1 | a2 | b2 | c2
+ ----+----+----+----
+ 10 | | |
+ 11 | | |
+ 12 | | |
+ 13 | 13 | | 10
+ 13 | 13 | | 11
+ 13 | 13 | | 12
+ 13 | 13 | 13 | 13
+ 13 | 13 | | 14
+ 13 | 13 | | 15
+ 14 | | |
+ 15 | | |
+ (11 rows)
+
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT ft2.c1, q2.* FROM ft2 RIGHT JOIN (SELECT 13, q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15) q2(a2, b2, c2) ON (ft2.c1 = q2.a2);
+ QUERY PLAN
+ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft2.c1, 13, (13), ft2_1.c1
+ Relations: ((public.ft2) LEFT JOIN (public.ft1)) LEFT JOIN (public.ft2)
+ Remote SQL: SELECT r5."C 1", r1."C 1", s7.c1 FROM (("S 1"."T 1" r5 LEFT JOIN (SELECT 13 FROM "S 1"."T 1" WHERE (("C 1" = 13))) s7(c1) ON (((13 = r5."C 1")))) LEFT JOIN "S 1"."T 1" r1 ON (((r1."C 1" = 13)))) WHERE ((r5."C 1" >= 10)) AND ((r5."C 1" <= 15))
+ (4 rows)
+
+ SELECT ft2.c1, q2.* FROM ft2 RIGHT JOIN (SELECT 13, q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15) q2(a2, b2, c2) ON (ft2.c1 = q2.a2);
+ c1 | a2 | b2 | c2
+ ----+----+----+----
+ 13 | 13 | | 10
+ 13 | 13 | | 11
+ 13 | 13 | | 12
+ 13 | 13 | 13 | 13
+ 13 | 13 | | 14
+ 13 | 13 | | 15
+ (6 rows)
+
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT ft4.c1, q2.* FROM ft4 LEFT JOIN (SELECT (q.a IS NOT NULL), ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15) q2(a2, b2, c2, d2, e2) ON (ft4.c1 = q2.b2) WHERE ft4.c1 BETWEEN 10 AND 15;
+ QUERY PLAN
+ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: ft4.c1, ((13 IS NOT NULL)), ft4_1.c1, (13), ft1.c1, ft2.c1
+ Relations: (public.ft4) LEFT JOIN ((public.ft4) LEFT JOIN ((public.ft1) INNER JOIN (public.ft2)))
+ Remote SQL: SELECT r1.c1, s11.c1, s11.c2, s11.c3, s11.c4, s11.c5 FROM ("S 1"."T 3" r1 LEFT JOIN (SELECT r4.c1, s10.c1, s10.c2, (s10.c3 IS NOT NULL), s10.c3 FROM ("S 1"."T 3" r4 LEFT JOIN (SELECT r7."C 1", r8."C 1", 13 FROM ("S 1"."T 1" r7 INNER JOIN "S 1"."T 1" r8 ON (((r8."C 1" = 12)) AND ((r7."C 1" = 12))))) s10(c1, c2, c3) ON (((r4.c1 = s10.c1)))) WHERE ((r4.c1 >= 10)) AND ((r4.c1 <= 15))) s11(c1, c2, c3, c4, c5) ON (((r1.c1 = s11.c1)))) WHERE ((r1.c1 >= 10)) AND ((r1.c1 <= 15))
+ (4 rows)
+
+ SELECT ft4.c1, q2.* FROM ft4 LEFT JOIN (SELECT (q.a IS NOT NULL), ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15) q2(a2, b2, c2, d2, e2) ON (ft4.c1 = q2.b2) WHERE ft4.c1 BETWEEN 10 AND 15;
+ c1 | a2 | b2 | c2 | d2 | e2
+ ----+----+----+----+----+----
+ 10 | f | 10 | | |
+ 12 | t | 12 | 13 | 12 | 12
+ 14 | f | 14 | | |
+ (3 rows)
+
-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: ft5.*, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2
Relations: (public.ft5) INNER JOIN (public.ft4)
! Remote SQL: SELECT s1.c1, s1.c2, s1.c3, s1.c4, r2.c1, r2.c2 FROM ((SELECT ROW(c1, c2, c3), c1, c2, c3 FROM "S 1"."T 4") s1(c1, c2, c3, c4) INNER JOIN "S 1"."T 3" r2 ON (((s1.c2 = r2.c1)) AND ((r2.c1 >= 10)) AND ((r2.c1 <= 30)))) ORDER BY s1.c2 ASC NULLS LAST
(4 rows)
SELECT ft5, ft5.c1, ft5.c2, ft5.c3, ft4.c1, ft4.c2 FROM ft5 left join ft4 on ft5.c1 = ft4.c1 WHERE ft4.c1 BETWEEN 10 and 30 ORDER BY ft5.c1, ft4.c1;
***************
*** 4151,4164 **** UPDATE ft2 SET c2 = c2 + 400, c3 = c3 || '_update7' WHERE c1 % 10 = 7 RETURNING
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down
! QUERY PLAN
! ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c3 = $3, c7 = $4 WHERE ctid = $1
-> Foreign Scan
Output: ft2.c1, (ft2.c2 + 500), NULL::integer, (ft2.c3 || '_update9'::text), ft2.c4, ft2.c5, ft2.c6, 'ft2 '::character(10), ft2.c8, ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
! Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c8, r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 9)))) FOR UPDATE OF r1
-> Hash Join
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid, ft1.*
Hash Cond: (ft2.c2 = ft1.c1)
--- 4198,4211 ----
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c2 = ft2.c2 + 500, c3 = ft2.c3 || '_update9', c7 = DEFAULT
FROM ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 9; -- can't be pushed down
! QUERY PLAN
! --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Remote SQL: UPDATE "S 1"."T 1" SET c2 = $2, c3 = $3, c7 = $4 WHERE ctid = $1
-> Foreign Scan
Output: ft2.c1, (ft2.c2 + 500), NULL::integer, (ft2.c3 || '_update9'::text), ft2.c4, ft2.c5, ft2.c6, 'ft2 '::character(10), ft2.c8, ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
! Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c8, r1.ctid, s2.c1 FROM ("S 1"."T 1" r1 INNER JOIN (SELECT ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1" FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 9))) s2(c1, c2) ON (((r1.c2 = s2.c2)))) FOR UPDATE OF r1
-> Hash Join
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c8, ft2.ctid, ft1.*
Hash Cond: (ft2.c2 = ft1.c1)
***************
*** 4294,4307 **** DELETE FROM ft2 WHERE c1 % 10 = 5 RETURNING c1, c4;
EXPLAIN (verbose, costs off)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down
! QUERY PLAN
! -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1
-> Foreign Scan
Output: ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
! Remote SQL: SELECT r1.ctid, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2."C 1", r2.c2, r2.c3, r2.c4, r2.c5, r2.c6, r2.c7, r2.c8) END FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r1.c2 = r2."C 1")) AND (((r2."C 1" % 10) = 2)))) FOR UPDATE OF r1
-> Hash Join
Output: ft2.ctid, ft1.*
Hash Cond: (ft2.c2 = ft1.c1)
--- 4341,4354 ----
EXPLAIN (verbose, costs off)
DELETE FROM ft2 USING ft1 WHERE ft1.c1 = ft2.c2 AND ft1.c1 % 10 = 2; -- can't be pushed down
! QUERY PLAN
! ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Delete on public.ft2
Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1
-> Foreign Scan
Output: ft2.ctid, ft1.*
Relations: (public.ft2) INNER JOIN (public.ft1)
! Remote SQL: SELECT r1.ctid, s2.c1 FROM ("S 1"."T 1" r1 INNER JOIN (SELECT ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1" FROM "S 1"."T 1" WHERE ((("C 1" % 10) = 2))) s2(c1, c2) ON (((r1.c2 = s2.c2)))) FOR UPDATE OF r1
-> Hash Join
Output: ft2.ctid, ft1.*
Hash Cond: (ft2.c2 = ft1.c1)
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 28,33 ****
--- 28,34 ----
#include "optimizer/clauses.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
+ #include "optimizer/placeholder.h"
#include "optimizer/planmain.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/var.h"
***************
*** 675,680 **** postgresGetForeignRelSize(PlannerInfo *root,
--- 676,741 ----
fpinfo->lower_subquery_rels = NULL;
/* Set the relation index. */
fpinfo->relation_index = baserel->relid;
+
+ /*
+ * Detect whether expressions in the relation's reltarget are shippable
+ * and whether there are any non-Vars in those expressions.
+ *
+ * Note: whole-row Vars and system columns other than ctid and oid are
+ * treated like PHVs. (See comments in postgres_fdw.h.)
+ *
+ * Note: if the relation is an appendrel child, it isn't in the main join
+ * tree, so those information is not used.
+ */
+ if (baserel->reloptkind == RELOPT_BASEREL)
+ {
+ bool reltarget_is_shippable = true;
+ bool reltarget_has_non_vars = false;
+
+ /*
+ * Check to see if any PHVs are requested from the baserel.
+ */
+ foreach(lc, root->placeholder_list)
+ {
+ PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(lc);
+
+ if (bms_nonempty_difference(phinfo->ph_needed, baserel->relids) &&
+ bms_is_subset(phinfo->ph_eval_at, baserel->relids))
+ {
+ reltarget_has_non_vars = true;
+
+ if (!is_foreign_expr(root, baserel, (Expr *) phinfo->ph_var))
+ {
+ reltarget_is_shippable = false;
+ break;
+ }
+ }
+ }
+
+ /*
+ * If not, check to see if the baserel is required to emit any
+ * whole-row Vars or system columns other than ctid and oid.
+ */
+ if (!reltarget_has_non_vars)
+ {
+ int i;
+
+ Assert(reltarget_is_shippable);
+
+ for (i = baserel->min_attr; i <= 0; i++)
+ {
+ if (i == SelfItemPointerAttributeNumber ||
+ i == ObjectIdAttributeNumber)
+ continue;
+
+ if (!bms_is_empty(baserel->attr_needed[i - baserel->min_attr]))
+ reltarget_has_non_vars = true;
+ }
+ }
+
+ fpinfo->reltarget_is_shippable = reltarget_is_shippable;
+ fpinfo->reltarget_has_non_vars = reltarget_has_non_vars;
+ }
}
/*
***************
*** 1198,1204 **** postgresGetForeignPlan(PlannerInfo *root,
local_exprs = fpinfo->local_conds;
/* Build the list of columns to be fetched from the foreign server. */
! fdw_scan_tlist = build_tlist_to_deparse(foreignrel);
/*
* Ensure that the outer plan produces a tuple whose descriptor
--- 1259,1265 ----
local_exprs = fpinfo->local_conds;
/* Build the list of columns to be fetched from the foreign server. */
! fdw_scan_tlist = build_tlist_to_deparse(root, foreignrel);
/*
* Ensure that the outer plan produces a tuple whose descriptor
***************
*** 2538,2544 **** estimate_path_cost_size(PlannerInfo *root,
/* Build the list of columns to be fetched from the foreign server. */
if (foreignrel->reloptkind == RELOPT_JOINREL ||
foreignrel->reloptkind == RELOPT_UPPER_REL)
! fdw_scan_tlist = build_tlist_to_deparse(foreignrel);
else
fdw_scan_tlist = NIL;
--- 2599,2605 ----
/* Build the list of columns to be fetched from the foreign server. */
if (foreignrel->reloptkind == RELOPT_JOINREL ||
foreignrel->reloptkind == RELOPT_UPPER_REL)
! fdw_scan_tlist = build_tlist_to_deparse(root, foreignrel);
else
fdw_scan_tlist = NIL;
***************
*** 4054,4059 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4115,4122 ----
ListCell *lc;
List *joinclauses;
List *otherclauses;
+ bool reltarget_is_shippable = true;
+ bool reltarget_has_non_vars = false;
/*
* We support pushing down INNER, LEFT, RIGHT and FULL OUTER joins.
***************
*** 4083,4088 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4146,4159 ----
if (fpinfo_o->local_conds || fpinfo_i->local_conds)
return false;
+ /*
+ * If the reltargets of joining relations are not shippable, those are
+ * required to be evaluated locally, so the join can not be pushed down.
+ */
+ if (!fpinfo_o->reltarget_is_shippable ||
+ !fpinfo_i->reltarget_is_shippable)
+ return false;
+
/* Separate restrict list into join quals and quals on join relation */
if (IS_OUTER_JOIN(jointype))
extract_actual_join_clauses(extra->restrictlist, &joinclauses, &otherclauses);
***************
*** 4108,4133 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
return false;
}
- /*
- * deparseExplicitTargetList() isn't smart enough to handle anything other
- * than a Var. In particular, if there's some PlaceHolderVar that would
- * need to be evaluated within this join tree (because there's an upper
- * reference to a quantity that may go to NULL as a result of an outer
- * join), then we can't try to push the join down because we'll fail when
- * we get to deparseExplicitTargetList(). However, a PlaceHolderVar that
- * needs to be evaluated *at the top* of this join tree is OK, because we
- * can do that locally after fetching the results from the remote side.
- */
- foreach(lc, root->placeholder_list)
- {
- PlaceHolderInfo *phinfo = lfirst(lc);
- Relids relids = joinrel->relids;
-
- if (bms_is_subset(phinfo->ph_eval_at, relids) &&
- bms_nonempty_difference(relids, phinfo->ph_eval_at))
- return false;
- }
-
/* Save the join clauses, for later use. */
fpinfo->joinclauses = joinclauses;
--- 4179,4184 ----
***************
*** 4185,4211 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
*
* The joining sides can not have local conditions, thus no need to test
* shippability of the clauses being pulled up.
*/
switch (jointype)
{
case JOIN_INNER:
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_o->remote_conds));
break;
case JOIN_LEFT:
! fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_i->remote_conds));
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_o->remote_conds));
break;
case JOIN_RIGHT:
! fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_o->remote_conds));
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
break;
--- 4236,4286 ----
*
* The joining sides can not have local conditions, thus no need to test
* shippability of the clauses being pulled up.
+ *
+ * If the joining relation has reltarget_has_non_vars=TRUE, then we
+ * deparse that relation as a subquery in which the remote_conds of that
+ * relation is transformed into the WHERE clause. So in that case, don't
+ * pull up the remote_conds into the joinclauses or remote_conds of this
+ * relation.
*/
switch (jointype)
{
case JOIN_INNER:
! if (fpinfo_i->reltarget_has_non_vars)
! fpinfo_i->is_subquery_rel = true;
! else
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
! if (fpinfo_o->reltarget_has_non_vars)
! fpinfo_o->is_subquery_rel = true;
! else
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_o->remote_conds));
break;
case JOIN_LEFT:
! if (fpinfo_i->reltarget_has_non_vars)
! fpinfo_i->is_subquery_rel = true;
! else
! fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_i->remote_conds));
! if (fpinfo_o->reltarget_has_non_vars)
! fpinfo_o->is_subquery_rel = true;
! else
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_o->remote_conds));
break;
case JOIN_RIGHT:
! if (fpinfo_o->reltarget_has_non_vars)
! fpinfo_o->is_subquery_rel = true;
! else
! fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_o->remote_conds));
! if (fpinfo_i->reltarget_has_non_vars)
! fpinfo_i->is_subquery_rel = true;
! else
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
break;
***************
*** 4216,4238 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
* we need to deparse that relation as a subquery so that the
* 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.
*/
! 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:
--- 4291,4301 ----
* we need to deparse that relation as a subquery so that the
* conditions can be evaluated before the join. Remember it in
* the fpinfo so that the deparser can take appropriate action.
*/
! if (fpinfo_o->reltarget_has_non_vars || fpinfo_o->remote_conds)
fpinfo_o->is_subquery_rel = true;
! if (fpinfo_i->reltarget_has_non_vars || fpinfo_i->remote_conds)
fpinfo_i->is_subquery_rel = true;
break;
default:
***************
*** 4241,4246 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4304,4322 ----
}
/*
+ * If the joining relation is deparsed as a subquery, save the relids of
+ * base relations covered by the subquery for later use by the deparser.
+ */
+ if (fpinfo_o->is_subquery_rel)
+ fpinfo->lower_subquery_rels =
+ bms_add_members(fpinfo->lower_subquery_rels,
+ outerrel->relids);
+ if (fpinfo_i->is_subquery_rel)
+ fpinfo->lower_subquery_rels =
+ bms_add_members(fpinfo->lower_subquery_rels,
+ innerrel->relids);
+
+ /*
* For an inner join, all restrictions can be treated alike. Treating the
* pushed down conditions as join conditions allows a top level full outer
* join to be deparsed without requiring subqueries.
***************
*** 4256,4261 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4332,4372 ----
fpinfo->pushdown_safe = true;
/*
+ * Detect whether expressions in the relation's reltarget are shippable
+ * and whether there are any non-Vars in those expressions.
+ *
+ * Note: whole-row Vars and system columns other than ctid and oid of a
+ * base relation are handled as output columns of a lower subquery. So
+ * no need to be careful about those columns.
+ */
+ foreach(lc, joinrel->reltarget->exprs)
+ {
+ Node *node = (Node *) lfirst(lc);
+
+ if (IsA(node, PlaceHolderVar))
+ {
+ PlaceHolderVar *phv = (PlaceHolderVar *) node;
+ PlaceHolderInfo *phinfo = find_placeholder_info(root, phv, false);
+
+ /* Ignore the PHV if it has bubbled up from an either input. */
+ if (bms_is_subset(phinfo->ph_eval_at, outerrel->relids) ||
+ bms_is_subset(phinfo->ph_eval_at, innerrel->relids))
+ continue;
+
+ reltarget_has_non_vars = true;
+
+ if (!is_foreign_expr(root, joinrel, (Expr *) node))
+ {
+ reltarget_is_shippable = false;
+ break;
+ }
+ }
+ }
+
+ fpinfo->reltarget_is_shippable = reltarget_is_shippable;
+ fpinfo->reltarget_has_non_vars = reltarget_has_non_vars;
+
+ /*
* If user is willing to estimate cost for a scan of either of the joining
* relations using EXPLAIN, he intends to estimate scans on that relation
* more accurately. Then, it makes sense to estimate the cost the join
*** a/contrib/postgres_fdw/postgres_fdw.h
--- b/contrib/postgres_fdw/postgres_fdw.h
***************
*** 33,38 **** typedef struct PgFdwRelationInfo
--- 33,61 ----
bool pushdown_safe;
/*
+ * Flags on the reltarget of the relation.
+ *
+ * If each expression in the relation's reltarget is shippable (i.e.,
+ * computable on the remote side), we set reltarget_is_shippalbe to TRUE.
+ * The flag is used to determine whether the relation can be joined with
+ * any other foreign table (or join) on the remote side. Note that Vars
+ * in the reltarget are shippable, so if any PHV in the reltarget is
+ * shippable, then the flag is set TRUE. Also, if there are any PHVs in
+ * in the reltarget, we set reltarget_has_non_vars to TRUE. In that case,
+ * we deparse the relation as a subquery emitting the PHVs when creating
+ * a remote query for doing a join of the relation with any other foreign
+ * table remotely. Note that whole-row Vars and system columns other than
+ * ctid and oid in the reltarget of a base relation are treated like PHVs,
+ * because (1) we deparse the whole-row Vars as ROW() expressions and the
+ * system columns as 0, except for tableoid, in which case it is deparsed
+ * as a valid value for the local table OID, and (2) the base table could
+ * be beneath an outer join, in which case those columns must go to NULL
+ * whenever the rest of the row does.
+ */
+ bool reltarget_is_shippable; /* are reltarget exprs shippable? */
+ bool reltarget_has_non_vars; /* are there any non-Var exprs? */
+
+ /*
* Restriction clauses, divided into safe and unsafe to pushdown subsets.
*
* For a base foreign relation this is a list of clauses along-with
***************
*** 168,174 **** extern void deparseAnalyzeSql(StringInfo buf, Relation rel,
List **retrieved_attrs);
extern void deparseStringLiteral(StringInfo buf, const char *val);
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 *rel,
List *tlist, List *remote_conds, List *pathkeys,
bool is_subquery, List **retrieved_attrs,
--- 191,197 ----
List **retrieved_attrs);
extern void deparseStringLiteral(StringInfo buf, const char *val);
extern Expr *find_em_expr_for_rel(EquivalenceClass *ec, RelOptInfo *rel);
! extern List *build_tlist_to_deparse(PlannerInfo *root, RelOptInfo *foreignrel);
extern void deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
List *tlist, List *remote_conds, List *pathkeys,
bool is_subquery, List **retrieved_attrs,
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 513,529 **** EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
! -- non-Var items in targelist of the nullable rel of a join preventing
! -- push-down in some cases
! -- unable to push {ft1, ft2}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
-
- -- ok to push {ft1, ft2} but not {ft1, ft2, ft4}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
--- 513,534 ----
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
SELECT t1."C 1" FROM "S 1"."T 1" t1, LATERAL (SELECT DISTINCT t2.c1, t3.c1 FROM ft1 t2, ft2 t3 WHERE t2.c1 = t3.c1 AND t2.c2 = t1.c2) q ORDER BY t1."C 1" OFFSET 10 LIMIT 10;
! -- check join pushdown in situations where PHVs are involved
EXPLAIN (VERBOSE, COSTS OFF)
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
SELECT q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
SELECT ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15;
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT ft2.c1, q2.* FROM ft2 LEFT JOIN (SELECT 13, q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15) q2(a2, b2, c2) ON (ft2.c1 = q2.a2) WHERE ft2.c1 BETWEEN 10 AND 15;
+ SELECT ft2.c1, q2.* FROM ft2 LEFT JOIN (SELECT 13, q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15) q2(a2, b2, c2) ON (ft2.c1 = q2.a2) WHERE ft2.c1 BETWEEN 10 AND 15;
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT ft2.c1, q2.* FROM ft2 RIGHT JOIN (SELECT 13, q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15) q2(a2, b2, c2) ON (ft2.c1 = q2.a2);
+ SELECT ft2.c1, q2.* FROM ft2 RIGHT JOIN (SELECT 13, q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.c1 BETWEEN 10 AND 15) q2(a2, b2, c2) ON (ft2.c1 = q2.a2);
+ EXPLAIN (VERBOSE, COSTS OFF)
+ SELECT ft4.c1, q2.* FROM ft4 LEFT JOIN (SELECT (q.a IS NOT NULL), ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15) q2(a2, b2, c2, d2, e2) ON (ft4.c1 = q2.b2) WHERE ft4.c1 BETWEEN 10 AND 15;
+ SELECT ft4.c1, q2.* FROM ft4 LEFT JOIN (SELECT (q.a IS NOT NULL), ft4.c1, q.* FROM ft4 LEFT JOIN (SELECT 13, ft1.c1, ft2.c1 FROM ft1 RIGHT JOIN ft2 ON (ft1.c1 = ft2.c1) WHERE ft1.c1 = 12) q(a, b, c) ON (ft4.c1 = q.b) WHERE ft4.c1 BETWEEN 10 AND 15) q2(a2, b2, c2, d2, e2) ON (ft4.c1 = q2.b2) WHERE ft4.c1 BETWEEN 10 AND 15;
-- join with nullable side with some columns with null values
UPDATE ft5 SET c3 = null where c1 % 9 = 0;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
