Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite5f5238

Browse files
committed
Consider ndistinct on the first column in cost_sort().
Task: 9578.Tags: optimized_group_by.
1 parent9c3dab9 commite5f5238

File tree

3 files changed

+52
-25
lines changed

3 files changed

+52
-25
lines changed

‎src/backend/optimizer/path/costsize.c

Lines changed: 32 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -194,6 +194,8 @@ static double page_size(double tuples, int width);
194194
staticdoubleget_parallel_divisor(Path*path);
195195
staticEquivalenceMember*identify_sort_ecmember(PlannerInfo*root,
196196
PathKey*key);
197+
staticdoublesort_comparisons_factor(PlannerInfo*root,List*pathkeys,
198+
doublentuples);
197199

198200

199201
/*
@@ -2113,8 +2115,7 @@ cost_sort(Path *path, PlannerInfo *root,
21132115
{
21142116
Coststartup_cost;
21152117
Costrun_cost;
2116-
doublecmpMultiplier=
2117-
(pathkeys==NIL) ?2.0 :list_length(pathkeys)+1.0;
2118+
doublecmpMultiplier=sort_comparisons_factor(root,pathkeys,tuples);
21182119

21192120
cost_tuplesort(&startup_cost,&run_cost,
21202121
tuples,width,cmpMultiplier,
@@ -6560,4 +6561,32 @@ identify_sort_ecmember(PlannerInfo *root, PathKey *key)
65606561

65616562
Assert(candidate!=NULL);
65626563
returncandidate;
6563-
}
6564+
}
6565+
6566+
staticdouble
6567+
sort_comparisons_factor(PlannerInfo*root,List*pathkeys,doublentuples)
6568+
{
6569+
intn=list_length(pathkeys);
6570+
doublecmpMultiplier= (n==0) ?2.0 :n+1;
6571+
6572+
if (root!=NULL&&ntuples>1&&n>1)
6573+
{
6574+
PathKey*key=linitial_node(PathKey,pathkeys);
6575+
EquivalenceMember*em=identify_sort_ecmember(root,key);
6576+
6577+
Assert(em->em_ndistinct >=0);
6578+
6579+
if (em->em_ndistinct==0.)
6580+
/*
6581+
* Optimiser doesn't have an info on ndistinct value, return
6582+
* extreme case
6583+
*/
6584+
returncmpMultiplier;
6585+
6586+
if (ntuples >=em->em_ndistinct)
6587+
cmpMultiplier=
6588+
2.0+ ((ntuples-em->em_ndistinct) / (ntuples-1))* (n-1);
6589+
}
6590+
6591+
returncmpMultiplier;
6592+
}

‎src/test/regress/expected/aggregates.out

Lines changed: 11 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -2842,19 +2842,18 @@ SELECT count(*)
28422842
QUERY PLAN
28432843
-------------------------------------------------------------------------------
28442844
GroupAggregate
2845-
Group Key: t1.z, t1.w, t1.x, t1.y
2846-
-> Incremental Sort
2847-
Sort Key: t1.z, t1.w, t1.x, t1.y
2848-
Presorted Key: t1.z, t1.w, t1.x
2845+
Group Key: t1.x, t1.y, t1.z, t1.w
2846+
-> Sort
2847+
Sort Key: t1.x, t1.y, t1.z, t1.w
28492848
-> Merge Join
2850-
Merge Cond: ((t1.z = t2.z) AND (t1.w = t2.w) AND (t1.x = t2.x))
2849+
Merge Cond: ((t1.w = t2.w) AND (t1.z = t2.z) AND (t1.x = t2.x))
28512850
-> Sort
2852-
Sort Key: t1.z, t1.w, t1.x
2851+
Sort Key: t1.w, t1.z, t1.x
28532852
-> Index Scan using btg_x_y_idx on btg t1
28542853
-> Sort
2855-
Sort Key: t2.z, t2.w, t2.x
2854+
Sort Key: t2.w, t2.z, t2.x
28562855
-> Index Scan using btg_x_y_idx on btg t2
2857-
(13 rows)
2856+
(12 rows)
28582857

28592858
RESET enable_nestloop;
28602859
RESET enable_hashjoin;
@@ -2878,12 +2877,11 @@ SELECT count(*) FROM btg GROUP BY w, x, y, z ORDER BY x*x, z;
28782877
Sort
28792878
Sort Key: ((x * x)), z
28802879
-> GroupAggregate
2881-
Group Key: x, y, w, z
2882-
-> Incremental Sort
2883-
Sort Key: x, y, w, z
2884-
Presorted Key: x, y
2880+
Group Key: w, x, y, z
2881+
-> Sort
2882+
Sort Key: w, x, y, z
28852883
-> Index Scan using btg_x_y_idx on btg
2886-
(8 rows)
2884+
(7 rows)
28872885

28882886
-- Test the case where the number of incoming subtree path keys is more than
28892887
-- the number of grouping keys.

‎src/test/regress/expected/partition_join.out

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -615,39 +615,39 @@ SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
615615
-> Sort
616616
Sort Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
617617
-> Merge Full Join
618-
Merge Cond: ((prt1.a = p2.a) AND (prt1.b = p2.b))
618+
Merge Cond: ((prt1.b = p2.b) AND (prt1.a = p2.a))
619619
Filter: ((COALESCE(prt1.a, p2.a) >= 490) AND (COALESCE(prt1.a, p2.a) <= 510))
620620
-> Sort
621-
Sort Key: prt1.a, prt1.b
621+
Sort Key: prt1.b, prt1.a
622622
-> Seq Scan on prt1_p1 prt1
623623
-> Sort
624-
Sort Key: p2.a, p2.b
624+
Sort Key: p2.b, p2.a
625625
-> Seq Scan on prt2_p1 p2
626626
-> Group
627627
Group Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
628628
-> Sort
629629
Sort Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
630630
-> Merge Full Join
631-
Merge Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
631+
Merge Cond: ((prt1_1.b = p2_1.b) AND (prt1_1.a = p2_1.a))
632632
Filter: ((COALESCE(prt1_1.a, p2_1.a) >= 490) AND (COALESCE(prt1_1.a, p2_1.a) <= 510))
633633
-> Sort
634-
Sort Key: prt1_1.a, prt1_1.b
634+
Sort Key: prt1_1.b, prt1_1.a
635635
-> Seq Scan on prt1_p2 prt1_1
636636
-> Sort
637-
Sort Key: p2_1.a, p2_1.b
637+
Sort Key: p2_1.b, p2_1.a
638638
-> Seq Scan on prt2_p2 p2_1
639639
-> Group
640640
Group Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
641641
-> Sort
642642
Sort Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
643643
-> Merge Full Join
644-
Merge Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
644+
Merge Cond: ((prt1_2.b = p2_2.b) AND (prt1_2.a = p2_2.a))
645645
Filter: ((COALESCE(prt1_2.a, p2_2.a) >= 490) AND (COALESCE(prt1_2.a, p2_2.a) <= 510))
646646
-> Sort
647-
Sort Key: prt1_2.a, prt1_2.b
647+
Sort Key: prt1_2.b, prt1_2.a
648648
-> Seq Scan on prt1_p3 prt1_2
649649
-> Sort
650-
Sort Key: p2_2.a, p2_2.b
650+
Sort Key: p2_2.b, p2_2.a
651651
-> Seq Scan on prt2_p3 p2_2
652652
(43 rows)
653653

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp