[jira] [Created] (TRAFODION-2537) Salted indexes do not result in parallel index scan plans

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

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)