[IMPALA-9183] TPC-DS query 13 - customer_address predicates not propagated to scan

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: Impala 3.4.0
• Fix Version/s: Impala 4.0.0
• Component/s: Frontend
• Labels: tpc-ds
• Epic Color: ghx-label-2
• Assignee: Aman Sinha
• Reporter: David Rorke
• Created: 22/Nov/19 01:16
• Updated: 01/Dec/22 17:37
• Resolved: 10/Apr/20 15:12

Description

TPC-DS query 13 has a set of predicates on the customer_address table, ca_state column that are currently evaluated after the join of customer_address and store_sales.   The ca_state predicates could be pushed down to the customer_address scan node.  This would reduce the size of the join input by a factor of 3.4.

As an experiment I added an additional redundant predicate to the query (see attached query13_mod.sql) which causes the planner to evaluate the predicate at the scan node. 

Performance of the original and modified queries at 10 TB scale factor:

Original:  164 seconds

Modified: 44 seconds

Query profiles for both versions attached.

Issue Links

• causes: IMPALA-11274 CNF Rewrite causes a regress in join node performance
• is related to: IMPALA-11770 Review Predicate arguments for impact of CNF rewrites
• is related to: IMPALA-9620 Predicates in the SELECT and GROUP-BY cause failure with CNF rewrite enabled
• relates to: IMPALA-8165 Planner does not push through predicates when there is a disjunction