QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort Sort Key: cs1.product_name, cs1.store_name, cs2.cnt CTE cs_ui -> HashAggregate Group Key: catalog_sales.cs_item_sk Filter: (sum(catalog_sales.cs_ext_list_price) > ('2'::numeric * sum(((catalog_returns.cr_refunded_cash + catalog_returns.cr_reversed_charge) + catalog_returns.cr_store_credit)))) -> Merge Join Merge Cond: (catalog_returns.cr_order_number = catalog_sales.cs_order_number) Join Filter: (catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) -> Index Scan using idx_cr_order_number on catalog_returns -> Materialize -> Index Scan using idx_cs_order_number on catalog_sales CTE cross_sales -> HashAggregate Group Key: item.i_product_name, item.i_item_sk, store.s_store_name, store.s_zip, ad1.ca_street_number, ad1.ca_street_name, ad1.ca_city, ad1.ca_zip, ad2.ca_street_number, ad2.ca_street_name, ad2.ca_city, ad2.ca_zip, d1.d_year, d2.d_year, d3.d_year -> Nested Loop Join Filter: (store_sales.ss_store_sk = store.s_store_sk) -> Nested Loop -> Nested Loop -> Nested Loop Join Filter: (cd1.cd_marital_status <> cd2.cd_marital_status) -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop -> Nested Loop Join Filter: (item.i_item_sk = store_sales.ss_item_sk) -> Nested Loop Join Filter: (item.i_item_sk = store_returns.sr_item_sk) -> Nested Loop -> CTE Scan on cs_ui -> Index Scan using item_pkey on item Index Cond: (i_item_sk = cs_ui.cs_item_sk) Filter: ((i_current_price >= '79'::numeric) AND (i_current_price <= '89'::numeric) AND (i_current_price >= '80'::numeric) AND (i_current_price <= '94'::numeric) AND (i_color = ANY ('{navajo,burlywood,cornflower,olive,turquoise,linen}'::bpchar[]))) -> Bitmap Heap Scan on store_returns Recheck Cond: (sr_item_sk = cs_ui.cs_item_sk) -> Bitmap Index Scan on idx_sr_item_sk Index Cond: (sr_item_sk = cs_ui.cs_item_sk) -> Index Scan using store_sales_pkey on store_sales Index Cond: ((ss_item_sk = store_returns.sr_item_sk) AND (ss_ticket_number = store_returns.sr_ticket_number)) -> Index Scan using customer_address_pkey on customer_address ad1 Index Cond: (ca_address_sk = store_sales.ss_addr_sk) -> Index Only Scan using promotion_pkey on promotion Index Cond: (p_promo_sk = store_sales.ss_promo_sk) -> Index Scan using date_dim_pkey on date_dim d1 Index Cond: (d_date_sk = store_sales.ss_sold_date_sk) -> Index Scan using customer_pkey on customer Index Cond: (c_customer_sk = store_sales.ss_customer_sk) -> Index Scan using customer_demographics_pkey on customer_demographics cd2 Index Cond: (cd_demo_sk = customer.c_current_cdemo_sk) -> Index Scan using customer_address_pkey on customer_address ad2 Index Cond: (ca_address_sk = customer.c_current_addr_sk) -> Index Scan using date_dim_pkey on date_dim d2 Index Cond: (d_date_sk = customer.c_first_sales_date_sk) -> Index Scan using date_dim_pkey on date_dim d3 Index Cond: (d_date_sk = customer.c_first_shipto_date_sk) -> Index Scan using household_demographics_pkey on household_demographics hd1 Index Cond: (hd_demo_sk = store_sales.ss_hdemo_sk) -> Index Only Scan using income_band_pkey on income_band ib1 Index Cond: (ib_income_band_sk = hd1.hd_income_band_sk) -> Index Scan using customer_demographics_pkey on customer_demographics cd1 Index Cond: (cd_demo_sk = store_sales.ss_cdemo_sk) -> Index Scan using household_demographics_pkey on household_demographics hd2 Index Cond: (hd_demo_sk = customer.c_current_hdemo_sk) -> Index Only Scan using income_band_pkey on income_band ib2 Index Cond: (ib_income_band_sk = hd2.hd_income_band_sk) -> Seq Scan on store -> Nested Loop Join Filter: ((cs2.cnt <= cs1.cnt) AND (cs1.item_sk = cs2.item_sk) AND ((cs1.store_name)::text = (cs2.store_name)::text) AND (cs1.store_zip = cs2.store_zip)) -> CTE Scan on cross_sales cs1 Filter: (syear = 2001) -> CTE Scan on cross_sales cs2 Filter: (syear = 2002) (79 rows) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit -> GroupAggregate Group Key: warehouse.w_warehouse_name, warehouse.w_warehouse_sq_ft, warehouse.w_city, warehouse.w_county, warehouse.w_state, warehouse.w_country, ('DHL,ZOUROS'::text), date_dim.d_year -> Sort Sort Key: warehouse.w_warehouse_name, warehouse.w_warehouse_sq_ft, warehouse.w_city, warehouse.w_county, warehouse.w_state, warehouse.w_country, ('DHL,ZOUROS'::text), date_dim.d_year -> Append -> HashAggregate Group Key: warehouse.w_warehouse_name, warehouse.w_warehouse_sq_ft, warehouse.w_city, warehouse.w_county, warehouse.w_state, warehouse.w_country, date_dim.d_year -> Nested Loop -> Nested Loop -> Nested Loop -> Hash Join Hash Cond: (web_sales.ws_ship_mode_sk = ship_mode.sm_ship_mode_sk) -> Seq Scan on web_sales -> Hash -> Seq Scan on ship_mode Filter: (sm_carrier = ANY ('{DHL,ZOUROS}'::bpchar[])) -> Index Scan using date_dim_pkey on date_dim Index Cond: (d_date_sk = web_sales.ws_sold_date_sk) Filter: (d_year = 1999) -> Index Scan using time_dim_pkey on time_dim Index Cond: (t_time_sk = web_sales.ws_sold_time_sk) Filter: ((t_time >= 41070) AND (t_time <= 69870)) -> Index Scan using warehouse_pkey on warehouse Index Cond: (w_warehouse_sk = web_sales.ws_warehouse_sk) -> HashAggregate Group Key: warehouse_1.w_warehouse_name, warehouse_1.w_warehouse_sq_ft, warehouse_1.w_city, warehouse_1.w_county, warehouse_1.w_state, warehouse_1.w_country, date_dim_1.d_year -> Nested Loop -> Nested Loop -> Hash Join Hash Cond: (catalog_sales.cs_ship_mode_sk = ship_mode_1.sm_ship_mode_sk) -> Nested Loop -> Seq Scan on date_dim date_dim_1 Filter: (d_year = 1999) -> Index Scan using idx_cs_sold_date_sk on catalog_sales Index Cond: (cs_sold_date_sk = date_dim_1.d_date_sk) -> Hash -> Seq Scan on ship_mode ship_mode_1 Filter: (sm_carrier = ANY ('{DHL,ZOUROS}'::bpchar[])) -> Index Scan using time_dim_pkey on time_dim time_dim_1 Index Cond: (t_time_sk = catalog_sales.cs_sold_time_sk) Filter: ((t_time >= 41070) AND (t_time <= 69870)) -> Index Scan using warehouse_pkey on warehouse warehouse_1 Index Cond: (w_warehouse_sk = catalog_sales.cs_warehouse_sk) (44 rows)