You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2017/04/07 04:04:41 UTC
[jira] [Commented] (TRAFODION-2568) bypass the operator work if the
predicate is FALSE
[ https://issues.apache.org/jira/browse/TRAFODION-2568?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15960243#comment-15960243 ]
Suresh Subbiah commented on TRAFODION-2568:
-------------------------------------------
This is what we get with a fix. The base table access and index access case go through different methods.Notice how begin and end keys are reversed.
-- Base table access
create table test1 (a int not null primary key, b int) salt using 2 partitions ;
insert into test1 values (1,1),(2,2),(3,3),(4,4) ;
prepare s2 from select * from test1 where a > 0 and 1 = 0;
explain s2 ;
set statistics on ;
execute s2 ;
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... TEST1
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est ........... 0
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan of table TRAFODION.SEABASE.TEST1
object_type ............ Trafodion
columns ................ all
begin_keys(incl) ....... 4294967295,??
end_keys(incl) ......... 0,
cache_size ........... 100
probes ................. 1
rows_accessed ........ 100
column_retrieved ....... #1:1
key_columns ............ _SALT_, A
executor_predicates .... 0.
--- SQL operation complete.
>>>>
--- 0 row(s) selected.
Start Time 2017/04/07 04:00:53.393346
End Time 2017/04/07 04:00:53.409268
Elapsed Time 00:00:00.015922
Compile Time 00:00:04.653079
Execution Time 00:00:00.015922
Table Name Records Records Hdfs Hdfs I/O Hdfs Access
Accessed Used I/Os Bytes Time(usec)
TRAFODION.SEABASE.TEST1
0 0 2 0 13600
-- Index access
create table tbl (
k1 int not null,
k2 int not null,
ts timestamp not null,
a char(10),
b varchar(30),
c largeint,
primary key (k1,k2,ts))
salt using 2 partitions
division by (date_trunc('MONTH', ts)) ;
upsert using load into tbl
select num/1000, num, DATEADD(SECOND,-num,CURRENT_TIMESTAMP),cast(num as char(10)), cast(num as varchar(30)), num*1000
from (select 100000*x100000+10000*x10000+1000*x1000+100*x100+10*x10+x1 as num
from (values (0)) seed(c)
transpose 0,1,2,3,4,5,6,7,8,9 as x1
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 x100
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 x10000
transpose 0,1,2,3,4,5,6,7,8,9 as x100000
) T
;
create index tbl_idx1_b on tbl(b) salt like table;
prepare s1 from select * from tbl where b > 5 and 1 = 0 ;
>>explain options 'f' s1 ;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
5 . 6 root 1.00E+000
2 4 5 nested_join 1.00E+000
3 . 4 probe_cache 1.00E+000
. . 3 trafodion_vsbb_scan TBL 1.00E+000
1 . 2 esp_exchange 1:2(hash2) 1.00E+000
. . 1 trafodion_index_scan TBL_IDX1_B 1.00E+000
--- SQL operation complete.
TRAFODION_INDEX_SCAN ====================== SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... TBL
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 1.03
EST_TOTAL_COST ........... 1.03
DESCRIPTION
max_card_est ........... 0
fragment_id ............ 2
parent_frag ............ 0
fragment_type .......... esp
scan_type .............. full scan of index TRAFODION.SEABASE.TBL_IDX1_B(TRAF
ODION.SEABASE.TBL)
object_type ............ Trafodion
cache_size ........ 10,000
probes ................. 1
rows_accessed .......... 1e+06
column_retrieved ....... #1:1
key_columns ............ TRAFODION.SEABASE.TBL_IDX1_B._SALT_,
TRAFODION.SEABASE.TBL_IDX1_B.B,
TRAFODION.SEABASE.TBL_IDX1_B._DIVISION_1_,
TRAFODION.SEABASE.TBL_IDX1_B.K1,
TRAFODION.SEABASE.TBL_IDX1_B.K2,
TRAFODION.SEABASE.TBL_IDX1_B.TS
executor_predicates .... 0. and (TRAFODION.SEABASE.TBL_IDX1_B._SALT_ >=
(\:_sys_HostVarLoHashPart Hash2Distrib 2)) and
(TRAFODION.SEABASE.TBL_IDX1_B._SALT_ <=
(\:_sys_HostVarHiHashPart Hash2Distrib 2))
begin_key .............. (TRAFODION.SEABASE.TBL_IDX1_B._SALT_ = <max>),
(TRAFODION.SEABASE.TBL_IDX1_B.B = '<max>'),
(TRAFODION.SEABASE.TBL_IDX1_B._DIVISION_1_ =
<max>), (TRAFODION.SEABASE.TBL_IDX1_B.K1 = <max>),
(TRAFODION.SEABASE.TBL_IDX1_B.K2 = <max>),
(TRAFODION.SEABASE.TBL_IDX1_B.TS = <max>)
end_key ................ (TRAFODION.SEABASE.TBL_IDX1_B._SALT_ = <min>),
(TRAFODION.SEABASE.TBL_IDX1_B.B = '<min>'),
(TRAFODION.SEABASE.TBL_IDX1_B._DIVISION_1_ =
<min>), (TRAFODION.SEABASE.TBL_IDX1_B.K1 = <min>),
(TRAFODION.SEABASE.TBL_IDX1_B.K2 = <min>),
(TRAFODION.SEABASE.TBL_IDX1_B.TS = <min>)
>>execute s1 ;
--- 0 row(s) selected.
>>display statistics ;
Start Time 2017/04/07 03:55:21.688124
End Time 2017/04/07 03:55:25.633487
Elapsed Time 00:00:03.945363
Compile Time 00:00:00.729450
Execution Time 00:00:03.945363
Table Name Records Records Hdfs Hdfs I/O Hdfs Access
Accessed Used I/Os Bytes Time(usec)
TRAFODION.SEABASE.TBL_IDX1_B
0 0 4 0 982939
TRAFODION.SEABASE.TBL
0 0 0 0 0
--- SQL operation complete.
>>
> bypass the operator work if the predicate is FALSE
> --------------------------------------------------
>
> Key: TRAFODION-2568
> URL: https://issues.apache.org/jira/browse/TRAFODION-2568
> Project: Apache Trafodion
> Issue Type: Improvement
> Reporter: liu ming
> Assignee: Suresh Subbiah
>
> select * from t where 1=0;
> Ideally, this should bypass the SCAN since there will be no result returned.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)