[ 
https://issues.apache.org/jira/browse/DRILL-4347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Victoria Markman updated DRILL-4347:
------------------------------------
    Fix Version/s: 1.6.0

> Planning time for query64 from TPCDS test suite has increased 10 times 
> compared to 1.4 release
> ----------------------------------------------------------------------------------------------
>
>                 Key: DRILL-4347
>                 URL: https://issues.apache.org/jira/browse/DRILL-4347
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 1.5.0
>            Reporter: Victoria Markman
>            Assignee: Jinfeng Ni
>             Fix For: 1.6.0
>
>         Attachments: 294e9fb9-cdda-a89f-d1a7-b852878926a1.sys.drill_1.4.0, 
> 294ea418-9fb8-3082-1725-74e3cfe38fe9.sys.drill_1.5.0
>
>
> mapr-drill-1.5.0.201602012001-1.noarch.rpm
> {code}
> 0: jdbc:drill:schema=dfs> WITH cs_ui
> . . . . . . . . . . . . >      AS (SELECT cs_item_sk,
> . . . . . . . . . . . . >                 Sum(cs_ext_list_price) AS sale,
> . . . . . . . . . . . . >                 Sum(cr_refunded_cash + 
> cr_reversed_charge
> . . . . . . . . . . . . >                     + cr_store_credit) AS refund
> . . . . . . . . . . . . >          FROM   catalog_sales,
> . . . . . . . . . . . . >                 catalog_returns
> . . . . . . . . . . . . >          WHERE  cs_item_sk = cr_item_sk
> . . . . . . . . . . . . >                 AND cs_order_number = 
> cr_order_number
> . . . . . . . . . . . . >          GROUP  BY cs_item_sk
> . . . . . . . . . . . . >          HAVING Sum(cs_ext_list_price) > 2 * Sum(
> . . . . . . . . . . . . >                 cr_refunded_cash + 
> cr_reversed_charge
> . . . . . . . . . . . . >                 + cr_store_credit)),
> . . . . . . . . . . . . >      cross_sales
> . . . . . . . . . . . . >      AS (SELECT i_product_name         product_name,
> . . . . . . . . . . . . >                 i_item_sk              item_sk,
> . . . . . . . . . . . . >                 s_store_name           store_name,
> . . . . . . . . . . . . >                 s_zip                  store_zip,
> . . . . . . . . . . . . >                 ad1.ca_street_number   
> b_street_number,
> . . . . . . . . . . . . >                 ad1.ca_street_name     
> b_streen_name,
> . . . . . . . . . . . . >                 ad1.ca_city            b_city,
> . . . . . . . . . . . . >                 ad1.ca_zip             b_zip,
> . . . . . . . . . . . . >                 ad2.ca_street_number   
> c_street_number,
> . . . . . . . . . . . . >                 ad2.ca_street_name     
> c_street_name,
> . . . . . . . . . . . . >                 ad2.ca_city            c_city,
> . . . . . . . . . . . . >                 ad2.ca_zip             c_zip,
> . . . . . . . . . . . . >                 d1.d_year              AS syear,
> . . . . . . . . . . . . >                 d2.d_year              AS fsyear,
> . . . . . . . . . . . . >                 d3.d_year              s2year,
> . . . . . . . . . . . . >                 Count(*)               cnt,
> . . . . . . . . . . . . >                 Sum(ss_wholesale_cost) s1,
> . . . . . . . . . . . . >                 Sum(ss_list_price)     s2,
> . . . . . . . . . . . . >                 Sum(ss_coupon_amt)     s3
> . . . . . . . . . . . . >          FROM   store_sales,
> . . . . . . . . . . . . >                 store_returns,
> . . . . . . . . . . . . >                 cs_ui,
> . . . . . . . . . . . . >                 date_dim d1,
> . . . . . . . . . . . . >                 date_dim d2,
> . . . . . . . . . . . . >                 date_dim d3,
> . . . . . . . . . . . . >                 store,
> . . . . . . . . . . . . >                 customer,
> . . . . . . . . . . . . >                 customer_demographics cd1,
> . . . . . . . . . . . . >                 customer_demographics cd2,
> . . . . . . . . . . . . >                 promotion,
> . . . . . . . . . . . . >                 household_demographics hd1,
> . . . . . . . . . . . . >                 household_demographics hd2,
> . . . . . . . . . . . . >                 customer_address ad1,
> . . . . . . . . . . . . >                 customer_address ad2,
> . . . . . . . . . . . . >                 income_band ib1,
> . . . . . . . . . . . . >                 income_band ib2,
> . . . . . . . . . . . . >                 item
> . . . . . . . . . . . . >          WHERE  ss_store_sk = s_store_sk
> . . . . . . . . . . . . >                 AND ss_sold_date_sk = d1.d_date_sk
> . . . . . . . . . . . . >                 AND ss_customer_sk = c_customer_sk
> . . . . . . . . . . . . >                 AND ss_cdemo_sk = cd1.cd_demo_sk
> . . . . . . . . . . . . >                 AND ss_hdemo_sk = hd1.hd_demo_sk
> . . . . . . . . . . . . >                 AND ss_addr_sk = ad1.ca_address_sk
> . . . . . . . . . . . . >                 AND ss_item_sk = i_item_sk
> . . . . . . . . . . . . >                 AND ss_item_sk = sr_item_sk
> . . . . . . . . . . . . >                 AND ss_ticket_number = 
> sr_ticket_number
> . . . . . . . . . . . . >                 AND ss_item_sk = cs_ui.cs_item_sk
> . . . . . . . . . . . . >                 AND c_current_cdemo_sk = 
> cd2.cd_demo_sk
> . . . . . . . . . . . . >                 AND c_current_hdemo_sk = 
> hd2.hd_demo_sk
> . . . . . . . . . . . . >                 AND c_current_addr_sk = 
> ad2.ca_address_sk
> . . . . . . . . . . . . >                 AND c_first_sales_date_sk = 
> d2.d_date_sk
> . . . . . . . . . . . . >                 AND c_first_shipto_date_sk = 
> d3.d_date_sk
> . . . . . . . . . . . . >                 AND ss_promo_sk = p_promo_sk
> . . . . . . . . . . . . >                 AND hd1.hd_income_band_sk = 
> ib1.ib_income_band_sk
> . . . . . . . . . . . . >                 AND hd2.hd_income_band_sk = 
> ib2.ib_income_band_sk
> . . . . . . . . . . . . >                 AND cd1.cd_marital_status <> 
> cd2.cd_marital_status
> . . . . . . . . . . . . >                 AND i_color IN ( 'cyan', 'peach', 
> 'blush', 'frosted',
> . . . . . . . . . . . . >                                  'powder', 'orange' 
> )
> . . . . . . . . . . . . >                 AND i_current_price BETWEEN 58 AND 
> 58 + 10
> . . . . . . . . . . . . >                 AND i_current_price BETWEEN 58 + 1 
> AND 58 + 15
> . . . . . . . . . . . . >          GROUP  BY i_product_name,
> . . . . . . . . . . . . >                    i_item_sk,
> . . . . . . . . . . . . >                    s_store_name,
> . . . . . . . . . . . . >                    s_zip,
> . . . . . . . . . . . . >                    ad1.ca_street_number,
> . . . . . . . . . . . . >                    ad1.ca_street_name,
> . . . . . . . . . . . . >                    ad1.ca_city,
> . . . . . . . . . . . . >                    ad1.ca_zip,
> . . . . . . . . . . . . >                    ad2.ca_street_number,
> . . . . . . . . . . . . >                    ad2.ca_street_name,
> . . . . . . . . . . . . >                    ad2.ca_city,
> . . . . . . . . . . . . >                    ad2.ca_zip,
> . . . . . . . . . . . . >                    d1.d_year,
> . . . . . . . . . . . . >                    d2.d_year,
> . . . . . . . . . . . . >                    d3.d_year)
> . . . . . . . . . . . . > SELECT cs1.product_name,
> . . . . . . . . . . . . >        cs1.store_name,
> . . . . . . . . . . . . >        cs1.store_zip,
> . . . . . . . . . . . . >        cs1.b_street_number,
> . . . . . . . . . . . . >        cs1.b_streen_name,
> . . . . . . . . . . . . >        cs1.b_city,
> . . . . . . . . . . . . >        cs1.b_zip,
> . . . . . . . . . . . . >        cs1.c_street_number,
> . . . . . . . . . . . . >        cs1.c_street_name,
> . . . . . . . . . . . . >        cs1.c_city,
> . . . . . . . . . . . . >        cs1.c_zip,
> . . . . . . . . . . . . >        cs1.syear,
> . . . . . . . . . . . . >        cs1.cnt,
> . . . . . . . . . . . . >        cs1.s1,
> . . . . . . . . . . . . >        cs1.s2,
> . . . . . . . . . . . . >        cs1.s3,
> . . . . . . . . . . . . >        cs2.s1,
> . . . . . . . . . . . . >        cs2.s2,
> . . . . . . . . . . . . >        cs2.s3,
> . . . . . . . . . . . . >        cs2.syear,
> . . . . . . . . . . . . >        cs2.cnt
> . . . . . . . . . . . . > FROM   cross_sales cs1,
> . . . . . . . . . . . . >        cross_sales cs2
> . . . . . . . . . . . . > WHERE  cs1.item_sk = cs2.item_sk
> . . . . . . . . . . . . >        AND cs1.syear = 2001
> . . . . . . . . . . . . >        AND cs2.syear = 2001 + 1
> . . . . . . . . . . . . >        AND cs2.cnt <= cs1.cnt
> . . . . . . . . . . . . >        AND cs1.store_name = cs2.store_name
> . . . . . . . . . . . . >        AND cs1.store_zip = cs2.store_zip
> . . . . . . . . . . . . > ORDER  BY cs1.product_name,
> . . . . . . . . . . . . >           cs1.store_name,
> . . . . . . . . . . . . >           cs2.cnt;
> +----------------+-------------+------------+------------------+----------------+------------+--------+------------------+----------------+----------------+--------+--------+------+--------+-------+------+--------+---------+--------+---------+-------+
> |  product_name  | store_name  | store_zip  | b_street_number  | 
> b_streen_name  |   b_city   | b_zip  | c_street_number  | c_street_name  |    
>  c_city     | c_zip  | syear  | cnt  |   s1   |  s2   |  s3  |  s10   |   s20 
>   |  s30   | syear0  | cnt0  |
> +----------------+-------------+------------+------------------+----------------+------------+--------+------------------+----------------+----------------+--------+--------+------+--------+-------+------+--------+---------+--------+---------+-------+
> | antin station  | ation       | 31904      | 483              | Maple        
>   | Woodville  | 14289  | 600              | 13th Highland  | Spring Valley  
> | 36060  | 2001   | 1    | 33.13  | 63.6  | 0.0  | 97.04  | 112.56  | 15.11  
> | 2002    | 1     |
> +----------------+-------------+------------+------------------+----------------+------------+--------+------------------+----------------+----------------+--------+--------+------+--------+-------+------+--------+---------+--------+---------+-------+
> 1 row selected (387.503 seconds)
> {code}
> Compared to mapr-drill-1.4.0.201601071151-1.noarch.rpm
> {code}
> 0: jdbc:drill:schema=dfs> WITH cs_ui
> . . . . . . . . . . . . >      AS (SELECT cs_item_sk,
> . . . . . . . . . . . . >                 Sum(cs_ext_list_price) AS sale,
> . . . . . . . . . . . . >                 Sum(cr_refunded_cash + 
> cr_reversed_charge
> . . . . . . . . . . . . >                     + cr_store_credit) AS refund
> . . . . . . . . . . . . >          FROM   catalog_sales,
> . . . . . . . . . . . . >                 catalog_returns
> . . . . . . . . . . . . >          WHERE  cs_item_sk = cr_item_sk
> . . . . . . . . . . . . >                 AND cs_order_number = 
> cr_order_number
> . . . . . . . . . . . . >          GROUP  BY cs_item_sk
> . . . . . . . . . . . . >          HAVING Sum(cs_ext_list_price) > 2 * Sum(
> . . . . . . . . . . . . >                 cr_refunded_cash + 
> cr_reversed_charge
> . . . . . . . . . . . . >                 + cr_store_credit)),
> . . . . . . . . . . . . >      cross_sales
> . . . . . . . . . . . . >      AS (SELECT i_product_name         product_name,
> . . . . . . . . . . . . >                 i_item_sk              item_sk,
> . . . . . . . . . . . . >                 s_store_name           store_name,
> . . . . . . . . . . . . >                 s_zip                  store_zip,
> . . . . . . . . . . . . >                 ad1.ca_street_number   
> b_street_number,
> . . . . . . . . . . . . >                 ad1.ca_street_name     
> b_streen_name,
> . . . . . . . . . . . . >                 ad1.ca_city            b_city,
> . . . . . . . . . . . . >                 ad1.ca_zip             b_zip,
> . . . . . . . . . . . . >                 ad2.ca_street_number   
> c_street_number,
> . . . . . . . . . . . . >                 ad2.ca_street_name     
> c_street_name,
> . . . . . . . . . . . . >                 ad2.ca_city            c_city,
> . . . . . . . . . . . . >                 ad2.ca_zip             c_zip,
> . . . . . . . . . . . . >                 d1.d_year              AS syear,
> . . . . . . . . . . . . >                 d2.d_year              AS fsyear,
> . . . . . . . . . . . . >                 d3.d_year              s2year,
> . . . . . . . . . . . . >                 Count(*)               cnt,
> . . . . . . . . . . . . >                 Sum(ss_wholesale_cost) s1,
> . . . . . . . . . . . . >                 Sum(ss_list_price)     s2,
> . . . . . . . . . . . . >                 Sum(ss_coupon_amt)     s3
> . . . . . . . . . . . . >          FROM   store_sales,
> . . . . . . . . . . . . >                 store_returns,
> . . . . . . . . . . . . >                 cs_ui,
> . . . . . . . . . . . . >                 date_dim d1,
> . . . . . . . . . . . . >                 date_dim d2,
> . . . . . . . . . . . . >                 date_dim d3,
> . . . . . . . . . . . . >                 store,
> . . . . . . . . . . . . >                 customer,
> . . . . . . . . . . . . >                 customer_demographics cd1,
> . . . . . . . . . . . . >                 customer_demographics cd2,
> . . . . . . . . . . . . >                 promotion,
> . . . . . . . . . . . . >                 household_demographics hd1,
> . . . . . . . . . . . . >                 household_demographics hd2,
> . . . . . . . . . . . . >                 customer_address ad1,
> . . . . . . . . . . . . >                 customer_address ad2,
> . . . . . . . . . . . . >                 income_band ib1,
> . . . . . . . . . . . . >                 income_band ib2,
> . . . . . . . . . . . . >                 item
> . . . . . . . . . . . . >          WHERE  ss_store_sk = s_store_sk
> . . . . . . . . . . . . >                 AND ss_sold_date_sk = d1.d_date_sk
> . . . . . . . . . . . . >                 AND ss_customer_sk = c_customer_sk
> . . . . . . . . . . . . >                 AND ss_cdemo_sk = cd1.cd_demo_sk
> . . . . . . . . . . . . >                 AND ss_hdemo_sk = hd1.hd_demo_sk
> . . . . . . . . . . . . >                 AND ss_addr_sk = ad1.ca_address_sk
> . . . . . . . . . . . . >                 AND ss_item_sk = i_item_sk
> . . . . . . . . . . . . >                 AND ss_item_sk = sr_item_sk
> . . . . . . . . . . . . >                 AND ss_ticket_number = 
> sr_ticket_number
> . . . . . . . . . . . . >                 AND ss_item_sk = cs_ui.cs_item_sk
> . . . . . . . . . . . . >                 AND c_current_cdemo_sk = 
> cd2.cd_demo_sk
> . . . . . . . . . . . . >                 AND c_current_hdemo_sk = 
> hd2.hd_demo_sk
> . . . . . . . . . . . . >                 AND c_current_addr_sk = 
> ad2.ca_address_sk
> . . . . . . . . . . . . >                 AND c_first_sales_date_sk = 
> d2.d_date_sk
> . . . . . . . . . . . . >                 AND c_first_shipto_date_sk = 
> d3.d_date_sk
> . . . . . . . . . . . . >                 AND ss_promo_sk = p_promo_sk
> . . . . . . . . . . . . >                 AND hd1.hd_income_band_sk = 
> ib1.ib_income_band_sk
> . . . . . . . . . . . . >                 AND hd2.hd_income_band_sk = 
> ib2.ib_income_band_sk
> . . . . . . . . . . . . >                 AND cd1.cd_marital_status <> 
> cd2.cd_marital_status
> . . . . . . . . . . . . >                 AND i_color IN ( 'cyan', 'peach', 
> 'blush', 'frosted',
> . . . . . . . . . . . . >                                  'powder', 'orange' 
> )
> . . . . . . . . . . . . >                 AND i_current_price BETWEEN 58 AND 
> 58 + 10
> . . . . . . . . . . . . >                 AND i_current_price BETWEEN 58 + 1 
> AND 58 + 15
> . . . . . . . . . . . . >          GROUP  BY i_product_name,
> . . . . . . . . . . . . >                    i_item_sk,
> . . . . . . . . . . . . >                    s_store_name,
> . . . . . . . . . . . . >                    s_zip,
> . . . . . . . . . . . . >                    ad1.ca_street_number,
> . . . . . . . . . . . . >                    ad1.ca_street_name,
> . . . . . . . . . . . . >                    ad1.ca_city,
> . . . . . . . . . . . . >                    ad1.ca_zip,
> . . . . . . . . . . . . >                    ad2.ca_street_number,
> . . . . . . . . . . . . >                    ad2.ca_street_name,
> . . . . . . . . . . . . >                    ad2.ca_city,
> . . . . . . . . . . . . >                    ad2.ca_zip,
> . . . . . . . . . . . . >                    d1.d_year,
> . . . . . . . . . . . . >                    d2.d_year,
> . . . . . . . . . . . . >                    d3.d_year)
> . . . . . . . . . . . . > SELECT cs1.product_name,
> . . . . . . . . . . . . >        cs1.store_name,
> . . . . . . . . . . . . >        cs1.store_zip,
> . . . . . . . . . . . . >        cs1.b_street_number,
> . . . . . . . . . . . . >        cs1.b_streen_name,
> . . . . . . . . . . . . >        cs1.b_city,
> . . . . . . . . . . . . >        cs1.b_zip,
> . . . . . . . . . . . . >        cs1.c_street_number,
> . . . . . . . . . . . . >        cs1.c_street_name,
> . . . . . . . . . . . . >        cs1.c_city,
> . . . . . . . . . . . . >        cs1.c_zip,
> . . . . . . . . . . . . >        cs1.syear,
> . . . . . . . . . . . . >        cs1.cnt,
> . . . . . . . . . . . . >        cs1.s1,
> . . . . . . . . . . . . >        cs1.s2,
> . . . . . . . . . . . . >        cs1.s3,
> . . . . . . . . . . . . >        cs2.s1,
> . . . . . . . . . . . . >        cs2.s2,
> . . . . . . . . . . . . >        cs2.s3,
> . . . . . . . . . . . . >        cs2.syear,
> . . . . . . . . . . . . >        cs2.cnt
> . . . . . . . . . . . . > FROM   cross_sales cs1,
> . . . . . . . . . . . . >        cross_sales cs2
> . . . . . . . . . . . . > WHERE  cs1.item_sk = cs2.item_sk
> . . . . . . . . . . . . >        AND cs1.syear = 2001
> . . . . . . . . . . . . >        AND cs2.syear = 2001 + 1
> . . . . . . . . . . . . >        AND cs2.cnt <= cs1.cnt
> . . . . . . . . . . . . >        AND cs1.store_name = cs2.store_name
> . . . . . . . . . . . . >        AND cs1.store_zip = cs2.store_zip
> . . . . . . . . . . . . > ORDER  BY cs1.product_name,
> . . . . . . . . . . . . >           cs1.store_name,
> . . . . . . . . . . . . >           cs2.cnt;
> +----------------+-------------+------------+------------------+----------------+------------+--------+------------------+----------------+----------------+--------+--------+------+--------+-------+------+--------+---------+--------+---------+-------+
> |  product_name  | store_name  | store_zip  | b_street_number  | 
> b_streen_name  |   b_city   | b_zip  | c_street_number  | c_street_name  |    
>  c_city     | c_zip  | syear  | cnt  |   s1   |  s2   |  s3  |  s10   |   s20 
>   |  s30   | syear0  | cnt0  |
> +----------------+-------------+------------+------------------+----------------+------------+--------+------------------+----------------+----------------+--------+--------+------+--------+-------+------+--------+---------+--------+---------+-------+
> | antin station  | ation       | 31904      | 483              | Maple        
>   | Woodville  | 14289  | 600              | 13th Highland  | Spring Valley  
> | 36060  | 2001   | 1    | 33.13  | 63.6  | 0.0  | 97.04  | 112.56  | 15.11  
> | 2002    | 1     |
> +----------------+-------------+------------+------------------+----------------+------------+--------+------------------+----------------+----------------+--------+--------+------+--------+-------+------+--------+---------+--------+---------+-------+
> 1 row selected (46.24 seconds)
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Reply via email to