You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Jinfeng Ni (JIRA)" <ji...@apache.org> on 2016/01/19 17:42:39 UTC
[jira] [Updated] (DRILL-3996) Apply filter pushdown and project
pushdown rules separately to reduce planning time
[ https://issues.apache.org/jira/browse/DRILL-3996?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jinfeng Ni updated DRILL-3996:
------------------------------
Summary: Apply filter pushdown and project pushdown rules separately to reduce planning time (was: Project/filter pushdown logic is causing excessive planning time as number of joins in the query increases)
> Apply filter pushdown and project pushdown rules separately to reduce planning time
> -----------------------------------------------------------------------------------
>
> 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)