[DRILL-3996] Apply filter pushdown and project pushdown rules separately to reduce planning time

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.2.0
• Component/s: Query Planning & Optimization
• Assignee: Jinfeng Ni
• Reporter: Victoria Markman
• Created: 29/Oct/15 17:15
• Updated: 19/Jan/16 16:47

Description

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
;

Query below takes between 25 and 40 seconds to plan:

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;

In our internal experiments we found out that if we disable projection pushdown for this particular query, planning time reduces to below 1 second.