You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "Gautam Parai (JIRA)" <ji...@apache.org> on 2019/03/28 23:29:00 UTC
[jira] [Resolved] (DRILL-7123) TPCDS query 83 runs slower when
Statistics is disabled
[ https://issues.apache.org/jira/browse/DRILL-7123?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Gautam Parai resolved DRILL-7123.
---------------------------------
Resolution: Fixed
> TPCDS query 83 runs slower when Statistics is disabled
> ------------------------------------------------------
>
> Key: DRILL-7123
> URL: https://issues.apache.org/jira/browse/DRILL-7123
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 1.16.0
> Reporter: Robert Hou
> Assignee: Gautam Parai
> Priority: Blocker
> Fix For: 1.16.0
>
>
> Query is TPCDS 83 with sf 100:
> {noformat}
> 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;
> {noformat}
> 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.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)