You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Robert Hou (JIRA)" <ji...@apache.org> on 2019/03/19 23:21:00 UTC
[jira] [Created] (DRILL-7123) TPCDS query 83 runs slower when
Statistics is disabled
Robert Hou created DRILL-7123:
---------------------------------
Summary: 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
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)