[
https://issues.apache.org/jira/browse/TRAFODION-2537?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15926989#comment-15926989
]
David Wayne Birdsall commented on TRAFODION-2537:
-------------------------------------------------
Some notes on the performance implications of this change. Using the script
given above, statement s1 changes to a parallel index scan with the fix.
I executed statement s1 with the fix, and with CQD ATTEMPT_ESP_PARALLELISM
'OFF', which gives the same serial plan that we had before the fix. The serial
plan was faster! Elapsed time was 10 seconds for the serial plan and 15 for the
parallel. Intrigued, I changed the test script to create 10 million rows
instead of 2 million. Elapsed time for the serial plan was now 39 seconds, and
for the parallel plan 17 seconds. Note that my tests were on a development
workstation in an uncontrolled environment, so other unknown workloads were
running at the same time.
The good news is that intuitively, with larger and larger tables, the parallel
plan should far outperform the serial plan, so the change is a good change. The
interesting news is that either the costing or the heuristics in
OptDefaults::estimateRequiredResources that control maximum degree of
parallelism set the bar too low for preferring parallel plans over serial. So
some additional calibration work could be done to make this sharper.
As with any change that changes plans, it would be useful to have a more
general and controlled before-and-after performance test.
> 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)
