[jira] [Created] (TRAFODION-301) LP Bug: 1323823 - compiler doesn't explore NJ plans for queries on seabase metadata table

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-301:
------------------------------------

             Summary: LP Bug: 1323823 - compiler doesn't explore NJ plans for queries on seabase metadata table
                 Key: TRAFODION-301
                 URL: https://issues.apache.org/jira/browse/TRAFODION-301
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Ravisha Neelakanthappa
            Assignee: Ravisha Neelakanthappa
            Priority: Critical
             Fix For: 0.7 (pre-incubation)


The optimizer engine doesn't explore NJ plans for queries on seabase metadata tables. For example the following query optimizer chooses a parallel MJ/HJ plan:

>>prepare s from
select trim(O.catalog_name || '.' || '\"' || O.schema_name || '\"' || '.' || '\"' || O.object_name || '\"' ) constr_name, trim(O2.catalog_name || '.' || '\"' || O2.schema_name || '\"' || '.' || '\"' || O2.object_name || '\"' ) table_name from trafodion."_MD_".unique_ref_constr_usage U, trafodion."_MD_".objects O, trafodion."_MD_".objects O2, trafodion."_MD_".table_constraints T where  O.object_uid = U.foreign_constraint_uid and O2.object_uid = T.table_uid and T.constraint_uid = U.foreign_constraint_uid and U.unique_constraint_uid = 0;
--- SQL command prepared.
>>explain options 'f' s;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

12   .    13   root                                                  1.25E+006
11   .    12   esp_exchange                    1:12(hash2)           1.25E+006
10   2    11   hybrid_hash_join      u                               1.25E+006
7    9    10   merge_join                                            2.50E+004
8    .    9    esp_exchange                    12(hash2):1 (m)       1.00E+002
.    .    8    trafodion_index_scan            OBJECTS               1.00E+002
6    .    7    sort                                                  5.00E+002
5    .    6    esp_exchange                    12(hash2):1           5.00E+002
4    3    5    hybrid_hash_join                                      5.00E+002
.    .    4    trafodion_scan                  UNIQUE_REF_CONSTR_US  1.00E+001
.    .    3    trafodion_scan                  TABLE_CONSTRAINTS     1.00E+002
1    .    2    esp_exchange                    12(rep-b):1 (m)       1.00E+002
.    .    1    trafodion_index_scan            OBJECTS               1.00E+002

--- SQL operation complete.

There are three issues associated with this plan:
1. Dop is 12, but hardly few rows return from the query. 
Qifan has checked in a fix where parallel plan is explored only if data size per ESP is > 64MB.

2. Cardinality estimates are on the higher side
Taoufik is working on this to limit join cardinality to left child’s row count.

3. Didn’t explore NJ even though join predicate is on primary key column.
I investigated and noticed that NJ is not explored due to heuristic in TSJ rule which says “inner table size < probes * FF”, then HJ is cheaper. But we know cardinality is not correct because of missing stats on MD tables.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)