On 2016/04/14 15:20, Ashutosh Bapat wrote:
On Thu, Apr 14, 2016 at 8:42 AM, Etsuro Fujita
<fujita.ets...@lab.ntt.co.jp <mailto:fujita.ets...@lab.ntt.co.jp>> wrote:
As you mentioned, we could
support FULL JOIN fully, by encapsulating a joining relation with
conditions into a subquery. And ISTM that it is relatively easy to
do that, by borrowing some ideas from Hanada-san's original join
pushdown patch. If it's OK, I'll create a patch for that in a few days.
In his patch the deparsing targetlist and conditions required that the
FROM clause entries were ready with the columns from base relations and
joins realiased. That's no more true. We deparse every Var node as
<relation alias>.<column name> where relation alias is nothing but rN; N
being index of RangeTblEntry. So, Hanada-san's method to deparse
recursively can not be applied as such now.
I think so, too. I don't think his ideas could be applied as is.
Here's what needs to be done:
When we identify that certain relation (base or join) needs a subquery
to be deparsed (because the join relation above it could not pull the
quals up), we remember it in the upper join relation. Before deparsing
1. we walk the join tree and collect targetlists of all such relations,
2. associate column aliases with those targetlists (save the column
alises in resname?) and craft a relation alias 3. associate the
relations alias, column aliases and targetlists with the base relations
involved in such relations (may be creating a list similar to rtable).
While deparsing a Var node, we check if corresponding base relation is
itself or part of a relation deparsed as a subquery. If it is then we
lookup that Var in the targetlist associated with the base relation and
use corresponding relation and column alias for deparsing it. Otherwise,
deparse it as <relation alias>.<column name> usually.
Good to know. That is what I have in mind, except for the way of
collecting subqueries' columns and associating those columns with
relation and column aliases, which I think can be done more easily.
Please find attached a WIP patch. That patch works well at least for
queries in your patch. Maybe I'm missing something, though.
That looks like a big code change to go after feature freeze. So, we
will leave it for 9.7, unless RMT allows us to introduce that change.
OK
Best regards,
Etsuro Fujita
*** a/contrib/postgres_fdw/deparse.c
--- b/contrib/postgres_fdw/deparse.c
***************
*** 88,93 **** typedef struct foreign_loc_cxt
--- 88,105 ----
} foreign_loc_cxt;
/*
+ * Structure for information on subqueries' column aliases
+ */
+ typedef struct ColumnAliases
+ {
+ List *exprs; /* subqueries' columns */
+ int max_exprs; /* maximum number of columns stored */
+ int *ssno; /* table alias numbers of columns */
+ int *sscolno; /* column alias numbers of columns */
+ int next_ssno; /* next subquery's table alias number */
+ } ColumnAliases;
+
+ /*
* Context for deparseExpr
*/
typedef struct deparse_expr_cxt
***************
*** 96,101 **** typedef struct deparse_expr_cxt
--- 108,114 ----
RelOptInfo *foreignrel; /* the foreign relation we are planning for */
StringInfo buf; /* output buffer to append to */
List **params_list; /* exprs that will become remote Params */
+ ColumnAliases *colaliases; /* subqueries' column aliases */
} deparse_expr_cxt;
#define REL_ALIAS_PREFIX "r"
***************
*** 103,108 **** typedef struct deparse_expr_cxt
--- 116,124 ----
#define ADD_REL_QUALIFIER(buf, varno) \
appendStringInfo((buf), "%s%d.", REL_ALIAS_PREFIX, (varno))
+ #define SS_ALIAS_PREFIX "ss"
+ #define SSCOL_ALIAS_PREFIX "c"
+
/*
* Functions to determine whether an expression can be evaluated safely on
* remote server.
***************
*** 152,164 **** static void printRemoteParam(int paramindex, Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
! static void deparseSelectSql(List *tlist, List **retrieved_attrs,
deparse_expr_cxt *context);
static void deparseLockingClause(deparse_expr_cxt *context);
static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context);
static void appendConditions(List *exprs, deparse_expr_cxt *context);
static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root,
! RelOptInfo *joinrel, bool use_alias, List **params_list);
/*
--- 168,188 ----
deparse_expr_cxt *context);
static void printRemotePlaceholder(Oid paramtype, int32 paramtypmod,
deparse_expr_cxt *context);
! static void deparseSelectSql(List *tlist,
! List *remote_conds,
! List **retrieved_attrs,
deparse_expr_cxt *context);
static void deparseLockingClause(deparse_expr_cxt *context);
static void appendOrderByClause(List *pathkeys, deparse_expr_cxt *context);
static void appendConditions(List *exprs, deparse_expr_cxt *context);
static void deparseFromExprForRel(StringInfo buf, PlannerInfo *root,
! RelOptInfo *joinrel, bool use_alias,
! List **params_list, ColumnAliases *colaliases);
! static void appendSubselectAlias(List *exprs, deparse_expr_cxt *context);
! static void updateColumnAliases(ColumnAliases *colaliases, Expr *expr,
! int ssno, int sscolno);
! static bool hasColumnAlias(ColumnAliases *colaliases, Expr *node,
! int *ssno, int *sscolno);
/*
***************
*** 764,769 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
--- 788,794 ----
List *tlist, List *remote_conds, List *pathkeys,
List **retrieved_attrs, List **params_list)
{
+ ColumnAliases colaliases;
deparse_expr_cxt context;
/* We handle relations for foreign tables and joins between those */
***************
*** 771,793 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
rel->reloptkind == RELOPT_BASEREL ||
rel->reloptkind == RELOPT_OTHER_MEMBER_REL);
/* Fill portions of context common to join and base relation */
context.buf = buf;
context.root = root;
context.foreignrel = rel;
context.params_list = params_list;
! /* Construct SELECT clause and FROM clause */
! deparseSelectSql(tlist, retrieved_attrs, &context);
!
! /*
! * Construct WHERE clause
! */
! if (remote_conds)
! {
! appendStringInfo(buf, " WHERE ");
! appendConditions(remote_conds, &context);
! }
/* Add ORDER BY clause if we found any useful pathkeys */
if (pathkeys)
--- 796,817 ----
rel->reloptkind == RELOPT_BASEREL ||
rel->reloptkind == RELOPT_OTHER_MEMBER_REL);
+ /* Initialize subqueries' column aliases */
+ colaliases.exprs = NIL;
+ colaliases.max_exprs = 32;
+ colaliases.ssno = (int *) palloc(colaliases.max_exprs * sizeof(int));
+ colaliases.sscolno = (int *) palloc(colaliases.max_exprs * sizeof(int));
+ colaliases.next_ssno = 1;
+
/* Fill portions of context common to join and base relation */
context.buf = buf;
context.root = root;
context.foreignrel = rel;
context.params_list = params_list;
+ context.colaliases = &colaliases;
! /* Construct SELECT clause and FROM clause and WHERE clause */
! deparseSelectSql(tlist, remote_conds, retrieved_attrs, &context);
/* Add ORDER BY clause if we found any useful pathkeys */
if (pathkeys)
***************
*** 800,806 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
/*
* Construct a simple SELECT statement that retrieves desired columns
* of the specified foreign table, and append it to "buf". The output
! * contains just "SELECT ... FROM ....".
*
* We also create an integer List of the columns being retrieved, which is
* returned to *retrieved_attrs.
--- 824,830 ----
/*
* Construct a simple SELECT statement that retrieves desired columns
* of the specified foreign table, and append it to "buf". The output
! * contains just "SELECT ... FROM ... WHERE ...".
*
* We also create an integer List of the columns being retrieved, which is
* returned to *retrieved_attrs.
***************
*** 809,820 **** deparseSelectStmtForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *rel,
* deparseSelectStmtForRel() for details.
*/
static void
! deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
RelOptInfo *foreignrel = context->foreignrel;
PlannerInfo *root = context->root;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
/*
* Construct SELECT list
--- 833,856 ----
* deparseSelectStmtForRel() for details.
*/
static void
! deparseSelectSql(List *tlist,
! List *remote_conds,
! List **retrieved_attrs,
! deparse_expr_cxt *context)
{
StringInfo buf = context->buf;
RelOptInfo *foreignrel = context->foreignrel;
PlannerInfo *root = context->root;
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
+ StringInfoData jointree;
+
+ /*
+ * Deparse a join tree expression in FROM clause first.
+ */
+ initStringInfo(&jointree);
+ deparseFromExprForRel(&jointree, root, foreignrel,
+ (foreignrel->reloptkind == RELOPT_JOINREL),
+ context->params_list, context->colaliases);
/*
* Construct SELECT list
***************
*** 848,857 **** deparseSelectSql(List *tlist, List **retrieved_attrs, deparse_expr_cxt *context)
/*
* Construct FROM clause
*/
! appendStringInfoString(buf, " FROM ");
! deparseFromExprForRel(buf, root, foreignrel,
! (foreignrel->reloptkind == RELOPT_JOINREL),
! context->params_list);
}
/*
--- 884,899 ----
/*
* Construct FROM clause
*/
! appendStringInfo(buf, " FROM %s", jointree.data);
!
! /*
! * Construct WHERE clause
! */
! if (remote_conds)
! {
! appendStringInfoString(buf, " WHERE ");
! appendConditions(remote_conds, context);
! }
}
/*
***************
*** 1117,1123 **** deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
if (i > 0)
appendStringInfoString(buf, ", ");
! deparseVar(var, context);
*retrieved_attrs = lappend_int(*retrieved_attrs, i + 1);
--- 1159,1165 ----
if (i > 0)
appendStringInfoString(buf, ", ");
! deparseExpr((Expr *) var, context);
*retrieved_attrs = lappend_int(*retrieved_attrs, i + 1);
***************
*** 1137,1143 **** deparseExplicitTargetList(List *tlist, List **retrieved_attrs,
*/
static void
deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
! bool use_alias, List **params_list)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
--- 1179,1185 ----
*/
static void
deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
! bool use_alias, List **params_list, ColumnAliases *colaliases)
{
PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) foreignrel->fdw_private;
***************
*** 1145,1160 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
{
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
--- 1187,1250 ----
{
RelOptInfo *rel_o = fpinfo->outerrel;
RelOptInfo *rel_i = fpinfo->innerrel;
+ PgFdwRelationInfo *fpinfo_o = (PgFdwRelationInfo *) rel_o->fdw_private;
+ PgFdwRelationInfo *fpinfo_i = (PgFdwRelationInfo *) rel_i->fdw_private;
StringInfoData join_sql_o;
StringInfoData join_sql_i;
/* Deparse outer relation */
initStringInfo(&join_sql_o);
! if (fpinfo->jointype == JOIN_FULL && fpinfo_o->remote_conds)
! {
! List *tlist = NIL;
! List *retrieved_attrs = NIL;
! deparse_expr_cxt context;
!
! context.buf = &join_sql_o;
! context.root = root;
! context.foreignrel = rel_o;
! context.params_list = params_list;
! context.colaliases = colaliases;
!
! tlist = add_to_flat_tlist(tlist, rel_o->reltarget->exprs);
! appendStringInfoChar(&join_sql_o, '(');
! deparseSelectSql(tlist,
! fpinfo_o->remote_conds,
! &retrieved_attrs,
! &context);
! appendStringInfoChar(&join_sql_o, ')');
! appendSubselectAlias(rel_o->reltarget->exprs, &context);
! }
! else
! deparseFromExprForRel(&join_sql_o, root, rel_o, true, params_list,
! colaliases);
/* Deparse inner relation */
initStringInfo(&join_sql_i);
! if (fpinfo->jointype == JOIN_FULL && fpinfo_i->remote_conds)
! {
! List *tlist = NIL;
! List *retrieved_attrs = NIL;
! deparse_expr_cxt context;
!
! context.buf = &join_sql_i;
! context.root = root;
! context.foreignrel = rel_i;
! context.params_list = params_list;
! context.colaliases = colaliases;
!
! tlist = add_to_flat_tlist(tlist, rel_o->reltarget->exprs);
! appendStringInfoChar(&join_sql_i, '(');
! deparseSelectSql(tlist,
! fpinfo_i->remote_conds,
! &retrieved_attrs,
! &context);
! appendStringInfoChar(&join_sql_i, ')');
! appendSubselectAlias(rel_i->reltarget->exprs, &context);
! }
! else
! deparseFromExprForRel(&join_sql_i, root, rel_i, true, params_list,
! colaliases);
/*
* For a join relation FROM clause entry is deparsed as
***************
*** 1173,1178 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
--- 1263,1269 ----
context.foreignrel = foreignrel;
context.root = root;
context.params_list = params_list;
+ context.colaliases = colaliases;
appendStringInfo(buf, "(");
appendConditions(fpinfo->joinclauses, &context);
***************
*** 1206,1212 **** deparseFromExprForRel(StringInfo buf, PlannerInfo *root, RelOptInfo *foreignrel,
heap_close(rel, NoLock);
}
! return;
}
/*
--- 1297,1396 ----
heap_close(rel, NoLock);
}
! }
!
! static void
! appendSubselectAlias(List *exprs, deparse_expr_cxt *context)
! {
! StringInfo buf = context->buf;
! ColumnAliases *colaliases = context->colaliases;
! int ssno;
! int sscolno;
! bool first;
! ListCell *lc;
!
! /* Append a table alias */
! ssno = colaliases->next_ssno;
! appendStringInfo(buf, " %s%d", SS_ALIAS_PREFIX, ssno);
!
! /* Append column aliases */
! sscolno = 1;
! first = true;
! appendStringInfoChar(buf, '(');
! foreach(lc, exprs)
! {
! Expr *expr = (Expr *) lfirst(lc);
!
! if (!first)
! appendStringInfoString(buf, ", ");
! first = false;
!
! appendStringInfo(buf, "%s%d", SSCOL_ALIAS_PREFIX, sscolno);
!
! updateColumnAliases(colaliases, expr, ssno, sscolno);
!
! sscolno++;
! }
! appendStringInfoChar(buf, ')');
!
! colaliases->next_ssno++;
! }
!
! static void
! updateColumnAliases(ColumnAliases *colaliases, Expr *expr,
! int ssno, int sscolno)
! {
! int num_exprs = list_length(colaliases->exprs);
! int max_exprs = colaliases->max_exprs;
! int i;
! ListCell *lc;
!
! i = 0;
! foreach(lc, colaliases->exprs)
! {
! if (equal(lfirst(lc), (Node *) expr))
! {
! colaliases->ssno[i] = ssno;
! colaliases->sscolno[i] = sscolno;
! return;
! }
! i++;
! }
! Assert(i == num_exprs);
!
! colaliases->exprs = lappend(colaliases->exprs, expr);
! if (num_exprs + 1 >= max_exprs)
! {
! max_exprs *= 2;
! colaliases->ssno = (int *) repalloc(colaliases->ssno,
! max_exprs * sizeof(int));
! colaliases->sscolno = (int *) repalloc(colaliases->sscolno,
! max_exprs * sizeof(int));
! colaliases->max_exprs = max_exprs;
! }
! colaliases->ssno[num_exprs] = ssno;
! colaliases->sscolno[num_exprs] = sscolno;
! }
!
! static bool
! hasColumnAlias(ColumnAliases *colaliases, Expr *node,
! int *ssno, int *sscolno)
! {
! int i;
! ListCell *lc;
!
! i = 0;
! foreach(lc, colaliases->exprs)
! {
! if (equal(lfirst(lc), (Node *) node))
! {
! *ssno = colaliases->ssno[i];
! *sscolno = colaliases->sscolno[i];
! return true;
! }
! i++;
! }
! return false;
}
/*
***************
*** 1342,1347 **** deparseDirectUpdateSql(StringInfo buf, PlannerInfo *root,
--- 1526,1532 ----
context.foreignrel = baserel;
context.buf = buf;
context.params_list = params_list;
+ context.colaliases = NULL;
appendStringInfoString(buf, "UPDATE ");
deparseRelation(buf, rel);
***************
*** 1426,1431 **** deparseDirectDeleteSql(StringInfo buf, PlannerInfo *root,
--- 1611,1617 ----
context.foreignrel = baserel;
context.buf = buf;
context.params_list = params_list;
+ context.colaliases = NULL;
appendStringInfoString(buf, "DELETE FROM ");
deparseRelation(buf, rel);
***************
*** 1741,1746 **** deparseExpr(Expr *node, deparse_expr_cxt *context)
--- 1927,1946 ----
if (node == NULL)
return;
+ if (context->colaliases)
+ {
+ int ssno;
+ int sscolno;
+
+ if (hasColumnAlias(context->colaliases, node, &ssno, &sscolno))
+ {
+ appendStringInfo(context->buf, "%s%d.%s%d",
+ SS_ALIAS_PREFIX, ssno,
+ SSCOL_ALIAS_PREFIX, sscolno);
+ return;
+ }
+ }
+
switch (nodeTag(node))
{
case T_Var:
*** a/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 1144,1149 **** SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.
--- 1144,1201 ----
| 27
(10 rows)
+ -- full outer join with restrictions on the joining relations
+ EXPLAIN (COSTS false, VERBOSE)
+ 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 ss1.c1, ss2.c1 FROM ((SELECT c1 FROM "S 1"."T 3" WHERE ((c1 >= 50)) AND ((c1 <= 60))) ss1(c1) FULL JOIN (SELECT c1 FROM "S 1"."T 4" WHERE ((c1 >= 50)) AND ((c1 <= 60))) ss2(c1) ON (((ss1.c1 = ss2.c1)))) ORDER BY ss1.c1 ASC NULLS LAST, ss2.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
+ ----+----
+ 50 |
+ 52 |
+ 54 | 54
+ 56 |
+ 58 |
+ 60 | 60
+ | 51
+ | 57
+ (8 rows)
+
+ -- full outer join + inner join
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ QUERY PLAN
+ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
+ Output: t1.c1, t2.c1, t3.c1
+ -> Foreign Scan
+ Output: t1.c1, t2.c1, t3.c1
+ Relations: ((public.ft4 t1) INNER JOIN (public.ft5 t2)) FULL JOIN (public.ft4 t3)
+ Remote SQL: SELECT ss1.c1, ss1.c2, r4.c1 FROM ((SELECT r1.c1, r2.c1 FROM ("S 1"."T 3" r1 INNER JOIN "S 1"."T 4" r2 ON (TRUE)) WHERE ((r1.c1 = (r2.c1 + 1))) AND ((r1.c1 >= 50)) AND ((r1.c1 <= 60))) ss1(c1, c2) FULL JOIN "S 1"."T 3" r4 ON (((ss1.c2 = r4.c1)))) ORDER BY ss1.c1 ASC NULLS LAST, ss1.c2 ASC NULLS LAST, r4.c1 ASC NULLS LAST
+ (6 rows)
+
+ SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ c1 | c1 | c1
+ ----+----+----
+ 52 | 51 |
+ 58 | 57 |
+ | | 2
+ | | 4
+ | | 6
+ | | 8
+ | | 10
+ | | 12
+ | | 14
+ | | 16
+ (10 rows)
+
-- full outer join three tables
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
*** a/contrib/postgres_fdw/postgres_fdw.c
--- b/contrib/postgres_fdw/postgres_fdw.c
***************
*** 4073,4082 **** foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
break;
case JOIN_FULL:
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
- list_copy(fpinfo_i->remote_conds));
- fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
- list_copy(fpinfo_o->remote_conds));
break;
default:
--- 4073,4078 ----
*** a/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 372,377 **** SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGH
--- 372,385 ----
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10;
+ -- full outer join with restrictions on the joining relations
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+ SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 BETWEEN 50 AND 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 BETWEEN 50 AND 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;
+ -- full outer join + inner join
+ EXPLAIN (COSTS false, VERBOSE)
+ SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
+ SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 AND t1.c1 BETWEEN 50 AND 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10;
-- full outer join three tables
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
