Uploaded image for project: 'Apache Trafodion (Retired)'
  1. Apache Trafodion (Retired)
  2. TRAFODION-301

LP Bug: 1323823 - compiler doesn't explore NJ plans for queries on seabase metadata table

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • None
    • 0.7 (pre-incubation)
    • sql-cmp
    • None

    Description

      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.

      Attachments

        Activity

          People

            nravisha Ravisha Neelakanthappa
            nravisha Ravisha Neelakanthappa
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: