You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "David Wayne Birdsall (JIRA)" <ji...@apache.org> on 2015/11/24 00:45:11 UTC
[jira] [Commented] (TRAFODION-1641) Apparent inappropriate choice
of MDAM in query plan
[ https://issues.apache.org/jira/browse/TRAFODION-1641?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15023370#comment-15023370 ]
David Wayne Birdsall commented on TRAFODION-1641:
-------------------------------------------------
Given the following table:
CREATE TABLE T1A
(
C1 VARCHAR(3 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'000' NOT NULL NOT DROPPABLE NOT SERIALIZED
, C2 VARCHAR(4 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'0000' NOT NULL NOT DROPPABLE NOT SERIALIZED
, C3 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT NULL NOT DROPPABLE NOT SERIALIZED
, C4 VARCHAR(2 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT NULL NOT DROPPABLE NOT SERIALIZED
, C5 VARCHAR(4 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT NULL NOT DROPPABLE NOT SERIALIZED
, C6 VARCHAR(6 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT NULL NOT DROPPABLE NOT SERIALIZED
, C7 VARCHAR(4 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C8 VARCHAR(3 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C9 VARCHAR(5 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C10 VARCHAR(3 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C11 VARCHAR(5 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C12 VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C13 VARCHAR(3 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C14 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C15 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C16 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C17 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C18 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C19 VARCHAR(16 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C20 VARCHAR(4 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C21 VARCHAR(4 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C22 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C23 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C24 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C25 VARCHAR(16 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C26 VARCHAR(4 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C27 VARCHAR(6 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C28 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C29 NUMERIC(23, 2) DEFAULT 0 NOT SERIALIZED
, C30 NUMERIC(23, 2) DEFAULT 0 NOT SERIALIZED
, C31 NUMERIC(23, 2) DEFAULT 0 NOT SERIALIZED
, C32 NUMERIC(23, 2) DEFAULT 0 NOT SERIALIZED
, C33 NUMERIC(23, 3) DEFAULT 0 NOT SERIALIZED
, C34 NUMERIC(23, 2) DEFAULT 0 NOT SERIALIZED
, C35 VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C36 VARCHAR(3 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'000' NOT SERIALIZED
, C37 VARCHAR(5 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C38 VARCHAR(4 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'0000' NOT SERIALIZED
, C39 VARCHAR(8 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'00000000' NOT SERIALIZED
, C40 VARCHAR(10 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C41 VARCHAR(3 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'000' NOT SERIALIZED
, C42 VARCHAR(2 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C43 VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C44 VARCHAR(5 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C45 VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C46 VARCHAR(12 CHARS) CHARACTER SET UTF8
COLLATE DEFAULT DEFAULT _UTF8'' NOT SERIALIZED
, C47 NUMERIC(15, 0) DEFAULT 0 NOT SERIALIZED
, PRIMARY KEY (C2 ASC, C6 ASC, C3 ASC, C1 ASC, C4 ASC, C5
ASC)
)
SALT USING 96 PARTITIONS
ON (C3)
ATTRIBUTES ALIGNED FORMAT
HBASE_OPTIONS
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
MEMSTORE_FLUSH_SIZE = '1073741824'
)
;
The following query:
select sum(C29) from T1A where C2='2014' and C5='A019';
would choose a plan using MDAM on all key columns. This happened on an example of T1A having 80,000,000 rows, with UECs of 96 in "_SALT_", 1267 in C6, 6,000,000 in C3, and 1 in C1 and C4.
There are two bugs that this example exposed.
1. The costing formulas in MDAMOptimalDisjunctPrefixWA::updateMinPrefix do not take into account the recursive nature of the MDAM run-time algorithm. The MDAM algorithm effectively materializes all the relevant values in the first key column, and for each one, traverses to the second column where it again materializes all values, and so on until reaching the "stop column", which is where MDAM decides to do a direct subset access. The costing algorithm was only considering the cost of returning rows at the stop column and neglected the materialize overhead. When the number of subsets exceeded the row count of the table, this fooled the costing code into thinking that it could traverse to additional columns for free, and its choice reflected that.
2. There is a feature called "range spec transformation" that attempts to more efficiently represent certain predicate trees within the compiler. It is used for many things. It interacts with MDAM costing in that MDAM costing sometimes will see these "range spec" predicates. There was code added to the MDAM costing that caused MDAM to consider traversing to all key columns as the cheapest plan for MDAM. As noted in point 1 above, this is incorrect. In fact, when the UEC of trailing columns is high, this can be grossly inefficient.
To fix #1 above, the costing code needs to take the cumulative cost of traversing to additional key columns into account.
To fix #2, some incorrect code concerning "range spec transformation" needs to be removed so that we will consider plans that don't traverse all of the key columns. Also, better costing code for the range spec case needs to be written.
> Apparent inappropriate choice of MDAM in query plan
> ---------------------------------------------------
>
> Key: TRAFODION-1641
> URL: https://issues.apache.org/jira/browse/TRAFODION-1641
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 1.3-incubating
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
>
> Details to be supplied.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)