[
https://issues.apache.org/jira/browse/TRAFODION-2537?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
David Wayne Birdsall updated TRAFODION-2537:
--------------------------------------------
Description:
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.
was:
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.
> 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)
