Details
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.
Attachments
Issue Links
- links to