You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Victoria Markman (JIRA)" <ji...@apache.org> on 2015/10/29 18:15:27 UTC
[jira] [Created] (DRILL-3996) Project/filter pushdown logic is
causing excessive planning time as number of joins in the query increases
Victoria Markman created DRILL-3996:
---------------------------------------
Summary: Project/filter pushdown logic is causing excessive planning time as number of joins in the query increases
Key: DRILL-3996
URL: https://issues.apache.org/jira/browse/DRILL-3996
Project: Apache Drill
Issue Type: Bug
Components: Query Planning & Optimization
Affects Versions: 1.2.0
Reporter: Victoria Markman
{code}
CREATE OR REPLACE VIEW v1 as
SELECT
wh.w_warehouse_name as warehouse_name,
wh.w_warehouse_sq_ft as warehouse_sq_ft,
i.i_item_desc as item_desc,
i.i_current_price as current_price,
p.p_promo_name as promo_name,
p.p_channel_catalog as channel_catalog,
sm.sm_carrier as carrier,
sm.sm_contract as contract,
c.c_first_name as first_name,
c.c_last_name as last_name,
c.c_email_address as email_address,
ca.ca_city as city,
ca.ca_county as county,
ca.ca_state as state,
ca.ca_zip as zip,
td.t_shift as shift,
ws.ws_sold_date_sk as sold_date,
ws.ws_sold_time_sk as sold_time,
ws.ws_ship_date_sk as ship_date,
ws.ws_bill_cdemo_sk as bill_cdemo,
ws.ws_bill_hdemo_sk as bill_hdemo,
ws.ws_bill_addr_sk as bill_addr,
ws.ws_ship_customer_sk as ship_customer,
ws.ws_ship_cdemo_sk as ship_cdemo,
ws.ws_ship_hdemo_sk as ship_hdemo,
ws.ws_ship_addr_sk as ship_addr,
ws.ws_web_page_sk as web_page,
ws.ws_order_number as order_number,
ws.ws_quantity as quanity,
ws.ws_wholesale_cost as wholesale_cost,
ws.ws_list_price as list_price,
ws.ws_sales_price as sales_price,
ws.ws_ext_discount_amt as ext_discount_amt,
ws.ws_ext_sales_price as ext_sales_price,
ws.ws_ext_wholesale_cost as ext_wholesale_cost,
ws.ws_ext_list_price as ext_list_price,
ws.ws_ext_tax as ext_tax ,
ws.ws_coupon_amt as coupon_amt,
ws.ws_ext_ship_cost as ext_ship_cost,
ws.ws_net_paid as net_paid,
ws.ws_net_paid_inc_tax as net_paid_inc_tax,
ws.ws_net_paid_inc_ship as net_paid_inc_ship,
ws.ws_net_paid_inc_ship_tax as net_paid_inc_ship_tax ,
ws.ws_net_profit as net_profit
FROM
web_sales ws
JOIN warehouse wh
ON ws.ws_warehouse_sk = wh.w_warehouse_sk
JOIN item i
ON ws.ws_item_sk = i.i_item_sk
JOIN promotion p
ON ws.ws_promo_sk = p.p_promo_sk
JOIN ship_mode sm
ON ws.ws_ship_mode_sk = sm.sm_ship_mode_sk
JOIN customer c
ON ws.ws_bill_customer_sk = c.c_customer_sk
JOIN customer_address ca
ON ws.ws_bill_addr_sk = ca.ca_address_sk
LEFT JOIN time_dim td
ON ws.ws_sold_time_sk = td.t_time_sk
WHERE
ws.ws_warehouse_sk = 5
;
{code}
Query below takes between 25 and 40 seconds to plan:
{code}
explain plan for select state from v1 where NOT (v1.contract IS NULL) AND (v1.sold_date NOT IN(9999)) AND (3=3) limit 0;
{code}
In our internal experiments we found out that if we disable projection pushdown for this particular query, planning time reduces to below 1 second.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)