drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jinfeng Ni (JIRA)" <j...@apache.org>
Subject [jira] [Assigned] (DRILL-3996) Apply filter pushdown and project pushdown rules separately to reduce planning time
Date Tue, 19 Jan 2016 16:47:39 GMT

     [ https://issues.apache.org/jira/browse/DRILL-3996?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Jinfeng Ni reassigned DRILL-3996:
---------------------------------

    Assignee: Jinfeng Ni

> 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
>            Assignee: Jinfeng Ni
>
> {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)

Mime
View raw message