Hi,
Here is a patch for $subject. This is the same as what I proposed in
combination with a feature for full joins [1]; this would allow us to
push down left/right/full joins with PHVs to the remote and improve how
to deparse whole-row references. Since this is implemented on top of
the feature for full-joins (ie, the deparser logic for subqueries), I
proposed this on that thread, but this is slightly independent from that
feature (and we haven't discussed this in detail on that thread), so I
think it's better to start new thread. Attached is a new version, which
is created on top of [2]. I'll add this to the upcoming CF.
Best regards,
Etsuro Fujita
[1]
https://www.postgresql.org/message-id/c449261a-b033-dc02-9254-2fe5b7044795%40lab.ntt.co.jp
[2]
https://www.postgresql.org/message-id/920e660b-6fec-6022-759d-e96e37dd5984%40lab.ntt.co.jp
*** 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"
***************
*** 158,163 **** static void deparseRelabelType(RelabelType *node, deparse_expr_cxt *context);
--- 159,165 ----
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,
***************
*** 184,192 **** static Node *deparseSortGroupClause(Index ref, List *tlist,
/*
* Helper functions
*/
! static bool is_subquery_var(Var *node, RelOptInfo *foreignrel,
! int *relno, int *colno);
! static void get_relation_column_alias_ids(Var *node, RelOptInfo *foreignrel,
int *relno, int *colno);
--- 186,195 ----
/*
* Helper functions
*/
! static bool is_subquery_expr(Expr *node, PlannerInfo *root,
! RelOptInfo *foreignrel,
! int *relno, int *colno);
! static void get_relation_column_alias_ids(Expr *node, RelOptInfo *foreignrel,
int *relno, int *colno);
***************
*** 771,776 **** foreign_expr_walker(Node *node,
--- 774,798 ----
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:
/*
***************
*** 865,871 **** 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;
--- 887,893 ----
* foreign server.
*/
List *
! build_tlist_to_deparse(PlannerInfo *root, RelOptInfo *foreignrel)
{
List *tlist = NIL;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
***************
*** 878,892 **** 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;
}
--- 900,972 ----
return fpinfo->grouped_tlist;
/*
! * Fetch all expressions in the given relation'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;
}
***************
*** 1502,1507 **** deparseRangeTblRef(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
--- 1582,1588 ----
foreignrel->reloptkind == RELOPT_JOINREL);
Assert(fpinfo->local_conds == NIL);
+ Assert(fpinfo->reltarget_is_shippable);
/* If make_subquery is true, deparse the relation as a subquery. */
if (make_subquery)
***************
*** 1926,1934 **** 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;
--- 2007,2013 ----
{
/*
* All other system attributes are fetched as 0, except for table OID,
! * which is fetched as the local table OID.
*/
Oid fetchval = 0;
***************
*** 1938,1951 **** 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)
{
--- 2017,2023 ----
fetchval = rte->relid;
}
! appendStringInfo(buf, "%u", fetchval);
}
else if (varattno == 0)
{
***************
*** 1976,2003 **** 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);
}
--- 2048,2058 ----
***************
*** 2170,2175 **** deparseExpr(Expr *node, deparse_expr_cxt *context)
--- 2225,2233 ----
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));
***************
*** 2200,2206 **** deparseVar(Var *node, deparse_expr_cxt *context)
* subquery, use the relation and column alias to the Var provided
* by the subquery, instead of the remote name.
*/
! if (is_subquery_var(node, context->scanrel, &relno, &colno))
{
appendStringInfo(context->buf, "%s%d.%s%d",
SUBQUERY_REL_ALIAS_PREFIX, relno,
--- 2258,2265 ----
* subquery, use the relation and column alias to the Var provided
* by the subquery, instead of the remote name.
*/
! if (is_subquery_expr((Expr *) node, context->root, context->scanrel,
! &relno, &colno))
{
appendStringInfo(context->buf, "%s%d.%s%d",
SUBQUERY_REL_ALIAS_PREFIX, relno,
***************
*** 2894,2899 **** appendAggOrderBy(List *orderList, List *targetList, deparse_expr_cxt *context)
--- 2953,2983 ----
}
/*
+ * 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 foreign 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",
+ SUBQUERY_REL_ALIAS_PREFIX, relno,
+ SUBQUERY_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
***************
*** 3088,3103 **** deparseSortGroupClause(Index ref, List *tlist, deparse_expr_cxt *context)
/*
! * Returns true if given Var is deparsed as a subquery output column, in
! * which case, *relno and *colno are set to the IDs for the relation and
! * column alias to the Var provided by the subquery.
*/
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;
/* Should only be called in these cases. */
Assert(foreignrel->reloptkind == RELOPT_BASEREL ||
--- 3172,3189 ----
/*
! * Returns true if given expression is deparsed as a subquery output column,
! * in which case, *relno and *colno are set to the IDs for the relation and
! * column alias to the expression provided by the subquery.
*/
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;
/* Should only be called in these cases. */
Assert(foreignrel->reloptkind == RELOPT_BASEREL ||
***************
*** 3111,3128 **** is_subquery_var(Var *node, RelOptInfo *foreignrel, int *relno, int *colno)
if (foreignrel->reloptkind != RELOPT_JOINREL)
return false;
! /*
! * If the Var doesn't belong to any lower subqueries, it isn't a subquery
! * output column.
! */
! if (!bms_is_member(node->varno, fpinfo->lower_subquery_rels))
! return false;
! if (bms_is_member(node->varno, outerrel->relids))
{
/*
! * If outer relation is deparsed as a subquery, the Var is an output
! * column of the subquery; get the IDs for the relation/column alias.
*/
if (fpinfo->make_outerrel_subquery)
{
--- 3197,3239 ----
if (foreignrel->reloptkind != RELOPT_JOINREL)
return false;
! if (IsA(node, Var))
! {
! Var *var = (Var *) node;
!
! /*
! * The Var not belonging to any lower subqueries isn't a subquery
! * output column.
! */
! 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);
!
! is_outer_var = bms_is_subset(phinfo->ph_eval_at, outerrel->relids);
! /*
! * The PHV computed here but not in either input isn't a subquery
! * output column.
! */
! if (!is_outer_var &&
! !bms_is_subset(phinfo->ph_eval_at, innerrel->relids))
! return false;
! }
! else
! elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
!
! if (is_outer_var)
{
/*
! * If outer relation is deparsed as a subquery, the Var/PHV is an
! * output column of the subquery; get the IDs for the relation/column
! * alias.
*/
if (fpinfo->make_outerrel_subquery)
{
***************
*** 3131,3145 **** 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
{
- Assert(bms_is_member(node->varno, innerrel->relids));
-
/*
! * If inner relation is deparsed as a subquery, the Var is an output
! * column of the subquery; get the IDs for the relation/column alias.
*/
if (fpinfo->make_innerrel_subquery)
{
--- 3242,3255 ----
}
/* Otherwise, recurse into the outer relation. */
! return is_subquery_expr(node, root, outerrel, relno, colno);
}
else
{
/*
! * If inner relation is deparsed as a subquery, the Var/PHV is an
! * output column of the subquery; get the IDs for the relation/column
! * alias.
*/
if (fpinfo->make_innerrel_subquery)
{
***************
*** 3148,3169 **** 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);
}
}
/*
! * Get the IDs for the relation and column alias to given Var belonging to
! * given relation, which are returned into *relno and *colno.
*/
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;
--- 3258,3282 ----
}
/* Otherwise, recurse into the inner relation. */
! return is_subquery_expr(node, root, innerrel, relno, colno);
}
}
/*
! * Get the IDs for the relation and column alias to given expression
! * belonging to given relation, which are returned into *relno and *colno.
*/
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;
+ if (!IsA(node, Var) && !IsA(node, PlaceHolderVar))
+ elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
+
/* Get the relation alias ID */
*relno = fpinfo->relation_index;
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 1619,1626 **** 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
--- 1619,1626 ----
-- 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
***************
*** 1628,1634 **** 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)
--- 1628,1634 ----
-> 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)
***************
*** 1663,1670 **** 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
--- 1663,1670 ----
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
***************
*** 1672,1678 **** 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)
--- 1672,1678 ----
-> 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)
***************
*** 1708,1715 **** 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
--- 1708,1715 ----
-- 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
***************
*** 1717,1723 **** 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)
--- 1717,1723 ----
-> 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)
***************
*** 1752,1759 **** 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
--- 1752,1759 ----
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
***************
*** 1761,1767 **** 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)
--- 1761,1767 ----
-> 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)
***************
*** 1831,1844 **** 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
--- 1831,1844 ----
-- 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
***************
*** 2167,2191 **** 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
--- 2167,2182 ----
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
***************
*** 2198,2221 **** 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
--- 2189,2203 ----
| 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
***************
*** 2225,2240 **** 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;
--- 2207,2287 ----
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;
***************
*** 4136,4149 **** 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)
--- 4183,4196 ----
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)
***************
*** 4279,4292 **** 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)
--- 4326,4339 ----
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"
***************
*** 403,408 **** static HeapTuple make_tuple_from_result_row(PGresult *res,
--- 404,413 ----
ForeignScanState *fsstate,
MemoryContext temp_context);
static void conversion_error_callback(void *arg);
+ static void check_reltarget_exprs(PlannerInfo *root,
+ RelOptInfo *foreignrel,
+ bool *is_shippable_p,
+ bool *has_non_vars_p);
static bool foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel,
JoinType jointype, RelOptInfo *outerrel, RelOptInfo *innerrel,
JoinPathExtraData *extra);
***************
*** 581,586 **** postgresGetForeignRelSize(PlannerInfo *root,
--- 586,596 ----
&fpinfo->attrs_used);
}
+ /* Initialize reltarget-related flags. */
+ fpinfo->reltarget_is_shippable = true;
+ fpinfo->reltarget_has_non_vars = false;
+ fpinfo->reltarget_is_checked = false;
+
/*
* Compute the selectivity and cost of the local_conds, so we don't have
* to do it over again for each path. The best we can do for these
***************
*** 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
--- 1208,1214 ----
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;
--- 2548,2554 ----
/* 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;
***************
*** 4038,4043 **** postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
--- 4048,4148 ----
return commands;
}
+
+ /*
+ * Determine whether expressions in the given relation's reltarget are
+ * shippable and whether there are any non-Vars in those expressions.
+ */
+ static void
+ check_reltarget_exprs(PlannerInfo *root,
+ RelOptInfo *foreignrel,
+ bool *is_shippable_p,
+ bool *has_non_vars_p)
+ {
+ bool is_shippable = true;
+ bool has_non_vars = false;
+
+ if (foreignrel->reloptkind == RELOPT_JOINREL)
+ {
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ RelOptInfo *outerrel = fpinfo->outerrel;
+ RelOptInfo *innerrel = fpinfo->innerrel;
+ ListCell *lc;
+
+ /*
+ * Check to see if any PHVs are requested from the joinrel.
+ */
+ foreach(lc, foreignrel->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;
+ has_non_vars = true;
+ if (!is_foreign_expr(root, foreignrel, (Expr *) node))
+ {
+ is_shippable = false;
+ break;
+ }
+ }
+ }
+ }
+ else
+ {
+ ListCell *lc;
+
+ Assert(foreignrel->reloptkind == RELOPT_BASEREL);
+
+ /*
+ * 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, foreignrel->relids) &&
+ bms_is_subset(phinfo->ph_eval_at, foreignrel->relids))
+ {
+ has_non_vars = true;
+ if (!is_foreign_expr(root, foreignrel, (Expr *) phinfo->ph_var))
+ {
+ 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 (!has_non_vars)
+ {
+ int i;
+
+ Assert(is_shippable);
+
+ for (i = foreignrel->min_attr; i <= 0; i++)
+ {
+ if (i == SelfItemPointerAttributeNumber ||
+ i == ObjectIdAttributeNumber)
+ continue;
+ if (!bms_is_empty(foreignrel->attr_needed[i - foreignrel->min_attr]))
+ has_non_vars = true;
+ }
+ }
+ }
+ *is_shippable_p = is_shippable;
+ *has_non_vars_p = has_non_vars;
+ }
+
/*
* Assess whether the join between inner and outer relations can be pushed down
* to the foreign server. As a side effect, save information we obtain in this
***************
*** 4083,4088 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4188,4220 ----
if (fpinfo_o->local_conds || fpinfo_i->local_conds)
return false;
+ /*
+ * Check to see if the reltargets of joining relations are shippable,
+ * if necessary.
+ */
+ if (!fpinfo_o->reltarget_is_checked)
+ {
+ check_reltarget_exprs(root, outerrel,
+ &fpinfo_o->reltarget_is_shippable,
+ &fpinfo_o->reltarget_has_non_vars);
+ fpinfo_o->reltarget_is_checked = true;
+ }
+ if (!fpinfo_i->reltarget_is_checked)
+ {
+ check_reltarget_exprs(root, innerrel,
+ &fpinfo_i->reltarget_is_shippable,
+ &fpinfo_i->reltarget_has_non_vars);
+ fpinfo_i->reltarget_is_checked = true;
+ }
+
+ /*
+ * 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;
--- 4240,4245 ----
***************
*** 4184,4210 **** 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;
--- 4296,4347 ----
*
* The joining sides can not have local conditions, thus no need to test
* shippability of the clauses being pulled up.
+ *
+ * If the input relation has reltarget_has_non_vars=TRUE, we deparse it
+ * as a subquery. Remember that in the fpinfo of this relation so that
+ * the deparser can take appropriate action. In that case don't pull up
+ * the remote_conds of the input relation into the joinclauses or
+ * remote_conds of this relation because the remote_conds is transformed
+ * into the WHERE clause of the subquery representing the input relation.
*/
switch (jointype)
{
case JOIN_INNER:
! if (fpinfo_i->reltarget_has_non_vars)
! fpinfo->make_innerrel_subquery = true;
! else
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
! if (fpinfo_o->reltarget_has_non_vars)
! fpinfo->make_outerrel_subquery = 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->make_innerrel_subquery = true;
! else
! fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_i->remote_conds));
! if (fpinfo_o->reltarget_has_non_vars)
! fpinfo->make_outerrel_subquery = 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->make_outerrel_subquery = true;
! else
! fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
list_copy(fpinfo_o->remote_conds));
! if (fpinfo_i->reltarget_has_non_vars)
! fpinfo->make_innerrel_subquery = true;
! else
! fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
list_copy(fpinfo_i->remote_conds));
break;
***************
*** 4215,4237 **** 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 of this relation so that the deparser can take
! * appropriate action. Also, save the relids of base relations
! * covered by that relation for later use by the deparser.
! */
! if (fpinfo_o->remote_conds)
! {
fpinfo->make_outerrel_subquery = true;
! fpinfo->lower_subquery_rels =
! bms_add_members(fpinfo->lower_subquery_rels,
! outerrel->relids);
! }
! if (fpinfo_i->remote_conds)
! {
fpinfo->make_innerrel_subquery = true;
- fpinfo->lower_subquery_rels =
- bms_add_members(fpinfo->lower_subquery_rels,
- innerrel->relids);
- }
break;
default:
--- 4352,4363 ----
* we need to deparse that relation as a subquery so that the
* conditions can be evaluated before the join. Remember it in
* the fpinfo of this relation so that the deparser can take
! * appropriate action.
! */
! if (fpinfo_o->remote_conds || fpinfo_o->reltarget_has_non_vars)
fpinfo->make_outerrel_subquery = true;
! if (fpinfo_i->remote_conds || fpinfo_i->reltarget_has_non_vars)
fpinfo->make_innerrel_subquery = true;
break;
default:
***************
*** 4240,4245 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
--- 4366,4382 ----
}
/*
+ * If the input relation is deparsed as a subquery, save the relids of
+ * the relation for later use by the deparser.
+ */
+ if (fpinfo->make_outerrel_subquery)
+ fpinfo->lower_subquery_rels =
+ bms_add_members(fpinfo->lower_subquery_rels, outerrel->relids);
+ if (fpinfo->make_innerrel_subquery)
+ 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.
***************
*** 4432,4437 **** postgresGetForeignJoinPaths(PlannerInfo *root,
--- 4569,4579 ----
return;
}
+ /* Initialize reltarget-related flags. */
+ fpinfo->reltarget_is_shippable = true;
+ fpinfo->reltarget_has_non_vars = false;
+ fpinfo->reltarget_is_checked = false;
+
/*
* Compute the selectivity and cost of the local_conds, so we don't have
* to do it over again for each path. The best we can do for these
*** a/contrib/postgres_fdw/postgres_fdw.h
--- b/contrib/postgres_fdw/postgres_fdw.h
***************
*** 51,56 **** typedef struct PgFdwRelationInfo
--- 51,73 ----
/* Bitmap of attr numbers we need to fetch from the remote server. */
Bitmapset *attrs_used;
+ /*
+ * Flags on the reltarget of the relation.
+ *
+ * These flags are used to determine whether the relation can be joined
+ * with any other foreign table (or join) on the remote side, and if so
+ * whether it should be deparsed as a subquery when creating a remote
+ * query. If the reltarget contains PHVs and the relation is joined,
+ * it's deparsed as a subquery emitting the PHVs. Whole-row Vars and
+ * system columns other than ctid and oid for base relations are treated
+ * like PHVs, because we deparse whole-row Vars as ROW() expressions and
+ * system columns as 0, except for tableoid, which is deparsed as a valid
+ * value for the local table OID.
+ */
+ bool reltarget_is_shippable; /* are reltarget exprs shippable? */
+ bool reltarget_has_non_vars; /* are there any non-Var exprs? */
+ bool reltarget_is_checked; /* are reltarget exprs checked? */
+
/* Cost and selectivity of local_conds. */
QualCost local_conds_cost;
Selectivity local_conds_sel;
***************
*** 172,178 **** 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 *foreignrel, List *tlist,
List *remote_conds, List *pathkeys, bool is_subquery,
--- 189,195 ----
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 *foreignrel, List *tlist,
List *remote_conds, List *pathkeys, bool is_subquery,
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 510,526 **** 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;
--- 510,531 ----
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
