[ https://issues.apache.org/jira/browse/TRAFODION-2537?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Work on TRAFODION-2537 started by David Wayne Birdsall. ------------------------------------------------------- > 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 2,000,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)