(2018/07/04 11:00), Etsuro Fujita wrote:
(2018/07/04 1:35), Andres Freund wrote:
What's the plan forward here?
I still think that this approach would be the right way to go, so I plan
to polish the patch.
The approach would not require extra cycles where partitioning is not
involved as discussed before, and makes code much simpler, which would
make maintenance and the FDW author's life easy, so I still think that
the approach would be the direction to go in. So, I updated the patch.
Here are changes:
* I assumed that the child plan passed to adjust_subplan_tlist was a
scan or join unless it was Append or MergeAppend, but I noticed I was
wrong; if the topmost scan/join relation is partitioned,
apply_scanjoin_target_to_paths might have pushed down ProjectSet or
Result to each of partitions. So, in that case the child's plan would
have ProjectSet or Result atop the scan or join plan. (I could not
produce such an example, though.) To address this, I modified
build_childrel_tlist so that we just apply adjust_appendrel_attrs to the
parent's targetlist to get the child's if the parent is the topmost
scan/join relation. So, we don't need to care about that in
adjust_subplan_tlist. I think this change would also increase the
efficiency for that case. I added regression test cases for that as well.
* I fixed an oversight in outfuncs.c.
* I renamed member variables I added to RelOptInfo before, and revised
code/comments a bit.
Attached is an updated version of the patch.
Best regards,
Etsuro Fujita
*** a/postgres-master/contrib/postgres_fdw/expected/postgres_fdw.out
--- b/postgres-master/contrib/postgres_fdw/expected/postgres_fdw.out
***************
*** 8337,8344 **** ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false');
ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
! CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (0) TO (250)
SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true');
CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500)
SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true');
ANALYZE fprt2;
--- 8337,8345 ----
ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
! CREATE FOREIGN TABLE ftprt2_p1 (b int, c varchar, a int)
SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true');
+ ALTER TABLE fprt2 ATTACH PARTITION ftprt2_p1 FOR VALUES FROM (0) TO (250);
CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500)
SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true');
ANALYZE fprt2;
***************
*** 8391,8416 **** SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10)
-- with whole-row reference
EXPLAIN (COSTS OFF)
! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2;
! QUERY PLAN
! ---------------------------------------------------------------------------------
Sort
Sort Key: ((t1.*)::fprt1), ((t2.*)::fprt2)
-> Append
-> Foreign Scan
! Relations: (public.ftprt1_p1 t1) INNER JOIN (public.ftprt2_p1 t2)
-> Foreign Scan
! Relations: (public.ftprt1_p2 t1) INNER JOIN (public.ftprt2_p2 t2)
(7 rows)
! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2;
! t1 | t2
----------------+----------------
(0,0,0000) | (0,0,0000)
(150,150,0003) | (150,150,0003)
(250,250,0005) | (250,250,0005)
(400,400,0008) | (400,400,0008)
! (4 rows)
-- join with lateral reference
EXPLAIN (COSTS OFF)
--- 8392,8427 ----
-- with whole-row reference
EXPLAIN (COSTS OFF)
! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
! QUERY PLAN
! --------------------------------------------------------------------------------
Sort
Sort Key: ((t1.*)::fprt1), ((t2.*)::fprt2)
-> Append
-> Foreign Scan
! Relations: (public.ftprt1_p1 t1) FULL JOIN (public.ftprt2_p1 t2)
-> Foreign Scan
! Relations: (public.ftprt1_p2 t1) FULL JOIN (public.ftprt2_p2 t2)
(7 rows)
! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
! wr | wr
----------------+----------------
(0,0,0000) | (0,0,0000)
+ (50,50,0001) |
+ (100,100,0002) |
(150,150,0003) | (150,150,0003)
+ (200,200,0004) |
(250,250,0005) | (250,250,0005)
+ (300,300,0006) |
+ (350,350,0007) |
(400,400,0008) | (400,400,0008)
! (450,450,0009) |
! | (75,75,0001)
! | (225,225,0004)
! | (325,325,0006)
! | (475,475,0009)
! (14 rows)
-- join with lateral reference
EXPLAIN (COSTS OFF)
***************
*** 8474,8479 **** SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE
--- 8485,8520 ----
| | 475 | t2_phv
(14 rows)
+ -- test FOR UPDATE
+ EXPLAIN (COSTS OFF)
+ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
+ QUERY PLAN
+ ---------------------------------------------------------------------------------------
+ LockRows
+ -> Sort
+ Sort Key: t1.a
+ -> Append
+ -> Foreign Scan
+ Relations: (public.ftprt1_p1 t1) INNER JOIN (public.ftprt2_p1 t2)
+ -> Nested Loop
+ -> Foreign Scan on ftprt1_p1 t1
+ -> Foreign Scan on ftprt2_p1 t2
+ -> Foreign Scan
+ Relations: (public.ftprt1_p2 t1) INNER JOIN (public.ftprt2_p2 t2)
+ -> Nested Loop
+ -> Foreign Scan on ftprt1_p2 t1_1
+ -> Foreign Scan on ftprt2_p2 t2_1
+ (14 rows)
+
+ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
+ a | b
+ -----+-----
+ 0 | 0
+ 150 | 150
+ 250 | 250
+ 400 | 400
+ (4 rows)
+
RESET enable_partitionwise_join;
-- ===================================================================
-- test partitionwise aggregates
*** a/postgres-master/contrib/postgres_fdw/sql/postgres_fdw.sql
--- b/postgres-master/contrib/postgres_fdw/sql/postgres_fdw.sql
***************
*** 2263,2270 **** ALTER TABLE fprt2_p1 SET (autovacuum_enabled = 'false');
ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
! CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (0) TO (250)
SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true');
CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500)
SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true');
ANALYZE fprt2;
--- 2263,2271 ----
ALTER TABLE fprt2_p2 SET (autovacuum_enabled = 'false');
INSERT INTO fprt2_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 249, 3) i;
INSERT INTO fprt2_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(250, 499, 3) i;
! CREATE FOREIGN TABLE ftprt2_p1 (b int, c varchar, a int)
SERVER loopback OPTIONS (table_name 'fprt2_p1', use_remote_estimate 'true');
+ ALTER TABLE fprt2 ATTACH PARTITION ftprt2_p1 FOR VALUES FROM (0) TO (250);
CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (250) TO (500)
SERVER loopback OPTIONS (table_name 'fprt2_p2', use_remote_estimate 'true');
ANALYZE fprt2;
***************
*** 2283,2290 **** SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10)
-- with whole-row reference
EXPLAIN (COSTS OFF)
! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2;
! SELECT t1,t2 FROM fprt1 t1 JOIN fprt2 t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a % 25 =0 ORDER BY 1,2;
-- join with lateral reference
EXPLAIN (COSTS OFF)
--- 2284,2291 ----
-- with whole-row reference
EXPLAIN (COSTS OFF)
! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
! SELECT t1.wr, t2.wr FROM (SELECT t1 wr, a FROM fprt1 t1 WHERE t1.a % 25 = 0) t1 FULL JOIN (SELECT t2 wr, b FROM fprt2 t2 WHERE t2.b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY 1,2;
-- join with lateral reference
EXPLAIN (COSTS OFF)
***************
*** 2296,2301 **** EXPLAIN (COSTS OFF)
--- 2297,2307 ----
SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
SELECT t1.a, t1.phv, t2.b, t2.phv FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE a % 25 = 0) t1 FULL JOIN (SELECT 't2_phv' phv, * FROM fprt2 WHERE b % 25 = 0) t2 ON (t1.a = t2.b) ORDER BY t1.a, t2.b;
+ -- test FOR UPDATE
+ EXPLAIN (COSTS OFF)
+ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
+ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
+
RESET enable_partitionwise_join;
*** a/postgres-master/src/backend/nodes/outfuncs.c
--- b/postgres-master/src/backend/nodes/outfuncs.c
***************
*** 2357,2363 **** _outRelOptInfo(StringInfo str, const RelOptInfo *node)
--- 2357,2367 ----
WRITE_UINT_FIELD(baserestrict_min_security);
WRITE_NODE_FIELD(joininfo);
WRITE_BOOL_FIELD(has_eclass_joins);
+ WRITE_OID_FIELD(top_parent_rowtype);
WRITE_BITMAPSET_FIELD(top_parent_relids);
+ WRITE_BOOL_FIELD(top_parent_parted);
+ WRITE_BOOL_FIELD(top_parent_topmost);
+ WRITE_BOOL_FIELD(need_adjust_tlist);
WRITE_NODE_FIELD(partitioned_child_rels);
}
*** a/postgres-master/src/backend/optimizer/path/allpaths.c
--- b/postgres-master/src/backend/optimizer/path/allpaths.c
***************
*** 909,914 **** set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
--- 909,917 ----
did_pruning = true;
}
+ if (rel->reloptkind == RELOPT_BASEREL)
+ rel->top_parent_topmost = bms_equal(rel->relids, root->all_baserels);
+
/*
* Initialize to compute size estimates for whole append relation.
*
***************
*** 956,1025 **** set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
childrel = find_base_rel(root, childRTindex);
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
- if (rel->part_scheme)
- {
- AttrNumber attno;
-
- /*
- * We need attr_needed data for building targetlist of a join
- * relation representing join between matching partitions for
- * partitionwise join. A given attribute of a child will be needed
- * in the same highest joinrel where the corresponding attribute
- * of parent is needed. Hence it suffices to use the same Relids
- * set for parent and child.
- */
- for (attno = rel->min_attr; attno <= rel->max_attr; attno++)
- {
- int index = attno - rel->min_attr;
- Relids attr_needed = rel->attr_needed[index];
-
- /* System attributes do not need translation. */
- if (attno <= 0)
- {
- Assert(rel->min_attr == childrel->min_attr);
- childrel->attr_needed[index] = attr_needed;
- }
- else
- {
- Var *var = list_nth_node(Var,
- appinfo->translated_vars,
- attno - 1);
- int child_index;
-
- /*
- * Ignore any column dropped from the parent.
- * Corresponding Var won't have any translation. It won't
- * have attr_needed information, since it can not be
- * referenced in the query.
- */
- if (var == NULL)
- {
- Assert(attr_needed == NULL);
- continue;
- }
-
- child_index = var->varattno - childrel->min_attr;
- childrel->attr_needed[child_index] = attr_needed;
- }
- }
- }
-
/*
! * Copy/Modify targetlist. Even if this child is deemed empty, we need
! * its targetlist in case it falls on nullable side in a child-join
! * because of partitionwise join.
*
! * NB: the resulting childrel->reltarget->exprs may contain arbitrary
! * expressions, which otherwise would not occur in a rel's targetlist.
! * Code that might be looking at an appendrel child must cope with
! * such. (Normally, a rel's targetlist would only include Vars and
! * PlaceHolderVars.) XXX we do not bother to update the cost or width
! * fields of childrel->reltarget; not clear if that would be useful.
*/
! childrel->reltarget->exprs = (List *)
! adjust_appendrel_attrs(root,
! (Node *) rel->reltarget->exprs,
! 1, &appinfo);
/*
* We have to make child entries in the EquivalenceClass data
--- 959,974 ----
childrel = find_base_rel(root, childRTindex);
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
/*
! * We have to copy the parent's targetlist to the child, with
! * appropriate substitution of variables.
*
! * Note: when considering partitionwise joining, we need the child's
! * targetlist in case it falls on nullable side in a child join even
! * if the child is deemed empty.
*/
! childrel->top_parent_topmost = rel->top_parent_topmost;
! build_childrel_tlist(root, rel, childrel, 1, &appinfo);
/*
* We have to make child entries in the EquivalenceClass data
***************
*** 1181,1186 **** set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
--- 1130,1143 ----
1, &appinfo);
/*
+ * Note: we could compute appropriate attr_needed data for the child's
+ * variables, by transforming the parent's attr_needed through the
+ * translated_vars mapping. However, currently there's no need
+ * because attr_needed is only examined for base relations not
+ * otherrels. So we just leave the child's attr_needed empty.
+ */
+
+ /*
* If parallelism is allowable for this query in general, see whether
* it's allowable for this childrel in particular. But if we've
* already decided the appendrel is not parallel-safe as a whole,
*** a/postgres-master/src/backend/optimizer/plan/createplan.c
--- b/postgres-master/src/backend/optimizer/plan/createplan.c
***************
*** 29,34 ****
--- 29,35 ----
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
+ #include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/placeholder.h"
#include "optimizer/plancat.h"
***************
*** 162,167 **** static void copy_generic_path_info(Plan *dest, Path *src);
--- 163,169 ----
static void copy_plan_costsize(Plan *dest, Plan *src);
static void label_sort_with_costsize(PlannerInfo *root, Sort *plan,
double limit_tuples);
+ static void adjust_subplan_tlist(PlannerInfo *root, RelOptInfo *rel, Plan *subplan);
static SeqScan *make_seqscan(List *qptlist, List *qpqual, Index scanrelid);
static SampleScan *make_samplescan(List *qptlist, List *qpqual, Index scanrelid,
TableSampleClause *tsc);
***************
*** 1065,1070 **** create_append_plan(PlannerInfo *root, AppendPath *best_path)
--- 1067,1076 ----
/* Must insist that all children return the same tlist */
subplan = create_plan_recurse(root, subpath, CP_EXACT_TLIST);
+ /* Adjust the plan's targetlist if necessary */
+ if (subpath->parent->need_adjust_tlist)
+ adjust_subplan_tlist(root, subpath->parent, subplan);
+
subplans = lappend(subplans, subplan);
}
***************
*** 1176,1181 **** create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path)
--- 1182,1191 ----
/* Must insist that all children return the same tlist */
subplan = create_plan_recurse(root, subpath, CP_EXACT_TLIST);
+ /* Adjust the plan's targetlist if necessary */
+ if (subpath->parent->need_adjust_tlist)
+ adjust_subplan_tlist(root, subpath->parent, subplan);
+
/* Compute sort column info, and adjust subplan's tlist as needed */
subplan = prepare_sort_from_pathkeys(subplan, pathkeys,
subpath->parent->relids,
***************
*** 4968,4973 **** bitmap_subplan_mark_shared(Plan *plan)
--- 4978,5045 ----
elog(ERROR, "unrecognized node type: %d", nodeTag(plan));
}
+ /*
+ * adjust_subplan_tlist
+ * If the child plan's targetlist contains a whole-row Var as an entry of
+ * that list, add a ConvertRowtypeExpr above the Var to convert the tuple
+ * layout of the Var back to the top parent's rowtype.
+ */
+ static void
+ adjust_subplan_tlist(PlannerInfo *root, RelOptInfo *rel, Plan *subplan)
+ {
+ List *newtlist;
+ ListCell *l;
+
+ /* The top parent appendrel should be partitioned */
+ Assert(rel->top_parent_parted);
+ /* But it should not be the topmost scan/join relation */
+ Assert(!rel->top_parent_topmost);
+
+ /* The given rel should represent a scan/join relation */
+ Assert(rel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
+ rel->reloptkind == RELOPT_OTHER_JOINREL);
+
+ /*
+ * If the child plan is an Append or MergeAppend, the targetlists of its
+ * subplans would have already been adjusted before we get here, so need
+ * to work here.
+ */
+ if (IsA(subplan, Append) || IsA(subplan, MergeAppend))
+ return;
+
+ /* The child plan should be a scan or join */
+ Assert(is_projection_capable_plan(subplan));
+
+ newtlist = NIL;
+ foreach(l, subplan->targetlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(l);
+ Var *var = (Var *) tle->expr;
+
+ if (IsA(var, Var) && var->varattno == 0)
+ {
+ RelOptInfo *partrel = find_base_rel(root, var->varno);
+ ConvertRowtypeExpr *cre = makeNode(ConvertRowtypeExpr);
+
+ /* The rel should be a partition */
+ Assert(partrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
+
+ /* The top parent rel should be a partitioned table */
+ Assert(OidIsValid(partrel->top_parent_rowtype));
+
+ cre->arg = (Expr *) var;
+ cre->resulttype = partrel->top_parent_rowtype;
+ cre->convertformat = COERCE_IMPLICIT_CAST;
+ cre->location = -1;
+
+ tle = flatCopyTargetEntry(tle);
+ tle->expr = (Expr *) cre;
+ }
+ newtlist = lappend(newtlist, tle);
+ }
+ subplan->targetlist = newtlist;
+ }
+
/*****************************************************************************
*
* PLAN NODE BUILDING ROUTINES
*** a/postgres-master/src/backend/optimizer/plan/setrefs.c
--- b/postgres-master/src/backend/optimizer/plan/setrefs.c
***************
*** 41,49 **** typedef struct
int num_vars; /* number of plain Var tlist entries */
bool has_ph_vars; /* are there PlaceHolderVar entries? */
bool has_non_vars; /* are there other entries? */
- bool has_conv_whole_rows; /* are there ConvertRowtypeExpr
- * entries encapsulating a whole-row
- * Var? */
tlist_vinfo vars[FLEXIBLE_ARRAY_MEMBER]; /* has num_vars entries */
} indexed_tlist;
--- 41,46 ----
***************
*** 143,149 **** static List *set_returning_clause_references(PlannerInfo *root,
int rtoffset);
static bool extract_query_dependencies_walker(Node *node,
PlannerInfo *context);
- static bool is_converted_whole_row_reference(Node *node);
/*****************************************************************************
*
--- 140,145 ----
***************
*** 1997,2003 **** build_tlist_index(List *tlist)
itlist->tlist = tlist;
itlist->has_ph_vars = false;
itlist->has_non_vars = false;
- itlist->has_conv_whole_rows = false;
/* Find the Vars and fill in the index array */
vinfo = itlist->vars;
--- 1993,1998 ----
***************
*** 2016,2023 **** build_tlist_index(List *tlist)
}
else if (tle->expr && IsA(tle->expr, PlaceHolderVar))
itlist->has_ph_vars = true;
- else if (is_converted_whole_row_reference((Node *) tle->expr))
- itlist->has_conv_whole_rows = true;
else
itlist->has_non_vars = true;
}
--- 2011,2016 ----
***************
*** 2033,2042 **** build_tlist_index(List *tlist)
* This is like build_tlist_index, but we only index tlist entries that
* are Vars belonging to some rel other than the one specified. We will set
* has_ph_vars (allowing PlaceHolderVars to be matched), but not has_non_vars
! * (so nothing other than Vars and PlaceHolderVars can be matched). In case of
! * DML, where this function will be used, returning lists from child relations
! * will be appended similar to a simple append relation. That does not require
! * fixing ConvertRowtypeExpr references. So, those are not considered here.
*/
static indexed_tlist *
build_tlist_index_other_vars(List *tlist, Index ignore_rel)
--- 2026,2032 ----
* This is like build_tlist_index, but we only index tlist entries that
* are Vars belonging to some rel other than the one specified. We will set
* has_ph_vars (allowing PlaceHolderVars to be matched), but not has_non_vars
! * (so nothing other than Vars and PlaceHolderVars can be matched).
*/
static indexed_tlist *
build_tlist_index_other_vars(List *tlist, Index ignore_rel)
***************
*** 2053,2059 **** build_tlist_index_other_vars(List *tlist, Index ignore_rel)
itlist->tlist = tlist;
itlist->has_ph_vars = false;
itlist->has_non_vars = false;
- itlist->has_conv_whole_rows = false;
/* Find the desired Vars and fill in the index array */
vinfo = itlist->vars;
--- 2043,2048 ----
***************
*** 2256,2262 **** static Node *
fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
{
Var *newvar;
- bool converted_whole_row;
if (node == NULL)
return NULL;
--- 2245,2250 ----
***************
*** 2326,2337 **** fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
}
if (IsA(node, Param))
return fix_param_node(context->root, (Param *) node);
-
/* Try matching more complex expressions too, if tlists have any */
! converted_whole_row = is_converted_whole_row_reference(node);
! if (context->outer_itlist &&
! (context->outer_itlist->has_non_vars ||
! (context->outer_itlist->has_conv_whole_rows && converted_whole_row)))
{
newvar = search_indexed_tlist_for_non_var((Expr *) node,
context->outer_itlist,
--- 2314,2321 ----
}
if (IsA(node, Param))
return fix_param_node(context->root, (Param *) node);
/* Try matching more complex expressions too, if tlists have any */
! if (context->outer_itlist && context->outer_itlist->has_non_vars)
{
newvar = search_indexed_tlist_for_non_var((Expr *) node,
context->outer_itlist,
***************
*** 2339,2347 **** fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
if (newvar)
return (Node *) newvar;
}
! if (context->inner_itlist &&
! (context->inner_itlist->has_non_vars ||
! (context->inner_itlist->has_conv_whole_rows && converted_whole_row)))
{
newvar = search_indexed_tlist_for_non_var((Expr *) node,
context->inner_itlist,
--- 2323,2329 ----
if (newvar)
return (Node *) newvar;
}
! if (context->inner_itlist && context->inner_itlist->has_non_vars)
{
newvar = search_indexed_tlist_for_non_var((Expr *) node,
context->inner_itlist,
***************
*** 2461,2469 **** fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
/* If no match, just fall through to process it normally */
}
/* Try matching more complex expressions too, if tlist has any */
! if (context->subplan_itlist->has_non_vars ||
! (context->subplan_itlist->has_conv_whole_rows &&
! is_converted_whole_row_reference(node)))
{
newvar = search_indexed_tlist_for_non_var((Expr *) node,
context->subplan_itlist,
--- 2443,2449 ----
/* If no match, just fall through to process it normally */
}
/* Try matching more complex expressions too, if tlist has any */
! if (context->subplan_itlist->has_non_vars)
{
newvar = search_indexed_tlist_for_non_var((Expr *) node,
context->subplan_itlist,
***************
*** 2670,2702 **** extract_query_dependencies_walker(Node *node, PlannerInfo *context)
return expression_tree_walker(node, extract_query_dependencies_walker,
(void *) context);
}
-
- /*
- * is_converted_whole_row_reference
- * If the given node is a ConvertRowtypeExpr encapsulating a whole-row
- * reference as implicit cast, return true. Otherwise return false.
- */
- static bool
- is_converted_whole_row_reference(Node *node)
- {
- ConvertRowtypeExpr *convexpr;
-
- if (!node || !IsA(node, ConvertRowtypeExpr))
- return false;
-
- /* Traverse nested ConvertRowtypeExpr's. */
- convexpr = castNode(ConvertRowtypeExpr, node);
- while (convexpr->convertformat == COERCE_IMPLICIT_CAST &&
- IsA(convexpr->arg, ConvertRowtypeExpr))
- convexpr = castNode(ConvertRowtypeExpr, convexpr->arg);
-
- if (IsA(convexpr->arg, Var))
- {
- Var *var = castNode(Var, convexpr->arg);
-
- if (var->varattno == 0)
- return true;
- }
-
- return false;
- }
--- 2650,2652 ----
*** a/postgres-master/src/backend/optimizer/util/placeholder.c
--- b/postgres-master/src/backend/optimizer/util/placeholder.c
***************
*** 20,26 ****
#include "optimizer/pathnode.h"
#include "optimizer/placeholder.h"
#include "optimizer/planmain.h"
- #include "optimizer/prep.h"
#include "optimizer/var.h"
#include "utils/lsyscache.h"
--- 20,25 ----
***************
*** 415,424 **** add_placeholders_to_joinrel(PlannerInfo *root, RelOptInfo *joinrel,
Relids relids = joinrel->relids;
ListCell *lc;
- /* This function is called only on the parent relations. */
- Assert(!IS_OTHER_REL(joinrel) && !IS_OTHER_REL(outer_rel) &&
- !IS_OTHER_REL(inner_rel));
-
foreach(lc, root->placeholder_list)
{
PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(lc);
--- 414,419 ----
***************
*** 464,519 **** add_placeholders_to_joinrel(PlannerInfo *root, RelOptInfo *joinrel,
}
}
}
-
- /*
- * add_placeholders_to_child_joinrel
- * Translate the PHVs in parent's targetlist and add them to the child's
- * targetlist. Also adjust the cost
- */
- void
- add_placeholders_to_child_joinrel(PlannerInfo *root, RelOptInfo *childrel,
- RelOptInfo *parentrel)
- {
- ListCell *lc;
- AppendRelInfo **appinfos;
- int nappinfos;
-
- Assert(IS_JOIN_REL(childrel) && IS_JOIN_REL(parentrel));
- Assert(IS_OTHER_REL(childrel));
-
- /* Nothing to do if no PHVs. */
- if (root->placeholder_list == NIL)
- return;
-
- appinfos = find_appinfos_by_relids(root, childrel->relids, &nappinfos);
- foreach(lc, parentrel->reltarget->exprs)
- {
- PlaceHolderVar *phv = lfirst(lc);
-
- if (IsA(phv, PlaceHolderVar))
- {
- /*
- * In case the placeholder Var refers to any of the parent
- * relations, translate it to refer to the corresponding child.
- */
- if (bms_overlap(phv->phrels, parentrel->relids) &&
- childrel->reloptkind == RELOPT_OTHER_JOINREL)
- {
- phv = (PlaceHolderVar *) adjust_appendrel_attrs(root,
- (Node *) phv,
- nappinfos,
- appinfos);
- }
-
- childrel->reltarget->exprs = lappend(childrel->reltarget->exprs,
- phv);
- }
- }
-
- /* Adjust the cost and width of child targetlist. */
- childrel->reltarget->cost.startup = parentrel->reltarget->cost.startup;
- childrel->reltarget->cost.per_tuple = parentrel->reltarget->cost.per_tuple;
- childrel->reltarget->width = parentrel->reltarget->width;
-
- pfree(appinfos);
- }
--- 459,461 ----
*** a/postgres-master/src/backend/optimizer/util/relnode.c
--- b/postgres-master/src/backend/optimizer/util/relnode.c
***************
*** 16,21 ****
--- 16,22 ----
#include <limits.h>
+ #include "catalog/pg_class.h"
#include "miscadmin.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
***************
*** 28,33 ****
--- 29,35 ----
#include "optimizer/tlist.h"
#include "partitioning/partbounds.h"
#include "utils/hsearch.h"
+ #include "utils/lsyscache.h"
typedef struct JoinHashEntry
***************
*** 57,62 **** static void add_join_rel(PlannerInfo *root, RelOptInfo *joinrel);
--- 59,69 ----
static void build_joinrel_partition_info(RelOptInfo *joinrel,
RelOptInfo *outer_rel, RelOptInfo *inner_rel,
List *restrictlist, JoinType jointype);
+ static void build_child_join_reltarget(PlannerInfo *root,
+ RelOptInfo *parentrel,
+ RelOptInfo *childrel,
+ int nappinfos,
+ AppendRelInfo **appinfos);
/*
***************
*** 188,193 **** build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
--- 195,205 ----
rel->baserestrict_min_security = UINT_MAX;
rel->joininfo = NIL;
rel->has_eclass_joins = false;
+ rel->top_parent_rowtype = InvalidOid;
+ rel->top_parent_relids = NULL;
+ rel->top_parent_parted = false;
+ rel->top_parent_topmost = false;
+ rel->need_adjust_tlist = false;
rel->part_scheme = NULL;
rel->nparts = 0;
rel->boundinfo = NULL;
***************
*** 201,207 **** build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
* Pass top parent's relids down the inheritance hierarchy. If the parent
* has top_parent_relids set, it's a direct or an indirect child of the
* top parent indicated by top_parent_relids. By extension this child is
! * also an indirect child of that parent.
*/
if (parent)
{
--- 213,221 ----
* Pass top parent's relids down the inheritance hierarchy. If the parent
* has top_parent_relids set, it's a direct or an indirect child of the
* top parent indicated by top_parent_relids. By extension this child is
! * also an indirect child of that parent. Likeweise for the flag to
! * indicate whether that parent is a partitioned table and the OID of that
! * parent's rowtype.
*/
if (parent)
{
***************
*** 209,218 **** build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
--- 223,243 ----
rel->top_parent_relids = parent->top_parent_relids;
else
rel->top_parent_relids = bms_copy(parent->relids);
+
+ rel->top_parent_parted = parent->top_parent_parted;
+ rel->top_parent_rowtype = parent->top_parent_rowtype;
}
else
+ {
rel->top_parent_relids = NULL;
+ if (rte->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ rel->top_parent_parted = true;
+ rel->top_parent_rowtype = get_rel_type_id(rte->relid);
+ }
+ }
+
/* Check type of rtable entry */
switch (rte->rtekind)
{
***************
*** 602,608 **** build_join_rel(PlannerInfo *root,
--- 627,637 ----
joinrel->baserestrict_min_security = UINT_MAX;
joinrel->joininfo = NIL;
joinrel->has_eclass_joins = false;
+ joinrel->top_parent_rowtype = InvalidOid;
joinrel->top_parent_relids = NULL;
+ joinrel->top_parent_parted = false;
+ joinrel->top_parent_topmost = false;
+ joinrel->need_adjust_tlist = false;
joinrel->part_scheme = NULL;
joinrel->nparts = 0;
joinrel->boundinfo = NULL;
***************
*** 659,664 **** build_join_rel(PlannerInfo *root,
--- 688,699 ----
/* Store the partition information. */
build_joinrel_partition_info(joinrel, outer_rel, inner_rel, restrictlist,
sjinfo->jointype);
+ if (IS_PARTITIONED_REL(joinrel))
+ {
+ joinrel->top_parent_parted = true;
+ joinrel->top_parent_topmost = bms_equal(joinrel->relids,
+ root->all_baserels);
+ }
/*
* Set estimates of the joinrel's size.
***************
*** 773,779 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
--- 808,818 ----
joinrel->baserestrictcost.per_tuple = 0;
joinrel->joininfo = NIL;
joinrel->has_eclass_joins = false;
+ joinrel->top_parent_rowtype = InvalidOid;
joinrel->top_parent_relids = NULL;
+ joinrel->top_parent_parted = parent_joinrel->top_parent_parted;
+ joinrel->top_parent_topmost = parent_joinrel->top_parent_topmost;
+ joinrel->need_adjust_tlist = false;
joinrel->part_scheme = NULL;
joinrel->nparts = 0;
joinrel->boundinfo = NULL;
***************
*** 785,802 **** build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
joinrel->top_parent_relids = bms_union(outer_rel->top_parent_relids,
inner_rel->top_parent_relids);
/* Compute information relevant to foreign relations. */
set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
! /* Build targetlist */
! build_joinrel_tlist(root, joinrel, outer_rel);
! build_joinrel_tlist(root, joinrel, inner_rel);
! /* Add placeholder variables. */
! add_placeholders_to_child_joinrel(root, joinrel, parent_joinrel);
/* Construct joininfo list. */
- appinfos = find_appinfos_by_relids(root, joinrel->relids, &nappinfos);
joinrel->joininfo = (List *) adjust_appendrel_attrs(root,
(Node *) parent_joinrel->joininfo,
nappinfos,
--- 824,841 ----
joinrel->top_parent_relids = bms_union(outer_rel->top_parent_relids,
inner_rel->top_parent_relids);
+ Assert(joinrel->top_parent_parted);
/* Compute information relevant to foreign relations. */
set_foreign_rel_properties(joinrel, outer_rel, inner_rel);
! appinfos = find_appinfos_by_relids(root, joinrel->relids, &nappinfos);
!
! /* Set up reltarget struct */
! build_child_join_reltarget(root, parent_joinrel, joinrel,
! nappinfos, appinfos);
/* Construct joininfo list. */
joinrel->joininfo = (List *) adjust_appendrel_attrs(root,
(Node *) parent_joinrel->joininfo,
nappinfos,
***************
*** 895,909 **** static void
build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *input_rel)
{
! Relids relids;
ListCell *vars;
- /* attrs_needed refers to parent relids and not those of a child. */
- if (joinrel->top_parent_relids)
- relids = joinrel->top_parent_relids;
- else
- relids = joinrel->relids;
-
foreach(vars, input_rel->reltarget->exprs)
{
Var *var = (Var *) lfirst(vars);
--- 934,942 ----
build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
RelOptInfo *input_rel)
{
! Relids relids = joinrel->relids;
ListCell *vars;
foreach(vars, input_rel->reltarget->exprs)
{
Var *var = (Var *) lfirst(vars);
***************
*** 919,972 **** build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
/*
* Otherwise, anything in a baserel or joinrel targetlist ought to be
! * a Var. Children of a partitioned table may have ConvertRowtypeExpr
! * translating whole-row Var of a child to that of the parent.
! * Children of an inherited table or subquery child rels can not
! * directly participate in a join, so other kinds of nodes here.
*/
! if (IsA(var, Var))
! {
! baserel = find_base_rel(root, var->varno);
! ndx = var->varattno - baserel->min_attr;
! }
! else if (IsA(var, ConvertRowtypeExpr))
! {
! ConvertRowtypeExpr *child_expr = (ConvertRowtypeExpr *) var;
! Var *childvar = (Var *) child_expr->arg;
!
! /*
! * Child's whole-row references are converted to look like those
! * of parent using ConvertRowtypeExpr. There can be as many
! * ConvertRowtypeExpr decorations as the depth of partition tree.
! * The argument to the deepest ConvertRowtypeExpr is expected to
! * be a whole-row reference of the child.
! */
! while (IsA(childvar, ConvertRowtypeExpr))
! {
! child_expr = (ConvertRowtypeExpr *) childvar;
! childvar = (Var *) child_expr->arg;
! }
! Assert(IsA(childvar, Var) &&childvar->varattno == 0);
!
! baserel = find_base_rel(root, childvar->varno);
! ndx = 0 - baserel->min_attr;
! }
! else
elog(ERROR, "unexpected node type in rel targetlist: %d",
(int) nodeTag(var));
! /* Is the target expression still needed above this joinrel? */
if (bms_nonempty_difference(baserel->attr_needed[ndx], relids))
{
/* Yup, add it to the output */
joinrel->reltarget->exprs = lappend(joinrel->reltarget->exprs, var);
!
! /*
! * Vars have cost zero, so no need to adjust reltarget->cost. Even
! * if it's a ConvertRowtypeExpr, it will be computed only for the
! * base relation, costing nothing for a join.
! */
joinrel->reltarget->width += baserel->attr_widths[ndx];
}
}
--- 952,974 ----
/*
* Otherwise, anything in a baserel or joinrel targetlist ought to be
! * a Var. (More general cases can only appear in appendrel child
! * rels, which will never be seen here.)
*/
! if (!IsA(var, Var))
elog(ERROR, "unexpected node type in rel targetlist: %d",
(int) nodeTag(var));
+ /* Get the Var's original base rel */
+ baserel = find_base_rel(root, var->varno);
! /* Is it still needed above this joinrel? */
! ndx = var->varattno - baserel->min_attr;
if (bms_nonempty_difference(baserel->attr_needed[ndx], relids))
{
/* Yup, add it to the output */
joinrel->reltarget->exprs = lappend(joinrel->reltarget->exprs, var);
! /* Vars have cost zero, so no need to adjust reltarget->cost */
joinrel->reltarget->width += baserel->attr_widths[ndx];
}
}
***************
*** 1215,1220 **** fetch_upper_rel(PlannerInfo *root, UpperRelationKind kind, Relids relids)
--- 1217,1224 ----
upperrel->cheapest_unique_path = NULL;
upperrel->cheapest_parameterized_paths = NIL;
+ upperrel->need_adjust_tlist = false;
+
root->upper_rels[kind] = lappend(root->upper_rels[kind], upperrel);
return upperrel;
***************
*** 1768,1770 **** build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
--- 1772,1884 ----
joinrel->nullable_partexprs[cnt] = nullable_partexpr;
}
}
+
+ /*
+ * build_childrel_tlist
+ * Build the child's targetlist from the parent's with
+ * adjust_appendrel_attrs
+ *
+ * When considering partitionwise joining, we need special handling: if the
+ * entry in the parent's targetlist is a whole-row Var for the parent rel,
+ * create a new whole-row Var for the child rel and add it to the child's
+ * targetlist. This allows us to handle the child's targetlist in the same
+ * way as the parent's during planning. The tuple layout of the child Var
+ * will be converted back to the parent's rowtype at create_plan time.
+ */
+ void
+ build_childrel_tlist(PlannerInfo *root,
+ RelOptInfo *parentrel,
+ RelOptInfo *childrel,
+ int nappinfos,
+ AppendRelInfo **appinfos)
+ {
+ ListCell *lc;
+
+ /*
+ * If enable_partitionwise_join=off, if the parent is not partitioned, or
+ * if the parent is the topmost scan/join relation, we don't consider
+ * partitionwise joining, so no need for special handling; just apply
+ * adjust_appendrel_attrs to the parent's targetlist to get the child's.
+ *
+ * NB: the resulting childrel->reltarget->exprs may contain arbitrary
+ * expressions, which otherwise would not occur in a rel's targetlist.
+ * Code that might be looking at an appendrel child must cope with such.
+ * (Normally, a rel's targetlist would only include Vars and
+ * PlaceHolderVars.) XXX we do not bother to update the cost or width
+ * fields of childrel->reltarget; not clear if that would be useful.
+ */
+ if (!enable_partitionwise_join ||
+ !childrel->top_parent_parted ||
+ childrel->top_parent_topmost)
+ {
+ childrel->reltarget->exprs = (List *)
+ adjust_appendrel_attrs(root,
+ (Node *) parentrel->reltarget->exprs,
+ nappinfos, appinfos);
+ return;
+ }
+
+ foreach(lc, parentrel->reltarget->exprs)
+ {
+ Node *node = (Node *) lfirst(lc);
+
+ /* Should be a simple Var or PlaceHolderVar ... */
+ Assert(IsA(node, Var) || IsA(node, PlaceHolderVar));
+
+ if (IsA(node, Var) && ((Var *) node)->varattno == 0)
+ {
+ Var *var = (Var *) copyObject(node);
+ AppendRelInfo *appinfo = NULL;
+ int cnt;
+
+ /* Find the AppendRelInfo associated with the parent */
+ for (cnt = 0; cnt < nappinfos; cnt++)
+ {
+ if (var->varno == appinfos[cnt]->parent_relid)
+ {
+ appinfo = appinfos[cnt];
+ break;
+ }
+ }
+
+ /* Should have found the entry ... */
+ Assert(appinfo);
+
+ var->varno = appinfo->child_relid;
+ var->varnoold = appinfo->child_relid;
+ var->vartype = appinfo->child_reltype;
+
+ childrel->reltarget->exprs =
+ lappend(childrel->reltarget->exprs, var);
+ childrel->need_adjust_tlist = true;
+ }
+ else
+ {
+ childrel->reltarget->exprs =
+ lappend(childrel->reltarget->exprs,
+ adjust_appendrel_attrs(root, node,
+ nappinfos,
+ appinfos));
+ }
+ }
+ }
+
+ /*
+ * build_child_join_reltarget
+ * Set up a child-join relation's reltarget from a parent-join relation.
+ */
+ static void
+ build_child_join_reltarget(PlannerInfo *root,
+ RelOptInfo *parentrel,
+ RelOptInfo *childrel,
+ int nappinfos,
+ AppendRelInfo **appinfos)
+ {
+ /* Build the targetlist */
+ build_childrel_tlist(root, parentrel, childrel, nappinfos, appinfos);
+
+ /* Set the cost and width fields */
+ childrel->reltarget->cost.startup = parentrel->reltarget->cost.startup;
+ childrel->reltarget->cost.per_tuple = parentrel->reltarget->cost.per_tuple;
+ childrel->reltarget->width = parentrel->reltarget->width;
+ }
*** a/postgres-master/src/include/nodes/relation.h
--- b/postgres-master/src/include/nodes/relation.h
***************
*** 687,694 **** typedef struct RelOptInfo
* involving this rel */
bool has_eclass_joins; /* T means joininfo is incomplete */
! /* used by "other" relations */
! Relids top_parent_relids; /* Relids of topmost parents */
/* used for partitioned relations */
PartitionScheme part_scheme; /* Partitioning scheme. */
--- 687,701 ----
* involving this rel */
bool has_eclass_joins; /* T means joininfo is incomplete */
! /* used by partitionwise joins: */
! Oid top_parent_rowtype; /* OID of topmost parent's rowtype */
! Relids top_parent_relids; /* Relids of topmost parent(s) */
! bool top_parent_parted; /* T means the topmost parent(s) rel is
! * partitioned */
! bool top_parent_topmost; /* T means the topmost parent(s) rel is
! * topmost scan/join one */
! bool need_adjust_tlist; /* T means reltarget->exprs has child
! * whole-row Var(s) */
/* used for partitioned relations */
PartitionScheme part_scheme; /* Partitioning scheme. */
*** a/postgres-master/src/include/optimizer/pathnode.h
--- b/postgres-master/src/include/optimizer/pathnode.h
***************
*** 297,301 **** extern RelOptInfo *build_child_join_rel(PlannerInfo *root,
--- 297,306 ----
RelOptInfo *outer_rel, RelOptInfo *inner_rel,
RelOptInfo *parent_joinrel, List *restrictlist,
SpecialJoinInfo *sjinfo, JoinType jointype);
+ extern void build_childrel_tlist(PlannerInfo *root,
+ RelOptInfo *parentrel,
+ RelOptInfo *childrel,
+ int nappinfos,
+ AppendRelInfo **appinfos);
#endif /* PATHNODE_H */
*** a/postgres-master/src/test/regress/expected/partition_join.out
--- b/postgres-master/src/test/regress/expected/partition_join.out
***************
*** 476,481 **** SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL
--- 476,554 ----
550 | |
(12 rows)
+ -- Check whole-row-Var handling in situations where the partitioned rel is not
+ -- the topmost scan/join relation
+ CREATE TABLE nonprt (a int, x text default 'testing');
+ INSERT INTO nonprt (a) SELECT i FROM generate_series(0, 599) i WHERE i % 6 = 0;
+ ANALYZE nonprt;
+ EXPLAIN (COSTS OFF)
+ SELECT t1, t3 FROM prt1 t1, nonprt t3 WHERE t1.a = t3.a AND t1.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+ --------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Hash Join
+ Hash Cond: (t3.a = t1.a)
+ -> Seq Scan on nonprt t3
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_2
+ Filter: (b = 0)
+ (13 rows)
+
+ SELECT t1, t3 FROM prt1 t1, nonprt t3 WHERE t1.a = t3.a AND t1.b = 0 ORDER BY t1.a;
+ t1 | t3
+ --------------+---------------
+ (0,0,0000) | (0,testing)
+ (150,0,0150) | (150,testing)
+ (300,0,0300) | (300,testing)
+ (450,0,0450) | (450,testing)
+ (4 rows)
+
+ EXPLAIN (COSTS OFF)
+ SELECT t1, t2, t3 FROM prt1 t1, prt2 t2, nonprt t3 WHERE t1.a = t2.b AND t1.a = t3.a AND t1.b = 0 ORDER BY t1.a;
+ QUERY PLAN
+ --------------------------------------------------------------
+ Sort
+ Sort Key: t1.a
+ -> Hash Join
+ Hash Cond: (t3.a = t1.a)
+ -> Seq Scan on nonprt t3
+ -> Hash
+ -> Append
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Seq Scan on prt2_p1 t2
+ -> Hash
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: (t2_1.b = t1_1.a)
+ -> Seq Scan on prt2_p2 t2_1
+ -> Hash
+ -> Seq Scan on prt1_p2 t1_1
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on prt2_p3 t2_2
+ -> Hash
+ -> Seq Scan on prt1_p3 t1_2
+ Filter: (b = 0)
+ (25 rows)
+
+ SELECT t1, t2, t3 FROM prt1 t1, prt2 t2, nonprt t3 WHERE t1.a = t2.b AND t1.a = t3.a AND t1.b = 0 ORDER BY t1.a;
+ t1 | t2 | t3
+ --------------+--------------+---------------
+ (0,0,0000) | (0,0,0000) | (0,testing)
+ (150,0,0150) | (0,150,0150) | (150,testing)
+ (300,0,0300) | (0,300,0300) | (300,testing)
+ (450,0,0450) | (0,450,0450) | (450,testing)
+ (4 rows)
+
--
-- partitioned by expression
--
***************
*** 1042,1047 **** SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT *
--- 1115,1163 ----
400 |
(9 rows)
+ -- merge join when expression with whole-row reference needs to be sorted
+ EXPLAIN (COSTS OFF)
+ SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
+ QUERY PLAN
+ -----------------------------------------------------------------------------------
+ Merge Append
+ Sort Key: t1.a
+ -> Merge Join
+ Merge Cond: ((t1.a = t2.b) AND (((t1.*)::text) = ((t2.*)::text)))
+ -> Sort
+ Sort Key: t1.a, ((t1.*)::text)
+ -> Seq Scan on prt1_p1 t1
+ -> Sort
+ Sort Key: t2.b, ((t2.*)::text)
+ -> Seq Scan on prt2_p1 t2
+ -> Merge Join
+ Merge Cond: ((t1_1.a = t2_1.b) AND (((t1_1.*)::text) = ((t2_1.*)::text)))
+ -> Sort
+ Sort Key: t1_1.a, ((t1_1.*)::text)
+ -> Seq Scan on prt1_p2 t1_1
+ -> Sort
+ Sort Key: t2_1.b, ((t2_1.*)::text)
+ -> Seq Scan on prt2_p2 t2_1
+ -> Merge Join
+ Merge Cond: ((t1_2.a = t2_2.b) AND (((t1_2.*)::text) = ((t2_2.*)::text)))
+ -> Sort
+ Sort Key: t1_2.a, ((t1_2.*)::text)
+ -> Seq Scan on prt1_p3 t1_2
+ -> Sort
+ Sort Key: t2_2.b, ((t2_2.*)::text)
+ -> Seq Scan on prt2_p3 t2_2
+ (26 rows)
+
+ SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
+ a | b
+ ----+----
+ 0 | 0
+ 6 | 6
+ 12 | 12
+ 18 | 18
+ 24 | 24
+ (5 rows)
+
RESET enable_hashjoin;
RESET enable_nestloop;
--
*** a/postgres-master/src/test/regress/sql/partition_join.sql
--- b/postgres-master/src/test/regress/sql/partition_join.sql
***************
*** 89,94 **** SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL
--- 89,108 ----
(SELECT t2.a AS t2a, t3.a AS t3a, t2.b t2b, t2.c t2c, least(t1.a,t2.a,t3.a) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a;
+ -- Check whole-row-Var handling in situations where the partitioned rel is not
+ -- the topmost scan/join relation
+ CREATE TABLE nonprt (a int, x text default 'testing');
+ INSERT INTO nonprt (a) SELECT i FROM generate_series(0, 599) i WHERE i % 6 = 0;
+ ANALYZE nonprt;
+
+ EXPLAIN (COSTS OFF)
+ SELECT t1, t3 FROM prt1 t1, nonprt t3 WHERE t1.a = t3.a AND t1.b = 0 ORDER BY t1.a;
+ SELECT t1, t3 FROM prt1 t1, nonprt t3 WHERE t1.a = t3.a AND t1.b = 0 ORDER BY t1.a;
+
+ EXPLAIN (COSTS OFF)
+ SELECT t1, t2, t3 FROM prt1 t1, prt2 t2, nonprt t3 WHERE t1.a = t2.b AND t1.a = t3.a AND t1.b = 0 ORDER BY t1.a;
+ SELECT t1, t2, t3 FROM prt1 t1, prt2 t2, nonprt t3 WHERE t1.a = t2.b AND t1.a = t3.a AND t1.b = 0 ORDER BY t1.a;
+
--
-- partitioned by expression
--
***************
*** 160,165 **** EXPLAIN (COSTS OFF)
--- 174,184 ----
SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
+ -- merge join when expression with whole-row reference needs to be sorted
+ EXPLAIN (COSTS OFF)
+ SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
+ SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
+
RESET enable_hashjoin;
RESET enable_nestloop;
