[
https://issues.apache.org/jira/browse/TRAFODION-2655?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16058407#comment-16058407
]
ASF GitHub Bot commented on TRAFODION-2655:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/incubator-trafodion/pull/1136
[TRAFODION-2655] Fix 2 MDAM optimizer bugs. Update optimizer simulator.
This set of changes does three things:
1. Increases the weight of cumulative MDAM probe costs by a factor of 3
(configurable via the new CQD MDAM_PROBE_TAX), to encourage consideration of
MDAM plans on fewer key columns. This addresses the issue with the first query
in this JIRA.
2. Changes the default of CQD MDAM_APPLY_RESTRICTION_CHECK from '2' to '0'.
This heuristic was put in place before cumulative probes were taken into
account for MDAM probes. Now that this is the case, and now that their weight
has been adjusted above, this heuristic should no longer be needed. The
heuristic prevents MDAM plans in some cases where they are beneficial, such as
the second query in this JIRA.
3. The predecessor product contained a feature called the "Optimizer
Simulator". This feature allows one to capture DDL, histogram statistics,
various cluster configuration information, and queries on one system, then load
that information on a workstation for optimizer debugging. It is useful for
precisely the sorts of plan and costing issues dealt with in this JIRA. The
changes here update the Optimizer Simulator so that it now works on Trafodion.
This should be considered a "technology preview" feature. This particular set
of changes is a group effort: Credit for this work lays mostly with @HowardQin,
with additional changes from @nonstop-qfchen, @zellerh and @eowhadi.
Changes for the first two items are in optimizer/ScanOptimizer.cpp,
sqlcomp/DefaultConstants.h and sqlcomp/nadefaults.cpp. The remaining files are
from the third item.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/incubator-trafodion MdamCosting
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/1136.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1136
----
commit 802029e583f790e7dd3ea0ecfbf3eb921dd8f073
Author: Dave Birdsall <dbirds...@apache.org>
Date: 2017-06-21T22:58:41Z
[TRAFODION-2655] Fix two MDAM optimizer bugs. Update optimizer simulator.
----
> MDAM plans on prefixes sometimes not chosen when they should be
> ---------------------------------------------------------------
>
> Key: TRAFODION-2655
> URL: https://issues.apache.org/jira/browse/TRAFODION-2655
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.2-incubating
> Environment: Large clusters
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
>
> Below are two scripts that reproduce the problems.
> In the first problem, a query using parameters does not get an MDAM plan,
> even though it would be beneficial to do so. If the parameters on column C2
> are replaced with literals that are close enough together, we do get the
> desired MDAM plan.
> In the second problem, using a larger version of the same table, a query with
> equality predicates on the first and third key columns ("first" and "third"
> ignoring the SALT column) does not get an MDAM plan on the first key column
> but chooses a full table scan instead.
> These problems were noticed on a cluster; it's not practical to create tables
> of the necessary size on a workstation. The scripts below reproduce the
> problem on a workstation by faking the Optimizer into thinking it is dealing
> with much larger tables.
> First script:
> ?section setup
> drop table if exists MDAM_Q1_TEST;
> create table MDAM_Q1_TEST
> ( c1 integer not null, -- will have uec 2
> c2 integer not null, -- will have uec 1,000,000
> c3 integer not null, -- will have uec 766
> c4 integer not null, -- will have uec 46
> c5 integer not null, -- will have uec don't care (but 1,000,000)
> primary key ( c1, c2, c3, c4 ) )
> salt using 4 partitions;
> upsert using load into MDAM_Q1_TEST
> select mod(c0+c1+c2+c3+c4,2),
> c0 + 10*c1 + 100*c2 + 1000*c3 + 10000*c4 + 100000*c5,
> c3 + 10*c1 + 74*c5,
> c4 + 4*c5,
> c5 + 10*c4 + 100*c3 + 1000*c2 + 10000*c1 + 100000*c0
> 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;
> update statistics for table MDAM_Q1_TEST on every column;
> -- next fake out the statistics so the optimizer will think there
> -- are 265 million rows in the table
> update sb_histograms set rowcount = 265 * rowcount
> where table_uid = (select object_uid from "_MD_".objects where object_name =
> 'MDAM_Q1_TEST');
> update sb_histogram_intervals set interval_rowcount = 265 * interval_rowcount
> where table_uid = (select object_uid from "_MD_".objects where object_name =
> 'MDAM_Q1_TEST');
> showstats for table MDAM_Q1_TEST on existing columns;
> ?section query1
> prepare xx from
> select * from MDAM_Q1_TEST
> where c1 = ? and c2 > ? and c2 <= ? and c3 = ? and c4 = ?;
> explain xx; -- the plan gets a full scan instead of MDAM
> Second script
> ?section setup
> drop table if exists MDAM_Q2_TEST;
> create table MDAM_Q2_TEST
> ( c1 integer not null, -- will have uec 100
> c2 integer not null, -- will have uec 2,000,000
> c3 integer not null, -- will have uec 9,100
> c4 integer not null, -- will have uec 11,800
> c5 integer not null, -- will have uec don't care
> primary key ( c1, c2, c3, c4 ) )
> salt using 4 partitions;
> upsert using load into MDAM_Q2_TEST
> select c3 + 10*c4,
> c0 + 10*c1 + 100*c2 + 1000*c3 + 10000*c4 + 100000*c5 + 1000000*c6,
> c3 + 10*c1 + 100*c5 + 900*c4,
> c4 + 10*c5 + 100*c2 + 600*c1 + 600*c3,
> c5 + 10*c4 + 100*c3 + 1000*c2 + 10000*c1 + 100000*c0 + 7*c6
> 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;
> ?section setupstats
> update statistics for table MDAM_Q2_TEST on every column;
> -- next fake out the statistics so the optimizer will think there
> -- are 11 billion rows in the table
> update sb_histograms set rowcount = 5500 * rowcount
> where table_uid = (select object_uid from "_MD_".objects where object_name =
> 'MDAM_Q2_TEST');
> update sb_histogram_intervals set interval_rowcount = 5500 * interval_rowcount
> where table_uid = (select object_uid from "_MD_".objects where object_name =
> 'MDAM_Q2_TEST');
> -- finally fake out the UEC on C2 so optimizer will think
> -- the UEC is 5 billion instead of 2 million
> update sb_histograms set total_uec = 2500 * total_uec
> where table_uid = (select object_uid from "_MD_".objects where object_name =
> 'MDAM_Q2_TEST')
> and column_number = 1 and colcount = 1;
> update sb_histogram_intervals i set interval_uec = 2500 * interval_uec
> where i.table_uid = (select object_uid from "_MD_".objects where object_name
> = 'MDAM_Q2_TEST')
> and i.histogram_id = (select histogram_id from sb_histograms h where
> h.table_uid = i.table_uid and h.column_number = 1 and h.colcount = 1);
> -- update the UECs in the multi-column histograms that include C2
> update sb_histograms set total_uec = 5000000000 where total_uec = 2000000
> and table_uid = (select object_uid from "_MD_".objects where object_name =
> 'MDAM_Q2_TEST');
> update sb_histogram_intervals set interval_uec = 5000000000 where
> interval_uec = 2000000
> and table_uid = (select object_uid from "_MD_".objects where object_name =
> 'MDAM_Q2_TEST');
> showstats for table MDAM_Q2_TEST on existing columns;
> ?section query2
> prepare xx from
> select * from MDAM_Q2_TEST
> where c1 = 23 and c3 = 497;
> explain xx; -- the plan does not get MDAM; but MDAM on C1 would be a good
> plan
> ?section query2x
> prepare yy from
> select * from MDAM_Q2_TEST
> where c1 = 23;
> explain yy; -- here we do get an MDAM plan on C1
>
>
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
