Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-4347

Planning time for query64 from TPCDS test suite has increased 10 times compared to 1.4 release

    XMLWordPrintableJSON

Details

    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)

      Attachments

        1. 294e9fb9-cdda-a89f-d1a7-b852878926a1.sys.drill_1.4.0
          660 kB
          Victoria Markman
        2. 294ea418-9fb8-3082-1725-74e3cfe38fe9.sys.drill_1.5.0
          822 kB
          Victoria Markman
        3. drill4347_jstack.txt
          63 kB
          Aman Sinha

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. DRILL-4851 TPCDS Query 64 just hang there at "starting" state

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. DRILL-4678 Tune metadata by generating a dispatcher at runtime

          • Critical - Crashes, loss of data, severe memory leak.
          • Resolved
          links to

          Web Link GitHub Pull Request #671

          Web Link GitHub Pull Request #671

          Activity

            People

              amansinha100 Aman Sinha
              vicky Victoria Markman
              Jinfeng Ni Jinfeng Ni
              Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: