improve the performance for TpcH Q7
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
Sort_53 770.30 root test.nation.n_name, test.nation.n_name, Column#49
└─Projection_55 770.30 root test.nation.n_name, test.nation.n_name, Column#49, Column#51
└─HashAgg_56 770.30 root group by:Column#55, Column#56, Column#57, funcs:sum(Column#54)->Column#51, funcs:firstrow(Column#55)->test.nation.n_name, funcs:firstrow(Column#56)->test.nation.n_name, funcs:firstrow(Column#57)->Column#49
└─Projection_160 1872717.54 root mul(test.lineitem.l_extendedprice, minus(1, test.lineitem.l_discount))->Column#54, test.nation.n_name->Column#55, test.nation.n_name->Column#56, extract(YEAR, test.lineitem.l_shipdate)->Column#57
└─HashJoin_71 1872717.54 root inner join, equal:[eq(test.lineitem.l_orderkey, test.orders.o_orderkey)], other cond:or(and(eq(test.nation.n_name, "JAPAN"), eq(test.nation.n_name, "INDIA")), and(eq(test.nation.n_name, "INDIA"), eq(test.nation.n_name, "JAPAN")))
├─HashJoin_133(Build) 1782771.30 root inner join, equal:[eq(test.orders.o_custkey, test.customer.c_custkey)]
│ ├─HashJoin_149(Build) 120000.00 root inner join, equal:[eq(test.customer.c_nationkey, test.nation.n_nationkey)]
│ │ ├─TableReader_158(Build) 2.00 root data:Selection_157
│ │ │ └─Selection_157 2.00 cop[tikv] or(eq(test.nation.n_name, "INDIA"), eq(test.nation.n_name, "JAPAN"))
│ │ │ └─TableFullScan_156 25.00 cop[tikv] table:n2 keep order:false
│ │ └─TableReader_152(Probe) 1500000.00 root data:TableFullScan_151
│ │ └─TableFullScan_151 1500000.00 cop[tikv] table:customer keep order:false
│ └─TableReader_136(Probe) 15000000.00 root data:TableFullScan_135
│ └─TableFullScan_135 15000000.00 cop[tikv] table:orders keep order:false
└─HashJoin_94(Probe) 4829794.85 root inner join, equal:[eq(test.lineitem.l_suppkey, test.supplier.s_suppkey)]
├─HashJoin_111(Build) 8000.00 root inner join, equal:[eq(test.supplier.s_nationkey, test.nation.n_nationkey)]
│ ├─TableReader_120(Build) 2.00 root data:Selection_119
│ │ └─Selection_119 2.00 cop[tikv] or(eq(test.nation.n_name, "JAPAN"), eq(test.nation.n_name, "INDIA"))
│ │ └─TableFullScan_118 25.00 cop[tikv] table:n1 keep order:false
│ └─TableReader_114(Probe) 100000.00 root data:TableFullScan_113
│ └─TableFullScan_113 100000.00 cop[tikv] table:supplier keep order:false
└─TableReader_98(Probe) 18292646.94 root data:Selection_97
└─Selection_97 18292646.94 cop[tikv] ge(test.lineitem.l_shipdate, 1995-01-01 00:00:00.000000), le(test.lineitem.l_shipdate, 1996-12-31 00:00:00.000000)
└─TableFullScan_96 59986052.00 cop[tikv] table:lineitem keep order:false
```
It is TpcH's Q7 explain.
Selection_119‘s condition can be transfer into In operator.
Contributor guide
Assessment
This issue has not been assessed yet.