FatLittle commented on a change in pull request #1991:
URL: https://github.com/apache/calcite/pull/1991#discussion_r450590729
##########
File path: plus/src/test/java/org/apache/calcite/adapter/tpcds/TpcdsTest.java
##########
@@ -220,29 +220,33 @@ protected void foo(CalciteAssert.AssertThat with, String
tableName,
.withHook(Hook.PROGRAM, handler(true, 2))
.explainMatches("including all attributes ",
CalciteAssert.checkMaskedResultContains(""
- + "EnumerableCalc(expr#0..9=[{inputs}], expr#10=[/($t4, $t3)],
expr#11=[CAST($t10):INTEGER NOT NULL], expr#12=[*($t4, $t4)], expr#13=[/($t12,
$t3)], expr#14=[-($t5, $t13)], expr#15=[1], expr#16=[=($t3, $t15)],
expr#17=[null:BIGINT], expr#18=[-($t3, $t15)], expr#19=[CASE($t16, $t17,
$t18)], expr#20=[/($t14, $t19)], expr#21=[0.5:DECIMAL(2, 1)],
expr#22=[POWER($t20, $t21)], expr#23=[CAST($t22):INTEGER NOT NULL],
expr#24=[/($t23, $t11)], expr#25=[/($t6, $t3)], expr#26=[CAST($t25):INTEGER NOT
NULL], expr#27=[*($t6, $t6)], expr#28=[/($t27, $t3)], expr#29=[-($t7, $t28)],
expr#30=[/($t29, $t19)], expr#31=[POWER($t30, $t21)],
expr#32=[CAST($t31):INTEGER NOT NULL], expr#33=[/($t32, $t26)], expr#34=[/($t8,
$t3)], expr#35=[CAST($t34):INTEGER NOT NULL], expr#36=[*($t8, $t8)],
expr#37=[/($t36, $t3)], expr#38=[-($t9, $t37)], expr#39=[/($t38, $t19)],
expr#40=[POWER($t39, $t21)], expr#41=[CAST($t40):INTEGER NOT NULL],
expr#42=[/($t41, $t35)], proj#0..3=[{exprs}], STORE_SALES_QUANTITYAVE=[$t11],
STORE_SALES_QUANTITYSTDEV=[$t23], STORE_SALES_QUANTITYCOV=[$t24],
AS_STORE_RETURNS_QUANTITYCOUNT=[$t3], AS_STORE_RETURNS_QUANTITYAVE=[$t26],
AS_STORE_RETURNS_QUANTITYSTDEV=[$t32], STORE_RETURNS_QUANTITYCOV=[$t33],
CATALOG_SALES_QUANTITYCOUNT=[$t3], CATALOG_SALES_QUANTITYAVE=[$t35],
CATALOG_SALES_QUANTITYSTDEV=[$t42], CATALOG_SALES_QUANTITYCOV=[$t42]): rowcount
= 100.0, cumulative cost = {1.2435775409784036E28 rows, 2.555295485909236E30
cpu, 0.0 io}\n"
- + " EnumerableLimit(fetch=[100]): rowcount = 100.0,
cumulative cost = {1.2435775409784036E28 rows, 2.555295485909236E30 cpu, 0.0
io}\n"
- + " EnumerableSort(sort0=[$0], sort1=[$1], sort2=[$2],
dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount = 5.434029018852197E26,
cumulative cost = {1.2435775409784036E28 rows, 2.555295485909236E30 cpu, 0.0
io}\n"
- + " EnumerableAggregate(group=[{0, 1, 2}],
STORE_SALES_QUANTITYCOUNT=[COUNT()], agg#1=[$SUM0($3)], agg#2=[$SUM0($6)],
agg#3=[$SUM0($4)], agg#4=[$SUM0($7)], agg#5=[$SUM0($5)], agg#6=[$SUM0($8)]):
rowcount = 5.434029018852197E26, cumulative cost = {1.1892372507898816E28 rows,
1.2172225002228922E30 cpu, 0.0 io}\n"
- + " EnumerableCalc(expr#0..211=[{inputs}],
expr#212=[*($t89, $t89)], expr#213=[*($t140, $t140)], expr#214=[*($t196,
$t196)], I_ITEM_ID=[$t58], I_ITEM_DESC=[$t61], S_STATE=[$t24],
SS_QUANTITY=[$t89], SR_RETURN_QUANTITY=[$t140], CS_QUANTITY=[$t196],
$f6=[$t212], $f7=[$t213], $f8=[$t214]): rowcount = 5.434029018852197E27,
cumulative cost = {1.0873492066864028E28 rows, 1.2172225002228922E30 cpu, 0.0
io}\n"
- + " EnumerableHashJoin(condition=[AND(=($82, $133),
=($81, $132), =($88, $139))], joinType=[inner]): rowcount =
5.434029018852197E27, cumulative cost = {5.439463048011832E27 rows, 1.8506796E7
cpu, 0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0, $86)],
joinType=[inner]): rowcount = 2.3008402586892598E13, cumulative cost =
{4.8588854672854766E13 rows, 7281360.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS, STORE]]):
rowcount = 12.0, cumulative cost = {12.0 rows, 13.0 cpu, 0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0, $50)],
joinType=[inner]): rowcount = 1.2782445881607E13, cumulative cost =
{1.279800620431234E13 rows, 7281347.0 cpu, 0.0 io}\n"
- + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], proj#0..27=[{exprs}],
$condition=[$t29]): rowcount = 10957.35, cumulative cost = {84006.35 rows,
4382941.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0, $24)],
joinType=[inner]): rowcount = 7.7770908E9, cumulative cost =
{7.783045975286664E9 rows, 2898406.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
ITEM]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 18001.0 cpu, 0.0
io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
STORE_SALES]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows,
2880405.0 cpu, 0.0 io}\n"
- + " EnumerableHashJoin(condition=[AND(=($31, $79),
=($30, $91))], joinType=[inner]): rowcount = 6.9978029381741304E16, cumulative
cost = {7.0048032234040472E16 rows, 1.1225436E7 cpu, 0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0, $28)],
joinType=[inner]): rowcount = 7.87597881975E8, cumulative cost =
{7.884434212216867E8 rows, 5035701.0 cpu, 0.0 io}\n"
- + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], expr#30=['1998Q2'],
expr#31=[=($t15, $t30)], expr#32=['1998Q3'], expr#33=[=($t15, $t32)],
expr#34=[OR($t29, $t31, $t33)], proj#0..27=[{exprs}], $condition=[$t34]):
rowcount = 18262.25, cumulative cost = {91311.25 rows, 4748186.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
STORE_RETURNS]]): rowcount = 287514.0, cumulative cost = {287514.0 rows,
287515.0 cpu, 0.0 io}\n"
- + " EnumerableHashJoin(condition=[=($0, $28)],
joinType=[inner]): rowcount = 3.94888649445E9, cumulative cost =
{3.9520401026966867E9 rows, 6189735.0 cpu, 0.0 io}\n"
- + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], expr#30=['1998Q2'],
expr#31=[=($t15, $t30)], expr#32=['1998Q3'], expr#33=[=($t15, $t32)],
expr#34=[OR($t29, $t31, $t33)], proj#0..27=[{exprs}], $condition=[$t34]):
rowcount = 18262.25, cumulative cost = {91311.25 rows, 4748186.0 cpu, 0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
- + " EnumerableTableScan(table=[[TPCDS,
CATALOG_SALES]]): rowcount = 1441548.0, cumulative cost = {1441548.0 rows,
1441549.0 cpu, 0.0 io}\n"));
+ + "EnumerableCalc(expr#0..9=[{inputs}], expr#10=[/($t4, $t3)],
expr#11=[CAST($t10):INTEGER NOT NULL], expr#12=[*($t4, $t4)], expr#13=[/($t12,
$t3)], expr#14=[-($t5, $t13)], expr#15=[1], expr#16=[=($t3, $t15)],
expr#17=[null:BIGINT], expr#18=[-($t3, $t15)], expr#19=[CASE($t16, $t17,
$t18)], expr#20=[/($t14, $t19)], expr#21=[0.5:DECIMAL(2, 1)],
expr#22=[POWER($t20, $t21)], expr#23=[CAST($t22):INTEGER NOT NULL],
expr#24=[/($t23, $t11)], expr#25=[/($t6, $t3)], expr#26=[CAST($t25):INTEGER NOT
NULL], expr#27=[*($t6, $t6)], expr#28=[/($t27, $t3)], expr#29=[-($t7, $t28)],
expr#30=[/($t29, $t19)], expr#31=[POWER($t30, $t21)],
expr#32=[CAST($t31):INTEGER NOT NULL], expr#33=[/($t32, $t26)], expr#34=[/($t8,
$t3)], expr#35=[CAST($t34):INTEGER NOT NULL], expr#36=[*($t8, $t8)],
expr#37=[/($t36, $t3)], expr#38=[-($t9, $t37)], expr#39=[/($t38, $t19)],
expr#40=[POWER($t39, $t21)], expr#41=[CAST($t40):INTEGER NOT NULL],
expr#42=[/($t41, $t35)], proj#0..3=[{exprs}], STORE_SALES_QUANTITYAVE=[$t11],
STORE_SALES_QUANTITYSTDEV=[$t23], STORE_SALES_QUANTITYCOV=[$t24],
AS_STORE_RETURNS_QUANTITYCOUNT=[$t3], AS_STORE_RETURNS_QUANTITYAVE=[$t26],
AS_STORE_RETURNS_QUANTITYSTDEV=[$t32], STORE_RETURNS_QUANTITYCOV=[$t33],
CATALOG_SALES_QUANTITYCOUNT=[$t3], CATALOG_SALES_QUANTITYAVE=[$t35],
CATALOG_SALES_QUANTITYSTDEV=[$t42], CATALOG_SALES_QUANTITYCOV=[$t42]): rowcount
= 100.0, cumulative cost = {1.2430341380834431E28 rows, 2.555295487103789E30
cpu, 0.0 io}\n"
+ + " EnumerableLimit(fetch=[100]): rowcount = 100.0,
cumulative cost = {1.2430341380834431E28 rows, 2.555295487103789E30 cpu, 0.0
io}\n"
+ + " EnumerableSort(sort0=[$0], sort1=[$1], sort2=[$2],
dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount = 5.434029018852197E26,
cumulative cost = {1.2430341380834431E28 rows, 2.555295487103789E30 cpu, 0.0
io}\n"
+ + " EnumerableAggregate(group=[{0, 1, 2}],
STORE_SALES_QUANTITYCOUNT=[COUNT()], agg#1=[$SUM0($3)], agg#2=[$SUM0($6)],
agg#3=[$SUM0($4)], agg#4=[$SUM0($7)], agg#5=[$SUM0($5)], agg#6=[$SUM0($8)]):
rowcount = 5.434029018852197E26, cumulative cost = {1.1886938478949211E28 rows,
1.2172225014174444E30 cpu, 0.0 io}\n"
+ + " EnumerableCalc(expr#0..211=[{inputs}],
expr#212=[*($t89, $t89)], expr#213=[*($t140, $t140)], expr#214=[*($t196,
$t196)], I_ITEM_ID=[$t58], I_ITEM_DESC=[$t61], S_STATE=[$t24],
SS_QUANTITY=[$t89], SR_RETURN_QUANTITY=[$t140], CS_QUANTITY=[$t196],
$f6=[$t212], $f7=[$t213], $f8=[$t214]): rowcount = 5.434029018852197E27,
cumulative cost = {1.0868058037914423E28 rows, 1.2172225014174444E30 cpu, 0.0
io}\n"
+ + " EnumerableMergeJoin(condition=[AND(=($82, $133),
=($81, $132), =($88, $139))], joinType=[inner]): rowcount =
5.434029018852197E27, cumulative cost = {5.434029019062226E27 rows,
1.1945521881363807E21 cpu, 0.0 io}\n"
+ + " EnumerableSort(sort0=[$82], sort1=[$81],
sort2=[$88], dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount =
2.3008402586892598E13, cumulative cost = {7.159725725974736E13 rows,
2.8882188423696301E17 cpu, 0.0 io}\n"
+ + " EnumerableHashJoin(condition=[=($0, $86)],
joinType=[inner]): rowcount = 2.3008402586892598E13, cumulative cost =
{4.8588854672854766E13 rows, 7281360.0 cpu, 0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
STORE]]): rowcount = 12.0, cumulative cost = {12.0 rows, 13.0 cpu, 0.0 io}\n"
+ + " EnumerableHashJoin(condition=[=($0, $50)],
joinType=[inner]): rowcount = 1.2782445881607E13, cumulative cost =
{1.279800620431234E13 rows, 7281347.0 cpu, 0.0 io}\n"
+ + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], proj#0..27=[{exprs}],
$condition=[$t29]): rowcount = 10957.35, cumulative cost = {84006.35 rows,
4382941.0 cpu, 0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
+ + " EnumerableHashJoin(condition=[=($0,
$24)], joinType=[inner]): rowcount = 7.7770908E9, cumulative cost =
{7.783045975286664E9 rows, 2898406.0 cpu, 0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
ITEM]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 18001.0 cpu, 0.0
io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
STORE_SALES]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows,
2880405.0 cpu, 0.0 io}\n"
+ + " EnumerableSort(sort0=[$31], sort1=[$30],
sort2=[$37], dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount =
6.9978029381741304E16, cumulative cost = {1.3995607297693488E17 rows,
1.1942633662521438E21 cpu, 0.0 io}\n"
+ + " EnumerableMergeJoin(condition=[AND(=($31,
$79), =($30, $91))], joinType=[inner]): rowcount = 6.9978029381741304E16,
cumulative cost = {6.9978043595193584E16 rows, 2.473747714463278E13 cpu, 0.0
io}\n"
+ + " EnumerableSort(sort0=[$31], sort1=[$30],
dir0=[ASC], dir1=[ASC]): rowcount = 7.87597881975E8, cumulative cost =
{1.5760413031966867E9 rows, 3.097646138009654E12 cpu, 0.0 io}\n"
+ + " EnumerableHashJoin(condition=[=($0,
$28)], joinType=[inner]): rowcount = 7.87597881975E8, cumulative cost =
{7.884434212216867E8 rows, 5035701.0 cpu, 0.0 io}\n"
+ + " EnumerableCalc(expr#0..27=[{inputs}],
expr#28=['1998Q1'], expr#29=[=($t15, $t28)], expr#30=['1998Q2'],
expr#31=[=($t15, $t30)], expr#32=['1998Q3'], expr#33=[=($t15, $t32)],
expr#34=[OR($t29, $t31, $t33)], proj#0..27=[{exprs}], $condition=[$t34]):
rowcount = 18262.25, cumulative cost = {91311.25 rows, 4748186.0 cpu, 0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu,
0.0 io}\n"
+ + " EnumerableTableScan(table=[[TPCDS,
STORE_RETURNS]]): rowcount = 287514.0, cumulative cost = {287514.0 rows,
287515.0 cpu, 0.0 io}\n"
+ + " EnumerableSort(sort0=[$31], sort1=[$43],
dir0=[ASC], dir1=[ASC]): rowcount = 3.94888649445E9, cumulative cost =
{7.900926597146687E9 rows, 2.163983100662313E13 cpu, 0.0 io}\n"
Review comment:
The only changes is that some HashJoin is changed to MergeJoin. But as
you can see from the cost, the new plan do have a lower cost.
(
old cost = 1.2435775409784036E28
VS
new cost = 1.2430341380834431E28
)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
