explain select c_last_name ,c_first_name ,ca_city ,bought_city ,ss_ticket_number ,extended_price ,extended_tax ,list_price from (select ss_ticket_number ,ss_customer_sk ,ca_city as bought_city ,sum(ss_ext_sales_price) as extended_price ,sum(ss_ext_list_price) as list_price ,sum(ss_ext_tax) as extended_tax from store_sales ,date_dim ,store ,household_demographics ,customer_address where store_sales.ss_sold_date_sk = date_dim.d_date_sk and store_sales.ss_store_sk = store.s_store_sk and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk and store_sales.ss_addr_sk = customer_address.ca_address_sk and date_dim.d_dom between 1 and 2 and (household_demographics.hd_dep_count = 2 or household_demographics.hd_vehicle_count= 4) and date_dim.d_year in (1999,1999+1,1999+2) and store.s_city in ('Midway','Fairview') group by ss_ticket_number ,ss_customer_sk ,ss_addr_sk,ca_city) dn ,customer ,customer_address current_addr where ss_customer_sk = c_customer_sk and customer.c_current_addr_sk = current_addr.ca_address_sk and current_addr.ca_city <> bought_city order by c_last_name ,ss_ticket_number limit 100; -- master QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=98756.70..98756.95 rows=100 width=170) (actual time=5743.848..5744.078 rows=100 loops=1) -> Sort (cost=98756.70..98758.13 rows=572 width=170) (actual time=5743.842..5743.920 rows=100 loops=1) Sort Key: customer.c_last_name, store_sales.ss_ticket_number Sort Method: top-N heapsort Memory: 39kB -> Nested Loop (cost=95136.84..98734.84 rows=572 width=170) (actual time=5706.670..5741.106 rows=2183 loops=1) Join Filter: ((current_addr.ca_city)::text <> (customer_address.ca_city)::text) Rows Removed by Join Filter: 161 -> Nested Loop (cost=95136.55..98523.38 rows=573 width=165) (actual time=5706.658..5726.039 rows=2344 loops=1) -> HashAggregate (cost=95136.26..95146.29 rows=573 width=40) (actual time=5706.646..5710.086 rows=2417 loops=1) Group Key: store_sales.ss_ticket_number, store_sales.ss_customer_sk, store_sales.ss_addr_sk, customer_address.ca_city -> Nested Loop (cost=2777.42..95126.23 rows=573 width=40) (actual time=34.894..5666.247 rows=25847 loops=1) -> Nested Loop (cost=2777.13..94934.71 rows=600 width=31) (actual time=34.882..5511.931 rows=26018 loops=1) -> Hash Join (cost=2776.84..94141.91 rows=2516 width=35) (actual time=33.605..5085.636 rows=106118 loops=1) Hash Cond: (store_sales.ss_store_sk = store.s_store_sk) -> Hash Join (cost=2775.54..94105.57 rows=2633 width=39) (actual time=33.442..4900.948 rows=108678 loops=1) Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) -> Seq Scan on store_sales (cost=0.00..80501.60 rows=2880560 width=43) (actual time=0.010..2439.102 rows=2880404 loops=1) -> Hash (cost=2774.67..2774.67 rows=70 width=4) (actual time=32.782..32.782 rows=72 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 11kB -> Seq Scan on date_dim (cost=0.00..2774.67 rows=70 width=4) (actual time=19.633..32.435 rows=72 loops=1) Filter: ((d_dom >= 1) AND (d_dom <= 2) AND (d_year = ANY ('{1999,2000,2001}'::integer[]))) Rows Removed by Filter: 72977 -> Hash (cost=1.15..1.15 rows=12 width=4) (actual time=0.143..0.143 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on store (cost=0.00..1.15 rows=12 width=4) (actual time=0.018..0.082 rows=12 loops=1) Filter: ((s_city)::text = ANY ('{Midway,Fairview}'::text[])) -> Index Scan using household_demographics_pkey on household_demographics (cost=0.28..0.31 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=106118) Index Cond: (hd_demo_sk = store_sales.ss_hdemo_sk) Filter: ((hd_dep_count = 2) OR (hd_vehicle_count = 4)) Rows Removed by Filter: 1 -> Index Scan using customer_address_pkey on customer_address (cost=0.29..0.31 rows=1 width=13) (actual time=0.002..0.003 rows=1 loops=26018) Index Cond: (ca_address_sk = store_sales.ss_addr_sk) -> Index Scan using customer_pkey on customer (cost=0.29..5.87 rows=1 width=60) (actual time=0.002..0.003 rows=1 loops=2417) Index Cond: (c_customer_sk = store_sales.ss_customer_sk) -> Index Scan using customer_address_pkey on customer_address current_addr (cost=0.29..0.36 rows=1 width=13) (actual time=0.002..0.003 rows=1 loops=2344) Index Cond: (ca_address_sk = customer.c_current_addr_sk) Planning time: 3.235 ms Execution time: 5744.682 ms (38 rows) Time: 518.330 ms -- patched QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=98136.87..98137.12 rows=100 width=170) (actual time=5245.299..5245.519 rows=100 loops=1) -> Sort (cost=98136.87..98138.30 rows=572 width=170) (actual time=5245.295..5245.360 rows=100 loops=1) Sort Key: customer.c_last_name, store_sales.ss_ticket_number Sort Method: top-N heapsort Memory: 39kB -> Nested Loop (cost=94517.01..98115.01 rows=572 width=170) (actual time=5208.434..5242.698 rows=2183 loops=1) Join Filter: ((current_addr.ca_city)::text <> (customer_address.ca_city)::text) Rows Removed by Join Filter: 161 -> Nested Loop (cost=94516.72..97903.55 rows=573 width=165) (actual time=5208.424..5227.489 rows=2344 loops=1) -> HashAggregate (cost=94516.43..94526.46 rows=573 width=40) (actual time=5208.415..5211.902 rows=2417 loops=1) Group Key: store_sales.ss_ticket_number, store_sales.ss_customer_sk, store_sales.ss_addr_sk, customer_address.ca_city -> Nested Loop (cost=2960.63..94506.40 rows=573 width=40) (actual time=14.488..5169.164 rows=25847 loops=1) -> Hash Join (cost=2960.34..94314.88 rows=600 width=31) (actual time=14.475..5014.204 rows=26018 loops=1) Hash Cond: (store_sales.ss_store_sk = store.s_store_sk) -> Hash Join (cost=2959.04..94305.23 rows=628 width=35) (actual time=14.432..4969.238 rows=26352 loops=1) Hash Cond: (store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk) -> Hash Join (cost=2775.54..94105.57 rows=2633 width=39) (actual time=9.857..4852.660 rows=108678 loops=1) Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk) -> Seq Scan on store_sales (cost=0.00..80501.60 rows=2880560 width=43) (actual time=0.003..2423.077 rows=2880404 loops=1) -> Hash (cost=2774.67..2774.67 rows=70 width=4) (actual time=9.374..9.374 rows=72 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 11kB -> Seq Scan on date_dim (cost=0.00..2774.67 rows=70 width=4) (actual time=4.261..9.305 rows=72 loops=1) Filter: ((d_dom >= 1) AND (d_dom <= 2) AND (d_year = ANY ('{1999,2000,2001}'::integer[]))) Rows Removed by Filter: 72977 -> Hash (cost=161.00..161.00 rows=1800 width=4) (actual time=3.629..3.629 rows=1800 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 80kB -> Seq Scan on household_demographics (cost=0.00..161.00 rows=1800 width=4) (actual time=0.025..2.083 rows=1800 loops=1) Filter: ((hd_dep_count = 2) OR (hd_vehicle_count = 4)) Rows Removed by Filter: 5400 -> Hash (cost=1.15..1.15 rows=12 width=4) (actual time=0.033..0.033 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on store (cost=0.00..1.15 rows=12 width=4) (actual time=0.004..0.019 rows=12 loops=1) Filter: ((s_city)::text = ANY ('{Midway,Fairview}'::text[])) -> Index Scan using customer_address_pkey on customer_address (cost=0.29..0.31 rows=1 width=13) (actual time=0.002..0.003 rows=1 loops=26018) Index Cond: (ca_address_sk = store_sales.ss_addr_sk) -> Index Scan using customer_pkey on customer (cost=0.29..5.87 rows=1 width=60) (actual time=0.002..0.003 rows=1 loops=2417) Index Cond: (c_customer_sk = store_sales.ss_customer_sk) -> Index Scan using customer_address_pkey on customer_address current_addr (cost=0.29..0.36 rows=1 width=13) (actual time=0.002..0.003 rows=1 loops=2344) Index Cond: (ca_address_sk = customer.c_current_addr_sk) Planning time: 0.669 ms Execution time: 5246.017 ms (40 rows) Actual Time: 405.310 ms