public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: David Rowley <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Zhang Mingli <[email protected]>
Subject: Re: make add_paths_to_append_rel aware of startup cost
Date: Mon, 18 Sep 2023 18:55:46 +0800
Message-ID: <CAKU4AWqaTNPrYcb_cMEDDYWZVGfFxuUjr75F5LBZwnUQ0JvVPw@mail.gmail.com> (raw)
In-Reply-To: <CAKU4AWp9aTafjWwCqOauC7jmbuX+tGEWkRrBwXYhW-CFQXmRig@mail.gmail.com>
References: <CAKU4AWrXSkUV=Pt-gRxQT7EbfUeNssprGyNsB=5mJibFZ6S3ww@mail.gmail.com>
<CAApHDvry0nSV62kAOH3iccvfPhGPLN0Q97+=b1RsDPXDz3=CiQ@mail.gmail.com>
<CAKU4AWoksFSs_6K5ZCzz68Oq+3uZb5g6sb8-dXJ6cChgYQm-yw@mail.gmail.com>
<CAApHDvoMGyc+1eb8g5rEMUUMeGWhe2c_f8yvJjUO1kUHZj0h7w@mail.gmail.com>
<CAKU4AWp9aTafjWwCqOauC7jmbuX+tGEWkRrBwXYhW-CFQXmRig@mail.gmail.com>
Hi,
> What do you think about this in my last reply? "If my above
> analysis is correct, I think the best way to handle this is if there
> is no more tables to join, we use cheapest fraction cost for all
> the kinds of relations, including plain relation, append rel,
> subquery and so on, If we have more tables to join, we use
> cheapest startup cost.". This is what is in my mind now.
>
>
Here is an updated version to show what's in my mind.
--
Best Regards
Andy Fan
Attachments:
[application/octet-stream] v3-0001-make-add_paths_to_append_rel-aware-of-startup_cos.patch (23.0K, ../CAKU4AWqaTNPrYcb_cMEDDYWZVGfFxuUjr75F5LBZwnUQ0JvVPw@mail.gmail.com/3-v3-0001-make-add_paths_to_append_rel-aware-of-startup_cos.patch)
download | inline diff:
From a4e79f53c090801da7328c61568eb35403d8ae72 Mon Sep 17 00:00:00 2001
From: Andy Fan <[email protected]>
Date: Mon, 18 Sep 2023 18:46:45 +0800
Subject: [PATCH v3] make add_paths_to_append_rel aware of startup_cost.
---
src/backend/optimizer/path/allpaths.c | 86 ++++++++++++++++---
src/backend/optimizer/plan/planner.c | 36 ++++++--
src/backend/optimizer/prep/prepjointree.c | 8 ++
src/backend/optimizer/util/pathnode.c | 18 ++++
src/include/nodes/pathnodes.h | 5 ++
src/include/optimizer/pathnode.h | 1 +
src/include/optimizer/planner.h | 6 ++
.../regress/expected/partition_aggregate.out | 22 +++++
src/test/regress/expected/partition_join.out | 27 ++++++
src/test/regress/expected/partition_prune.out | 52 +++++++++++
src/test/regress/expected/union.out | 29 +++++++
src/test/regress/sql/partition_aggregate.sql | 3 +
src/test/regress/sql/partition_join.sql | 3 +
src/test/regress/sql/partition_prune.sql | 2 +
src/test/regress/sql/union.sql | 10 +++
15 files changed, 288 insertions(+), 20 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 9bdc70c702e..ebc591ac5cc 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -1239,6 +1239,11 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
List *live_childrels = NIL;
ListCell *l;
+ if (rel->consider_startup &&
+ bms_equal(rel->relids, root->all_baserels) &&
+ !root->has_stoper_op)
+ rel->tuple_fraction = root->tuple_fraction;
+
/*
* Generate access paths for each member relation, and remember the
* non-dummy children.
@@ -1268,6 +1273,8 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
if (!rel->consider_parallel)
childrel->consider_parallel = false;
+ childrel->tuple_fraction = rel->tuple_fraction;
+
/*
* Compute the child's access paths.
*/
@@ -1307,6 +1314,8 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
{
List *subpaths = NIL;
bool subpaths_valid = true;
+ List *startup_subpaths = NIL;
+ bool startup_subpaths_valid = true;
List *partial_subpaths = NIL;
List *pa_partial_subpaths = NIL;
List *pa_nonpartial_subpaths = NIL;
@@ -1329,7 +1338,9 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
{
RelOptInfo *childrel = lfirst(l);
ListCell *lcp;
+ Path *cheapest_startup_path = NULL;
Path *cheapest_partial_path = NULL;
+ Cost cheapest_partial_cost = 0;
/*
* If child has an unparameterized cheapest-total path, add that to
@@ -1339,19 +1350,46 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
* With partitionwise aggregates, the child rel's pathlist may be
* empty, so don't assume that a path exists here.
*/
- if (childrel->pathlist != NIL &&
+ if (childrel->tuple_fraction &&
+ (cheapest_startup_path = get_cheapest_fractional_path_ext(childrel,
+ childrel->tuple_fraction,
+ false, false, false)) != NULL)
+ accumulate_append_subpath(cheapest_startup_path,
+ &subpaths, NULL);
+ else if (childrel->pathlist != NIL &&
childrel->cheapest_total_path->param_info == NULL)
accumulate_append_subpath(childrel->cheapest_total_path,
&subpaths, NULL);
else
subpaths_valid = false;
+ if (rel->consider_startup && !childrel->tuple_fraction &&
+ childrel->pathlist != NIL &&
+ childrel->cheapest_startup_path->param_info == NULL)
+ {
+ accumulate_append_subpath(childrel->cheapest_startup_path, &startup_subpaths, NULL);
+ }
+ else
+ startup_subpaths_valid = false;
+
+ if (childrel->tuple_fraction &&
+ (cheapest_partial_path = get_cheapest_fractional_path_ext(childrel,
+ childrel->tuple_fraction,
+ false, true, false)) !=NULL)
+ {
+ accumulate_append_subpath(cheapest_partial_path,
+ &partial_subpaths, NULL);
+ cheapest_partial_cost = get_fractional_path_cost(cheapest_partial_path,
+ root->tuple_fraction);
+ }
+
/* Same idea, but for a partial plan. */
- if (childrel->partial_pathlist != NIL)
+ else if (childrel->partial_pathlist != NIL)
{
cheapest_partial_path = linitial(childrel->partial_pathlist);
accumulate_append_subpath(cheapest_partial_path,
&partial_subpaths, NULL);
+ cheapest_partial_cost = cheapest_partial_path->total_cost;
}
else
partial_subpaths_valid = false;
@@ -1363,9 +1401,33 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
if (pa_subpaths_valid)
{
Path *nppath = NULL;
+ Cost nppath_cost = 0;
- nppath =
- get_cheapest_parallel_safe_total_inner(childrel->pathlist);
+ /*
+ * Check if the cheapest non-partial and parallel safe path (nppath)
+ * is cheaper than cheapest_partial_path, if so use the nppath instead of
+ * cheapest_partial_path. Note the cheapest_partial_path has been startup
+ * aware already.
+ */
+ if (childrel->tuple_fraction && cheapest_startup_path)
+ {
+ if (cheapest_startup_path->parallel_safe)
+ nppath = cheapest_startup_path;
+ else
+ nppath = get_cheapest_fractional_path_ext(childrel, childrel->tuple_fraction,
+ false, false, true);
+
+ if (nppath)
+ nppath_cost = get_fractional_path_cost(nppath,
+ root->tuple_fraction);
+ }
+ else
+ {
+ nppath =
+ get_cheapest_parallel_safe_total_inner(childrel->pathlist);
+ if (nppath)
+ nppath_cost = nppath->total_cost;
+ }
if (cheapest_partial_path == NULL && nppath == NULL)
{
@@ -1374,7 +1436,7 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
}
else if (nppath == NULL ||
(cheapest_partial_path != NULL &&
- cheapest_partial_path->total_cost < nppath->total_cost))
+ cheapest_partial_cost < nppath_cost))
{
/* Partial path is cheaper or the only option. */
Assert(cheapest_partial_path != NULL);
@@ -1478,6 +1540,11 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
NIL, NULL, 0, false,
-1));
+ if (startup_subpaths_valid && startup_subpaths != NIL)
+ add_path(rel, (Path *) create_append_path(root, rel, startup_subpaths,
+ NIL, NIL, NULL, 0, false,
+ -1));
+
/*
* Consider an append of unordered, unparameterized partial paths. Make
* it parallel-aware if possible.
@@ -2495,7 +2562,6 @@ static void
set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
Index rti, RangeTblEntry *rte)
{
- Query *parse = root->parse;
Query *subquery = rte->subquery;
bool trivial_pathtarget;
Relids required_outer;
@@ -2631,13 +2697,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
* we'd better tell the subquery to plan for full retrieval. (XXX This
* could probably be made more intelligent ...)
*/
- if (parse->hasAggs ||
- parse->groupClause ||
- parse->groupingSets ||
- root->hasHavingQual ||
- parse->distinctClause ||
- parse->sortClause ||
- has_multiple_baserels(root))
+ if (root->has_stoper_op || has_multiple_baserels(root))
tuple_fraction = 0.0; /* default case */
else
tuple_fraction = root->tuple_fraction;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 44efb1f4ebc..db920ccd9e5 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -674,6 +674,14 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
root->non_recursive_path = NULL;
root->partColsUpdated = false;
+ if (parse->hasAggs ||
+ parse->groupClause ||
+ parse->groupingSets ||
+ parse->havingQual != NULL ||
+ parse->distinctClause ||
+ parse->sortClause)
+ root->has_stoper_op = true;
+
/*
* Create the top-level join domain. This won't have valid contents until
* deconstruct_jointree fills it in, but the node needs to exist before
@@ -6328,26 +6336,40 @@ make_sort_input_target(PlannerInfo *root,
Path *
get_cheapest_fractional_path(RelOptInfo *rel, double tuple_fraction)
{
- Path *best_path = rel->cheapest_total_path;
+ return get_cheapest_fractional_path_ext(rel, tuple_fraction, true, false, false);
+}
+
+Path *
+get_cheapest_fractional_path_ext(RelOptInfo *rel, double tuple_fraction,
+ bool allow_parameterized, bool look_partial,
+ bool must_parallel_safe)
+{
+ List *pathlist = look_partial ? rel->partial_pathlist : rel->pathlist;
+ Path *best_path = allow_parameterized ? linitial(pathlist) : NULL;
ListCell *l;
+ double total_rows = ((Path *)linitial(rel->pathlist))->rows;
/* If all tuples will be retrieved, just return the cheapest-total path */
if (tuple_fraction <= 0.0)
return best_path;
/* Convert absolute # of tuples to a fraction; no need to clamp to 0..1 */
- if (tuple_fraction >= 1.0 && best_path->rows > 0)
- tuple_fraction /= best_path->rows;
+ if (tuple_fraction >= 1.0 && total_rows > 0)
+ tuple_fraction /= total_rows;
- foreach(l, rel->pathlist)
+ foreach(l, pathlist)
{
Path *path = (Path *) lfirst(l);
- if (path == rel->cheapest_total_path ||
- compare_fractional_path_costs(best_path, path, tuple_fraction) <= 0)
+ if (!allow_parameterized && !bms_is_empty(PATH_REQ_OUTER(path)))
+ continue;
+
+ if (must_parallel_safe && !path->parallel_safe)
continue;
- best_path = path;
+ if (best_path == NULL ||
+ compare_fractional_path_costs(best_path, path, tuple_fraction) > 0)
+ best_path = path;
}
return best_path;
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 73ff40721c9..513c404ab06 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -1015,6 +1015,14 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
subroot->non_recursive_path = NULL;
/* We don't currently need a top JoinDomain for the subroot */
+ if (subquery->hasAggs ||
+ subquery->groupClause ||
+ subquery->groupingSets ||
+ subquery->havingQual != NULL ||
+ subquery->distinctClause ||
+ subquery->sortClause)
+ subroot->has_stoper_op = true;
+
/* No CTEs to worry about */
Assert(subquery->cteList == NIL);
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index 211ba65389d..dcf694dd9cb 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -120,6 +120,9 @@ compare_fractional_path_costs(Path *path1, Path *path2,
Cost cost1,
cost2;
+ if (path1 == path2)
+ return 0;
+
if (fraction <= 0.0 || fraction >= 1.0)
return compare_path_costs(path1, path2, TOTAL_COST);
cost1 = path1->startup_cost +
@@ -133,6 +136,21 @@ compare_fractional_path_costs(Path *path1, Path *path2,
return 0;
}
+Cost
+get_fractional_path_cost(Path *path1, double fraction)
+{
+ double total_rows = path1->rows;
+
+ if (total_rows <= 0)
+ return path1->total_cost;
+
+ if (fraction >= 1.0)
+ fraction = Min(1.0, fraction / path1->rows);
+
+ return path1->startup_cost + fraction * (path1->total_cost - path1->startup_cost);
+}
+
+
/*
* compare_path_costs_fuzzily
* Compare the costs of two paths to see if either can be said to
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 5702fbba60c..0ab11d7429e 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -479,6 +479,9 @@ struct PlannerInfo
/* limit_tuples passed to query_planner */
Cardinality limit_tuples;
+ /* Like order by, group by, distinct and so. */
+ bool has_stoper_op;
+
/*
* Minimum security_level for quals. Note: qual_security_level is zero if
* there are no securityQuals.
@@ -870,6 +873,8 @@ typedef struct RelOptInfo
bool consider_param_startup;
/* consider parallel paths? */
bool consider_parallel;
+ /* the tuple fraction of rows. */
+ Selectivity tuple_fraction;
/*
* default result targetlist for Paths scanning this relation; list of
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 6e557bebc44..b1544a13cff 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -25,6 +25,7 @@ extern int compare_path_costs(Path *path1, Path *path2,
CostSelector criterion);
extern int compare_fractional_path_costs(Path *path1, Path *path2,
double fraction);
+extern Cost get_fractional_path_cost(Path *path1, double fraction);
extern void set_cheapest(RelOptInfo *parent_rel);
extern void add_path(RelOptInfo *parent_rel, Path *new_path);
extern bool add_path_precheck(RelOptInfo *parent_rel,
diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h
index fc2e15496dd..469faeabfb3 100644
--- a/src/include/optimizer/planner.h
+++ b/src/include/optimizer/planner.h
@@ -56,6 +56,12 @@ extern void mark_partial_aggref(Aggref *agg, AggSplit aggsplit);
extern Path *get_cheapest_fractional_path(RelOptInfo *rel,
double tuple_fraction);
+extern Path *get_cheapest_fractional_path_ext(RelOptInfo *rel,
+ double tuple_fraction,
+ bool allow_parameterized,
+ bool look_partial,
+ bool must_parallel_safe);
+
extern Expr *preprocess_phv_expression(PlannerInfo *root, Expr *expr);
#endif /* PLANNER_H */
diff --git a/src/test/regress/expected/partition_aggregate.out b/src/test/regress/expected/partition_aggregate.out
index 1b900fddf8e..ccd5108a439 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -55,6 +55,28 @@ SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVI
0008 | 2000 | 14.0000000000000000 | 250 | 0 | 26
(6 rows)
+EXPLAIN (COSTS OFF)
+SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3 LIMIT 1;
+ QUERY PLAN
+--------------------------------------------------------------------
+ Limit
+ -> Sort
+ Sort Key: pagg_tab.c, (sum(pagg_tab.a)), (avg(pagg_tab.b))
+ -> Append
+ -> HashAggregate
+ Group Key: pagg_tab.c
+ Filter: (avg(pagg_tab.d) < '15'::numeric)
+ -> Seq Scan on pagg_tab_p1 pagg_tab
+ -> HashAggregate
+ Group Key: pagg_tab_1.c
+ Filter: (avg(pagg_tab_1.d) < '15'::numeric)
+ -> Seq Scan on pagg_tab_p2 pagg_tab_1
+ -> HashAggregate
+ Group Key: pagg_tab_2.c
+ Filter: (avg(pagg_tab_2.d) < '15'::numeric)
+ -> Seq Scan on pagg_tab_p3 pagg_tab_2
+(16 rows)
+
-- When GROUP BY clause does not match; partial aggregation is performed for each partition.
EXPLAIN (COSTS OFF)
SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVING avg(d) < 15 ORDER BY 1, 2, 3;
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6560fe2416f..c41c26dc482 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -62,6 +62,33 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b =
450 | 0450 | 450 | 0450
(4 rows)
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b LIMIT 1;
+ QUERY PLAN
+---------------------------------------------------------------
+ Limit
+ -> Merge Append
+ Sort Key: t1.a
+ -> Nested Loop
+ Join Filter: (t1_1.a = t2_1.b)
+ -> Index Scan using iprt2_p1_b on prt2_p1 t2_1
+ -> Materialize
+ -> Seq Scan on prt1_p1 t1_1
+ Filter: (b = 0)
+ -> Nested Loop
+ Join Filter: (t1_2.a = t2_2.b)
+ -> Index Scan using iprt2_p2_b on prt2_p2 t2_2
+ -> Materialize
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Nested Loop
+ Join Filter: (t1_3.a = t2_3.b)
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
+ -> Materialize
+ -> Seq Scan on prt1_p3 t1_3
+ Filter: (b = 0)
+(21 rows)
+
-- left outer join, 3-way
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM prt1 t1
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index bb1223e2b13..4a8d9357b89 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -143,6 +143,58 @@ explain (costs off) select * from lp where a not in ('a', 'd');
Filter: (a <> ALL ('{a,d}'::bpchar[]))
(9 rows)
+explain (costs off) select * from lp limit 3;
+ QUERY PLAN
+-----------------------------------------
+ Limit
+ -> Append
+ -> Seq Scan on lp_ad lp_1
+ -> Seq Scan on lp_bc lp_2
+ -> Seq Scan on lp_ef lp_3
+ -> Seq Scan on lp_g lp_4
+ -> Seq Scan on lp_null lp_5
+ -> Seq Scan on lp_default lp_6
+(8 rows)
+
+explain (costs off) select * from lp where exists (select 1 from lp where random() > 0.1) limit 3;
+ QUERY PLAN
+--------------------------------------------------------------
+ Limit
+ InitPlan 1 (returns $0)
+ -> Append
+ -> Seq Scan on lp_ad lp_8
+ Filter: (random() > '0.1'::double precision)
+ -> Seq Scan on lp_bc lp_9
+ Filter: (random() > '0.1'::double precision)
+ -> Seq Scan on lp_ef lp_10
+ Filter: (random() > '0.1'::double precision)
+ -> Seq Scan on lp_g lp_11
+ Filter: (random() > '0.1'::double precision)
+ -> Seq Scan on lp_null lp_12
+ Filter: (random() > '0.1'::double precision)
+ -> Seq Scan on lp_default lp_13
+ Filter: (random() > '0.1'::double precision)
+ -> Append
+ -> Result
+ One-Time Filter: $0
+ -> Seq Scan on lp_ad lp_1
+ -> Result
+ One-Time Filter: $0
+ -> Seq Scan on lp_bc lp_2
+ -> Result
+ One-Time Filter: $0
+ -> Seq Scan on lp_ef lp_3
+ -> Result
+ One-Time Filter: $0
+ -> Seq Scan on lp_g lp_4
+ -> Result
+ One-Time Filter: $0
+ -> Seq Scan on lp_null lp_5
+ -> Result
+ One-Time Filter: $0
+ -> Seq Scan on lp_default lp_6
+(34 rows)
+
-- collation matches the partitioning collation, pruning works
create table coll_pruning (a text collate "C") partition by list (a);
create table coll_pruning_a partition of coll_pruning for values in ('a');
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index e2613d6777e..166dd90cb85 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -1432,3 +1432,32 @@ where (x = 0) or (q1 >= q2 and q1 <= q2);
4567890123456789 | 4567890123456789 | 1
(6 rows)
+explain (costs off)
+with s1 as
+(select * from tenk1 join tenk2 using (hundred)),
+s2 as
+(select * from tenk1 join tenk2 using (hundred))
+select * from s1
+union all
+select * from s2
+limit 3;
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Limit
+ -> Append
+ -> Nested Loop
+ -> Seq Scan on tenk1
+ -> Memoize
+ Cache Key: tenk1.hundred
+ Cache Mode: logical
+ -> Index Scan using tenk2_hundred on tenk2
+ Index Cond: (hundred = tenk1.hundred)
+ -> Nested Loop
+ -> Seq Scan on tenk1 tenk1_1
+ -> Memoize
+ Cache Key: tenk1_1.hundred
+ Cache Mode: logical
+ -> Index Scan using tenk2_hundred on tenk2 tenk2_1
+ Index Cond: (hundred = tenk1_1.hundred)
+(16 rows)
+
diff --git a/src/test/regress/sql/partition_aggregate.sql b/src/test/regress/sql/partition_aggregate.sql
index ab070fee244..a2943a62961 100644
--- a/src/test/regress/sql/partition_aggregate.sql
+++ b/src/test/regress/sql/partition_aggregate.sql
@@ -30,6 +30,9 @@ EXPLAIN (COSTS OFF)
SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3;
SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3;
+EXPLAIN (COSTS OFF)
+SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3 LIMIT 1;
+
-- When GROUP BY clause does not match; partial aggregation is performed for each partition.
EXPLAIN (COSTS OFF)
SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVING avg(d) < 15 ORDER BY 1, 2, 3;
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 48daf3aee39..88b6e5168ae 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -34,6 +34,9 @@ EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b LIMIT 1;
+
-- left outer join, 3-way
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM prt1 t1
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 83fed54b8c6..0087d8da32a 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -24,6 +24,8 @@ explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'
explain (costs off) select * from lp where a <> 'g';
explain (costs off) select * from lp where a <> 'a' and a <> 'd';
explain (costs off) select * from lp where a not in ('a', 'd');
+explain (costs off) select * from lp limit 3;
+explain (costs off) select * from lp where exists (select 1 from lp where random() > 0.1) limit 3;
-- collation matches the partitioning collation, pruning works
create table coll_pruning (a text collate "C") partition by list (a);
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index ca8c9b4d128..21c80fc1d96 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -540,3 +540,13 @@ select * from
union all
select *, 1 as x from int8_tbl b) ss
where (x = 0) or (q1 >= q2 and q1 <= q2);
+
+explain (costs off)
+with s1 as
+(select * from tenk1 join tenk2 using (hundred)),
+s2 as
+(select * from tenk1 join tenk2 using (hundred))
+select * from s1
+union all
+select * from s2
+limit 3;
--
2.21.0
view thread (18+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: make add_paths_to_append_rel aware of startup cost
In-Reply-To: <CAKU4AWqaTNPrYcb_cMEDDYWZVGfFxuUjr75F5LBZwnUQ0JvVPw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox