gistfile1.txt

=> "SELECT deliveries.id,\n            deliveries.title,\n            deliveries.description,\n            company_sends_log.sent_at,\n            companies.id as company_id,\n            regions.name as region_name,\n            users.email,\n            row_number() OVER () AS row_num FROM \"mail_deliveries\".\"company_sends_log\" INNER JOIN \"companies\" ON \"companies\".\"id\" = \"mail_deliveries\".\"company_sends_log\".\"company_id\" INNER JOIN \"company_users\" ON \"company_users\".\"company_id\" = \"companies\".\"id\" INNER JOIN \"users\" ON \"users\".\"id\" = \"company_users\".\"user_id\" INNER JOIN \"regions\" ON \"regions\".\"id\" = \"companies\".\"main_region_id\" INNER JOIN mail_deliveries.deliveries\n                ON deliveries.id = company_sends_log.delivery_id LEFT JOIN mail_deliveries.unsubscribed_users ON unsubscribed_users.user_id = users.id WHERE (users.email_is_unreachable != true AND unsubscribed_users.id is NULL) AND (company_sends_log.sent_at >= '2015-07-01') AND (company_sends_log.sent_at <= '2015-07-31')"



=> [["WindowAgg  (cost=174782.88..272974.53 rows=1 width=159) (actual time=2105.698..4876.519 rows=173562 loops=1)"],
 ["  ->  Nested Loop  (cost=174782.88..272974.52 rows=1 width=159) (actual time=2105.691..4727.767 rows=173562 loops=1)"],
 ["        ->  Nested Loop  (cost=174782.59..272974.19 rows=1 width=52) (actual time=2105.679..4408.647 rows=173562 loops=1)"],
 ["              ->  Hash Left Join  (cost=174782.32..272973.89 rows=1 width=39) (actual time=2105.668..4108.685 rows=173562 loops=1)"],
 ["                    Hash Cond: (users.id = unsubscribed_users.user_id)"],
 ["                    Filter: (unsubscribed_users.id IS NULL)"],
 ["                    Rows Removed by Filter: 17858"],
 ["                    ->  Nested Loop  (cost=172825.29..269037.69 rows=92036 width=43) (actual time=2086.425..3966.360 rows=178781 loops=1)"],
 ["                          ->  Hash Join  (cost=172824.86..190705.82 rows=93724 width=24) (actual time=2086.402..3091.585 rows=182670 loops=1)"],
 ["                                Hash Cond: (company_users.company_id = companies.id)"],
 ["                                ->  Seq Scan on company_users  (cost=0.00..14171.52 rows=739252 width=8) (actual time=0.016..137.965 rows=728950 loops=1)"],
 ["                                ->  Hash  (cost=170998.49..170998.49 rows=146110 width=24) (actual time=2086.299..2086.299 rows=145385 loops=1)"],
 ["                                      Buckets: 16384  Batches: 1  Memory Usage: 7951kB"],
 ["                                      ->  Hash Join  (cost=78991.21..170998.49 rows=146110 width=24) (actual time=1706.734..2052.236 rows=145385 loops=1)"],
 ["                                            Hash Cond: (company_sends_log.company_id = companies.id)"],
 ["                                            ->  Seq Scan on company_sends_log  (cost=0.00..88537.16 rows=146110 width=16) (actual time=766.855..863.772 rows=145385 loops=1)"],
 ["                                                  Filter: ((sent_at >= '2015-07-01 00:00:00'::timestamp without time zone) AND (sent_at <= '2015-07-31 00:00:00'::timestamp without time zone))"],
 ["                                                  Rows Removed by Filter: 3997741"],
 ["                                            ->  Hash  (cost=64585.54..64585.54 rows=1152454 width=8) (actual time=939.717..939.717 rows=1198895 loops=1)"],
 ["                                                  Buckets: 131072  Batches: 1  Memory Usage: 46832kB"],
 ["                                                  ->  Seq Scan on companies  (cost=0.00..64585.54 rows=1152454 width=8) (actual time=0.010..661.704 rows=1198895 loops=1)"],
 ["                          ->  Index Scan using users_pkey on users  (cost=0.43..0.83 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=182670)"],
 ["                                Index Cond: (id = company_users.user_id)"],
 ["                                Filter: (NOT email_is_unreachable)"],
 ["                                Rows Removed by Filter: 0"],
 ["                    ->  Hash  (cost=1138.68..1138.68 rows=65468 width=8) (actual time=19.184..19.184 rows=62351 loops=1)"],
 ["                          Buckets: 8192  Batches: 1  Memory Usage: 2436kB"],
 ["                          ->  Seq Scan on unsubscribed_users  (cost=0.00..1138.68 rows=65468 width=8) (actual time=0.011..8.867 rows=62351 loops=1)"],
 ["              ->  Index Scan using regions_pkey on regions  (cost=0.27..0.29 rows=1 width=21) (actual time=0.001..0.001 rows=1 loops=173562)"],
 ["                    Index Cond: (id = companies.main_region_id)"],
 ["        ->  Index Scan using deliveries_pkey on deliveries  (cost=0.28..0.31 rows=1 width=111) (actual time=0.001..0.001 rows=1 loops=173562)"],
 ["              Index Cond: (id = company_sends_log.delivery_id)"],
 ["Total runtime: 4899.547 ms"]]