David Wayne Birdsall created TRAFODION-2537: -----------------------------------------------
Summary: Salted indexes do not result in parallel index scan plans Key: TRAFODION-2537 URL: https://issues.apache.org/jira/browse/TRAFODION-2537 Project: Apache Trafodion Issue Type: Bug Components: sql-cmp Affects Versions: 2.1-incubating, 2.2-incubating Environment: All, though it is more likely on a cluster. Reporter: David Wayne Birdsall Assignee: David Wayne Birdsall The following script reproduces the problem: ?section setup create table test2735(a int not null not droppable primary key, b int, c int) salt using 4 partitions; -- put 200,000 rows into it upsert using load into test2735 select c0+c1*10+c2*100+c3*1000+c4*10000+c5*100000+c6*1000000, c0+2*c1+4*c5, c3+3*c4-7*c2 from (values(1)) t transpose 0,1,2,3,4,5,6,7,8,9 as c0 transpose 0,1,2,3,4,5,6,7,8,9 as c1 transpose 0,1,2,3,4,5,6,7,8,9 as c2 transpose 0,1,2,3,4,5,6,7,8,9 as c3 transpose 0,1,2,3,4,5,6,7,8,9 as c4 transpose 0,1,2,3,4,5,6,7,8,9 as c5 transpose 0,1 as c6; update statistics for table test2735 on every column; ?section indexsetup create index itest2735s on test2735(b,c) salt like table; create index itest2735ns on test2735(c); ?section doit prepare s0 from select a,count(*) from test2735 group by a; explain options 'f' s0; prepare s1 from select b,count(*) from test2735 group by b; explain options 'f' s1; prepare s2 from select c,count(*) from test2735 group by c; explain options 'f' s2; When the above script is run, the explain output is as follows: >>prepare s0 from select a,count(*) from test2735 group by a; --- SQL command prepared. >>explain options 'f' s0; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 1 . 2 root 2.00E+006 . . 1 trafodion_index_scan ITEST2735NS 2.00E+006 --- SQL operation complete. >> >>prepare s1 from select b,count(*) from test2735 group by b; --- SQL command prepared. >>explain options 'f' s1; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 2 . 3 root 6.40E+001 1 . 2 hash_groupby 6.40E+001 . . 1 trafodion_index_scan ITEST2735S 2.00E+006 --- SQL operation complete. >> >>prepare s2 from select c,count(*) from test2735 group by c; --- SQL command prepared. >>explain options 'f' s2; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 2 . 3 root 1.00E+002 1 . 2 sort_groupby 1.00E+002 . . 1 trafodion_index_scan ITEST2735NS 2.00E+006 --- SQL operation complete. >> In particular, statement S1 chooses a serial plan, but should choose a parallel plan. -- This message was sent by Atlassian JIRA (v6.3.15#6346)