                                                                                    QUERY PLAN                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=27350973.98..27350973.98 rows=1 width=72) (actual time=1079387.988..1079388.000 rows=1 loops=1)
   Buffers: shared hit=837265, temp read=6458659 written=225485
   CTE ws_wh
     ->  Hash Join  (cost=377817.50..7260666.23 rows=426765799 width=12) (actual time=5029.576..35159.721 rows=74793180 loops=1)
           Hash Cond: (ws1_1.ws_order_number = ws2.ws_order_number)
           Join Filter: (ws1_1.ws_warehouse_sk <> ws2.ws_warehouse_sk)
           Rows Removed by Join Filter: 15550502
           Buffers: shared hit=375496, temp read=24624 written=24624
           ->  Seq Scan on web_sales ws1_1  (cost=0.00..259726.89 rows=7197889 width=8) (actual time=0.009..3573.677 rows=7197566 loops=1)
                 Buffers: shared hit=187748
           ->  Hash  (cost=259726.89..259726.89 rows=7197889 width=8) (actual time=5003.254..5003.256 rows=7197566 loops=1)
                 Buckets: 8388608  Batches: 2  Memory Usage: 205975kB
                 Buffers: shared hit=187748, temp written=12311
                 ->  Seq Scan on web_sales ws2  (cost=0.00..259726.89 rows=7197889 width=8) (actual time=0.021..2729.523 rows=7197566 loops=1)
                       Buffers: shared hit=187748
   ->  Sort  (cost=20090307.74..20090307.75 rows=1 width=72) (actual time=1079387.986..1079387.993 rows=1 loops=1)
         Sort Key: (count(DISTINCT ws1.ws_order_number))
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=837265, temp read=6458659 written=225485
         ->  Aggregate  (cost=20090307.72..20090307.73 rows=1 width=72) (actual time=1079387.955..1079387.962 rows=1 loops=1)
               Buffers: shared hit=837262, temp read=6458659 written=225485
               ->  Hash Join  (cost=9607633.92..20090307.70 rows=2 width=16) (actual time=113311.881..1079387.399 rows=61 loops=1)
                     Hash Cond: (ws1.ws_order_number = ws_wh.ws_order_number)
                     Buffers: shared hit=837259, temp read=6458659 written=225485
                     ->  Nested Loop Semi Join  (cost=5398.95..10488072.69 rows=5 width=24) (actual time=6043.481..972118.787 rows=61 loops=1)
                           Buffers: shared hit=653322, temp read=6434035 written=12560
                           ->  Nested Loop  (cost=4.35..315955.32 rows=5 width=16) (actual time=113.572..4949.246 rows=79 loops=1)
                                 Buffers: shared hit=271609
                                 ->  Nested Loop  (cost=3.93..315406.38 rows=1238 width=20) (actual time=0.521..4819.886 rows=19506 loops=1)
                                       Buffers: shared hit=193589
                                       ->  Hash Join  (cost=3.62..280530.99 rows=1370615 width=24) (actual time=0.084..4092.070 rows=1026143 loops=1)
                                             Hash Cond: (ws1.ws_web_site_sk = web_site.web_site_sk)
                                             Buffers: shared hit=187751
                                             ->  Seq Scan on web_sales ws1  (cost=0.00..259726.89 rows=7197889 width=28) (actual time=0.018..1048.788 rows=7197566 loops=1)
                                                   Buffers: shared hit=187748
                                             ->  Hash  (cost=3.52..3.52 rows=8 width=4) (actual time=0.052..0.053 rows=8 loops=1)
                                                   Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                   Buffers: shared hit=3
                                                   ->  Seq Scan on web_site  (cost=0.00..3.52 rows=8 width=4) (actual time=0.021..0.045 rows=8 loops=1)
                                                         Filter: (web_company_name = 'pri'::bpchar)
                                                         Rows Removed by Filter: 34
                                                         Buffers: shared hit=3
                                       ->  Result Cache  (cost=0.30..0.33 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1026143)
                                             Cache Key: ws1.ws_ship_date_sk
                                             Hits: 1024196  Misses: 1947  Evictions: 0  Overflows: 0  Memory Usage: 132kB
                                             Buffers: shared hit=5838
                                             ->  Index Scan using date_dim_pkey on date_dim  (cost=0.29..0.32 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1947)
                                                   Index Cond: (d_date_sk = ws1.ws_ship_date_sk)
                                                   Filter: ((d_date >= '2002-05-01'::date) AND (d_date <= '2002-06-30 00:00:00'::timestamp without time zone))
                                                   Rows Removed by Filter: 1
                                                   Buffers: shared hit=5838
                                 ->  Index Scan using customer_address_pkey on customer_address  (cost=0.42..0.44 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=19506)
                                       Index Cond: (ca_address_sk = ws1.ws_ship_addr_sk)
                                       Filter: (ca_state = 'MA'::bpchar)
                                       Rows Removed by Filter: 1
                                       Buffers: shared hit=78020
                           ->  Hash Join  (cost=5394.60..10141113.46 rows=2491 width=8) (actual time=12242.645..12242.645 rows=1 loops=79)
                                 Hash Cond: (ws_wh_1.ws_order_number = web_returns.wr_order_number)
                                 Join Filter: (ws1.ws_order_number = ws_wh_1.ws_order_number)
                                 Buffers: shared hit=381713, temp read=6434035 written=12560
                                 ->  CTE Scan on ws_wh ws_wh_1  (cost=0.00..8535315.98 rows=426765799 width=4) (actual time=82.465..7357.138 rows=39287770 loops=61)
                                       Buffers: shared hit=191559, temp read=6434035 written=12560
                                 ->  Hash  (cost=5394.57..5394.57 rows=2 width=4) (actual time=31.098..31.098 rows=1 loops=79)
                                       Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                       Buffers: shared hit=190154
                                       ->  Index Only Scan using web_returns_pkey on web_returns  (cost=0.42..5394.57 rows=2 width=4) (actual time=18.061..31.092 rows=1 loops=79)
                                             Index Cond: (wr_order_number = ws1.ws_order_number)
                                             Heap Fetches: 0
                                             Buffers: shared hit=190154
                     ->  Hash  (cost=9602232.48..9602232.48 rows=200 width=4) (actual time=107268.374..107268.375 rows=600000 loops=1)
                           Buckets: 1048576 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 29286kB
                           Buffers: shared hit=183937, temp read=24624 written=212925
                           ->  HashAggregate  (cost=9602230.48..9602232.48 rows=200 width=4) (actual time=106905.022..107093.781 rows=600000 loops=1)
                                 Group Key: ws_wh.ws_order_number
                                 Batches: 1  Memory Usage: 57377kB
                                 Buffers: shared hit=183937, temp read=24624 written=212925
                                 ->  CTE Scan on ws_wh  (cost=0.00..8535315.98 rows=426765799 width=4) (actual time=0.001..74793.156 rows=74793180 loops=1)
                                       Buffers: shared hit=183937, temp read=24624 written=212925
 Planning:
   Buffers: shared hit=279
 Planning Time: 3.189 ms
 Execution Time: 1079580.908 ms
(82 rows)

