explain select c_last_name ,c_first_name ,c_salutation ,c_preferred_cust_flag ,ss_ticket_number ,cnt from (select ss_ticket_number ,ss_customer_sk ,count(*) as cnt from store_sales,date_dim,store,household_demographics 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 date_dim.d_dom between 1 and 2 and (household_demographics.hd_buy_potential = '>10000' or household_demographics.hd_buy_potential = '0-500') and household_demographics.hd_vehicle_count > 0 and case when household_demographics.hd_vehicle_count > 0 then household_demographics.hd_dep_count/ household_demographics.hd_vehicle_count else null end > 1 and date_dim.d_year in (1999,1999+1,1999+2) and store.s_county in ('Williamson County','Williamson County','Williamson County','Williamson County') group by ss_ticket_number,ss_customer_sk) dj,customer where ss_customer_sk = c_customer_sk and cnt between 1 and 5 order by cnt desc; -- master QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------- Sort (cost=96164.16..96164.57 rows=163 width=77) (actual time=5483.922..5483.923 rows=2 loops=1) Sort Key: (count(*)) DESC Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=94962.23..96158.17 rows=163 width=77) (actual time=5483.682..5483.904 rows=2 loops=1) -> HashAggregate (cost=94961.94..94964.38 rows=163 width=8) (actual time=5483.408..5483.684 rows=76 loops=1) Group Key: store_sales.ss_ticket_number, store_sales.ss_customer_sk Filter: ((count(*) >= 1) AND (count(*) <= 5)) Rows Removed by Filter: 1044 -> Nested Loop (cost=2777.16..94959.90 rows=163 width=8) (actual time=24.572..5471.538 rows=11656 loops=1) -> Hash Join (cost=2776.87..94141.94 rows=2516 width=12) (actual time=22.660..5079.744 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=16) (actual time=22.498..4896.782 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=20) (actual time=0.009..2446.929 rows=2880404 loops=1) -> Hash (cost=2774.67..2774.67 rows=70 width=4) (actual time=21.981..21.981 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=16.511..21.891 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.18..1.18 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.18 rows=12 width=4) (actual time=0.019..0.079 rows=12 loops=1) Filter: ((s_county)::text = ANY ('{"Williamson County","Williamson County","Williamson County","Williamson County"}'::text[])) -> Index Scan using household_demographics_pkey on household_demographics (cost=0.28..0.32 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_vehicle_count > 0) AND ((hd_buy_potential = '>10000'::bpchar) OR (hd_buy_potential = '0-500'::bpchar)) AND (CASE WHEN (hd_vehicle_count > 0) THEN (hd_dep_count / hd_vehicl e_count) ELSE NULL::integer END > 1)) Rows Removed by Filter: 1 -> Index Scan using customer_pkey on customer (cost=0.29..7.30 rows=1 width=69) (actual time=0.001..0.001 rows=0 loops=76) Index Cond: (c_customer_sk = store_sales.ss_customer_sk) Planning time: 2.046 ms Execution time: 5484.150 ms (31 rows) Actual Time: 466.539 ms -- patched QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=95564.11..95564.52 rows=163 width=77) (actual time=4964.788..4964.790 rows=2 loops=1) Sort Key: (count(*)) DESC Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=94362.18..95558.12 rows=163 width=77) (actual time=4964.599..4964.778 rows=2 loops=1) -> HashAggregate (cost=94361.88..94364.33 rows=163 width=8) (actual time=4964.324..4964.567 rows=76 loops=1) Group Key: store_sales.ss_ticket_number, store_sales.ss_customer_sk Filter: ((count(*) >= 1) AND (count(*) <= 5)) Rows Removed by Filter: 1044 -> Hash Join (cost=3015.99..94359.85 rows=163 width=8) (actual time=13.436..4952.839 rows=11656 loops=1) Hash Cond: (store_sales.ss_store_sk = store.s_store_sk) -> Hash Join (cost=3014.66..94356.26 rows=170 width=12) (actual time=13.400..4933.273 rows=11796 loops=1) Hash Cond: (store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk) -> Hash Join (cost=2775.54..94105.57 rows=2633 width=16) (actual time=9.489..4831.807 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=20) (actual time=0.003..2417.805 rows=2880404 loops=1) -> Hash (cost=2774.67..2774.67 rows=70 width=4) (actual time=8.997..8.997 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.271..8.932 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=233.00..233.00 rows=489 width=4) (actual time=2.246..2.246 rows=800 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 37kB -> Seq Scan on household_demographics (cost=0.00..233.00 rows=489 width=4) (actual time=0.132..1.580 rows=800 loops=1) Filter: ((hd_vehicle_count > 0) AND ((hd_buy_potential = '>10000'::bpchar) OR (hd_buy_potential = '0-500'::bpchar)) AND (CASE WHEN (hd_vehicle_count > 0) THEN (hd_dep_count / hd_vehicle_count) ELSE NULL::integer END > 1)) Rows Removed by Filter: 6400 -> Hash (cost=1.18..1.18 rows=12 width=4) (actual time=0.030..0.030 rows=12 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on store (cost=0.00..1.18 rows=12 width=4) (actual time=0.005..0.018 rows=12 loops=1) Filter: ((s_county)::text = ANY ('{"Williamson County","Williamson County","Williamson County","Williamson County"}'::text[])) -> Index Scan using customer_pkey on customer (cost=0.29..7.30 rows=1 width=69) (actual time=0.001..0.001 rows=0 loops=76) Index Cond: (c_customer_sk = store_sales.ss_customer_sk) Planning time: 0.463 ms Execution time: 4964.880 ms (33 rows) Actual Time: 340.506 ms