You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:15:53 UTC
[jira] [Created] (TRAFODION-391) LP Bug: 1333800 - Good MDAM plan
is missing for a query with range predicate
Alice Chen created TRAFODION-391:
------------------------------------
Summary: LP Bug: 1333800 - Good MDAM plan is missing for a query with range predicate
Key: TRAFODION-391
URL: https://issues.apache.org/jira/browse/TRAFODION-391
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Reporter: Ravisha Neelakanthappa
Assignee: Ravisha Neelakanthappa
Priority: Critical
For the following query, optimizer chooses a serial plan and reads all the rows from the table even though query has a range predicate on a key column. Looks like costing issue.
set schema trafodion.hbase;
drop table shb1;
create table shb1
(uniq int not null,
c10K int ,
c1K int,
c100 int,
c10 int,
c1 int,
primary key (uniq)
) salt using 4 partitions ;
upsert with no rollback
into shb1
select
0 + (10000 * x10000) + (1000 * x1000) + (100 * x100) + (10 * x10) +( 1 * x1),
0 + (1000 * x1000) + (100 * x100) + (10 * x10) + (1 * x1),
0 + (100 * x100) + (10 * x10) + (1 * x1),
0 + (10 * x10) + (1 * x1),
0 + (1 * x1),
0
from (values(0))t
transpose 0,1,2,3,4,5,6,7,8,9 as x10000
transpose 0,1,2,3,4,5,6,7,8,9 as x1000
transpose 0,1,2,3,4,5,6,7,8,9 as x100
transpose 0,1,2,3,4,5,6,7,8,9 as x10
transpose 0,1,2,3,4,5,6,7,8,9 as x1
;
update statistics for table shb1 on every column;
Good MDAM plan -- range predicate < 20000, takes 0.58 seconds
-----------------------------------------------------------------
>>prepare xx from
+>select c10, sum(c100) as c100_sum
+>from shb1
+>where shb1.uniq < 20000
+>group by c10
+>for read uncommitted access;
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
2 . 3 root 1.00E+001
1 . 2 hash_groupby 1.00E+001
. . 1 trafodion_scan SHB1 2.00E+004
--- SQL operation complete.
>>execute xx;
C10 C100_SUM
----------- --------------------
2 94000
6 102000
3 96000
9 108000
8 106000
7 104000
5 100000
1 92000
4 98000
0 90000
--- 10 row(s) selected.
>>
>>display statistics detail;
Start Time 2014/06/19 18:31:56.952284
End Time 2014/06/19 18:31:57.534260
Elapsed Time 00:00:00.581976
Compile Time 00:00:00.047856
Execution Time 00:00:00.581976
Table Name Records Records Hdfs Hdfs I/O Hdfs Access
Accessed Used I/Os Bytes Time(usec)
TRAFODION.HBASE.SHB1
20000 20000 0 0 570931
--- SQL operation complete.
Missing good MDAM plan -- range predicate < 30000, takes 4.75 seconds to execute
----------------------------------------------------------------------------------------------------------------
>>prepare xx from
+>select c10, sum(c100) as c100_sum
+>from shb1
+>where shb1.uniq < 30000
+>group by c10
+>for read uncommitted access;
--- SQL command prepared.
>>
>>explain options 'f' xx;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
2 . 3 root 1.00E+001
1 . 2 hash_groupby 1.00E+001
. . 1 trafodion_scan SHB1 3.00E+004
--- SQL operation complete.
>>
>>execute xx;
C10 C100_SUM
----------- --------------------
2 141000
6 153000
3 144000
9 162000
8 159000
7 156000
5 150000
1 138000
4 147000
0 135000
--- 10 row(s) selected.
>>
>>display statistics detail;
Start Time 2014/06/19 18:31:57.649265
End Time 2014/06/19 18:32:02.401063
Elapsed Time 00:00:04.751798
Compile Time 00:00:00.046374
Execution Time 00:00:04.751798
Table Name Records Records Hdfs Hdfs I/O Hdfs Access
Accessed Used I/Os Bytes Time(usec)
TRAFODION.HBASE.SHB1
100000 30000 0 0 4737266
--- SQL operation complete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)