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

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: