                                                                                             QUERY PLAN                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=933256.76..933257.01 rows=100 width=138) (actual time=17007.424..17007.456 rows=100 loops=1)
   Buffers: shared hit=2018271
   ->  Sort  (cost=933256.76..933301.58 rows=17926 width=138) (actual time=17007.422..17007.442 rows=100 loops=1)
         Sort Key: customer.c_last_name, customer.c_first_name, current_addr.ca_city, dn.bought_city, dn.ss_ticket_number
         Sort Method: top-N heapsort  Memory: 49kB
         Buffers: shared hit=2018271
         ->  Nested Loop  (cost=895257.33..932571.65 rows=17926 width=138) (actual time=16514.713..16983.600 rows=28915 loops=1)
               Join Filter: ((current_addr.ca_city)::text <> (dn.bought_city)::text)
               Rows Removed by Join Filter: 2029
               Buffers: shared hit=2018265
               ->  Merge Join  (cost=895256.91..923612.53 rows=17955 width=133) (actual time=16514.675..16825.237 rows=30944 loops=1)
                     Merge Cond: (customer.c_customer_sk = dn.ss_customer_sk)
                     Buffers: shared hit=1894489
                     ->  Index Scan using customer_pkey on customer  (cost=0.42..26836.72 rows=500000 width=60) (actual time=0.022..185.885 rows=500000 loops=1)
                           Buffers: shared hit=15906
                     ->  Sort  (cost=895256.48..895301.37 rows=17955 width=81) (actual time=16514.634..16521.530 rows=30945 loops=1)
                           Sort Key: dn.ss_customer_sk
                           Sort Method: quicksort  Memory: 3256kB
                           Buffers: shared hit=1878583
                           ->  Subquery Scan on dn  (cost=893224.69..893987.77 rows=17955 width=81) (actual time=16199.485..16492.748 rows=31957 loops=1)
                                 Buffers: shared hit=1878583
                                 ->  GroupAggregate  (cost=893224.69..893808.22 rows=17955 width=85) (actual time=16199.483..16485.005 rows=31957 loops=1)
                                       Group Key: store_sales.ss_ticket_number, store_sales.ss_customer_sk, store_sales.ss_addr_sk, customer_address.ca_city
                                       Buffers: shared hit=1878583
                                       ->  Sort  (cost=893224.69..893269.57 rows=17955 width=30) (actual time=16199.435..16226.914 rows=340053 loops=1)
                                             Sort Key: store_sales.ss_ticket_number, store_sales.ss_customer_sk, store_sales.ss_addr_sk, customer_address.ca_city
                                             Sort Method: quicksort  Memory: 38852kB
                                             Buffers: shared hit=1878583
                                             ->  Nested Loop  (cost=2787.36..891955.98 rows=17955 width=30) (actual time=25.246..15886.366 rows=340053 loops=1)
                                                   Buffers: shared hit=1878583
                                                   ->  Hash Join  (cost=2786.94..883689.09 rows=18796 width=21) (actual time=25.215..14456.811 rows=342187 loops=1)
                                                         Hash Cond: (store_sales.ss_store_sk = store.s_store_sk)
                                                         Buffers: shared hit=518371
                                                         ->  Hash Join  (cost=2779.37..883606.54 rows=27475 width=25) (actual time=25.064..14139.193 rows=1170079 loops=1)
                                                               Hash Cond: (store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk)
                                                               Buffers: shared hit=518366
                                                               ->  Hash Join  (cost=2595.87..883120.58 rows=115161 width=29) (actual time=22.620..12913.451 rows=4786133 loops=1)
                                                                     Hash Cond: (store_sales.ss_sold_date_sk = date_dim.d_date_sk)
                                                                     Buffers: shared hit=518313
                                                                     ->  Seq Scan on store_sales  (cost=0.00..804917.92 rows=28800992 width=33) (actual time=0.005..3926.245 rows=28800991 loops=1)
                                                                           Buffers: shared hit=516908
                                                                     ->  Hash  (cost=2592.05..2592.05 rows=306 width=4) (actual time=22.595..22.597 rows=314 loops=1)
                                                                           Buckets: 1024  Batches: 1  Memory Usage: 20kB
                                                                           Buffers: shared hit=1405
                                                                           ->  Seq Scan on date_dim  (cost=0.00..2592.05 rows=306 width=4) (actual time=11.458..22.500 rows=314 loops=1)
                                                                                 Filter: ((d_dow = ANY ('{6,0}'::integer[])) AND (d_year = ANY ('{1999,2000,2001}'::integer[])))
                                                                                 Rows Removed by Filter: 72735
                                                                                 Buffers: shared hit=1405
                                                               ->  Hash  (cost=161.00..161.00 rows=1800 width=4) (actual time=2.315..2.316 rows=1800 loops=1)
                                                                     Buckets: 2048  Batches: 1  Memory Usage: 80kB
                                                                     Buffers: shared hit=53
                                                                     ->  Seq Scan on household_demographics  (cost=0.00..161.00 rows=1800 width=4) (actual time=0.098..1.822 rows=1800 loops=1)
                                                                           Filter: ((hd_dep_count = 3) OR (hd_vehicle_count = 1))
                                                                           Rows Removed by Filter: 5400
                                                                           Buffers: shared hit=53
                                                         ->  Hash  (cost=6.66..6.66 rows=73 width=4) (actual time=0.136..0.137 rows=29 loops=1)
                                                               Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                                               Buffers: shared hit=5
                                                               ->  Seq Scan on store  (cost=0.00..6.66 rows=73 width=4) (actual time=0.012..0.124 rows=29 loops=1)
                                                                     Filter: ((s_city)::text = ANY ('{Midway,Fairview,Fairview,Midway,Fairview}'::text[]))
                                                                     Rows Removed by Filter: 73
                                                                     Buffers: shared hit=5
                                                   ->  Index Scan using customer_address_pkey on customer_address  (cost=0.42..0.44 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=342187)
                                                         Index Cond: (ca_address_sk = store_sales.ss_addr_sk)
                                                         Buffers: shared hit=1360212
               ->  Index Scan using customer_address_pkey on customer_address current_addr  (cost=0.42..0.49 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=30944)
                     Index Cond: (ca_address_sk = customer.c_current_addr_sk)
                     Buffers: shared hit=123776
 Planning:
   Buffers: shared hit=731
 Planning Time: 4.093 ms
 Execution Time: 17010.657 ms
(72 rows)

