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

TPCDS query 10, 35, 69 take longer with sf 1000 when Statistics are disabled

    XMLWordPrintableJSON

    Details

      Description

      Query 69 runs 150% slower when Statistics is disabled. Here is the query:

      SELECT
        cd_gender,
        cd_marital_status,
        cd_education_status,
        count(*) cnt1,
        cd_purchase_estimate,
        count(*) cnt2,
        cd_credit_rating,
        count(*) cnt3
      FROM
        customer c, customer_address ca, customer_demographics
      WHERE
        c.c_current_addr_sk = ca.ca_address_sk AND
          ca_state IN ('KY', 'GA', 'NM') AND
          cd_demo_sk = c.c_current_cdemo_sk AND
          exists(SELECT *
                 FROM store_sales, date_dim
                 WHERE c.c_customer_sk = ss_customer_sk AND
                   ss_sold_date_sk = d_date_sk AND
                   d_year = 2001 AND
                   d_moy BETWEEN 4 AND 4 + 2) AND
          (NOT exists(SELECT *
                      FROM web_sales, date_dim
                      WHERE c.c_customer_sk = ws_bill_customer_sk AND
                        ws_sold_date_sk = d_date_sk AND
                        d_year = 2001 AND
                        d_moy BETWEEN 4 AND 4 + 2) AND
            NOT exists(SELECT *
                       FROM catalog_sales, date_dim
                       WHERE c.c_customer_sk = cs_ship_customer_sk AND
                         cs_sold_date_sk = d_date_sk AND
                         d_year = 2001 AND
                         d_moy BETWEEN 4 AND 4 + 2))
      GROUP BY cd_gender, cd_marital_status, cd_education_status,
        cd_purchase_estimate, cd_credit_rating
      ORDER BY cd_gender, cd_marital_status, cd_education_status,
        cd_purchase_estimate, cd_credit_rating
      LIMIT 100;
      

      This regression is caused by commit 982e98061e029a39f1c593f695c0d93ec7079f0d. This commit should be reverted for now.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                hanu.ncr Hanumath Rao Maduri
                Reporter:
                rhou Robert Hou
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: