Echo predicate functinality for sub queries
Echo predicate is not working properly to eliminate partition and query is taking longer time to complete
Filter With Hard Coded date: Query completes in 2 secs , since it is directly hitting the July 31st partition alone
SELECT * FROM ESA_QV_IL_DATAT1.MDM_IP WHERE SNAPSHOT_DT = '2017-07-31';
Filter With Sub Query: Query took 8 mins to complete and we are facing performance issue.
SELECT * FROM ESA_QV_IL_DATAT1.MDM_IP WHERE SNAPSHOT_DT = (SELECT MAX(SNAPSHOT_DT) FROM ESA_QV_IL_DATAT1.MDM_IP );
Note: SELECT MAX(SNAPSHOT_DT) FROM ESA_QV_IL_DATAT1.MDM_IP Ã Query returned results in 42 seconds
Usually it will process sub query first and pass the latest date results to the outer sql, So that outer sql will process only for the filtered latest date. But here, it is doing nested loop join between these 2 tables for entire history and at last it is eliminating the latest date partition. so query is running for long time.
We like to include this feature in the product, so that it will improve performance in our process.