Stamatis Zampetakis created IMPALA-15327:
--------------------------------------------
Summary: CTE rewrite algorithm cannot explore all suggestions
Key: IMPALA-15327
URL: https://issues.apache.org/jira/browse/IMPALA-15327
Project: IMPALA
Issue Type: Sub-task
Reporter: Stamatis Zampetakis
Assignee: Stamatis Zampetakis
The [materialized view rewrite
algorithm|https://github.com/apache/impala/blob/7a3cb82b49d845bc5ef597e598f589593dd13549/java/calcite-planner/src/main/java/org/apache/impala/calcite/service/CalciteOptimizer.java#L406]
that is used to pick CTEs cannot explore the entire search space ([all
candidates|https://github.com/apache/impala/blob/7a3cb82b49d845bc5ef597e598f589593dd13549/java/calcite-planner/src/main/java/org/apache/impala/calcite/service/CalciteOptimizer.java#L384]
provided by the suggester) so in some cases it makes sub-optimal choices.
Consider for example TPC-DS query 75. The basic suggester implementation at the
moment returns the following candidate suggestions (among others):
+cte_suggestion_1+
{noformat}
LogicalJoin(condition=[=($5, $0)], joinType=[inner])
LogicalFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($4))])
LogicalProject(ss_item_sk=[$1], ss_ticket_number=[$8], ss_quantity=[$9],
ss_ext_sales_price=[$14], ss_sold_date_sk=[$22])
LogicalTableScan(table=[[tpcds_partitioned_parquet_snap, store_sales]])
LogicalProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2],
i_category_id=[$3], i_manufact_id=[$5])
LogicalFilter(condition=[AND(=($4, _UTF-8'Books'), IS NOT NULL($0), IS NOT
NULL($1), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($5))])
LogicalProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9],
i_category_id=[$11], i_category=[$12], i_manufact_id=[$13])
LogicalTableScan(table=[[tpcds_partitioned_parquet_snap, item]])
{noformat}
+cte_suggestion_5+
{noformat}
LogicalProject(i_item_sk=[$0], i_brand_id=[$1], i_class_id=[$2],
i_category_id=[$3], i_manufact_id=[$5])
LogicalFilter(condition=[AND(=($4, _UTF-8'Books'), IS NOT NULL($0), IS NOT
NULL($1), IS NOT NULL($2), IS NOT NULL($3), IS NOT NULL($5))])
LogicalProject(i_item_sk=[$0], i_brand_id=[$7], i_class_id=[$9],
i_category_id=[$11], i_category=[$12], i_manufact_id=[$13])
LogicalTableScan(table=[[tpcds_partitioned_parquet_snap, item]])
{noformat}
Currently the algorithm picks cte_suggestion_5 and never considers
cte_suggestion_1. As a result the [final plan for TPC-DS query
75|https://github.com/apache/impala/blob/7a3cb82b49d845bc5ef597e598f589593dd13549/testdata/workloads/functional-planner/queries/PlannerTest/tpcds_cte/tpcds-q75.test#L168]
only contains the smaller (single-table) suggestions.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
