QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=113144.77..113144.77 rows=1 width=575) (actual time=23789.637..23789.642 rows=7 loops=1) Sort Key: cs1.product_name, cs1.store_name, cs2.cnt Sort Method: quicksort Memory: 26kB CTE cs_ui -> HashAggregate (cost=112389.78..112392.48 rows=108 width=29) (actual time=7264.658..7336.579 rows=17169 loops=1) 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)))) Rows Removed by Filter: 693 -> Merge Join (cost=4.20..112387.35 rows=108 width=29) (actual time=0.087..6960.681 rows=144067 loops=1) Merge Cond: (catalog_returns.cr_order_number = catalog_sales.cs_order_number) Join Filter: (catalog_sales.cs_item_sk = catalog_returns.cr_item_sk) Rows Removed by Join Filter: 1310234 -> Index Scan using idx_cr_order_number on catalog_returns (cost=0.42..6642.37 rows=144067 width=26) (actual time=0.007..204.316 rows=144067 loops=1) -> Materialize (cost=0.43..78457.59 rows=1441548 width=15) (actual time=0.019..4589.634 rows=1977818 loops=1) -> Index Scan using idx_cs_order_number on catalog_sales (cost=0.43..74853.72 rows=1441548 width=15) (actual time=0.007..1720.572 rows=1441548 loops=1) CTE cross_sales -> HashAggregate (cost=752.19..752.21 rows=1 width=219) (actual time=23788.889..23788.990 rows=76 loops=1) 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 (cost=8.68..752.14 rows=1 width=219) (actual time=8681.978..23786.432 rows=76 loops=1) Join Filter: (store_sales.ss_store_sk = store.s_store_sk) Rows Removed by Join Filter: 836 -> Nested Loop (cost=8.68..750.87 rows=1 width=166) (actual time=8681.888..23776.431 rows=76 loops=1) -> Nested Loop (cost=8.53..750.69 rows=1 width=170) (actual time=8681.835..23773.583 rows=76 loops=1) -> Nested Loop (cost=8.24..750.37 rows=1 width=170) (actual time=8681.756..23768.853 rows=76 loops=1) Join Filter: (cd1.cd_marital_status <> cd2.cd_marital_status) Rows Removed by Join Filter: 21 -> Nested Loop (cost=7.82..749.88 rows=1 width=176) (actual time=8649.404..21604.055 rows=97 loops=1) -> Nested Loop (cost=7.66..749.69 rows=1 width=180) (actual time=8631.702..21582.785 rows=97 loops=1) -> Nested Loop (cost=7.38..749.38 rows=1 width=180) (actual time=8614.148..21560.747 rows=97 loops=1) -> Nested Loop (cost=7.09..749.00 rows=1 width=180) (actual time=8614.107..21515.457 rows=97 loops=1) -> Nested Loop (cost=6.80..748.61 rows=1 width=180) (actual time=8603.209..21277.084 rows=99 loops=1) -> Nested Loop (cost=6.51..748.25 rows=1 width=144) (actual time=8593.350..19998.566 rows=99 loops=1) -> Nested Loop (cost=6.08..746.71 rows=1 width=146) (actual time=8455.554..17878.994 rows=105 loops=1) -> Nested Loop (cost=5.79..746.38 rows=1 width=130) (actual time=8455.473..17130.226 rows=105 loops=1) -> Nested Loop (cost=5.49..746.06 rows=1 width=130) (actual time=8405.083..16989.052 rows=106 loops=1) -> Nested Loop (cost=5.35..745.89 rows=1 width=134) (actual time=8404.926..16983.270 rows=110 loops=1) -> Nested Loop (cost=5.06..745.57 rows=1 width=98) (actual time=8382.329..14967.267 rows=117 loops=1) Join Filter: (item.i_item_sk = store_sales.ss_item_sk) -> Nested Loop (cost=4.63..724.34 rows=16 width=67) (actual time=8346.904..14024.947 rows=117 loops=1) Join Filter: (item.i_item_sk = store_returns.sr_item_sk) -> Nested Loop (cost=0.29..662.07 rows=1 width=59) (actual time=8324.352..13618.106 rows=8 loops=1) -> CTE Scan on cs_ui (cost=0.00..2.16 rows=108 width=4) (actual time=7264.670..7424.096 rows=17169 loops=1) -> Index Scan using item_pkey on item (cost=0.29..6.10 rows=1 width=55) (actual time=0.356..0.356 rows=0 loops=17169) 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[]))) Rows Removed by Filter: 1 -> Bitmap Heap Scan on store_returns (cost=4.34..62.05 rows=18 width=8) (actual time=32.525..50.662 rows=15 loops=8) Recheck Cond: (sr_item_sk = cs_ui.cs_item_sk) Heap Blocks: exact=117 -> Bitmap Index Scan on idx_sr_item_sk (cost=0.00..4.34 rows=18 width=0) (actual time=19.747..19.747 rows=15 loops=8) Index Cond: (sr_item_sk = cs_ui.cs_item_sk) -> Index Scan using store_sales_pkey on store_sales (cost=0.43..1.31 rows=1 width=51) (actual time=7.754..8.028 rows=1 loops=117) 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 (cost=0.29..0.31 rows=1 width=44) (actual time=17.202..17.207 rows=1 loops=117) Index Cond: (ca_address_sk = store_sales.ss_addr_sk) -> Index Only Scan using promotion_pkey on promotion (cost=0.15..0.17 rows=1 width=4) (actual time=0.022..0.028 rows=1 loops=110) Index Cond: (p_promo_sk = store_sales.ss_promo_sk) Heap Fetches: 106 -> Index Scan using date_dim_pkey on date_dim d1 (cost=0.29..0.31 rows=1 width=8) (actual time=1.305..1.311 rows=1 loops=106) Index Cond: (d_date_sk = store_sales.ss_sold_date_sk) -> Index Scan using customer_pkey on customer (cost=0.29..0.31 rows=1 width=24) (actual time=7.105..7.109 rows=1 loops=105) Index Cond: (c_customer_sk = store_sales.ss_customer_sk) -> Index Scan using customer_demographics_pkey on customer_demographics cd2 (cost=0.43..1.53 rows=1 width=6) (actual time=20.157..20.162 rows=1 loops=105) Index Cond: (cd_demo_sk = customer.c_current_cdemo_sk) -> Index Scan using customer_address_pkey on customer_address ad2 (cost=0.29..0.36 rows=1 width=44) (actual time=12.882..12.887 rows=1 loops=99) Index Cond: (ca_address_sk = customer.c_current_addr_sk) -> Index Scan using date_dim_pkey on date_dim d2 (cost=0.29..0.37 rows=1 width=8) (actual time=2.378..2.383 rows=1 loops=99) Index Cond: (d_date_sk = customer.c_first_sales_date_sk) -> Index Scan using date_dim_pkey on date_dim d3 (cost=0.29..0.37 rows=1 width=8) (actual time=0.442..0.447 rows=1 loops=97) Index Cond: (d_date_sk = customer.c_first_shipto_date_sk) -> Index Scan using household_demographics_pkey on household_demographics hd1 (cost=0.28..0.30 rows=1 width=8) (actual time=0.202..0.207 rows=1 loops=97) Index Cond: (hd_demo_sk = store_sales.ss_hdemo_sk) -> Index Only Scan using income_band_pkey on income_band ib1 (cost=0.15..0.18 rows=1 width=4) (actual time=0.194..0.200 rows=1 loops=97) Index Cond: (ib_income_band_sk = hd1.hd_income_band_sk) Heap Fetches: 97 -> Index Scan using customer_demographics_pkey on customer_demographics cd1 (cost=0.43..0.48 rows=1 width=6) (actual time=22.287..22.293 rows=1 loops=97) Index Cond: (cd_demo_sk = store_sales.ss_cdemo_sk) -> Index Scan using household_demographics_pkey on household_demographics hd2 (cost=0.28..0.30 rows=1 width=8) (actual time=0.033..0.038 rows=1 loops=76) Index Cond: (hd_demo_sk = customer.c_current_hdemo_sk) -> Index Only Scan using income_band_pkey on income_band ib2 (cost=0.15..0.18 rows=1 width=4) (actual time=0.011..0.017 rows=1 loops=76) Index Cond: (ib_income_band_sk = hd2.hd_income_band_sk) Heap Fetches: 76 -> Seq Scan on store (cost=0.00..1.12 rows=12 width=61) (actual time=0.009..0.057 rows=12 loops=76) -> Nested Loop (cost=0.00..0.07 rows=1 width=575) (actual time=23789.143..23789.569 rows=7 loops=1) 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)) Rows Removed by Join Filter: 128 -> CTE Scan on cross_sales cs1 (cost=0.00..0.02 rows=1 width=471) (actual time=23788.898..23788.929 rows=15 loops=1) Filter: (syear = 2001) Rows Removed by Filter: 61 -> CTE Scan on cross_sales cs2 (cost=0.00..0.02 rows=1 width=169) (actual time=0.007..0.032 rows=9 loops=15) Filter: (syear = 2002) Rows Removed by Filter: 67 Planning time: 94.034 ms Execution time: 23795.694 ms (94 rows) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=73267.53..73278.15 rows=39 width=943) (actual time=18554.196..18554.656 rows=5 loops=1) -> GroupAggregate (cost=73267.53..73278.15 rows=39 width=943) (actual time=18554.189..18554.611 rows=5 loops=1) 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 (cost=73267.53..73267.62 rows=39 width=943) (actual time=18553.957..18553.999 rows=10 loops=1) 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 Sort Method: quicksort Memory: 30kB -> Append (cost=31372.69..73266.49 rows=39 width=943) (actual time=16762.044..18553.850 rows=10 loops=1) -> HashAggregate (cost=31372.69..31373.60 rows=13 width=165) (actual time=16762.042..16762.051 rows=5 loops=1) 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 (cost=17.68..31369.35 rows=13 width=165) (actual time=87.227..16630.424 rows=6160 loops=1) -> Nested Loop (cost=17.54..31367.07 rows=13 width=30) (actual time=73.828..16509.931 rows=6160 loops=1) -> Nested Loop (cost=17.24..31354.04 rows=39 width=34) (actual time=20.265..6491.836 rows=14224 loops=1) -> Hash Join (cost=16.95..28753.93 rows=7845 width=30) (actual time=20.253..5627.307 rows=71949 loops=1) Hash Cond: (web_sales.ws_ship_mode_sk = ship_mode.sm_ship_mode_sk) -> Seq Scan on web_sales (cost=0.00..25960.84 rows=719384 width=34) (actual time=0.010..3678.740 rows=719384 loops=1) -> Hash (cost=16.88..16.88 rows=6 width=4) (actual time=20.216..20.216 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on ship_mode (cost=0.00..16.88 rows=6 width=4) (actual time=20.202..20.206 rows=2 loops=1) Filter: (sm_carrier = ANY ('{DHL,ZOUROS}'::bpchar[])) Rows Removed by Filter: 18 -> Index Scan using date_dim_pkey on date_dim (cost=0.29..0.32 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=71949) Index Cond: (d_date_sk = web_sales.ws_sold_date_sk) Filter: (d_year = 1999) Rows Removed by Filter: 1 -> Index Scan using time_dim_pkey on time_dim (cost=0.29..0.32 rows=1 width=4) (actual time=0.696..0.697 rows=0 loops=14224) Index Cond: (t_time_sk = web_sales.ws_sold_time_sk) Filter: ((t_time >= 41070) AND (t_time <= 69870)) Rows Removed by Filter: 1 -> Index Scan using warehouse_pkey on warehouse (cost=0.15..0.17 rows=1 width=143) (actual time=0.007..0.009 rows=1 loops=6160) Index Cond: (w_warehouse_sk = web_sales.ws_warehouse_sk) -> HashAggregate (cost=41890.68..41892.50 rows=26 width=163) (actual time=1791.696..1791.746 rows=5 loops=1) 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 (cost=17.82..41883.99 rows=26 width=163) (actual time=205.068..1724.879 rows=12381 loops=1) -> Nested Loop (cost=17.67..41879.43 rows=26 width=28) (actual time=205.045..1652.281 rows=12395 loops=1) -> Hash Join (cost=17.38..41854.06 rows=77 width=32) (actual time=204.605..1458.539 rows=28822 loops=1) Hash Cond: (catalog_sales.cs_ship_mode_sk = ship_mode_1.sm_ship_mode_sk) -> Nested Loop (cost=0.43..41809.60 rows=7132 width=36) (actual time=204.313..1191.777 rows=285693 loops=1) -> Seq Scan on date_dim date_dim_1 (cost=0.00..2318.11 rows=363 width=12) (actual time=187.581..370.425 rows=365 loops=1) Filter: (d_year = 1999) Rows Removed by Filter: 72684 -> Index Scan using idx_cs_sold_date_sk on catalog_sales (cost=0.43..100.95 rows=784 width=32) (actual time=0.051..0.864 rows=783 loops=365) Index Cond: (cs_sold_date_sk = date_dim_1.d_date_sk) -> Hash (cost=16.88..16.88 rows=6 width=4) (actual time=0.073..0.073 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on ship_mode ship_mode_1 (cost=0.00..16.88 rows=6 width=4) (actual time=0.023..0.041 rows=2 loops=1) Filter: (sm_carrier = ANY ('{DHL,ZOUROS}'::bpchar[])) Rows Removed by Filter: 18 -> Index Scan using time_dim_pkey on time_dim time_dim_1 (cost=0.29..0.32 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=28822) Index Cond: (t_time_sk = catalog_sales.cs_sold_time_sk) Filter: ((t_time >= 41070) AND (t_time <= 69870)) Rows Removed by Filter: 1 -> Index Scan using warehouse_pkey on warehouse warehouse_1 (cost=0.15..0.17 rows=1 width=143) (actual time=0.001..0.002 rows=1 loops=12395) Index Cond: (w_warehouse_sk = catalog_sales.cs_warehouse_sk) Planning time: 2.152 ms Execution time: 18556.204 ms (55 rows)