diff options
| -rw-r--r-- | src/backend/optimizer/path/joinpath.c | 67 | ||||
| -rw-r--r-- | src/backend/optimizer/plan/createplan.c | 17 | ||||
| -rw-r--r-- | src/backend/optimizer/util/pathnode.c | 292 | ||||
| -rw-r--r-- | src/include/optimizer/pathnode.h | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/partition_join.out | 168 | ||||
| -rw-r--r-- | src/test/regress/sql/partition_join.sql | 40 |
6 files changed, 485 insertions, 101 deletions
diff --git a/src/backend/optimizer/path/joinpath.c b/src/backend/optimizer/path/joinpath.c index 6aca66f1962..5be8da9e095 100644 --- a/src/backend/optimizer/path/joinpath.c +++ b/src/backend/optimizer/path/joinpath.c @@ -30,8 +30,9 @@ set_join_pathlist_hook_type set_join_pathlist_hook = NULL; /* - * Paths parameterized by the parent can be considered to be parameterized by - * any of its child. + * Paths parameterized by a parent rel can be considered to be parameterized + * by any of its children, when we are performing partitionwise joins. These + * macros simplify checking for such cases. Beware multiple eval of args. */ #define PATH_PARAM_BY_PARENT(path, rel) \ ((path)->param_info && bms_overlap(PATH_REQ_OUTER(path), \ @@ -785,6 +786,20 @@ try_nestloop_path(PlannerInfo *root, Assert(!have_unsafe_outer_join_ref(root, outerrelids, inner_paramrels)); /* + * If the inner path is parameterized, it is parameterized by the topmost + * parent of the outer rel, not the outer rel itself. We will need to + * translate the parameterization, if this path is chosen, during + * create_plan(). Here we just check whether we will be able to perform + * the translation, and if not avoid creating a nestloop path. + */ + if (PATH_PARAM_BY_PARENT(inner_path, outer_path->parent) && + !path_is_reparameterizable_by_child(inner_path, outer_path->parent)) + { + bms_free(required_outer); + return; + } + + /* * Do a precheck to quickly eliminate obviously-inferior paths. We * calculate a cheap lower bound on the path's cost and then use * add_path_precheck() to see if the path is clearly going to be dominated @@ -800,27 +815,6 @@ try_nestloop_path(PlannerInfo *root, workspace.startup_cost, workspace.total_cost, pathkeys, required_outer)) { - /* - * If the inner path is parameterized, it is parameterized by the - * topmost parent of the outer rel, not the outer rel itself. Fix - * that. - */ - if (PATH_PARAM_BY_PARENT(inner_path, outer_path->parent)) - { - inner_path = reparameterize_path_by_child(root, inner_path, - outer_path->parent); - - /* - * If we could not translate the path, we can't create nest loop - * path. - */ - if (!inner_path) - { - bms_free(required_outer); - return; - } - } - add_path(joinrel, (Path *) create_nestloop_path(root, joinrel, @@ -884,6 +878,17 @@ try_partial_nestloop_path(PlannerInfo *root, } /* + * If the inner path is parameterized, it is parameterized by the topmost + * parent of the outer rel, not the outer rel itself. We will need to + * translate the parameterization, if this path is chosen, during + * create_plan(). Here we just check whether we will be able to perform + * the translation, and if not avoid creating a nestloop path. + */ + if (PATH_PARAM_BY_PARENT(inner_path, outer_path->parent) && + !path_is_reparameterizable_by_child(inner_path, outer_path->parent)) + return; + + /* * Before creating a path, get a quick lower bound on what it is likely to * cost. Bail out right away if it looks terrible. */ @@ -892,22 +897,6 @@ try_partial_nestloop_path(PlannerInfo *root, if (!add_partial_path_precheck(joinrel, workspace.total_cost, pathkeys)) return; - /* - * If the inner path is parameterized, it is parameterized by the topmost - * parent of the outer rel, not the outer rel itself. Fix that. - */ - if (PATH_PARAM_BY_PARENT(inner_path, outer_path->parent)) - { - inner_path = reparameterize_path_by_child(root, inner_path, - outer_path->parent); - - /* - * If we could not translate the path, we can't create nest loop path. - */ - if (!inner_path) - return; - } - /* Might be good enough to be worth trying, so let's try it. */ add_partial_path(joinrel, (Path *) create_nestloop_path(root, diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 610f4a56d6b..5f479fc56c1 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -29,6 +29,7 @@ #include "optimizer/cost.h" #include "optimizer/optimizer.h" #include "optimizer/paramassign.h" +#include "optimizer/pathnode.h" #include "optimizer/paths.h" #include "optimizer/placeholder.h" #include "optimizer/plancat.h" @@ -4355,6 +4356,22 @@ create_nestloop_plan(PlannerInfo *root, List *nestParams; Relids saveOuterRels = root->curOuterRels; + /* + * If the inner path is parameterized by the topmost parent of the outer + * rel rather than the outer rel itself, fix that. (Nothing happens here + * if it is not so parameterized.) + */ + best_path->jpath.innerjoinpath = + reparameterize_path_by_child(root, + best_path->jpath.innerjoinpath, + best_path->jpath.outerjoinpath->parent); + + /* + * Failure here probably means that reparameterize_path_by_child() is not + * in sync with path_is_reparameterizable_by_child(). + */ + Assert(best_path->jpath.innerjoinpath != NULL); + /* NestLoop can project, so no need to be picky about child tlists */ outer_plan = create_plan_recurse(root, best_path->jpath.outerjoinpath, 0); diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 246cd8f7476..0a7e5c2678f 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -52,6 +52,8 @@ static int append_startup_cost_compare(const ListCell *a, const ListCell *b); static List *reparameterize_pathlist_by_child(PlannerInfo *root, List *pathlist, RelOptInfo *child_rel); +static bool pathlist_is_reparameterizable_by_child(List *pathlist, + RelOptInfo *child_rel); /***************************************************************************** @@ -2463,6 +2465,16 @@ create_nestloop_path(PlannerInfo *root, { NestPath *pathnode = makeNode(NestPath); Relids inner_req_outer = PATH_REQ_OUTER(inner_path); + Relids outerrelids; + + /* + * Paths are parameterized by top-level parents, so run parameterization + * tests on the parent relids. + */ + if (outer_path->parent->top_parent_relids) + outerrelids = outer_path->parent->top_parent_relids; + else + outerrelids = outer_path->parent->relids; /* * If the inner path is parameterized by the outer, we must drop any @@ -2472,7 +2484,7 @@ create_nestloop_path(PlannerInfo *root, * estimates for this path. We detect such clauses by checking for serial * number match to clauses already enforced in the inner path. */ - if (bms_overlap(inner_req_outer, outer_path->parent->relids)) + if (bms_overlap(inner_req_outer, outerrelids)) { Bitmapset *enforced_serials = get_param_path_clause_serials(inner_path); List *jclauses = NIL; @@ -4072,34 +4084,39 @@ reparameterize_path(PlannerInfo *root, Path *path, * Given a path parameterized by the parent of the given child relation, * translate the path to be parameterized by the given child relation. * - * The function creates a new path of the same type as the given path, but - * parameterized by the given child relation. Most fields from the original - * path can simply be flat-copied, but any expressions must be adjusted to - * refer to the correct varnos, and any paths must be recursively - * reparameterized. Other fields that refer to specific relids also need - * adjustment. + * Most fields in the path are not changed, but any expressions must be + * adjusted to refer to the correct varnos, and any subpaths must be + * recursively reparameterized. Other fields that refer to specific relids + * also need adjustment. * * The cost, number of rows, width and parallel path properties depend upon - * path->parent, which does not change during the translation. Hence those - * members are copied as they are. + * path->parent, which does not change during the translation. So we need + * not change those. * * Currently, only a few path types are supported here, though more could be * added at need. We return NULL if we can't reparameterize the given path. + * + * Note that this function can change referenced RangeTblEntries, RelOptInfos + * and IndexOptInfos as well as the Path structures. Therefore, it's only safe + * to call during create_plan(), when we have made a final choice of which Path + * to use for each RangeTblEntry/RelOptInfo/IndexOptInfo. + * + * Keep this code in sync with path_is_reparameterizable_by_child()! */ Path * reparameterize_path_by_child(PlannerInfo *root, Path *path, RelOptInfo *child_rel) { - -#define FLAT_COPY_PATH(newnode, node, nodetype) \ - ( (newnode) = makeNode(nodetype), \ - memcpy((newnode), (node), sizeof(nodetype)) ) + Path *new_path; + ParamPathInfo *new_ppi; + ParamPathInfo *old_ppi; + Relids required_outer; #define ADJUST_CHILD_ATTRS(node) \ - ((node) = \ - (List *) adjust_appendrel_attrs_multilevel(root, (Node *) (node), \ - child_rel, \ - child_rel->top_parent)) + ((node) = (void *) adjust_appendrel_attrs_multilevel(root, \ + (Node *) (node), \ + child_rel, \ + child_rel->top_parent)) #define REPARAMETERIZE_CHILD_PATH(path) \ do { \ @@ -4119,21 +4136,16 @@ do { \ } \ } while(0) - Path *new_path; - ParamPathInfo *new_ppi; - ParamPathInfo *old_ppi; - Relids required_outer; - /* - * If the path is not parameterized by parent of the given relation, it - * doesn't need reparameterization. + * If the path is not parameterized by the parent of the given relation, + * it doesn't need reparameterization. */ if (!path->param_info || !bms_overlap(PATH_REQ_OUTER(path), child_rel->top_parent_relids)) return path; /* - * If possible, reparameterize the given path, making a copy. + * If possible, reparameterize the given path. * * This function is currently only applied to the inner side of a nestloop * join that is being partitioned by the partitionwise-join code. Hence, @@ -4147,14 +4159,28 @@ do { \ switch (nodeTag(path)) { case T_Path: - FLAT_COPY_PATH(new_path, path, Path); + new_path = path; + ADJUST_CHILD_ATTRS(new_path->parent->baserestrictinfo); + if (path->pathtype == T_SampleScan) + { + Index scan_relid = path->parent->relid; + RangeTblEntry *rte; + + /* it should be a base rel with a tablesample clause... */ + Assert(scan_relid > 0); + rte = planner_rt_fetch(scan_relid, root); + Assert(rte->rtekind == RTE_RELATION); + Assert(rte->tablesample != NULL); + + ADJUST_CHILD_ATTRS(rte->tablesample); + } break; case T_IndexPath: { - IndexPath *ipath; + IndexPath *ipath = (IndexPath *) path; - FLAT_COPY_PATH(ipath, path, IndexPath); + ADJUST_CHILD_ATTRS(ipath->indexinfo->indrestrictinfo); ADJUST_CHILD_ATTRS(ipath->indexclauses); new_path = (Path *) ipath; } @@ -4162,9 +4188,9 @@ do { \ case T_BitmapHeapPath: { - BitmapHeapPath *bhpath; + BitmapHeapPath *bhpath = (BitmapHeapPath *) path; - FLAT_COPY_PATH(bhpath, path, BitmapHeapPath); + ADJUST_CHILD_ATTRS(bhpath->path.parent->baserestrictinfo); REPARAMETERIZE_CHILD_PATH(bhpath->bitmapqual); new_path = (Path *) bhpath; } @@ -4172,9 +4198,8 @@ do { \ case T_BitmapAndPath: { - BitmapAndPath *bapath; + BitmapAndPath *bapath = (BitmapAndPath *) path; - FLAT_COPY_PATH(bapath, path, BitmapAndPath); REPARAMETERIZE_CHILD_PATH_LIST(bapath->bitmapquals); new_path = (Path *) bapath; } @@ -4182,9 +4207,8 @@ do { \ case T_BitmapOrPath: { - BitmapOrPath *bopath; + BitmapOrPath *bopath = (BitmapOrPath *) path; - FLAT_COPY_PATH(bopath, path, BitmapOrPath); REPARAMETERIZE_CHILD_PATH_LIST(bopath->bitmapquals); new_path = (Path *) bopath; } @@ -4192,10 +4216,10 @@ do { \ case T_ForeignPath: { - ForeignPath *fpath; + ForeignPath *fpath = (ForeignPath *) path; ReparameterizeForeignPathByChild_function rfpc_func; - FLAT_COPY_PATH(fpath, path, ForeignPath); + ADJUST_CHILD_ATTRS(fpath->path.parent->baserestrictinfo); if (fpath->fdw_outerpath) REPARAMETERIZE_CHILD_PATH(fpath->fdw_outerpath); if (fpath->fdw_restrictinfo) @@ -4213,9 +4237,9 @@ do { \ case T_CustomPath: { - CustomPath *cpath; + CustomPath *cpath = (CustomPath *) path; - FLAT_COPY_PATH(cpath, path, CustomPath); + ADJUST_CHILD_ATTRS(cpath->path.parent->baserestrictinfo); REPARAMETERIZE_CHILD_PATH_LIST(cpath->custom_paths); if (cpath->custom_restrictinfo) ADJUST_CHILD_ATTRS(cpath->custom_restrictinfo); @@ -4231,12 +4255,9 @@ do { \ case T_NestPath: { - JoinPath *jpath; - NestPath *npath; - - FLAT_COPY_PATH(npath, path, NestPath); + NestPath *npath = (NestPath *) path; + JoinPath *jpath = (JoinPath *) npath; - jpath = (JoinPath *) npath; REPARAMETERIZE_CHILD_PATH(jpath->outerjoinpath); REPARAMETERIZE_CHILD_PATH(jpath->innerjoinpath); ADJUST_CHILD_ATTRS(jpath->joinrestrictinfo); @@ -4246,12 +4267,9 @@ do { \ case T_MergePath: { - JoinPath *jpath; - MergePath *mpath; + MergePath *mpath = (MergePath *) path; + JoinPath *jpath = (JoinPath *) mpath; - FLAT_COPY_PATH(mpath, path, MergePath); - - jpath = (JoinPath *) mpath; REPARAMETERIZE_CHILD_PATH(jpath->outerjoinpath); REPARAMETERIZE_CHILD_PATH(jpath->innerjoinpath); ADJUST_CHILD_ATTRS(jpath->joinrestrictinfo); @@ -4262,12 +4280,9 @@ do { \ case T_HashPath: { - JoinPath *jpath; - HashPath *hpath; - - FLAT_COPY_PATH(hpath, path, HashPath); + HashPath *hpath = (HashPath *) path; + JoinPath *jpath = (JoinPath *) hpath; - jpath = (JoinPath *) hpath; REPARAMETERIZE_CHILD_PATH(jpath->outerjoinpath); REPARAMETERIZE_CHILD_PATH(jpath->innerjoinpath); ADJUST_CHILD_ATTRS(jpath->joinrestrictinfo); @@ -4278,9 +4293,8 @@ do { \ case T_AppendPath: { - AppendPath *apath; + AppendPath *apath = (AppendPath *) path; - FLAT_COPY_PATH(apath, path, AppendPath); REPARAMETERIZE_CHILD_PATH_LIST(apath->subpaths); new_path = (Path *) apath; } @@ -4288,9 +4302,8 @@ do { \ case T_MaterialPath: { - MaterialPath *mpath; + MaterialPath *mpath = (MaterialPath *) path; - FLAT_COPY_PATH(mpath, path, MaterialPath); REPARAMETERIZE_CHILD_PATH(mpath->subpath); new_path = (Path *) mpath; } @@ -4298,9 +4311,8 @@ do { \ case T_MemoizePath: { - MemoizePath *mpath; + MemoizePath *mpath = (MemoizePath *) path; - FLAT_COPY_PATH(mpath, path, MemoizePath); REPARAMETERIZE_CHILD_PATH(mpath->subpath); ADJUST_CHILD_ATTRS(mpath->param_exprs); new_path = (Path *) mpath; @@ -4309,16 +4321,14 @@ do { \ case T_GatherPath: { - GatherPath *gpath; + GatherPath *gpath = (GatherPath *) path; - FLAT_COPY_PATH(gpath, path, GatherPath); REPARAMETERIZE_CHILD_PATH(gpath->subpath); new_path = (Path *) gpath; } break; default: - /* We don't know how to reparameterize this path. */ return NULL; } @@ -4379,8 +4389,146 @@ do { \ } /* + * path_is_reparameterizable_by_child + * Given a path parameterized by the parent of the given child relation, + * see if it can be translated to be parameterized by the child relation. + * + * This must return true if and only if reparameterize_path_by_child() + * would succeed on this path. Currently it's sufficient to verify that + * the path and all of its subpaths (if any) are of the types handled by + * that function. However, subpaths that are not parameterized can be + * disregarded since they won't require translation. + */ +bool +path_is_reparameterizable_by_child(Path *path, RelOptInfo *child_rel) +{ +#define REJECT_IF_PATH_NOT_REPARAMETERIZABLE(path) \ +do { \ + if (!path_is_reparameterizable_by_child(path, child_rel)) \ + return false; \ +} while(0) + +#define REJECT_IF_PATH_LIST_NOT_REPARAMETERIZABLE(pathlist) \ +do { \ + if (!pathlist_is_reparameterizable_by_child(pathlist, child_rel)) \ + return false; \ +} while(0) + + /* + * If the path is not parameterized by the parent of the given relation, + * it doesn't need reparameterization. + */ + if (!path->param_info || + !bms_overlap(PATH_REQ_OUTER(path), child_rel->top_parent_relids)) + return true; + + /* + * Check that the path type is one that reparameterize_path_by_child() can + * handle, and recursively check subpaths. + */ + switch (nodeTag(path)) + { + case T_Path: + case T_IndexPath: + break; + + case T_BitmapHeapPath: + { + BitmapHeapPath *bhpath = (BitmapHeapPath *) path; + + REJECT_IF_PATH_NOT_REPARAMETERIZABLE(bhpath->bitmapqual); + } + break; + + case T_BitmapAndPath: + { + BitmapAndPath *bapath = (BitmapAndPath *) path; + + REJECT_IF_PATH_LIST_NOT_REPARAMETERIZABLE(bapath->bitmapquals); + } + break; + + case T_BitmapOrPath: + { + BitmapOrPath *bopath = (BitmapOrPath *) path; + + REJECT_IF_PATH_LIST_NOT_REPARAMETERIZABLE(bopath->bitmapquals); + } + break; + + case T_ForeignPath: + { + ForeignPath *fpath = (ForeignPath *) path; + + if (fpath->fdw_outerpath) + REJECT_IF_PATH_NOT_REPARAMETERIZABLE(fpath->fdw_outerpath); + } + break; + + case T_CustomPath: + { + CustomPath *cpath = (CustomPath *) path; + + REJECT_IF_PATH_LIST_NOT_REPARAMETERIZABLE(cpath->custom_paths); + } + break; + + case T_NestPath: + case T_MergePath: + case T_HashPath: + { + JoinPath *jpath = (JoinPath *) path; + + REJECT_IF_PATH_NOT_REPARAMETERIZABLE(jpath->outerjoinpath); + REJECT_IF_PATH_NOT_REPARAMETERIZABLE(jpath->innerjoinpath); + } + break; + + case T_AppendPath: + { + AppendPath *apath = (AppendPath *) path; + + REJECT_IF_PATH_LIST_NOT_REPARAMETERIZABLE(apath->subpaths); + } + break; + + case T_MaterialPath: + { + MaterialPath *mpath = (MaterialPath *) path; + + REJECT_IF_PATH_NOT_REPARAMETERIZABLE(mpath->subpath); + } + break; + + case T_MemoizePath: + { + MemoizePath *mpath = (MemoizePath *) path; + + REJECT_IF_PATH_NOT_REPARAMETERIZABLE(mpath->subpath); + } + break; + + case T_GatherPath: + { + GatherPath *gpath = (GatherPath *) path; + + REJECT_IF_PATH_NOT_REPARAMETERIZABLE(gpath->subpath); + } + break; + + default: + /* We don't know how to reparameterize this path. */ + return false; + } + + return true; +} + +/* * reparameterize_pathlist_by_child * Helper function to reparameterize a list of paths by given child rel. + * + * Returns NIL to indicate failure, so pathlist had better not be NIL. */ static List * reparameterize_pathlist_by_child(PlannerInfo *root, @@ -4406,3 +4554,23 @@ reparameterize_pathlist_by_child(PlannerInfo *root, return result; } + +/* + * pathlist_is_reparameterizable_by_child + * Helper function to check if a list of paths can be reparameterized. + */ +static bool +pathlist_is_reparameterizable_by_child(List *pathlist, RelOptInfo *child_rel) +{ + ListCell *lc; + + foreach(lc, pathlist) + { + Path *path = (Path *) lfirst(lc); + + if (!path_is_reparameterizable_by_child(path, child_rel)) + return false; + } + + return true; +} diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index c43d97b48a6..99c2f955aab 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -298,6 +298,8 @@ extern Path *reparameterize_path(PlannerInfo *root, Path *path, double loop_count); extern Path *reparameterize_path_by_child(PlannerInfo *root, Path *path, RelOptInfo *child_rel); +extern bool path_is_reparameterizable_by_child(Path *path, + RelOptInfo *child_rel); /* * prototypes for relnode.c diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out index 6560fe2416f..6d07f86b9bc 100644 --- a/src/test/regress/expected/partition_join.out +++ b/src/test/regress/expected/partition_join.out @@ -505,6 +505,98 @@ SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL 550 | | (12 rows) +-- lateral reference in sample scan +EXPLAIN (COSTS OFF) +SELECT * FROM prt1 t1 JOIN LATERAL + (SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s + ON t1.a = s.a; + QUERY PLAN +------------------------------------------------------------- + Append + -> Nested Loop + -> Seq Scan on prt1_p1 t1_1 + -> Sample Scan on prt1_p1 t2_1 + Sampling: system (t1_1.a) REPEATABLE (t1_1.b) + Filter: (t1_1.a = a) + -> Nested Loop + -> Seq Scan on prt1_p2 t1_2 + -> Sample Scan on prt1_p2 t2_2 + Sampling: system (t1_2.a) REPEATABLE (t1_2.b) + Filter: (t1_2.a = a) + -> Nested Loop + -> Seq Scan on prt1_p3 t1_3 + -> Sample Scan on prt1_p3 t2_3 + Sampling: system (t1_3.a) REPEATABLE (t1_3.b) + Filter: (t1_3.a = a) +(16 rows) + +-- lateral reference in scan's restriction clauses +EXPLAIN (COSTS OFF) +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.a; + QUERY PLAN +--------------------------------------------------------------- + Aggregate + -> Append + -> Nested Loop + -> Seq Scan on prt1_p1 t1_1 + -> Index Scan using iprt2_p1_b on prt2_p1 t2_1 + Index Cond: (b = t1_1.a) + Filter: (t1_1.b = a) + -> Nested Loop + -> Seq Scan on prt1_p2 t1_2 + -> Index Scan using iprt2_p2_b on prt2_p2 t2_2 + Index Cond: (b = t1_2.a) + Filter: (t1_2.b = a) + -> Nested Loop + -> Seq Scan on prt1_p3 t1_3 + -> Index Scan using iprt2_p3_b on prt2_p3 t2_3 + Index Cond: (b = t1_3.a) + Filter: (t1_3.b = a) +(17 rows) + +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.a; + count +------- + 100 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.b; + QUERY PLAN +-------------------------------------------------------------------- + Aggregate + -> Append + -> Nested Loop + -> Seq Scan on prt1_p1 t1_1 + -> Index Only Scan using iprt2_p1_b on prt2_p1 t2_1 + Index Cond: (b = t1_1.a) + Filter: (b = t1_1.b) + -> Nested Loop + -> Seq Scan on prt1_p2 t1_2 + -> Index Only Scan using iprt2_p2_b on prt2_p2 t2_2 + Index Cond: (b = t1_2.a) + Filter: (b = t1_2.b) + -> Nested Loop + -> Seq Scan on prt1_p3 t1_3 + -> Index Only Scan using iprt2_p3_b on prt2_p3 t2_3 + Index Cond: (b = t1_3.a) + Filter: (b = t1_3.b) +(17 rows) + +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.b; + count +------- + 5 +(1 row) + -- bug with inadequate sort key representation SET enable_partitionwise_aggregate TO true; SET enable_hashjoin TO false; @@ -1944,6 +2036,82 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL 550 | 0 | 0002 | | | | | (12 rows) +-- partitionwise join with lateral reference in sample scan +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_l t1 JOIN LATERAL + (SELECT * FROM prt1_l t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s + ON t1.a = s.a AND t1.b = s.b AND t1.c = s.c; + QUERY PLAN +---------------------------------------------------------------------------------------- + Append + -> Nested Loop + -> Seq Scan on prt1_l_p1 t1_1 + -> Sample Scan on prt1_l_p1 t2_1 + Sampling: system (t1_1.a) REPEATABLE (t1_1.b) + Filter: ((t1_1.a = a) AND (t1_1.b = b) AND ((t1_1.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p2_p1 t1_2 + -> Sample Scan on prt1_l_p2_p1 t2_2 + Sampling: system (t1_2.a) REPEATABLE (t1_2.b) + Filter: ((t1_2.a = a) AND (t1_2.b = b) AND ((t1_2.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p2_p2 t1_3 + -> Sample Scan on prt1_l_p2_p2 t2_3 + Sampling: system (t1_3.a) REPEATABLE (t1_3.b) + Filter: ((t1_3.a = a) AND (t1_3.b = b) AND ((t1_3.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p3_p1 t1_4 + -> Sample Scan on prt1_l_p3_p1 t2_4 + Sampling: system (t1_4.a) REPEATABLE (t1_4.b) + Filter: ((t1_4.a = a) AND (t1_4.b = b) AND ((t1_4.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p3_p2 t1_5 + -> Sample Scan on prt1_l_p3_p2 t2_5 + Sampling: system (t1_5.a) REPEATABLE (t1_5.b) + Filter: ((t1_5.a = a) AND (t1_5.b = b) AND ((t1_5.c)::text = (c)::text)) +(26 rows) + +-- partitionwise join with lateral reference in scan's restriction clauses +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s + ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c + WHERE s.t1b = s.a; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------- + Aggregate + -> Append + -> Nested Loop + -> Seq Scan on prt1_l_p1 t1_1 + -> Seq Scan on prt2_l_p1 t2_1 + Filter: ((a = t1_1.b) AND (t1_1.a = b) AND (t1_1.b = a) AND ((t1_1.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p2_p1 t1_2 + -> Seq Scan on prt2_l_p2_p1 t2_2 + Filter: ((a = t1_2.b) AND (t1_2.a = b) AND (t1_2.b = a) AND ((t1_2.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p2_p2 t1_3 + -> Seq Scan on prt2_l_p2_p2 t2_3 + Filter: ((a = t1_3.b) AND (t1_3.a = b) AND (t1_3.b = a) AND ((t1_3.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p3_p1 t1_4 + -> Seq Scan on prt2_l_p3_p1 t2_4 + Filter: ((a = t1_4.b) AND (t1_4.a = b) AND (t1_4.b = a) AND ((t1_4.c)::text = (c)::text)) + -> Nested Loop + -> Seq Scan on prt1_l_p3_p2 t1_5 + -> Seq Scan on prt2_l_p3_p2 t2_5 + Filter: ((a = t1_5.b) AND (t1_5.a = b) AND (t1_5.b = a) AND ((t1_5.c)::text = (c)::text)) +(22 rows) + +SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s + ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c + WHERE s.t1b = s.a; + count +------- + 100 +(1 row) + -- join with one side empty EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c; diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql index 48daf3aee39..128ce8376e6 100644 --- a/src/test/regress/sql/partition_join.sql +++ b/src/test/regress/sql/partition_join.sql @@ -100,6 +100,29 @@ SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL (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; +-- lateral reference in sample scan +EXPLAIN (COSTS OFF) +SELECT * FROM prt1 t1 JOIN LATERAL + (SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s + ON t1.a = s.a; + +-- lateral reference in scan's restriction clauses +EXPLAIN (COSTS OFF) +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.a; +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.a; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.b; +SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s + ON t1.a = s.b WHERE s.t1b = s.b; + -- bug with inadequate sort key representation SET enable_partitionwise_aggregate TO true; SET enable_hashjoin TO false; @@ -387,6 +410,23 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL (SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a; +-- partitionwise join with lateral reference in sample scan +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_l t1 JOIN LATERAL + (SELECT * FROM prt1_l t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s + ON t1.a = s.a AND t1.b = s.b AND t1.c = s.c; + +-- partitionwise join with lateral reference in scan's restriction clauses +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s + ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c + WHERE s.t1b = s.a; +SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL + (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s + ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c + WHERE s.t1b = s.a; + -- join with one side empty EXPLAIN (COSTS OFF) SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c; |