[DRILL-4347] Planning time for query64 from TPCDS test suite has increased 10 times compared to 1.4 release

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.5.0
• Fix Version/s: Future, 1.11.0
• Component/s: Query Planning & Optimization
• Assignee: Aman Sinha
• Reporter: Victoria Markman
• Reviewer: Jinfeng Ni
• Created: 03/Feb/16 18:13
• Updated: 11/Apr/19 20:17
• Resolved: 30/Mar/17 00:34

Description

mapr-drill-1.5.0.201602012001-1.noarch.rpm

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)

Compared to mapr-drill-1.4.0.201601071151-1.noarch.rpm

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)

Issue Links

• duplicates: DRILL-4851 TPCDS Query 64 just hang there at "starting" state
• relates to: DRILL-4678 Tune metadata by generating a dispatcher at runtime
• links to: GitHub Pull Request #671
• links to: GitHub Pull Request #671