Details
-
Bug
-
Status: Closed
-
Critical
-
Resolution: Fixed
-
None
-
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.