Return-Path: X-Original-To: apmail-drill-issues-archive@minotaur.apache.org Delivered-To: apmail-drill-issues-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id AFC9A1848C for ; Wed, 3 Feb 2016 18:13:46 +0000 (UTC) Received: (qmail 15489 invoked by uid 500); 3 Feb 2016 18:13:40 -0000 Delivered-To: apmail-drill-issues-archive@drill.apache.org Received: (qmail 15412 invoked by uid 500); 3 Feb 2016 18:13:40 -0000 Mailing-List: contact issues-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@drill.apache.org Delivered-To: mailing list issues@drill.apache.org Received: (qmail 15398 invoked by uid 99); 3 Feb 2016 18:13:40 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 03 Feb 2016 18:13:40 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id DF83A2C1F57 for ; Wed, 3 Feb 2016 18:13:39 +0000 (UTC) Date: Wed, 3 Feb 2016 18:13:39 +0000 (UTC) From: "Victoria Markman (JIRA)" To: issues@drill.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Created] (DRILL-4347) Planning time for query64 from TPCDS test suite has increased 10 times compared to 1.4 release MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 Victoria Markman created DRILL-4347: --------------------------------------- Summary: 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: 0.5.0 Reporter: Victoria Markman 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)