On 06/02/15 01:47, Josh Berkus wrote:
On 06/01/2015 03:22 PM, Tomas Vondra wrote:On 06/01/15 23:47, Josh Berkus wrote:On 06/01/2015 02:18 PM, Tom Lane wrote:Anybody else want to speak for or against back-patching the patch as posted? I intentionally didn't push it in before today's releases, but I will push it later this week if there are not objections.I would like Mark Wong to test this on DBT3, if that's possible for him. I'm very worried about an unanticipated regression.AFAIK Mark is busy with other stuff at the moment, but I can do the TPC-H (which DBT3 is equal to, IIRC).Yeah, I just want something which has a chance of catching unanticipated regression in queries which should be unaffected by the patch.
OK, so I did the testing today - with TPC-H and TPC-DS benchmarks. The results are good, IMHO.
With TPC-H, I've used 1GB and 4GB datasets, and I've seen no plan changes at all. I don't plan to run the tests on larger data sets, I do expect the behavior to remain the same, considering the uniformity of TPC-H data sets.
With TPC-DS (using the 63 queries supported by PostgreSQL), I've seen two cases of plan changes - see the plans attached. In both cases however the plan change results in much better performance. While on master the queries took 23 and 18 seconds, with the two patches it's only 7 and 3. This is just the 1GB dataset. I'll repeat the test with the 4GB dataset and post an update if there are any changes.
While this can't prove there are no regressions, in these two benchmarks the patches actually improve some of the queries.
regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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
-> Nested Loop
-> 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
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 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 date_dim_pkey on date_dim d2
Index Cond: (d_date_sk = customer.c_first_sales_date_sk)
->
Index Scan using customer_address_pkey on customer_address ad2
Index Cond: (ca_address_sk = customer.c_current_addr_sk)
-> Index Scan
using customer_demographics_pkey on customer_demographics cd1
Index
Cond: (cd_demo_sk = store_sales.ss_cdemo_sk)
-> Index Scan using
date_dim_pkey on date_dim d3
Index Cond:
(d_date_sk = customer.c_first_shipto_date_sk)
-> Index Only Scan using
promotion_pkey on promotion
Index Cond:
(p_promo_sk = store_sales.ss_promo_sk)
-> Index Scan using
customer_demographics_pkey on customer_demographics cd2
Index Cond: (cd_demo_sk =
customer.c_current_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
-> 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)
-> 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
-> Hash Join
Hash Cond:
(web_sales.ws_ship_mode_sk = ship_mode.sm_ship_mode_sk)
-> Hash Join
Hash Cond:
(web_sales.ws_sold_date_sk = date_dim.d_date_sk)
-> Seq Scan on web_sales
-> Hash
-> Seq Scan on
date_dim
Filter: (d_year
= 1999)
-> Hash
-> Seq Scan on ship_mode
Filter: (sm_carrier =
ANY ('{DHL,ZOUROS}'::bpchar[]))
-> 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)
(45 rows)
tpcds-differences.sql
Description: application/sql
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)
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)
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)
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
