0003-Add-new-upper-rel-to-represent-projecting-toplevel-s.patch
application/octet-stream
Filename: 0003-Add-new-upper-rel-to-represent-projecting-toplevel-s.patch
Type: application/octet-stream
Part: 4
Patch
Format: format-patch
Series: patch 0003
Subject: Add new upper rel to represent projecting toplevel scan/join rel.
| File | + | − |
|---|---|---|
| src/backend/optimizer/plan/planner.c | 161 | 101 |
| src/include/nodes/relation.h | 1 | 0 |
| src/test/regress/expected/partition_join.out | 380 | 392 |
From d077b7fab8c771f52fe24496b2d943742f189d20 Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Mon, 12 Mar 2018 13:16:30 -0400
Subject: [PATCH 3/7] Add new upper rel to represent projecting toplevel
scan/join rel.
UPPERREL_TLIST represents the result of applying the scan/join target
to the final scan/join relation. This requires us to use
create_projection_path() rather than apply_projection_to_path()
when projection non-partial paths from the scan/join relation, but
it also enables us to avoid needing to modify those paths in place.
It also avoids the need to clear the topmost scan/join rel's
partial_pathlist when the scan/join target is not parallel-safe, which
is sort of hack; see commit 3bf05e096b9f8375e640c5d7996aa57efd7f240c
for an example of a previous fix that eliminated a similar hack.
This also cleans up what appears to be incorrect SRF handling
introduced in commit e2f1eb0ee30d144628ab523432320f174a2c8966: the old
code had no knowledge of SRFs for child scan/join rels.
Patch by me, reviewed by Amit Kapila.
---
src/backend/optimizer/plan/planner.c | 262 +++++----
src/include/nodes/relation.h | 1 +
src/test/regress/expected/partition_join.out | 772 +++++++++++++--------------
3 files changed, 542 insertions(+), 493 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 50f858e420..1e1b363402 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -220,11 +220,11 @@ static RelOptInfo *create_partial_grouping_paths(PlannerInfo *root,
static void gather_grouping_paths(PlannerInfo *root, RelOptInfo *rel);
static bool can_partial_agg(PlannerInfo *root,
const AggClauseCosts *agg_costs);
-static void apply_scanjoin_target_to_paths(PlannerInfo *root,
- RelOptInfo *rel,
- PathTarget *scanjoin_target,
- bool scanjoin_target_parallel_safe,
- bool modify_in_place);
+static RelOptInfo *create_tlist_paths(PlannerInfo *root,
+ RelOptInfo *input_rel,
+ List *scanjoin_targets,
+ List *scanjoin_targets_contain_srfs,
+ bool scanjoin_target_parallel_safe);
static void create_partitionwise_grouping_paths(PlannerInfo *root,
RelOptInfo *input_rel,
RelOptInfo *grouped_rel,
@@ -1962,25 +1962,21 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
}
else
{
- /* initialize lists, just to keep compiler quiet */
+ /* initialize lists; for most of these, dummy values are OK */
final_targets = final_targets_contain_srfs = NIL;
sort_input_targets = sort_input_targets_contain_srfs = NIL;
grouping_targets = grouping_targets_contain_srfs = NIL;
- scanjoin_targets = scanjoin_targets_contain_srfs = NIL;
+ scanjoin_targets = list_make1(scanjoin_target);
+ scanjoin_targets_contain_srfs = NIL;
}
/*
- * Forcibly apply SRF-free scan/join target to all the Paths for the
- * scan/join rel.
+ * Apply SRF-free scan/join target to all Paths for the scanjoin rel
+ * to produce paths for the tlist rel.
*/
- apply_scanjoin_target_to_paths(root, current_rel, scanjoin_target,
- scanjoin_target_parallel_safe, true);
-
- /* Now fix things up if scan/join target contains SRFs */
- if (parse->hasTargetSRFs)
- adjust_paths_for_srfs(root, current_rel,
- scanjoin_targets,
- scanjoin_targets_contain_srfs);
+ current_rel = create_tlist_paths(root, current_rel, scanjoin_targets,
+ scanjoin_targets_contain_srfs,
+ scanjoin_target_parallel_safe);
/*
* Save the various upper-rel PathTargets we just computed into
@@ -1992,6 +1988,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
root->upper_targets[UPPERREL_FINAL] = final_target;
root->upper_targets[UPPERREL_WINDOW] = sort_input_target;
root->upper_targets[UPPERREL_GROUP_AGG] = grouping_target;
+ root->upper_targets[UPPERREL_TLIST] = scanjoin_target;
/*
* If we have grouping and/or aggregation, consider ways to implement
@@ -6780,97 +6777,181 @@ can_partial_agg(PlannerInfo *root, const AggClauseCosts *agg_costs)
}
/*
- * apply_scanjoin_target_to_paths
+ * create_tlist_paths
*
- * Applies scan/join target to all the Paths for the scan/join rel.
+ * Build up a new relation representing the result of applying the final
+ * scan/join targetlist to the paths returned for the topmost scan/join rel.
*/
-static void
-apply_scanjoin_target_to_paths(PlannerInfo *root,
- RelOptInfo *rel,
- PathTarget *scanjoin_target,
- bool scanjoin_target_parallel_safe,
- bool modify_in_place)
+static RelOptInfo *
+create_tlist_paths(PlannerInfo *root,
+ RelOptInfo *input_rel,
+ List *scanjoin_targets,
+ List *scanjoin_targets_contain_srfs,
+ bool scanjoin_target_parallel_safe)
{
ListCell *lc;
+ RelOptInfo *tlist_rel;
+
+ check_stack_depth();
/*
- * In principle we should re-run set_cheapest() here to identify the
- * cheapest path, but it seems unlikely that adding the same tlist eval
- * costs to all the paths would change that, so we don't bother. Instead,
- * just assume that the cheapest-startup and cheapest-total paths remain
- * so. (There should be no parameterized paths anymore, so we needn't
- * worry about updating cheapest_parameterized_paths.)
+ * Create a new upper relation to represent the result of scan/join
+ * projection.
*/
- foreach(lc, rel->pathlist)
+ tlist_rel = fetch_upper_rel(root, UPPERREL_TLIST, input_rel->relids);
+ if (IS_OTHER_REL(input_rel))
+ tlist_rel->reloptkind = RELOPT_OTHER_UPPER_REL;
+ tlist_rel->rows = input_rel->rows;
+ tlist_rel->reltarget = llast_node(PathTarget, scanjoin_targets);
+ tlist_rel->consider_parallel = input_rel->consider_parallel &&
+ scanjoin_target_parallel_safe;
+
+ /*
+ * If the input rel belongs to a single FDW, so does the tlist rel.
+ */
+ tlist_rel->serverid = input_rel->serverid;
+ tlist_rel->userid = input_rel->userid;
+ tlist_rel->useridiscurrent = input_rel->useridiscurrent;
+ tlist_rel->fdwroutine = input_rel->fdwroutine;
+
+ /* If the input rel is dummy, so is this. */
+ if (IS_DUMMY_REL(input_rel))
{
- Path *subpath = (Path *) lfirst(lc);
- Path *newpath;
+ mark_dummy_rel(tlist_rel);
- Assert(subpath->param_info == NULL);
+ return tlist_rel;
+ }
+
+ /*
+ * If the input rel is partitioned, so is the tlist rel. And, in fact, we
+ * want to do the projection steps on a per-partition basis in this case.
+ * Since Append is not projection-capable, that might save a separate
+ * Result node, and it also is important for partitionwise aggregate.
+ */
+ if (IS_PARTITIONED_REL(input_rel))
+ {
+ int nparts = input_rel->nparts;
+ int partition_idx;
+ List *live_children = NIL;
/*
- * Don't use apply_projection_to_path() when modify_in_place is false,
- * because there could be other pointers to these paths, and therefore
- * we mustn't modify them in place.
+ * Copy partitioning properties from underlying relation -- except for
+ * part_rels.
*/
- if (modify_in_place)
- newpath = apply_projection_to_path(root, rel, subpath,
- scanjoin_target);
- else
- newpath = (Path *) create_projection_path(root, rel, subpath,
- scanjoin_target);
+ tlist_rel->part_scheme = input_rel->part_scheme;
+ tlist_rel->nparts = input_rel->nparts;
+ tlist_rel->boundinfo = input_rel->boundinfo;
+ tlist_rel->partexprs = input_rel->partexprs;
+ tlist_rel->nullable_partexprs = input_rel->nullable_partexprs;
- /* If we had to add a Result, newpath is different from subpath */
- if (newpath != subpath)
+ /*
+ * Populate part_rels by generating a child tlist rel for each child
+ * input rel.
+ */
+ tlist_rel->part_rels =
+ palloc(tlist_rel->nparts * sizeof(RelOptInfo *));
+ for (partition_idx = 0; partition_idx < nparts; partition_idx++)
{
- lfirst(lc) = newpath;
- if (subpath == rel->cheapest_startup_path)
- rel->cheapest_startup_path = newpath;
- if (subpath == rel->cheapest_total_path)
- rel->cheapest_total_path = newpath;
+ RelOptInfo *child_input_rel = input_rel->part_rels[partition_idx];
+ RelOptInfo *child_tlist_rel;
+ ListCell *lc;
+ AppendRelInfo **appinfos;
+ int nappinfos;
+ List *child_scanjoin_targets = NIL;
+
+ /* Translate scan/join targets for this child. */
+ appinfos = find_appinfos_by_relids(root, child_input_rel->relids,
+ &nappinfos);
+ foreach(lc, scanjoin_targets)
+ {
+ PathTarget *target = lfirst_node(PathTarget, lc);
+
+ target = copy_pathtarget(target);
+ target->exprs = (List *)
+ adjust_appendrel_attrs(root,
+ (Node *) target->exprs,
+ nappinfos, appinfos);
+ child_scanjoin_targets = lappend(child_scanjoin_targets,
+ target);
+ }
+ pfree(appinfos);
+
+ /* Now we can build the child rel. */
+ child_tlist_rel =
+ create_tlist_paths(root, child_input_rel,
+ child_scanjoin_targets,
+ scanjoin_targets_contain_srfs,
+ scanjoin_target_parallel_safe);
+ tlist_rel->part_rels[partition_idx] = child_tlist_rel;
+
+ /* Save non-dummy children for Append paths. */
+ if (!IS_DUMMY_REL(child_tlist_rel))
+ live_children = lappend(live_children, child_tlist_rel);
}
- }
- /*
- * Upper planning steps which make use of the top scan/join rel's partial
- * pathlist will expect partial paths for that rel to produce the same
- * output as complete paths ... and we just changed the output for the
- * complete paths, so we'll need to do the same thing for partial paths.
- * But only parallel-safe expressions can be computed by partial paths.
- */
- if (rel->partial_pathlist && scanjoin_target_parallel_safe)
+ /* Build paths for this relation by appending child paths. */
+ add_paths_to_append_rel(root, tlist_rel, live_children);
+ }
+ else
{
- /* Apply the scan/join target to each partial path */
- foreach(lc, rel->partial_pathlist)
+ PathTarget *scanjoin_target;
+
+ /* Extract SRF-free scan/join target. */
+ scanjoin_target = linitial_node(PathTarget, scanjoin_targets);
+
+ /*
+ * This is not a partitioned relation, so just create a projection
+ * path for each input path, in each case applying the SRF-free
+ * scan/join target.
+ */
+ foreach(lc, input_rel->pathlist)
{
Path *subpath = (Path *) lfirst(lc);
Path *newpath;
- /* Shouldn't have any parameterized paths anymore */
Assert(subpath->param_info == NULL);
- /*
- * Don't use apply_projection_to_path() here, because there could
- * be other pointers to these paths, and therefore we mustn't
- * modify them in place.
- */
- newpath = (Path *) create_projection_path(root,
- rel,
- subpath,
+ newpath = (Path *) create_projection_path(root, tlist_rel, subpath,
scanjoin_target);
- lfirst(lc) = newpath;
+
+ add_path(tlist_rel, newpath);
}
- }
- else
- {
+
/*
- * In the unfortunate event that scanjoin_target is not parallel-safe,
- * we can't apply it to the partial paths; in that case, we'll need to
- * forget about the partial paths, which aren't valid input for upper
- * planning steps.
+ * If parallel query is possible at this level, also generate partial
+ * paths for the tlist_rel; these may be useful to upper planning
+ * stages.
*/
- rel->partial_pathlist = NIL;
+ if (tlist_rel->consider_parallel)
+ {
+ /* Apply the scan/join target to each partial path */
+ foreach(lc, input_rel->partial_pathlist)
+ {
+ Path *subpath = (Path *) lfirst(lc);
+ Path *newpath;
+
+ /* Shouldn't have any parameterized paths anymore */
+ Assert(subpath->param_info == NULL);
+
+ newpath = (Path *) create_projection_path(root,
+ tlist_rel,
+ subpath,
+ scanjoin_target);
+ add_partial_path(tlist_rel, newpath);
+ }
+ }
+
+ /* Now fix things up if scan/join target contains SRFs */
+ if (root->parse->hasTargetSRFs)
+ adjust_paths_for_srfs(root, tlist_rel,
+ scanjoin_targets,
+ scanjoin_targets_contain_srfs);
}
+
+ /* Determine cheapest paths, for the benefit of future planning steps. */
+ set_cheapest(tlist_rel);
+
+ return tlist_rel;
}
/*
@@ -6917,7 +6998,6 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
PathTarget *child_target = copy_pathtarget(target);
AppendRelInfo **appinfos;
int nappinfos;
- PathTarget *scanjoin_target;
GroupPathExtraData child_extra;
RelOptInfo *child_grouped_rel;
RelOptInfo *child_partially_grouped_rel;
@@ -6974,26 +7054,6 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
continue;
}
- /*
- * Copy pathtarget from underneath scan/join as we are modifying it
- * and translate its Vars with respect to this appendrel. The input
- * relation's reltarget might not be the final scanjoin_target, but
- * the pathtarget any given individual path should be.
- */
- scanjoin_target =
- copy_pathtarget(input_rel->cheapest_startup_path->pathtarget);
- scanjoin_target->exprs = (List *)
- adjust_appendrel_attrs(root,
- (Node *) scanjoin_target->exprs,
- nappinfos, appinfos);
-
- /*
- * Forcibly apply scan/join target to all the Paths for the scan/join
- * rel.
- */
- apply_scanjoin_target_to_paths(root, child_input_rel, scanjoin_target,
- extra->target_parallel_safe, false);
-
/* Create grouping paths for this child relation. */
create_ordinary_grouping_paths(root, child_input_rel,
child_grouped_rel,
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index abbbda9e91..d4bffbc281 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -71,6 +71,7 @@ typedef struct AggClauseCosts
typedef enum UpperRelationKind
{
UPPERREL_SETOP, /* result of UNION/INTERSECT/EXCEPT, if any */
+ UPPERREL_TLIST, /* result of projecting final scan/join rel */
UPPERREL_PARTIAL_GROUP_AGG, /* result of partial grouping/aggregation, if
* any */
UPPERREL_GROUP_AGG, /* result of grouping/aggregation, if any */
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 4fccd9ae54..b983f9c506 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -65,31 +65,30 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
-- left outer join, with whole-row reference
EXPLAIN (COSTS OFF)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Sort
Sort Key: t1.a, t2.b
- -> Result
- -> Append
- -> Hash Right Join
- Hash Cond: (t2.b = t1.a)
- -> Seq Scan on prt2_p1 t2
- -> Hash
- -> Seq Scan on prt1_p1 t1
- Filter: (b = 0)
- -> Hash Right 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 Right 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)
-(22 rows)
+ -> Append
+ -> Hash Right Join
+ Hash Cond: (t2.b = t1.a)
+ -> Seq Scan on prt2_p1 t2
+ -> Hash
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Hash Right 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 Right 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)
+(21 rows)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
t1 | t2
@@ -111,30 +110,29 @@ SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER
-- right outer join
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Sort
Sort Key: t1.a, t2.b
- -> Result
- -> Append
- -> Hash Right Join
- Hash Cond: (t1.a = t2.b)
- -> Seq Scan on prt1_p1 t1
- -> Hash
- -> Seq Scan on prt2_p1 t2
- Filter: (a = 0)
- -> Hash Right Join
- Hash Cond: (t1_1.a = t2_1.b)
- -> Seq Scan on prt1_p2 t1_1
- -> Hash
- -> Seq Scan on prt2_p2 t2_1
- Filter: (a = 0)
- -> Nested Loop Left Join
- -> Seq Scan on prt2_p3 t2_2
+ -> Append
+ -> Hash Right Join
+ Hash Cond: (t1.a = t2.b)
+ -> Seq Scan on prt1_p1 t1
+ -> Hash
+ -> Seq Scan on prt2_p1 t2
Filter: (a = 0)
- -> Index Scan using iprt1_p3_a on prt1_p3 t1_2
- Index Cond: (a = t2_2.b)
-(21 rows)
+ -> Hash Right Join
+ Hash Cond: (t1_1.a = t2_1.b)
+ -> Seq Scan on prt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on prt2_p2 t2_1
+ Filter: (a = 0)
+ -> Nested Loop Left Join
+ -> Seq Scan on prt2_p3 t2_2
+ Filter: (a = 0)
+ -> Index Scan using iprt1_p3_a on prt1_p3 t1_2
+ Index Cond: (a = t2_2.b)
+(20 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
a | c | b | c
@@ -375,37 +373,36 @@ EXPLAIN (COSTS OFF)
SELECT * FROM prt1 t1 LEFT JOIN LATERAL
(SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
ON t1.a = ss.t2a WHERE t1.b = 0 ORDER BY t1.a;
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------
Sort
Sort Key: t1.a
- -> Result
- -> Append
- -> Nested Loop Left Join
- -> Seq Scan on prt1_p1 t1
- Filter: (b = 0)
- -> Nested Loop
- -> Index Only Scan using iprt1_p1_a on prt1_p1 t2
- Index Cond: (a = t1.a)
- -> Index Scan using iprt2_p1_b on prt2_p1 t3
- Index Cond: (b = t2.a)
- -> Nested Loop Left Join
- -> Seq Scan on prt1_p2 t1_1
- Filter: (b = 0)
- -> Nested Loop
- -> Index Only Scan using iprt1_p2_a on prt1_p2 t2_1
- Index Cond: (a = t1_1.a)
- -> Index Scan using iprt2_p2_b on prt2_p2 t3_1
- Index Cond: (b = t2_1.a)
- -> Nested Loop Left Join
- -> Seq Scan on prt1_p3 t1_2
- Filter: (b = 0)
- -> Nested Loop
- -> Index Only Scan using iprt1_p3_a on prt1_p3 t2_2
- Index Cond: (a = t1_2.a)
- -> Index Scan using iprt2_p3_b on prt2_p3 t3_2
- Index Cond: (b = t2_2.a)
-(28 rows)
+ -> Append
+ -> Nested Loop Left Join
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Nested Loop
+ -> Index Only Scan using iprt1_p1_a on prt1_p1 t2
+ Index Cond: (a = t1.a)
+ -> Index Scan using iprt2_p1_b on prt2_p1 t3
+ Index Cond: (b = t2.a)
+ -> Nested Loop Left Join
+ -> Seq Scan on prt1_p2 t1_1
+ Filter: (b = 0)
+ -> Nested Loop
+ -> Index Only Scan using iprt1_p2_a on prt1_p2 t2_1
+ Index Cond: (a = t1_1.a)
+ -> Index Scan using iprt2_p2_b on prt2_p2 t3_1
+ Index Cond: (b = t2_1.a)
+ -> Nested Loop Left Join
+ -> Seq Scan on prt1_p3 t1_2
+ Filter: (b = 0)
+ -> Nested Loop
+ -> Index Only Scan using iprt1_p3_a on prt1_p3 t2_2
+ Index Cond: (a = t1_2.a)
+ -> Index Scan using iprt2_p3_b on prt2_p3 t3_2
+ Index Cond: (b = t2_2.a)
+(27 rows)
SELECT * FROM prt1 t1 LEFT JOIN LATERAL
(SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
@@ -538,92 +535,90 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_e t1, prt2_e t2 WHERE (t1.a + t1.b)/2 =
--
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
----------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Sort
Sort Key: t1.a
- -> Result
- -> Append
- -> Nested Loop
- Join Filter: (t1.a = ((t3.a + t3.b) / 2))
- -> Hash Join
+ -> Append
+ -> Nested Loop
+ Join Filter: (t1.a = ((t3.a + t3.b) / 2))
+ -> Hash Join
+ Hash Cond: (t2.b = t1.a)
+ -> Seq Scan on prt2_p1 t2
+ -> Hash
+ -> Seq Scan on prt1_p1 t1
+ Filter: (b = 0)
+ -> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3
+ Index Cond: (((a + b) / 2) = t2.b)
+ -> Nested Loop
+ Join Filter: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
+ -> 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)
+ -> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_1
+ Index Cond: (((a + b) / 2) = t2_1.b)
+ -> Nested Loop
+ Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
+ -> 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)
+ -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_2
+ Index Cond: (((a + b) / 2) = t2_2.b)
+(33 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
+ a | c | b | c | ?column? | c
+-----+------+-----+------+----------+---
+ 0 | 0000 | 0 | 0000 | 0 | 0
+ 150 | 0150 | 150 | 0150 | 300 | 0
+ 300 | 0300 | 300 | 0300 | 600 | 0
+ 450 | 0450 | 450 | 0450 | 900 | 0
+(4 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+ QUERY PLAN
+--------------------------------------------------------------
+ Sort
+ Sort Key: t1.a, t2.b, ((t3.a + t3.b))
+ -> Append
+ -> Hash Right Join
+ Hash Cond: (((t3.a + t3.b) / 2) = t1.a)
+ -> Seq Scan on prt1_e_p1 t3
+ -> Hash
+ -> Hash Right Join
Hash Cond: (t2.b = t1.a)
-> Seq Scan on prt2_p1 t2
-> Hash
-> Seq Scan on prt1_p1 t1
Filter: (b = 0)
- -> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3
- Index Cond: (((a + b) / 2) = t2.b)
- -> Nested Loop
- Join Filter: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
- -> Hash Join
+ -> Hash Right Join
+ Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a)
+ -> Seq Scan on prt1_e_p2 t3_1
+ -> Hash
+ -> Hash Right 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)
- -> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_1
- Index Cond: (((a + b) / 2) = t2_1.b)
- -> Nested Loop
- Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
- -> Hash Join
+ -> Hash Right Join
+ Hash Cond: (((t3_2.a + t3_2.b) / 2) = t1_2.a)
+ -> Seq Scan on prt1_e_p3 t3_2
+ -> Hash
+ -> Hash Right 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)
- -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_2
- Index Cond: (((a + b) / 2) = t2_2.b)
-(34 rows)
-
-SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
- a | c | b | c | ?column? | c
------+------+-----+------+----------+---
- 0 | 0000 | 0 | 0000 | 0 | 0
- 150 | 0150 | 150 | 0150 | 300 | 0
- 300 | 0300 | 300 | 0300 | 600 | 0
- 450 | 0450 | 450 | 0450 | 900 | 0
-(4 rows)
-
-EXPLAIN (COSTS OFF)
-SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
- QUERY PLAN
---------------------------------------------------------------------
- Sort
- Sort Key: t1.a, t2.b, ((t3.a + t3.b))
- -> Result
- -> Append
- -> Hash Right Join
- Hash Cond: (((t3.a + t3.b) / 2) = t1.a)
- -> Seq Scan on prt1_e_p1 t3
- -> Hash
- -> Hash Right Join
- Hash Cond: (t2.b = t1.a)
- -> Seq Scan on prt2_p1 t2
- -> Hash
- -> Seq Scan on prt1_p1 t1
- Filter: (b = 0)
- -> Hash Right Join
- Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a)
- -> Seq Scan on prt1_e_p2 t3_1
- -> Hash
- -> Hash Right 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 Right Join
- Hash Cond: (((t3_2.a + t3_2.b) / 2) = t1_2.a)
- -> Seq Scan on prt1_e_p3 t3_2
- -> Hash
- -> Hash Right 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)
-(34 rows)
+(33 rows)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
a | c | b | c | ?column? | c
@@ -644,40 +639,39 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Sort
Sort Key: t1.a, t2.b, ((t3.a + t3.b))
- -> Result
- -> Append
- -> Nested Loop Left Join
- -> Hash Right Join
- Hash Cond: (t1.a = ((t3.a + t3.b) / 2))
- -> Seq Scan on prt1_p1 t1
- -> Hash
- -> Seq Scan on prt1_e_p1 t3
- Filter: (c = 0)
- -> Index Scan using iprt2_p1_b on prt2_p1 t2
- Index Cond: (t1.a = b)
- -> Nested Loop Left Join
- -> Hash Right Join
- Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
- -> Seq Scan on prt1_p2 t1_1
- -> Hash
- -> Seq Scan on prt1_e_p2 t3_1
- Filter: (c = 0)
- -> Index Scan using iprt2_p2_b on prt2_p2 t2_1
- Index Cond: (t1_1.a = b)
- -> Nested Loop Left Join
- -> Hash Right Join
- Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
- -> Seq Scan on prt1_p3 t1_2
- -> Hash
- -> Seq Scan on prt1_e_p3 t3_2
- Filter: (c = 0)
- -> Index Scan using iprt2_p3_b on prt2_p3 t2_2
- Index Cond: (t1_2.a = b)
-(31 rows)
+ -> Append
+ -> Nested Loop Left Join
+ -> Hash Right Join
+ Hash Cond: (t1.a = ((t3.a + t3.b) / 2))
+ -> Seq Scan on prt1_p1 t1
+ -> Hash
+ -> Seq Scan on prt1_e_p1 t3
+ Filter: (c = 0)
+ -> Index Scan using iprt2_p1_b on prt2_p1 t2
+ Index Cond: (t1.a = b)
+ -> Nested Loop Left Join
+ -> Hash Right Join
+ Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
+ -> Seq Scan on prt1_p2 t1_1
+ -> Hash
+ -> Seq Scan on prt1_e_p2 t3_1
+ Filter: (c = 0)
+ -> Index Scan using iprt2_p2_b on prt2_p2 t2_1
+ Index Cond: (t1_1.a = b)
+ -> Nested Loop Left Join
+ -> Hash Right Join
+ Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
+ -> Seq Scan on prt1_p3 t1_2
+ -> Hash
+ -> Seq Scan on prt1_e_p3 t3_2
+ Filter: (c = 0)
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_2
+ Index Cond: (t1_2.a = b)
+(30 rows)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
a | c | b | c | ?column? | c
@@ -700,52 +694,51 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
-- make sure these go to null as expected
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE prt1_e.c = 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY t1.a, t2.b, t3.a + t3.b;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
Sort
Sort Key: prt1_p1.a, prt2_p1.b, ((prt1_e_p1.a + prt1_e_p1.b))
- -> Result
- -> Append
+ -> Append
+ -> Hash Full Join
+ Hash Cond: (prt1_p1.a = ((prt1_e_p1.a + prt1_e_p1.b) / 2))
+ Filter: ((prt1_p1.a = (50)) OR (prt2_p1.b = (75)) OR (((prt1_e_p1.a + prt1_e_p1.b) / 2) = (50)))
-> Hash Full Join
- Hash Cond: (prt1_p1.a = ((prt1_e_p1.a + prt1_e_p1.b) / 2))
- Filter: ((prt1_p1.a = (50)) OR (prt2_p1.b = (75)) OR (((prt1_e_p1.a + prt1_e_p1.b) / 2) = (50)))
- -> Hash Full Join
- Hash Cond: (prt1_p1.a = prt2_p1.b)
- -> Seq Scan on prt1_p1
- Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p1
- Filter: (a = 0)
+ Hash Cond: (prt1_p1.a = prt2_p1.b)
+ -> Seq Scan on prt1_p1
+ Filter: (b = 0)
-> Hash
- -> Seq Scan on prt1_e_p1
- Filter: (c = 0)
+ -> Seq Scan on prt2_p1
+ Filter: (a = 0)
+ -> Hash
+ -> Seq Scan on prt1_e_p1
+ Filter: (c = 0)
+ -> Hash Full Join
+ Hash Cond: (prt1_p2.a = ((prt1_e_p2.a + prt1_e_p2.b) / 2))
+ Filter: ((prt1_p2.a = (50)) OR (prt2_p2.b = (75)) OR (((prt1_e_p2.a + prt1_e_p2.b) / 2) = (50)))
-> Hash Full Join
- Hash Cond: (prt1_p2.a = ((prt1_e_p2.a + prt1_e_p2.b) / 2))
- Filter: ((prt1_p2.a = (50)) OR (prt2_p2.b = (75)) OR (((prt1_e_p2.a + prt1_e_p2.b) / 2) = (50)))
- -> Hash Full Join
- Hash Cond: (prt1_p2.a = prt2_p2.b)
- -> Seq Scan on prt1_p2
- Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p2
- Filter: (a = 0)
+ Hash Cond: (prt1_p2.a = prt2_p2.b)
+ -> Seq Scan on prt1_p2
+ Filter: (b = 0)
-> Hash
- -> Seq Scan on prt1_e_p2
- Filter: (c = 0)
+ -> Seq Scan on prt2_p2
+ Filter: (a = 0)
+ -> Hash
+ -> Seq Scan on prt1_e_p2
+ Filter: (c = 0)
+ -> Hash Full Join
+ Hash Cond: (prt1_p3.a = ((prt1_e_p3.a + prt1_e_p3.b) / 2))
+ Filter: ((prt1_p3.a = (50)) OR (prt2_p3.b = (75)) OR (((prt1_e_p3.a + prt1_e_p3.b) / 2) = (50)))
-> Hash Full Join
- Hash Cond: (prt1_p3.a = ((prt1_e_p3.a + prt1_e_p3.b) / 2))
- Filter: ((prt1_p3.a = (50)) OR (prt2_p3.b = (75)) OR (((prt1_e_p3.a + prt1_e_p3.b) / 2) = (50)))
- -> Hash Full Join
- Hash Cond: (prt1_p3.a = prt2_p3.b)
- -> Seq Scan on prt1_p3
- Filter: (b = 0)
- -> Hash
- -> Seq Scan on prt2_p3
- Filter: (a = 0)
+ Hash Cond: (prt1_p3.a = prt2_p3.b)
+ -> Seq Scan on prt1_p3
+ Filter: (b = 0)
-> Hash
- -> Seq Scan on prt1_e_p3
- Filter: (c = 0)
-(43 rows)
+ -> Seq Scan on prt2_p3
+ Filter: (a = 0)
+ -> Hash
+ -> Seq Scan on prt1_e_p3
+ Filter: (c = 0)
+(42 rows)
SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE prt1_e.c = 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY t1.a, t2.b, t3.a + t3.b;
a | phv | b | phv | ?column? | phv
@@ -933,61 +926,60 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Sort
Sort Key: t1.a, t2.b, ((t3.a + t3.b))
- -> Result
- -> Append
- -> Merge Left Join
- Merge Cond: (t1.a = t2.b)
- -> Sort
- Sort Key: t1.a
- -> Merge Left Join
- Merge Cond: ((((t3.a + t3.b) / 2)) = t1.a)
- -> Sort
- Sort Key: (((t3.a + t3.b) / 2))
- -> Seq Scan on prt1_e_p1 t3
- Filter: (c = 0)
- -> Sort
- Sort Key: t1.a
- -> Seq Scan on prt1_p1 t1
- -> Sort
- Sort Key: t2.b
- -> Seq Scan on prt2_p1 t2
- -> Merge Left Join
- Merge Cond: (t1_1.a = t2_1.b)
- -> Sort
- Sort Key: t1_1.a
- -> Merge Left Join
- Merge Cond: ((((t3_1.a + t3_1.b) / 2)) = t1_1.a)
- -> Sort
- Sort Key: (((t3_1.a + t3_1.b) / 2))
- -> Seq Scan on prt1_e_p2 t3_1
- Filter: (c = 0)
- -> Sort
- Sort Key: t1_1.a
- -> Seq Scan on prt1_p2 t1_1
- -> Sort
- Sort Key: t2_1.b
- -> Seq Scan on prt2_p2 t2_1
- -> Merge Left Join
- Merge Cond: (t1_2.a = t2_2.b)
- -> Sort
- Sort Key: t1_2.a
- -> Merge Left Join
- Merge Cond: ((((t3_2.a + t3_2.b) / 2)) = t1_2.a)
- -> Sort
- Sort Key: (((t3_2.a + t3_2.b) / 2))
- -> Seq Scan on prt1_e_p3 t3_2
- Filter: (c = 0)
- -> Sort
- Sort Key: t1_2.a
- -> Seq Scan on prt1_p3 t1_2
- -> Sort
- Sort Key: t2_2.b
- -> Seq Scan on prt2_p3 t2_2
-(52 rows)
+ -> Append
+ -> Merge Left Join
+ Merge Cond: (t1.a = t2.b)
+ -> Sort
+ Sort Key: t1.a
+ -> Merge Left Join
+ Merge Cond: ((((t3.a + t3.b) / 2)) = t1.a)
+ -> Sort
+ Sort Key: (((t3.a + t3.b) / 2))
+ -> Seq Scan on prt1_e_p1 t3
+ Filter: (c = 0)
+ -> Sort
+ Sort Key: t1.a
+ -> Seq Scan on prt1_p1 t1
+ -> Sort
+ Sort Key: t2.b
+ -> Seq Scan on prt2_p1 t2
+ -> Merge Left Join
+ Merge Cond: (t1_1.a = t2_1.b)
+ -> Sort
+ Sort Key: t1_1.a
+ -> Merge Left Join
+ Merge Cond: ((((t3_1.a + t3_1.b) / 2)) = t1_1.a)
+ -> Sort
+ Sort Key: (((t3_1.a + t3_1.b) / 2))
+ -> Seq Scan on prt1_e_p2 t3_1
+ Filter: (c = 0)
+ -> Sort
+ Sort Key: t1_1.a
+ -> Seq Scan on prt1_p2 t1_1
+ -> Sort
+ Sort Key: t2_1.b
+ -> Seq Scan on prt2_p2 t2_1
+ -> Merge Left Join
+ Merge Cond: (t1_2.a = t2_2.b)
+ -> Sort
+ Sort Key: t1_2.a
+ -> Merge Left Join
+ Merge Cond: ((((t3_2.a + t3_2.b) / 2)) = t1_2.a)
+ -> Sort
+ Sort Key: (((t3_2.a + t3_2.b) / 2))
+ -> Seq Scan on prt1_e_p3 t3_2
+ Filter: (c = 0)
+ -> Sort
+ Sort Key: t1_2.a
+ -> Seq Scan on prt1_p3 t1_2
+ -> Sort
+ Sort Key: t2_2.b
+ -> Seq Scan on prt2_p3 t2_2
+(51 rows)
SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
a | c | b | c | ?column? | c
@@ -1145,42 +1137,41 @@ ANALYZE plt1_e;
-- test partition matching with N-way join
EXPLAIN (COSTS OFF)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
- QUERY PLAN
---------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
GroupAggregate
Group Key: t1.c, t2.c, t3.c
-> Sort
Sort Key: t1.c, t3.c
- -> Result
- -> Append
+ -> Append
+ -> Hash Join
+ Hash Cond: (t1.c = ltrim(t3.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1.c = ltrim(t3.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c))
- -> Seq Scan on plt1_p1 t1
- -> Hash
- -> Seq Scan on plt2_p1 t2
+ Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c))
+ -> Seq Scan on plt1_p1 t1
-> Hash
- -> Seq Scan on plt1_e_p1 t3
+ -> Seq Scan on plt2_p1 t2
+ -> Hash
+ -> Seq Scan on plt1_e_p1 t3
+ -> Hash Join
+ Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
- -> Seq Scan on plt1_p2 t1_1
- -> Hash
- -> Seq Scan on plt2_p2 t2_1
+ Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
+ -> Seq Scan on plt1_p2 t1_1
-> Hash
- -> Seq Scan on plt1_e_p2 t3_1
+ -> Seq Scan on plt2_p2 t2_1
+ -> Hash
+ -> Seq Scan on plt1_e_p2 t3_1
+ -> Hash Join
+ Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
- -> Seq Scan on plt1_p3 t1_2
- -> Hash
- -> Seq Scan on plt2_p3 t2_2
+ Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
+ -> Seq Scan on plt1_p3 t1_2
-> Hash
- -> Seq Scan on plt1_e_p3 t3_2
-(33 rows)
+ -> Seq Scan on plt2_p3 t2_2
+ -> Hash
+ -> Seq Scan on plt1_e_p3 t3_2
+(32 rows)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
avg | avg | avg | c | c | c
@@ -1290,42 +1281,41 @@ ANALYZE pht1_e;
-- test partition matching with N-way join
EXPLAIN (COSTS OFF)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
- QUERY PLAN
---------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
GroupAggregate
Group Key: t1.c, t2.c, t3.c
-> Sort
Sort Key: t1.c, t3.c
- -> Result
- -> Append
+ -> Append
+ -> Hash Join
+ Hash Cond: (t1.c = ltrim(t3.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1.c = ltrim(t3.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c))
- -> Seq Scan on pht1_p1 t1
- -> Hash
- -> Seq Scan on pht2_p1 t2
+ Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c))
+ -> Seq Scan on pht1_p1 t1
-> Hash
- -> Seq Scan on pht1_e_p1 t3
+ -> Seq Scan on pht2_p1 t2
+ -> Hash
+ -> Seq Scan on pht1_e_p1 t3
+ -> Hash Join
+ Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
- -> Seq Scan on pht1_p2 t1_1
- -> Hash
- -> Seq Scan on pht2_p2 t2_1
+ Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
+ -> Seq Scan on pht1_p2 t1_1
-> Hash
- -> Seq Scan on pht1_e_p2 t3_1
+ -> Seq Scan on pht2_p2 t2_1
+ -> Hash
+ -> Seq Scan on pht1_e_p2 t3_1
+ -> Hash Join
+ Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
-> Hash Join
- Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
- -> Hash Join
- Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
- -> Seq Scan on pht1_p3 t1_2
- -> Hash
- -> Seq Scan on pht2_p3 t2_2
+ Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
+ -> Seq Scan on pht1_p3 t1_2
-> Hash
- -> Seq Scan on pht1_e_p3 t3_2
-(33 rows)
+ -> Seq Scan on pht2_p3 t2_2
+ -> Hash
+ -> Seq Scan on pht1_e_p3 t3_2
+(32 rows)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
avg | avg | avg | c | c | c
@@ -1463,40 +1453,39 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b
-- right join
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
-------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Sort
Sort Key: t1.a, t2.b
- -> Result
- -> Append
- -> Hash Right Join
- Hash Cond: ((t1.a = t2.b) AND ((t1.c)::text = (t2.c)::text))
- -> Seq Scan on prt1_l_p1 t1
- -> Hash
- -> Seq Scan on prt2_l_p1 t2
- Filter: (a = 0)
- -> Hash Right Join
- Hash Cond: ((t1_1.a = t2_1.b) AND ((t1_1.c)::text = (t2_1.c)::text))
- -> Seq Scan on prt1_l_p2_p1 t1_1
- -> Hash
- -> Seq Scan on prt2_l_p2_p1 t2_1
- Filter: (a = 0)
- -> Hash Right Join
- Hash Cond: ((t1_2.a = t2_2.b) AND ((t1_2.c)::text = (t2_2.c)::text))
- -> Seq Scan on prt1_l_p2_p2 t1_2
- -> Hash
- -> Seq Scan on prt2_l_p2_p2 t2_2
- Filter: (a = 0)
- -> Hash Right Join
- Hash Cond: ((t1_3.a = t2_3.b) AND ((t1_3.c)::text = (t2_3.c)::text))
+ -> Append
+ -> Hash Right Join
+ Hash Cond: ((t1.a = t2.b) AND ((t1.c)::text = (t2.c)::text))
+ -> Seq Scan on prt1_l_p1 t1
+ -> Hash
+ -> Seq Scan on prt2_l_p1 t2
+ Filter: (a = 0)
+ -> Hash Right Join
+ Hash Cond: ((t1_1.a = t2_1.b) AND ((t1_1.c)::text = (t2_1.c)::text))
+ -> Seq Scan on prt1_l_p2_p1 t1_1
+ -> Hash
+ -> Seq Scan on prt2_l_p2_p1 t2_1
+ Filter: (a = 0)
+ -> Hash Right Join
+ Hash Cond: ((t1_2.a = t2_2.b) AND ((t1_2.c)::text = (t2_2.c)::text))
+ -> Seq Scan on prt1_l_p2_p2 t1_2
+ -> Hash
+ -> Seq Scan on prt2_l_p2_p2 t2_2
+ Filter: (a = 0)
+ -> Hash Right Join
+ Hash Cond: ((t1_3.a = t2_3.b) AND ((t1_3.c)::text = (t2_3.c)::text))
+ -> Append
+ -> Seq Scan on prt1_l_p3_p1 t1_3
+ -> Seq Scan on prt1_l_p3_p2 t1_4
+ -> Hash
-> Append
- -> Seq Scan on prt1_l_p3_p1 t1_3
- -> Seq Scan on prt1_l_p3_p2 t1_4
- -> Hash
- -> Append
- -> Seq Scan on prt2_l_p3_p1 t2_3
- Filter: (a = 0)
-(31 rows)
+ -> Seq Scan on prt2_l_p3_p1 t2_3
+ Filter: (a = 0)
+(30 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b;
a | c | b | c
@@ -1577,55 +1566,54 @@ EXPLAIN (COSTS OFF)
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;
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
Sort
Sort Key: t1.a
- -> Result
- -> Append
- -> Nested Loop Left Join
- -> Seq Scan on prt1_l_p1 t1
- Filter: (b = 0)
- -> Hash Join
- Hash Cond: ((t3.b = t2.a) AND ((t3.c)::text = (t2.c)::text))
- -> Seq Scan on prt2_l_p1 t3
- -> Hash
- -> Seq Scan on prt1_l_p1 t2
- Filter: ((t1.a = a) AND ((t1.c)::text = (c)::text))
- -> Nested Loop Left Join
- -> Seq Scan on prt1_l_p2_p1 t1_1
- Filter: (b = 0)
- -> Hash Join
- Hash Cond: ((t3_1.b = t2_1.a) AND ((t3_1.c)::text = (t2_1.c)::text))
- -> Seq Scan on prt2_l_p2_p1 t3_1
- -> Hash
- -> Seq Scan on prt1_l_p2_p1 t2_1
- Filter: ((t1_1.a = a) AND ((t1_1.c)::text = (c)::text))
- -> Nested Loop Left Join
- -> Seq Scan on prt1_l_p2_p2 t1_2
+ -> Append
+ -> Nested Loop Left Join
+ -> Seq Scan on prt1_l_p1 t1
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: ((t3.b = t2.a) AND ((t3.c)::text = (t2.c)::text))
+ -> Seq Scan on prt2_l_p1 t3
+ -> Hash
+ -> Seq Scan on prt1_l_p1 t2
+ Filter: ((t1.a = a) AND ((t1.c)::text = (c)::text))
+ -> Nested Loop Left Join
+ -> Seq Scan on prt1_l_p2_p1 t1_1
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: ((t3_1.b = t2_1.a) AND ((t3_1.c)::text = (t2_1.c)::text))
+ -> Seq Scan on prt2_l_p2_p1 t3_1
+ -> Hash
+ -> Seq Scan on prt1_l_p2_p1 t2_1
+ Filter: ((t1_1.a = a) AND ((t1_1.c)::text = (c)::text))
+ -> Nested Loop Left Join
+ -> Seq Scan on prt1_l_p2_p2 t1_2
+ Filter: (b = 0)
+ -> Hash Join
+ Hash Cond: ((t3_2.b = t2_2.a) AND ((t3_2.c)::text = (t2_2.c)::text))
+ -> Seq Scan on prt2_l_p2_p2 t3_2
+ -> Hash
+ -> Seq Scan on prt1_l_p2_p2 t2_2
+ Filter: ((t1_2.a = a) AND ((t1_2.c)::text = (c)::text))
+ -> Nested Loop Left Join
+ -> Append
+ -> Seq Scan on prt1_l_p3_p1 t1_3
Filter: (b = 0)
- -> Hash Join
- Hash Cond: ((t3_2.b = t2_2.a) AND ((t3_2.c)::text = (t2_2.c)::text))
- -> Seq Scan on prt2_l_p2_p2 t3_2
- -> Hash
- -> Seq Scan on prt1_l_p2_p2 t2_2
- Filter: ((t1_2.a = a) AND ((t1_2.c)::text = (c)::text))
- -> Nested Loop Left Join
+ -> Hash Join
+ Hash Cond: ((t3_3.b = t2_3.a) AND ((t3_3.c)::text = (t2_3.c)::text))
-> Append
- -> Seq Scan on prt1_l_p3_p1 t1_3
- Filter: (b = 0)
- -> Hash Join
- Hash Cond: ((t3_3.b = t2_3.a) AND ((t3_3.c)::text = (t2_3.c)::text))
+ -> Seq Scan on prt2_l_p3_p1 t3_3
+ -> Seq Scan on prt2_l_p3_p2 t3_4
+ -> Hash
-> Append
- -> Seq Scan on prt2_l_p3_p1 t3_3
- -> Seq Scan on prt2_l_p3_p2 t3_4
- -> Hash
- -> Append
- -> Seq Scan on prt1_l_p3_p1 t2_3
- Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text))
- -> Seq Scan on prt1_l_p3_p2 t2_4
- Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text))
-(46 rows)
+ -> Seq Scan on prt1_l_p3_p1 t2_3
+ Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text))
+ -> Seq Scan on prt1_l_p3_p2 t2_4
+ Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text))
+(45 rows)
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
--
2.14.3 (Apple Git-98)