[jira] [Commented] (SPARK-23750) [Performance] Inner Join Elimination based on Informational RI constraints

["Apache Spark (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SPARK-23750?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16407248#comment-16407248 ] 

Apache Spark commented on SPARK-23750:
--------------------------------------

User 'ioana-delaney' has created a pull request for this issue:
https://github.com/apache/spark/pull/20868

> [Performance] Inner Join Elimination based on Informational RI constraints
> --------------------------------------------------------------------------
>
>                 Key: SPARK-23750
>                 URL: https://issues.apache.org/jira/browse/SPARK-23750
>             Project: Spark
>          Issue Type: Sub-task
>          Components: SQL
>    Affects Versions: 3.0.0
>            Reporter: Ioana Delaney
>            Priority: Major
>
> +*Inner Join Elimination based on Informational RI constraints*+
> This transformation detects RI joins and eliminates the parent/PK table if none of its columns, other than the PK columns, are referenced in the query.
> Typical examples that benefit from this rewrite are queries over complex views.
> *View using TPC-DS schema:*
> {code}
> create view customer_purchases_2002 (id, last, first, product, store_id, month, quantity) as
> select c_customer_id, c_last_name, c_first_name, i_product_name, s_store_id, d_moy, ss_quantity 
> from store_sales, date_dim, customer, item, store
> where d_date_sk = ss_sold_date_sk and
>       c_customer_sk = ss_customer_sk and 
>       i_item_sk = ss_item_sk and
>       s_store_sk = ss_store_sk and
>       d_year = 2002
> {code}
> The view returns customer purchases made in year 2002. It is a join between fact table _store_sales_ and dimensions _customer_, _item,_ _store_, and _date_. The tables are joined using RI predicates.
> If we write a query that only selects a subset of columns from the view, for example, we are only interested in the items bought and not the stores, internally, the Optimizer, will first merge the view into the query, and then, based on the _primary key – foreign key_ join predicate analysis, it will decide that the join with the _store_ table is not needed, and therefore the _store_ table is removed.
> *Query:*
> {code}
> select id, first, last, product, quantity 
> from customer_purchases_2002
> where product like ‘bicycle%’ and
>       month between 1 and 2
> {code}
> *Internal query after view expansion:*
> {code}
> select c_customer_id as id, c_first_name as first, c_last_name as last,
>            i_product_name as product,ss_quantity as quantity 
> from store_sales, date_dim, customer, item, store
> where d_date_sk = ss_sold_date_sk and
>       c_customer_sk = ss_customer_sk and 
>       i_item_sk = ss_item_sk and
>       s_store_sk = ss_store_sk and
>       d_year = 2002 and
>       month between 1 and 2 and
>       product like ‘bicycle%’
> {code}
> *Internal optimized query after join elimination:*
> {code:java}
> select c_customer_id as id, c_first_name as first, c_last_name as last,
>            i_product_name as product,ss_quantity as quantity 
> from store_sales, date_dim, customer, item
> where d_date_sk = ss_sold_date_sk and
>       c_customer_sk = ss_customer_sk and 
>       i_item_sk = ss_item_sk and
>       d_year = 2002 and
>       month between 1 and 2 and
>       product like ‘bicycle%’
> {code}
> The join with _store_ table can be removed since no columns are retrieved from the table, and every row from the _store_sales_ fact table will find a match in _store_ based on the RI relationship.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org