                                                                                                       QUERY PLAN                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=937164.61..937164.86 rows=100 width=138) (actual time=17842.641..17842.670 rows=100 loops=1)
   Buffers: shared hit=2039818
   ->  Sort  (cost=937164.61..937209.43 rows=17926 width=138) (actual time=17842.638..17842.656 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=2039818
         ->  Nested Loop  (cost=899165.17..936479.49 rows=17926 width=138) (actual time=17349.955..17818.966 rows=28915 loops=1)
               Join Filter: ((current_addr.ca_city)::text <> (dn.bought_city)::text)
               Rows Removed by Join Filter: 2029
               Buffers: shared hit=2039812
               ->  Merge Join  (cost=899164.75..927520.38 rows=17955 width=133) (actual time=17349.918..17657.453 rows=30944 loops=1)
                     Merge Cond: (customer.c_customer_sk = dn.ss_customer_sk)
                     Buffers: shared hit=1916036
                     ->  Index Scan using customer_pkey on customer  (cost=0.42..26836.72 rows=500000 width=60) (actual time=0.026..184.173 rows=500000 loops=1)
                           Buffers: shared hit=15906
                     ->  Sort  (cost=899164.33..899209.22 rows=17955 width=81) (actual time=17349.873..17357.068 rows=30945 loops=1)
                           Sort Key: dn.ss_customer_sk
                           Sort Method: quicksort  Memory: 3256kB
                           Buffers: shared hit=1900130
                           ->  Subquery Scan on dn  (cost=897132.53..897895.62 rows=17955 width=81) (actual time=17044.185..17328.024 rows=31957 loops=1)
                                 Buffers: shared hit=1900130
                                 ->  GroupAggregate  (cost=897132.53..897716.07 rows=17955 width=85) (actual time=17044.182..17320.634 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=1900130
                                       ->  Sort  (cost=897132.53..897177.42 rows=17955 width=30) (actual time=17044.113..17070.683 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=1900130
                                             ->  Nested Loop  (cost=2604.15..895863.83 rows=17955 width=30) (actual time=24.255..16727.570 rows=340053 loops=1)
                                                   Buffers: shared hit=1900130
                                                   ->  Nested Loop  (cost=2603.73..887596.94 rows=18796 width=21) (actual time=24.229..15282.517 rows=342187 loops=1)
                                                         Buffers: shared hit=539918
                                                         ->  Hash Join  (cost=2603.44..883442.45 rows=78784 width=25) (actual time=24.075..14222.044 rows=1374104 loops=1)
                                                               Hash Cond: (store_sales.ss_store_sk = store.s_store_sk)
                                                               Buffers: shared hit=518318
                                                               ->  Hash Join  (cost=2595.87..883120.58 rows=115161 width=29) (actual time=23.890..12977.765 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..3954.115 rows=28800991 loops=1)
                                                                           Buffers: shared hit=516908
                                                                     ->  Hash  (cost=2592.05..2592.05 rows=306 width=4) (actual time=23.853..23.854 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=12.143..23.758 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=6.66..6.66 rows=73 width=4) (actual time=0.137..0.138 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.011..0.125 rows=29 loops=1)
                                                                           Filter: ((s_city)::text = ANY ('{Midway,Fairview,Fairview,Midway,Fairview}'::text[]))
                                                                           Rows Removed by Filter: 73
                                                                           Buffers: shared hit=5
                                                         ->  Result Cache  (cost=0.29..0.32 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1374104)
                                                               Cache Key: store_sales.ss_hdemo_sk
                                                               Hits: 1366903  Misses: 7201  Evictions: 0  Overflows: 0  Memory Usage: 542kB
                                                               Buffers: shared hit=21600
                                                               ->  Index Scan using household_demographics_pkey on household_demographics  (cost=0.28..0.31 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=7201)
                                                                     Index Cond: (hd_demo_sk = store_sales.ss_hdemo_sk)
                                                                     Filter: ((hd_dep_count = 3) OR (hd_vehicle_count = 1))
                                                                     Rows Removed by Filter: 1
                                                                     Buffers: shared hit=21600
                                                   ->  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.456 ms
 Execution Time: 17853.034 ms
(73 rows)

