You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Arina Ielchiieva (Jira)" <ji...@apache.org> on 2019/11/04 12:46:00 UTC
[jira] [Updated] (DRILL-7129) Join with more than 1 condition is
not using stats to compute row count estimate
[ https://issues.apache.org/jira/browse/DRILL-7129?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Arina Ielchiieva updated DRILL-7129:
------------------------------------
Fix Version/s: (was: 1.17.0)
> Join with more than 1 condition is not using stats to compute row count estimate
> --------------------------------------------------------------------------------
>
> Key: DRILL-7129
> URL: https://issues.apache.org/jira/browse/DRILL-7129
> Project: Apache Drill
> Issue Type: Bug
> Affects Versions: 1.16.0
> Reporter: Anisha Reddy
> Priority: Major
>
> Below are the details:
>
> {code:java}
> 0: jdbc:drill:drillbit=10.10.101.108> select count(*) from `table_stats/Tpch0.01/parquet/lineitem`; +---------+ | EXPR$0 | +---------+ | 57068 | +---------+ 1 row selected (0.179 seconds)
> 0: jdbc:drill:drillbit=10.10.101.108> select count(*) from `table_stats/Tpch0.01/parquet/partsupp`; +---------+ | EXPR$0 | +---------+ | 7474 | +---------+ 1 row selected (0.171 seconds)
> 0: jdbc:drill:drillbit=10.10.101.108> select count(*) from `table_stats/Tpch0.01/parquet/lineitem` l, `table_stats/Tpch0.01/parquet/partsupp` ps where l.l_partkey = ps.ps_partkey and l.l_suppkey = ps.ps_suppkey; +---------+ | EXPR$0 | +---------+ | 53401 | +---------+ 1 row selected (0.769 seconds)
> 0: jdbc:drill:drillbit=10.10.101.108> explain plan including all attributes for select * from `table_stats/Tpch0.01/parquet/lineitem` l, `table_stats/Tpch0.01/parquet/partsupp` ps where l.l_partkey = ps.ps_partkey and l.l_suppkey = ps.ps_suppkey; +----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+ | text | json | +----------------------------------------------------------------------------------+----------------------------------------------------------------------------------+ | 00-00 Screen : rowType = RecordType(DYNAMIC_STAR **, DYNAMIC_STAR **0): rowcount = 57068.0, cumulative cost = {313468.8 rows, 2110446.8 cpu, 193626.0 io, 0.0 network, 197313.6 memory}, id = 107578 00-01 ProjectAllowDup(**=[$0], **0=[$1]) : rowType = RecordType(DYNAMIC_STAR **, DYNAMIC_STAR **0): rowcount = 57068.0, cumulative cost = {307762.0 rows, 2104740.0 cpu, 193626.0 io, 0.0 network, 197313.6 memory}, id = 107577 00-02 Project(T10¦¦**=[$0], T11¦¦**=[$3]) : rowType = RecordType(DYNAMIC_STAR T10¦¦**, DYNAMIC_STAR T11¦¦**): rowcount = 57068.0, cumulative cost = {250694.0 rows, 1990604.0 cpu, 193626.0 io, 0.0 network, 197313.6 memory}, id = 107576 00-03 HashJoin(condition=[AND(=($1, $4), =($2, $5))], joinType=[inner], semi-join: =[false]) : rowType = RecordType(DYNAMIC_STAR T10¦¦**, ANY l_partkey, ANY l_suppkey, DYNAMIC_STAR T11¦¦**, ANY ps_partkey, ANY ps_suppkey): rowcount = 57068.0, cumulative cost = {193626.0 rows, 1876468.0 cpu, 193626.0 io, 0.0 network, 197313.6 memory}, id = 107575 00-05 Project(T10¦¦**=[$0], l_partkey=[$1], l_suppkey=[$2]) : rowType = RecordType(DYNAMIC_STAR T10¦¦**, ANY l_partkey, ANY l_suppkey): rowcount = 57068.0, cumulative cost = {114136.0 rows, 342408.0 cpu, 171204.0 io, 0.0 network, 0.0 memory}, id = 107572 00-07 Scan(table=[[dfs, drilltestdir, table_stats/Tpch0.01/parquet/lineitem]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///drill/testdata/table_stats/Tpch0.01/parquet/lineitem]], selectionRoot=maprfs:/drill/testdata/table_stats/Tpch0.01/parquet/lineitem, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`, `l_partkey`, `l_suppkey`]]]) : rowType = RecordType(DYNAMIC_STAR **, ANY l_partkey, ANY l_suppkey): rowcount = 57068.0, cumulative cost = {57068.0 rows, 171204.0 cpu, 171204.0 io, 0.0 network, 0.0 memory}, id = 107571 00-04 Project(T11¦¦**=[$0], ps_partkey=[$1], ps_suppkey=[$2]) : rowType = RecordType(DYNAMIC_STAR T11¦¦**, ANY ps_partkey, ANY ps_suppkey): rowcount = 7474.0, cumulative cost = {14948.0 rows, 44844.0 cpu, 22422.0 io, 0.0 network, 0.0 memory}, id = 107574 00-06 Scan(table=[[dfs, drilltestdir, table_stats/Tpch0.01/parquet/partsupp]], groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///drill/testdata/table_stats/Tpch0.01/parquet/partsupp]], selectionRoot=maprfs:/drill/testdata/table_stats/Tpch0.01/parquet/partsupp, numFiles=1, numRowGroups=1, usedMetadataFile=false, columns=[`**`, `ps_partkey`, `ps_suppkey`]]]) : rowType = RecordType(DYNAMIC_STAR **, ANY ps_partkey, ANY ps_suppkey): rowcount = 7474.0, cumulative cost = {7474.0 rows, 22422.0 cpu, 22422.0 io, 0.0 network, 0.0 memory}, id = 107573
> {code}
> The ndv for l_partkey = 2000
> ps_partkey = 1817
> l_supkey = 100
> ps_suppkey = 100
> We see that such joins is just taking the max of left side and the right side table.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)