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

TPCDS query 83 runs slower when Statistics is disabled

    XMLWordPrintableJSON

    Details

      Description

      Query is TPCDS 83 with sf 100:

      WITH sr_items 
           AS (SELECT i_item_id               item_id, 
                      Sum(sr_return_quantity) sr_item_qty 
               FROM   store_returns, 
                      item, 
                      date_dim 
               WHERE  sr_item_sk = i_item_sk 
                      AND d_date IN (SELECT d_date 
                                     FROM   date_dim 
                                     WHERE  d_week_seq IN (SELECT d_week_seq 
                                                           FROM   date_dim 
                                                           WHERE 
                                            d_date IN ( '1999-06-30', 
                                                        '1999-08-28', 
                                                        '1999-11-18' 
                                                      ))) 
                      AND sr_returned_date_sk = d_date_sk 
               GROUP  BY i_item_id), 
           cr_items 
           AS (SELECT i_item_id               item_id, 
                      Sum(cr_return_quantity) cr_item_qty 
               FROM   catalog_returns, 
                      item, 
                      date_dim 
               WHERE  cr_item_sk = i_item_sk 
                      AND d_date IN (SELECT d_date 
                                     FROM   date_dim 
                                     WHERE  d_week_seq IN (SELECT d_week_seq 
                                                           FROM   date_dim 
                                                           WHERE 
                                            d_date IN ( '1999-06-30', 
                                                        '1999-08-28', 
                                                        '1999-11-18' 
                                                      ))) 
                      AND cr_returned_date_sk = d_date_sk 
               GROUP  BY i_item_id), 
           wr_items 
           AS (SELECT i_item_id               item_id, 
                      Sum(wr_return_quantity) wr_item_qty 
               FROM   web_returns, 
                      item, 
                      date_dim 
               WHERE  wr_item_sk = i_item_sk 
                      AND d_date IN (SELECT d_date 
                                     FROM   date_dim 
                                     WHERE  d_week_seq IN (SELECT d_week_seq 
                                                           FROM   date_dim 
                                                           WHERE 
                                            d_date IN ( '1999-06-30', 
                                                        '1999-08-28', 
                                                        '1999-11-18' 
                                                      ))) 
                      AND wr_returned_date_sk = d_date_sk 
               GROUP  BY i_item_id) 
      SELECT sr_items.item_id, 
                     sr_item_qty, 
                     sr_item_qty / ( sr_item_qty + cr_item_qty + wr_item_qty ) / 3.0 * 
                     100 sr_dev, 
                     cr_item_qty, 
                     cr_item_qty / ( sr_item_qty + cr_item_qty + wr_item_qty ) / 3.0 * 
                     100 cr_dev, 
                     wr_item_qty, 
                     wr_item_qty / ( sr_item_qty + cr_item_qty + wr_item_qty ) / 3.0 * 
                     100 wr_dev, 
                     ( sr_item_qty + cr_item_qty + wr_item_qty ) / 3.0 
                     average 
      FROM   sr_items, 
             cr_items, 
             wr_items 
      WHERE  sr_items.item_id = cr_items.item_id 
             AND sr_items.item_id = wr_items.item_id 
      ORDER  BY sr_items.item_id, 
                sr_item_qty
      LIMIT 100; 
      

      The number of threads for major fragments 1 and 2 has changed when Statistics is disabled. The number of minor fragments has been reduced from 10 and 15 fragments down to 3 fragments. Rowcount has changed for major fragment 2 from 1439754.0 down to 287950.8.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                gparai Gautam Parai
                Reporter:
                rhou Robert Hou
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: