QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=113144.77..113144.77 rows=1 width=575) (actual time=7018.506..7018.511 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=6865.789..6893.658 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.085..6577.143 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.008..154.543 rows=144067 loops=1) -> Materialize (cost=0.43..78457.59 rows=1441548 width=15) (actual time=0.016..4329.797 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.006..1544.435 rows=1441548 loops=1) CTE cross_sales -> HashAggregate (cost=752.19..752.21 rows=1 width=219) (actual time=7017.790..7017.892 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=6868.359..7017.513 rows=76 loops=1) -> Nested Loop (cost=8.53..751.96 rows=1 width=223) (actual time=6868.351..7017.041 rows=76 loops=1) -> Nested Loop (cost=8.24..751.65 rows=1 width=223) (actual time=6868.338..7016.345 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.24..750.38 rows=1 width=170) (actual time=6868.318..7014.442 rows=76 loops=1) -> Nested Loop (cost=8.09..750.19 rows=1 width=174) (actual time=6868.288..7013.939 rows=76 loops=1) -> Nested Loop (cost=7.81..749.88 rows=1 width=174) (actual time=6868.267..7013.235 rows=76 loops=1) Join Filter: (cd1.cd_marital_status <> cd2.cd_marital_status) Rows Removed by Join Filter: 21 -> Nested Loop (cost=7.38..748.34 rows=1 width=180) (actual time=6868.240..7011.715 rows=98 loops=1) -> Nested Loop (cost=7.23..748.16 rows=1 width=184) (actual time=6868.190..7011.008 rows=99 loops=1) -> Nested Loop (cost=6.94..747.78 rows=1 width=184) (actual time=6868.182..7010.229 rows=99 loops=1) -> Nested Loop (cost=6.51..747.28 rows=1 width=186) (actual time=6868.158..7008.634 rows=99 loops=1) -> Nested Loop (cost=6.22..746.92 rows=1 width=150) (actual time=6868.145..7007.381 rows=99 loops=1) -> Nested Loop (cost=5.93..746.53 rows=1 width=150) (actual time=6867.993..7006.575 rows=106 loops=1) -> Nested Loop (cost=5.64..746.21 rows=1 width=134) (actual time=6867.976..7005.078 rows=107 loops=1) -> Nested Loop (cost=5.35..745.89 rows=1 width=134) (actual time=6867.961..7004.254 rows=110 loops=1) -> Nested Loop (cost=5.06..745.57 rows=1 width=98) (actual time=6867.945..7002.836 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=6867.921..7001.417 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=6867.874..7000.211 rows=8 loops=1) -> CTE Scan on cs_ui (cost=0.00..2.16 rows=108 width=4) (actual time=6865.792..6924.816 rows=17169 loops=1) -> Index Scan using item_pkey on item (cost=0.29..6.10 rows=1 width=55) (actual time=0.003..0.003 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=0.025..0.116 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=0.017..0.017 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=0.007..0.008 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=0.008..0.008 rows=1 loops=117) Index Cond: (ca_address_sk = store_sales.ss_addr_sk) -> Index Scan using date_dim_pkey on date_dim d1 (cost=0.29..0.31 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=110) 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=0.010..0.010 rows=1 loops=107) Index Cond: (c_customer_sk = store_sales.ss_customer_sk) -> Index Scan using date_dim_pkey on date_dim d2 (cost=0.29..0.37 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=106) Index Cond: (d_date_sk = customer.c_first_sales_date_sk) -> Index Scan using customer_address_pkey on customer_address ad2 (cost=0.29..0.36 rows=1 width=44) (actual time=0.008..0.009 rows=1 loops=99) Index Cond: (ca_address_sk = customer.c_current_addr_sk) -> Index Scan using customer_demographics_pkey on customer_demographics cd1 (cost=0.43..0.48 rows=1 width=6) (actual time=0.012..0.012 rows=1 loops=99) Index Cond: (cd_demo_sk = store_sales.ss_cdemo_sk) -> Index Scan using date_dim_pkey on date_dim d3 (cost=0.29..0.37 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=99) Index Cond: (d_date_sk = customer.c_first_shipto_date_sk) -> Index Only Scan using promotion_pkey on promotion (cost=0.15..0.17 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=99) Index Cond: (p_promo_sk = store_sales.ss_promo_sk) Heap Fetches: 98 -> Index Scan using customer_demographics_pkey on customer_demographics cd2 (cost=0.43..1.53 rows=1 width=6) (actual time=0.011..0.012 rows=1 loops=98) Index Cond: (cd_demo_sk = customer.c_current_cdemo_sk) -> Index Scan using household_demographics_pkey on household_demographics hd2 (cost=0.28..0.30 rows=1 width=8) (actual time=0.005..0.006 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.002..0.003 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.001..0.011 rows=12 loops=76) -> Index Scan using household_demographics_pkey on household_demographics hd1 (cost=0.28..0.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=76) 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.001..0.002 rows=1 loops=76) Index Cond: (ib_income_band_sk = hd1.hd_income_band_sk) Heap Fetches: 76 -> Nested Loop (cost=0.00..0.07 rows=1 width=575) (actual time=7018.046..7018.436 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=7017.800..7017.822 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.031 rows=9 loops=15) Filter: (syear = 2002) Rows Removed by Filter: 67 Planning time: 93.424 ms Execution time: 7023.894 ms (94 rows) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=72961.15..72971.77 rows=39 width=943) (actual time=3088.979..3089.100 rows=5 loops=1) -> GroupAggregate (cost=72961.15..72971.77 rows=39 width=943) (actual time=3088.975..3089.089 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=72961.15..72961.24 rows=39 width=943) (actual time=3088.909..3088.919 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=31066.32..72960.12 rows=39 width=943) (actual time=1723.163..3088.863 rows=10 loops=1) -> HashAggregate (cost=31066.32..31067.23 rows=13 width=165) (actual time=1723.160..1723.169 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=2340.04..31062.97 rows=13 width=165) (actual time=10.783..1684.279 rows=6160 loops=1) -> Nested Loop (cost=2339.89..31060.69 rows=13 width=30) (actual time=10.772..1646.487 rows=6160 loops=1) -> Hash Join (cost=2339.60..31047.66 rows=39 width=34) (actual time=10.614..1557.963 rows=14224 loops=1) Hash Cond: (web_sales.ws_ship_mode_sk = ship_mode.sm_ship_mode_sk) -> Hash Join (cost=2322.65..31016.92 rows=3574 width=38) (actual time=10.547..1422.411 rows=142857 loops=1) Hash Cond: (web_sales.ws_sold_date_sk = date_dim.d_date_sk) -> Seq Scan on web_sales (cost=0.00..25960.84 rows=719384 width=34) (actual time=0.006..657.219 rows=719384 loops=1) -> Hash (cost=2318.11..2318.11 rows=363 width=12) (actual time=10.527..10.527 rows=365 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 24kB -> Seq Scan on date_dim (cost=0.00..2318.11 rows=363 width=12) (actual time=4.803..10.200 rows=365 loops=1) Filter: (d_year = 1999) Rows Removed by Filter: 72684 -> Hash (cost=16.88..16.88 rows=6 width=4) (actual time=0.025..0.025 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=0.012..0.017 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 (cost=0.29..0.32 rows=1 width=4) (actual time=0.003..0.004 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.001..0.003 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=1365.663..1365.672 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=3.428..1298.478 rows=12381 loops=1) -> Nested Loop (cost=17.67..41879.43 rows=26 width=28) (actual time=3.412..1225.926 rows=12395 loops=1) -> Hash Join (cost=17.38..41854.06 rows=77 width=32) (actual time=3.320..1069.584 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=3.253..803.104 rows=285693 loops=1) -> Seq Scan on date_dim date_dim_1 (cost=0.00..2318.11 rows=363 width=12) (actual time=3.225..6.889 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.006..0.805 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.018..0.018 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.007..0.011 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.003..0.003 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.178 ms Execution time: 3090.233 ms (57 rows)