explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fjO7

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 16,167.885 ↑ 89.4 5,505 1

GroupAggregate (cost=3,470,758.85..4,196,957.69 rows=492,360 width=541) (actual time=16,100.074..16,167.885 rows=5,505 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.452 9.534 ↑ 6.4 39 1

GroupAggregate (cost=1,625.95..1,685.81 rows=249 width=50) (actual time=9.118..9.534 rows=39 loops=1)

  • Group Key: ct_1.theater_id
4. 0.309 9.082 ↑ 13.9 273 1

Sort (cost=1,625.95..1,635.41 rows=3,783 width=82) (actual time=8.968..9.082 rows=273 loops=1)

  • Sort Key: ct_1.theater_id
  • Sort Method: quicksort Memory: 46kB
5. 0.296 8.773 ↑ 13.9 273 1

Hash Join (cost=85.77..1,401.14 rows=3,783 width=82) (actual time=3.391..8.773 rows=273 loops=1)

  • Hash Cond: (ct_1.user_id = us.id)
6. 5.384 5.384 ↓ 1.0 273 1

Seq Scan on data_recovery_contacted_theaters ct_1 (cost=0.00..1,182.42 rows=267 width=26) (actual time=0.259..5.384 rows=273 loops=1)

  • Filter: ((contacted_date >= '2017-10-20'::date) AND (contacted_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 47822
7. 1.646 3.093 ↑ 1.0 2,834 1

Hash (cost=50.34..50.34 rows=2,834 width=72) (actual time=3.093..3.093 rows=2,834 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
8. 1.447 1.447 ↑ 1.0 2,834 1

Seq Scan on decrypted_users us (cost=0.00..50.34 rows=2,834 width=72) (actual time=0.010..1.447 rows=2,834 loops=1)

9. 16.122 16,131.444 ↑ 823.3 8,474 1

Nested Loop Left Join (cost=3,469,073.05..3,541,307.04 rows=6,976,741 width=279) (actual time=16,100.029..16,131.444 rows=8,474 loops=1)

  • Join Filter: false
10. 10.584 16,115.322 ↑ 823.3 8,474 1

Merge Left Join (cost=3,469,073.05..3,471,539.63 rows=6,976,741 width=279) (actual time=16,100.024..16,115.322 rows=8,474 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
11. 15.924 16,092.282 ↑ 58.5 8,413 1

Sort (cost=3,468,582.90..3,469,813.80 rows=492,360 width=203) (actual time=16,087.609..16,092.282 rows=8,413 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 2599kB
12. 10.960 16,076.358 ↑ 58.5 8,413 1

Hash Left Join (cost=2,119,892.04..3,373,227.85 rows=492,360 width=203) (actual time=4,438.101..16,076.358 rows=8,413 loops=1)

  • Hash Cond: (((th.country_id)::text = (s.country_id)::text) AND ((th.state_id)::text = (s.id)::text))
13. 9.939 16,065.331 ↑ 58.5 8,413 1

Hash Left Join (cost=2,119,889.67..3,365,725.98 rows=492,360 width=194) (actual time=4,438.022..16,065.331 rows=8,413 loops=1)

  • Hash Cond: (dj.justification_id = (ju.id)::text)
14. 10.188 16,055.319 ↑ 58.5 8,413 1

Hash Left Join (cost=2,119,887.68..3,359,607.26 rows=492,360 width=178) (actual time=4,437.934..16,055.319 rows=8,413 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
15. 71.215 16,033.413 ↑ 58.5 8,413 1

Hash Right Join (cost=2,119,695.85..3,353,200.08 rows=492,360 width=157) (actual time=4,426.164..16,033.413 rows=8,413 loops=1)

  • Hash Cond: (pr.theater_id = th.id)
16. 11,545.791 11,545.791 ↓ 3.5 107,568 1

Seq Scan on theater_properties pr (cost=0.00..1,233,374.65 rows=30,887 width=10) (actual time=7.001..11,545.791 rows=107,568 loops=1)

  • Filter: (as_of_date = '2017-10-20'::date)
  • Rows Removed by Filter: 35631845
17. 7.532 4,416.407 ↑ 58.5 8,413 1

Hash (cost=2,113,541.35..2,113,541.35 rows=492,360 width=155) (actual time=4,416.407..4,416.407 rows=8,413 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 5225kB
18. 12.337 4,408.875 ↑ 58.5 8,413 1

Hash Right Join (cost=2,096,519.85..2,113,541.35 rows=492,360 width=155) (actual time=4,284.986..4,408.875 rows=8,413 loops=1)

  • Hash Cond: (dj.theater_id = th.id)
19. 148.844 148.844 ↓ 1.0 4,013 1

Seq Scan on data_recovery_justifications dj (cost=0.00..17,005.38 rows=3,844 width=14) (actual time=34.589..148.844 rows=4,013 loops=1)

  • Filter: ((booking_date >= '2017-10-20'::date) AND (booking_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 784709
20. 5.681 4,247.694 ↑ 84.1 5,851 1

Hash (cost=2,090,365.35..2,090,365.35 rows=492,360 width=149) (actual time=4,247.694..4,247.694 rows=5,851 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4879kB
21. 16.163 4,242.013 ↑ 84.1 5,851 1

Hash Right Join (cost=2,085,269.88..2,090,365.35 rows=492,360 width=149) (actual time=4,168.312..4,242.013 rows=5,851 loops=1)

  • Hash Cond: (id.theater_id = th.id)
22. 60.577 64.530 ↑ 1.0 13,860 1

Bitmap Heap Scan on theater_aliases id (cost=485.74..5,522.07 rows=14,106 width=15) (actual time=4.359..64.530 rows=13,860 loops=1)

  • Recheck Cond: ((source_id)::text = 'BOE'::text)
  • Heap Blocks: exact=381
23. 3.953 3.953 ↑ 1.0 13,860 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..482.22 rows=14,106 width=0) (actual time=3.953..3.953 rows=13,860 loops=1)

  • Index Cond: ((source_id)::text = 'BOE'::text)
24. 5.126 4,161.320 ↑ 84.1 5,851 1

Hash (cost=2,078,629.63..2,078,629.63 rows=492,360 width=142) (actual time=4,161.320..4,161.320 rows=5,851 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4846kB
25. 7.880 4,156.194 ↑ 84.1 5,851 1

Hash Right Join (cost=2,078,623.61..2,078,629.63 rows=492,360 width=142) (actual time=4,150.494..4,156.194 rows=5,851 loops=1)

  • Hash Cond: (ct.theater_id = th.id)
26. 9.581 9.581 ↑ 6.4 39 1

CTE Scan on contacted_theaters_cte ct (cost=0.00..4.98 rows=249 width=50) (actual time=9.124..9.581 rows=39 loops=1)

27. 6.062 4,138.733 ↑ 84.1 5,851 1

Hash (cost=2,072,469.11..2,072,469.11 rows=492,360 width=100) (actual time=4,138.733..4,138.733 rows=5,851 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4810kB
28. 13.111 4,132.671 ↑ 84.1 5,851 1

Hash Right Join (cost=2,070,417.98..2,072,469.11 rows=492,360 width=100) (actual time=3,826.334..4,132.671 rows=5,851 loops=1)

  • Hash Cond: (ta.theater_id = th.id)
29. 296.842 561.246 ↓ 8.2 6,018 1

Bitmap Heap Scan on theater_aliases ta (cost=2,222.25..4,264.15 rows=731 width=15) (actual time=264.844..561.246 rows=6,018 loops=1)

  • Recheck Cond: (((source_id)::text = ANY ('{AMC,AGILE,ALAMODRAFTHOUSE,ALLURE,AZTECADELRIOBRAVO,BNB,BONNERMALL,CMX,CAPRITHEATRE,CARIBBEANCINEMAS,CENTEREDGE,CINELEO,CINECENTRE,CINEMACOLIBRI,CINEMAPARAMOUNT,CINEMAGIC,CINEMAGIC-USA,CINEMARK,CINEMARK-PERU,CINEMASDELCOUNTRY,CINEMASHENRY,CINEMEX,CINEPLANET,CINEPOLIS,CINEPOLISPERU,CINERAMA,CINESTAR,CITICINEMAS,COBB-IMPORT,EMS,EASYWARE,EXHIBIDORADELBRAVO,FILMBOT,FINO,HARKINS,KWIC,LANDMARK,LIMELIGHT,LOEKS,LOOKCINEMAS,MJR,MALCO,MANUAL-ENTRY,MARCUS,MARQUEE,MAYA,MOVIEHEROES,NCSOFTWARE,NATIONALAMUSEMENTS,OCEANCINEMAS,OMNITERM,OMNITERM-ALT,ORPHEUM,PACIFIC,PACIFIC-VISTA,POHNPEI,RTS,RADIANT,ALT-TAX-RADIANT,RADIANT-HEADQUARTERS,READING,READINGVISTA,REGAL,RETRIEVER,RETRIEVER-AU,RETRIEVER2,SENSIBLE,SOUTHERNTHEATRES,STARLIGHT,STUDIOMOVIEGRILL,SYSTEMBCN,SYUFY,TAPOS,TAPOS-AU,TEXASCINEMAS,THELOT,TITAN,UVK,ULTRASTAR,VEEZI,VENUE,VISTA,WUNDERLANDGAMES}'::text[])) AND (source_type = 'EXT'::text))
  • Heap Blocks: exact=955
30. 0.057 264.404 ↓ 0.0 0 1

BitmapAnd (cost=2,222.25..2,222.25 rows=731 width=0) (actual time=264.404..264.404 rows=0 loops=1)

31. 41.938 41.938 ↑ 1.1 6,537 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..419.09 rows=7,502 width=0) (actual time=41.938..41.938 rows=6,537 loops=1)

  • Index Cond: ((source_id)::text = ANY ('{AMC,AGILE,ALAMODRAFTHOUSE,ALLURE,AZTECADELRIOBRAVO,BNB,BONNERMALL,CMX,CAPRITHEATRE,CARIBBEANCINEMAS,CENTEREDGE,CINELEO,CINECENTRE,CINEMACOLIBRI,CINEMAPARAMOUNT,CINEMAGIC,CINEMAGIC-USA,CINEMARK,CINEMARK-PERU,CINEMASDELCOUNTRY,CINEMASHENRY,CINEMEX,CINEPLANET,CINEPOLIS,CINEPOLISPERU,CINERAMA,CINESTAR,CITICINEMAS,COBB-IMPORT,EMS,EASYWARE,EXHIBIDORADELBRAVO,FILMBOT,FINO,HARKINS,KWIC,LANDMARK,LIMELIGHT,LOEKS,LOOKCINEMAS,MJR,MALCO,MANUAL-ENTRY,MARCUS,MARQUEE,MAYA,MOVIEHEROES,NCSOFTWARE,NATIONALAMUSEMENTS,OCEANCINEMAS,OMNITERM,OMNITERM-ALT,ORPHEUM,PACIFIC,PACIFIC-VISTA,POHNPEI,RTS,RADIANT,ALT-TAX-RADIANT,RADIANT-HEADQUARTERS,READING,READINGVISTA,REGAL,RETRIEVER,RETRIEVER-AU,RETRIEVER2,SENSIBLE,SOUTHERNTHEATRES,STARLIGHT,STUDIOMOVIEGRILL,SYSTEMBCN,SYUFY,TAPOS,TAPOS-AU,TEXASCINEMAS,THELOT,TITAN,UVK,ULTRASTAR,VEEZI,VENUE,VISTA,WUNDERLANDGAMES}'::text[]))
32. 222.409 222.409 ↑ 1.0 35,309 1

Bitmap Index Scan on ix_theater_aliases_source_type (cost=0.00..1,802.54 rows=35,482 width=0) (actual time=222.409..222.409 rows=35,309 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
33. 9.590 3,558.314 ↑ 89.4 5,505 1

Hash (cost=2,062,041.23..2,062,041.23 rows=492,360 width=93) (actual time=3,558.314..3,558.314 rows=5,505 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4730kB
34. 258.187 3,548.724 ↑ 89.4 5,505 1

Bitmap Heap Scan on theaters th (cost=45,769.05..2,062,041.23 rows=492,360 width=93) (actual time=362.066..3,548.724 rows=5,505 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 6909
  • Heap Blocks: exact=3894
35. 360.833 360.833 ↑ 72.8 13,528 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..45,645.96 rows=984,720 width=0) (actual time=360.833..360.833 rows=13,528 loops=1)

  • Index Cond: ((country_id)::text = 'US'::text)
36.          

SubPlan (for Bitmap Heap Scan)

37. 2,929.704 2,929.704 ↓ 0.0 0 12,414

Index Only Scan using ix_theater_properties_is_reporting on theater_properties (cost=0.57..29.13 rows=37 width=0) (actual time=0.236..0.236 rows=0 loops=12,414)

  • Index Cond: ((theater_id = th.id) AND (as_of_date >= ('2017-10-20'::date - ('180 DAYS'::cstring)::interval)) AND (is_reporting = true))
  • Filter: is_reporting
  • Heap Fetches: 4229
38. 3.671 11.718 ↑ 1.0 6,259 1

Hash (cost=113.59..113.59 rows=6,259 width=29) (actual time=11.718..11.718 rows=6,259 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 445kB
39. 8.047 8.047 ↑ 1.0 6,259 1

Seq Scan on circuits ci (cost=0.00..113.59 rows=6,259 width=29) (actual time=3.646..8.047 rows=6,259 loops=1)

40. 0.042 0.073 ↑ 1.0 44 1

Hash (cost=1.44..1.44 rows=44 width=18) (actual time=0.073..0.073 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
41. 0.031 0.031 ↑ 1.0 44 1

Seq Scan on justifications ju (cost=0.00..1.44 rows=44 width=18) (actual time=0.006..0.031 rows=44 loops=1)

42. 0.034 0.067 ↑ 1.0 50 1

Hash (cost=1.62..1.62 rows=50 width=15) (actual time=0.067..0.067 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
43. 0.033 0.033 ↑ 1.0 50 1

Seq Scan on states s (cost=0.00..1.62 rows=50 width=15) (actual time=0.007..0.033 rows=50 loops=1)

  • Filter: ((country_id)::text = 'US'::text)
44. 0.111 12.456 ↑ 8.4 105 1

Sort (cost=490.15..492.35 rows=879 width=84) (actual time=12.407..12.456 rows=105 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 31kB
45. 0.100 12.345 ↑ 16.6 53 1

Hash Left Join (cost=85.77..447.17 rows=879 width=84) (actual time=11.500..12.345 rows=53 loops=1)

  • Hash Cond: (mg.user_id = nu.id)
46. 9.159 9.159 ↑ 1.2 53 1

Seq Scan on data_recovery_mg_ignored_dates mg (cost=0.00..330.52 rows=62 width=28) (actual time=8.366..9.159 rows=53 loops=1)

  • Filter: ((ignored_date >= '2017-10-20'::date) AND (ignored_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 13382
47. 1.645 3.086 ↑ 1.0 2,834 1

Hash (cost=50.34..50.34 rows=2,834 width=72) (actual time=3.086..3.086 rows=2,834 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
48. 1.441 1.441 ↑ 1.0 2,834 1

Seq Scan on decrypted_users nu (cost=0.00..50.34 rows=2,834 width=72) (actual time=0.012..1.441 rows=2,834 loops=1)

49. 0.000 0.000 ↓ 0.0 0 8,474

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=8,474)

  • One-Time Filter: false
50. 1.646 3.093 ↑ 1.0 2,834 1

Hash (cost=50.34..50.34 rows=2,834 width=72) (actual time=3.093..3.093 rows=2,834 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
51. 1.447 1.447 ↑ 1.0 2,834 1

Seq Scan on decrypted_users us (cost=0.00..50.34 rows=2,834 width=72) (actual time=0.010..1.447 rows=2,834 loops=1)

52. 16.122 16,131.444 ↑ 823.3 8,474 1

Nested Loop Left Join (cost=3,469,073.05..3,541,307.04 rows=6,976,741 width=279) (actual time=16,100.029..16,131.444 rows=8,474 loops=1)

  • Join Filter: false
53. 10.584 16,115.322 ↑ 823.3 8,474 1

Merge Left Join (cost=3,469,073.05..3,471,539.63 rows=6,976,741 width=279) (actual time=16,100.024..16,115.322 rows=8,474 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
54. 15.924 16,092.282 ↑ 58.5 8,413 1

Sort (cost=3,468,582.90..3,469,813.80 rows=492,360 width=203) (actual time=16,087.609..16,092.282 rows=8,413 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 2599kB
55. 10.960 16,076.358 ↑ 58.5 8,413 1

Hash Left Join (cost=2,119,892.04..3,373,227.85 rows=492,360 width=203) (actual time=4,438.101..16,076.358 rows=8,413 loops=1)

  • Hash Cond: (((th.country_id)::text = (s.country_id)::text) AND ((th.state_id)::text = (s.id)::text))
56. 9.939 16,065.331 ↑ 58.5 8,413 1

Hash Left Join (cost=2,119,889.67..3,365,725.98 rows=492,360 width=194) (actual time=4,438.022..16,065.331 rows=8,413 loops=1)

  • Hash Cond: (dj.justification_id = (ju.id)::text)
57. 10.188 16,055.319 ↑ 58.5 8,413 1

Hash Left Join (cost=2,119,887.68..3,359,607.26 rows=492,360 width=178) (actual time=4,437.934..16,055.319 rows=8,413 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
58. 71.215 16,033.413 ↑ 58.5 8,413 1

Hash Right Join (cost=2,119,695.85..3,353,200.08 rows=492,360 width=157) (actual time=4,426.164..16,033.413 rows=8,413 loops=1)

  • Hash Cond: (pr.theater_id = th.id)
59. 11,545.791 11,545.791 ↓ 3.5 107,568 1

Seq Scan on theater_properties pr (cost=0.00..1,233,374.65 rows=30,887 width=10) (actual time=7.001..11,545.791 rows=107,568 loops=1)

  • Filter: (as_of_date = '2017-10-20'::date)
  • Rows Removed by Filter: 35631845
60. 7.532 4,416.407 ↑ 58.5 8,413 1

Hash (cost=2,113,541.35..2,113,541.35 rows=492,360 width=155) (actual time=4,416.407..4,416.407 rows=8,413 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 5225kB
61. 12.337 4,408.875 ↑ 58.5 8,413 1

Hash Right Join (cost=2,096,519.85..2,113,541.35 rows=492,360 width=155) (actual time=4,284.986..4,408.875 rows=8,413 loops=1)

  • Hash Cond: (dj.theater_id = th.id)
62. 148.844 148.844 ↓ 1.0 4,013 1

Seq Scan on data_recovery_justifications dj (cost=0.00..17,005.38 rows=3,844 width=14) (actual time=34.589..148.844 rows=4,013 loops=1)

  • Filter: ((booking_date >= '2017-10-20'::date) AND (booking_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 784709
63. 5.681 4,247.694 ↑ 84.1 5,851 1

Hash (cost=2,090,365.35..2,090,365.35 rows=492,360 width=149) (actual time=4,247.694..4,247.694 rows=5,851 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4879kB
64. 16.163 4,242.013 ↑ 84.1 5,851 1

Hash Right Join (cost=2,085,269.88..2,090,365.35 rows=492,360 width=149) (actual time=4,168.312..4,242.013 rows=5,851 loops=1)

  • Hash Cond: (id.theater_id = th.id)
65. 60.577 64.530 ↑ 1.0 13,860 1

Bitmap Heap Scan on theater_aliases id (cost=485.74..5,522.07 rows=14,106 width=15) (actual time=4.359..64.530 rows=13,860 loops=1)

  • Recheck Cond: ((source_id)::text = 'BOE'::text)
  • Heap Blocks: exact=381
66. 3.953 3.953 ↑ 1.0 13,860 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..482.22 rows=14,106 width=0) (actual time=3.953..3.953 rows=13,860 loops=1)

  • Index Cond: ((source_id)::text = 'BOE'::text)
67. 5.126 4,161.320 ↑ 84.1 5,851 1

Hash (cost=2,078,629.63..2,078,629.63 rows=492,360 width=142) (actual time=4,161.320..4,161.320 rows=5,851 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4846kB
68. 7.880 4,156.194 ↑ 84.1 5,851 1

Hash Right Join (cost=2,078,623.61..2,078,629.63 rows=492,360 width=142) (actual time=4,150.494..4,156.194 rows=5,851 loops=1)

  • Hash Cond: (ct.theater_id = th.id)
69. 9.581 9.581 ↑ 6.4 39 1

CTE Scan on contacted_theaters_cte ct (cost=0.00..4.98 rows=249 width=50) (actual time=9.124..9.581 rows=39 loops=1)

70. 6.062 4,138.733 ↑ 84.1 5,851 1

Hash (cost=2,072,469.11..2,072,469.11 rows=492,360 width=100) (actual time=4,138.733..4,138.733 rows=5,851 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4810kB
71. 13.111 4,132.671 ↑ 84.1 5,851 1

Hash Right Join (cost=2,070,417.98..2,072,469.11 rows=492,360 width=100) (actual time=3,826.334..4,132.671 rows=5,851 loops=1)

  • Hash Cond: (ta.theater_id = th.id)
72. 296.842 561.246 ↓ 8.2 6,018 1

Bitmap Heap Scan on theater_aliases ta (cost=2,222.25..4,264.15 rows=731 width=15) (actual time=264.844..561.246 rows=6,018 loops=1)

  • Recheck Cond: (((source_id)::text = ANY ('{AMC,AGILE,ALAMODRAFTHOUSE,ALLURE,AZTECADELRIOBRAVO,BNB,BONNERMALL,CMX,CAPRITHEATRE,CARIBBEANCINEMAS,CENTEREDGE,CINELEO,CINECENTRE,CINEMACOLIBRI,CINEMAPARAMOUNT,CINEMAGIC,CINEMAGIC-USA,CINEMARK,CINEMARK-PERU,CINEMASDELCOUNTRY,CINEMASHENRY,CINEMEX,CINEPLANET,CINEPOLIS,CINEPOLISPERU,CINERAMA,CINESTAR,CITICINEMAS,COBB-IMPORT,EMS,EASYWARE,EXHIBIDORADELBRAVO,FILMBOT,FINO,HARKINS,KWIC,LANDMARK,LIMELIGHT,LOEKS,LOOKCINEMAS,MJR,MALCO,MANUAL-ENTRY,MARCUS,MARQUEE,MAYA,MOVIEHEROES,NCSOFTWARE,NATIONALAMUSEMENTS,OCEANCINEMAS,OMNITERM,OMNITERM-ALT,ORPHEUM,PACIFIC,PACIFIC-VISTA,POHNPEI,RTS,RADIANT,ALT-TAX-RADIANT,RADIANT-HEADQUARTERS,READING,READINGVISTA,REGAL,RETRIEVER,RETRIEVER-AU,RETRIEVER2,SENSIBLE,SOUTHERNTHEATRES,STARLIGHT,STUDIOMOVIEGRILL,SYSTEMBCN,SYUFY,TAPOS,TAPOS-AU,TEXASCINEMAS,THELOT,TITAN,UVK,ULTRASTAR,VEEZI,VENUE,VISTA,WUNDERLANDGAMES}'::text[])) AND (source_type = 'EXT'::text))
  • Heap Blocks: exact=955
73. 0.057 264.404 ↓ 0.0 0 1

BitmapAnd (cost=2,222.25..2,222.25 rows=731 width=0) (actual time=264.404..264.404 rows=0 loops=1)

74. 41.938 41.938 ↑ 1.1 6,537 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..419.09 rows=7,502 width=0) (actual time=41.938..41.938 rows=6,537 loops=1)

  • Index Cond: ((source_id)::text = ANY ('{AMC,AGILE,ALAMODRAFTHOUSE,ALLURE,AZTECADELRIOBRAVO,BNB,BONNERMALL,CMX,CAPRITHEATRE,CARIBBEANCINEMAS,CENTEREDGE,CINELEO,CINECENTRE,CINEMACOLIBRI,CINEMAPARAMOUNT,CINEMAGIC,CINEMAGIC-USA,CINEMARK,CINEMARK-PERU,CINEMASDELCOUNTRY,CINEMASHENRY,CINEMEX,CINEPLANET,CINEPOLIS,CINEPOLISPERU,CINERAMA,CINESTAR,CITICINEMAS,COBB-IMPORT,EMS,EASYWARE,EXHIBIDORADELBRAVO,FILMBOT,FINO,HARKINS,KWIC,LANDMARK,LIMELIGHT,LOEKS,LOOKCINEMAS,MJR,MALCO,MANUAL-ENTRY,MARCUS,MARQUEE,MAYA,MOVIEHEROES,NCSOFTWARE,NATIONALAMUSEMENTS,OCEANCINEMAS,OMNITERM,OMNITERM-ALT,ORPHEUM,PACIFIC,PACIFIC-VISTA,POHNPEI,RTS,RADIANT,ALT-TAX-RADIANT,RADIANT-HEADQUARTERS,READING,READINGVISTA,REGAL,RETRIEVER,RETRIEVER-AU,RETRIEVER2,SENSIBLE,SOUTHERNTHEATRES,STARLIGHT,STUDIOMOVIEGRILL,SYSTEMBCN,SYUFY,TAPOS,TAPOS-AU,TEXASCINEMAS,THELOT,TITAN,UVK,ULTRASTAR,VEEZI,VENUE,VISTA,WUNDERLANDGAMES}'::text[]))
75. 222.409 222.409 ↑ 1.0 35,309 1

Bitmap Index Scan on ix_theater_aliases_source_type (cost=0.00..1,802.54 rows=35,482 width=0) (actual time=222.409..222.409 rows=35,309 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
76. 9.590 3,558.314 ↑ 89.4 5,505 1

Hash (cost=2,062,041.23..2,062,041.23 rows=492,360 width=93) (actual time=3,558.314..3,558.314 rows=5,505 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4730kB
77. 258.187 3,548.724 ↑ 89.4 5,505 1

Bitmap Heap Scan on theaters th (cost=45,769.05..2,062,041.23 rows=492,360 width=93) (actual time=362.066..3,548.724 rows=5,505 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 6909
  • Heap Blocks: exact=3894
78. 360.833 360.833 ↑ 72.8 13,528 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..45,645.96 rows=984,720 width=0) (actual time=360.833..360.833 rows=13,528 loops=1)

  • Index Cond: ((country_id)::text = 'US'::text)
79.          

SubPlan (for Bitmap Heap Scan)

80. 2,929.704 2,929.704 ↓ 0.0 0 12,414

Index Only Scan using ix_theater_properties_is_reporting on theater_properties (cost=0.57..29.13 rows=37 width=0) (actual time=0.236..0.236 rows=0 loops=12,414)

  • Index Cond: ((theater_id = th.id) AND (as_of_date >= ('2017-10-20'::date - ('180 DAYS'::cstring)::interval)) AND (is_reporting = true))
  • Filter: is_reporting
  • Heap Fetches: 4229
81. 3.671 11.718 ↑ 1.0 6,259 1

Hash (cost=113.59..113.59 rows=6,259 width=29) (actual time=11.718..11.718 rows=6,259 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 445kB
82. 8.047 8.047 ↑ 1.0 6,259 1

Seq Scan on circuits ci (cost=0.00..113.59 rows=6,259 width=29) (actual time=3.646..8.047 rows=6,259 loops=1)

83. 0.042 0.073 ↑ 1.0 44 1

Hash (cost=1.44..1.44 rows=44 width=18) (actual time=0.073..0.073 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
84. 0.031 0.031 ↑ 1.0 44 1

Seq Scan on justifications ju (cost=0.00..1.44 rows=44 width=18) (actual time=0.006..0.031 rows=44 loops=1)

85. 0.034 0.067 ↑ 1.0 50 1

Hash (cost=1.62..1.62 rows=50 width=15) (actual time=0.067..0.067 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
86. 0.033 0.033 ↑ 1.0 50 1

Seq Scan on states s (cost=0.00..1.62 rows=50 width=15) (actual time=0.007..0.033 rows=50 loops=1)

  • Filter: ((country_id)::text = 'US'::text)
87. 0.111 12.456 ↑ 8.4 105 1

Sort (cost=490.15..492.35 rows=879 width=84) (actual time=12.407..12.456 rows=105 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 31kB
88. 0.100 12.345 ↑ 16.6 53 1

Hash Left Join (cost=85.77..447.17 rows=879 width=84) (actual time=11.500..12.345 rows=53 loops=1)

  • Hash Cond: (mg.user_id = nu.id)
89. 9.159 9.159 ↑ 1.2 53 1

Seq Scan on data_recovery_mg_ignored_dates mg (cost=0.00..330.52 rows=62 width=28) (actual time=8.366..9.159 rows=53 loops=1)

  • Filter: ((ignored_date >= '2017-10-20'::date) AND (ignored_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 13382
90. 1.645 3.086 ↑ 1.0 2,834 1

Hash (cost=50.34..50.34 rows=2,834 width=72) (actual time=3.086..3.086 rows=2,834 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
91. 1.441 1.441 ↑ 1.0 2,834 1

Seq Scan on decrypted_users nu (cost=0.00..50.34 rows=2,834 width=72) (actual time=0.012..1.441 rows=2,834 loops=1)

92. 0.000 0.000 ↓ 0.0 0 8,474

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=8,474)

  • One-Time Filter: false
93. 1.646 3.093 ↑ 1.0 2,834 1

Hash (cost=50.34..50.34 rows=2,834 width=72) (actual time=3.093..3.093 rows=2,834 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
94. 1.447 1.447 ↑ 1.0 2,834 1

Seq Scan on decrypted_users us (cost=0.00..50.34 rows=2,834 width=72) (actual time=0.010..1.447 rows=2,834 loops=1)

95. 16.122 16,131.444 ↑ 823.3 8,474 1

Nested Loop Left Join (cost=3,469,073.05..3,541,307.04 rows=6,976,741 width=279) (actual time=16,100.029..16,131.444 rows=8,474 loops=1)

  • Join Filter: false
96. 10.584 16,115.322 ↑ 823.3 8,474 1

Merge Left Join (cost=3,469,073.05..3,471,539.63 rows=6,976,741 width=279) (actual time=16,100.024..16,115.322 rows=8,474 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
97. 15.924 16,092.282 ↑ 58.5 8,413 1

Sort (cost=3,468,582.90..3,469,813.80 rows=492,360 width=203) (actual time=16,087.609..16,092.282 rows=8,413 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 2599kB
98. 10.960 16,076.358 ↑ 58.5 8,413 1

Hash Left Join (cost=2,119,892.04..3,373,227.85 rows=492,360 width=203) (actual time=4,438.101..16,076.358 rows=8,413 loops=1)

  • Hash Cond: (((th.country_id)::text = (s.country_id)::text) AND ((th.state_id)::text = (s.id)::text))
99. 9.939 16,065.331 ↑ 58.5 8,413 1

Hash Left Join (cost=2,119,889.67..3,365,725.98 rows=492,360 width=194) (actual time=4,438.022..16,065.331 rows=8,413 loops=1)

  • Hash Cond: (dj.justification_id = (ju.id)::text)
100. 10.188 16,055.319 ↑ 58.5 8,413 1

Hash Left Join (cost=2,119,887.68..3,359,607.26 rows=492,360 width=178) (actual time=4,437.934..16,055.319 rows=8,413 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
101. 71.215 16,033.413 ↑ 58.5 8,413 1

Hash Right Join (cost=2,119,695.85..3,353,200.08 rows=492,360 width=157) (actual time=4,426.164..16,033.413 rows=8,413 loops=1)

  • Hash Cond: (pr.theater_id = th.id)
102. 11,545.791 11,545.791 ↓ 3.5 107,568 1

Seq Scan on theater_properties pr (cost=0.00..1,233,374.65 rows=30,887 width=10) (actual time=7.001..11,545.791 rows=107,568 loops=1)

  • Filter: (as_of_date = '2017-10-20'::date)
  • Rows Removed by Filter: 35631845
103. 7.532 4,416.407 ↑ 58.5 8,413 1

Hash (cost=2,113,541.35..2,113,541.35 rows=492,360 width=155) (actual time=4,416.407..4,416.407 rows=8,413 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 5225kB
104. 12.337 4,408.875 ↑ 58.5 8,413 1

Hash Right Join (cost=2,096,519.85..2,113,541.35 rows=492,360 width=155) (actual time=4,284.986..4,408.875 rows=8,413 loops=1)

  • Hash Cond: (dj.theater_id = th.id)
105. 148.844 148.844 ↓ 1.0 4,013 1

Seq Scan on data_recovery_justifications dj (cost=0.00..17,005.38 rows=3,844 width=14) (actual time=34.589..148.844 rows=4,013 loops=1)

  • Filter: ((booking_date >= '2017-10-20'::date) AND (booking_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 784709
106. 5.681 4,247.694 ↑ 84.1 5,851 1

Hash (cost=2,090,365.35..2,090,365.35 rows=492,360 width=149) (actual time=4,247.694..4,247.694 rows=5,851 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4879kB
107. 16.163 4,242.013 ↑ 84.1 5,851 1

Hash Right Join (cost=2,085,269.88..2,090,365.35 rows=492,360 width=149) (actual time=4,168.312..4,242.013 rows=5,851 loops=1)

  • Hash Cond: (id.theater_id = th.id)
108. 60.577 64.530 ↑ 1.0 13,860 1

Bitmap Heap Scan on theater_aliases id (cost=485.74..5,522.07 rows=14,106 width=15) (actual time=4.359..64.530 rows=13,860 loops=1)

  • Recheck Cond: ((source_id)::text = 'BOE'::text)
  • Heap Blocks: exact=381
109. 3.953 3.953 ↑ 1.0 13,860 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..482.22 rows=14,106 width=0) (actual time=3.953..3.953 rows=13,860 loops=1)

  • Index Cond: ((source_id)::text = 'BOE'::text)
110. 5.126 4,161.320 ↑ 84.1 5,851 1

Hash (cost=2,078,629.63..2,078,629.63 rows=492,360 width=142) (actual time=4,161.320..4,161.320 rows=5,851 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4846kB
111. 7.880 4,156.194 ↑ 84.1 5,851 1

Hash Right Join (cost=2,078,623.61..2,078,629.63 rows=492,360 width=142) (actual time=4,150.494..4,156.194 rows=5,851 loops=1)

  • Hash Cond: (ct.theater_id = th.id)
112. 9.581 9.581 ↑ 6.4 39 1

CTE Scan on contacted_theaters_cte ct (cost=0.00..4.98 rows=249 width=50) (actual time=9.124..9.581 rows=39 loops=1)

113. 6.062 4,138.733 ↑ 84.1 5,851 1

Hash (cost=2,072,469.11..2,072,469.11 rows=492,360 width=100) (actual time=4,138.733..4,138.733 rows=5,851 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4810kB
114. 13.111 4,132.671 ↑ 84.1 5,851 1

Hash Right Join (cost=2,070,417.98..2,072,469.11 rows=492,360 width=100) (actual time=3,826.334..4,132.671 rows=5,851 loops=1)

  • Hash Cond: (ta.theater_id = th.id)
115. 296.842 561.246 ↓ 8.2 6,018 1

Bitmap Heap Scan on theater_aliases ta (cost=2,222.25..4,264.15 rows=731 width=15) (actual time=264.844..561.246 rows=6,018 loops=1)

  • Recheck Cond: (((source_id)::text = ANY ('{AMC,AGILE,ALAMODRAFTHOUSE,ALLURE,AZTECADELRIOBRAVO,BNB,BONNERMALL,CMX,CAPRITHEATRE,CARIBBEANCINEMAS,CENTEREDGE,CINELEO,CINECENTRE,CINEMACOLIBRI,CINEMAPARAMOUNT,CINEMAGIC,CINEMAGIC-USA,CINEMARK,CINEMARK-PERU,CINEMASDELCOUNTRY,CINEMASHENRY,CINEMEX,CINEPLANET,CINEPOLIS,CINEPOLISPERU,CINERAMA,CINESTAR,CITICINEMAS,COBB-IMPORT,EMS,EASYWARE,EXHIBIDORADELBRAVO,FILMBOT,FINO,HARKINS,KWIC,LANDMARK,LIMELIGHT,LOEKS,LOOKCINEMAS,MJR,MALCO,MANUAL-ENTRY,MARCUS,MARQUEE,MAYA,MOVIEHEROES,NCSOFTWARE,NATIONALAMUSEMENTS,OCEANCINEMAS,OMNITERM,OMNITERM-ALT,ORPHEUM,PACIFIC,PACIFIC-VISTA,POHNPEI,RTS,RADIANT,ALT-TAX-RADIANT,RADIANT-HEADQUARTERS,READING,READINGVISTA,REGAL,RETRIEVER,RETRIEVER-AU,RETRIEVER2,SENSIBLE,SOUTHERNTHEATRES,STARLIGHT,STUDIOMOVIEGRILL,SYSTEMBCN,SYUFY,TAPOS,TAPOS-AU,TEXASCINEMAS,THELOT,TITAN,UVK,ULTRASTAR,VEEZI,VENUE,VISTA,WUNDERLANDGAMES}'::text[])) AND (source_type = 'EXT'::text))
  • Heap Blocks: exact=955
116. 0.057 264.404 ↓ 0.0 0 1

BitmapAnd (cost=2,222.25..2,222.25 rows=731 width=0) (actual time=264.404..264.404 rows=0 loops=1)

117. 41.938 41.938 ↑ 1.1 6,537 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..419.09 rows=7,502 width=0) (actual time=41.938..41.938 rows=6,537 loops=1)

  • Index Cond: ((source_id)::text = ANY ('{AMC,AGILE,ALAMODRAFTHOUSE,ALLURE,AZTECADELRIOBRAVO,BNB,BONNERMALL,CMX,CAPRITHEATRE,CARIBBEANCINEMAS,CENTEREDGE,CINELEO,CINECENTRE,CINEMACOLIBRI,CINEMAPARAMOUNT,CINEMAGIC,CINEMAGIC-USA,CINEMARK,CINEMARK-PERU,CINEMASDELCOUNTRY,CINEMASHENRY,CINEMEX,CINEPLANET,CINEPOLIS,CINEPOLISPERU,CINERAMA,CINESTAR,CITICINEMAS,COBB-IMPORT,EMS,EASYWARE,EXHIBIDORADELBRAVO,FILMBOT,FINO,HARKINS,KWIC,LANDMARK,LIMELIGHT,LOEKS,LOOKCINEMAS,MJR,MALCO,MANUAL-ENTRY,MARCUS,MARQUEE,MAYA,MOVIEHEROES,NCSOFTWARE,NATIONALAMUSEMENTS,OCEANCINEMAS,OMNITERM,OMNITERM-ALT,ORPHEUM,PACIFIC,PACIFIC-VISTA,POHNPEI,RTS,RADIANT,ALT-TAX-RADIANT,RADIANT-HEADQUARTERS,READING,READINGVISTA,REGAL,RETRIEVER,RETRIEVER-AU,RETRIEVER2,SENSIBLE,SOUTHERNTHEATRES,STARLIGHT,STUDIOMOVIEGRILL,SYSTEMBCN,SYUFY,TAPOS,TAPOS-AU,TEXASCINEMAS,THELOT,TITAN,UVK,ULTRASTAR,VEEZI,VENUE,VISTA,WUNDERLANDGAMES}'::text[]))
118. 222.409 222.409 ↑ 1.0 35,309 1

Bitmap Index Scan on ix_theater_aliases_source_type (cost=0.00..1,802.54 rows=35,482 width=0) (actual time=222.409..222.409 rows=35,309 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
119. 9.590 3,558.314 ↑ 89.4 5,505 1

Hash (cost=2,062,041.23..2,062,041.23 rows=492,360 width=93) (actual time=3,558.314..3,558.314 rows=5,505 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4730kB
120. 258.187 3,548.724 ↑ 89.4 5,505 1

Bitmap Heap Scan on theaters th (cost=45,769.05..2,062,041.23 rows=492,360 width=93) (actual time=362.066..3,548.724 rows=5,505 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 6909
  • Heap Blocks: exact=3894
121. 360.833 360.833 ↑ 72.8 13,528 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..45,645.96 rows=984,720 width=0) (actual time=360.833..360.833 rows=13,528 loops=1)

  • Index Cond: ((country_id)::text = 'US'::text)
122.          

SubPlan (for Bitmap Heap Scan)

123. 2,929.704 2,929.704 ↓ 0.0 0 12,414

Index Only Scan using ix_theater_properties_is_reporting on theater_properties (cost=0.57..29.13 rows=37 width=0) (actual time=0.236..0.236 rows=0 loops=12,414)

  • Index Cond: ((theater_id = th.id) AND (as_of_date >= ('2017-10-20'::date - ('180 DAYS'::cstring)::interval)) AND (is_reporting = true))
  • Filter: is_reporting
  • Heap Fetches: 4229
124. 3.671 11.718 ↑ 1.0 6,259 1

Hash (cost=113.59..113.59 rows=6,259 width=29) (actual time=11.718..11.718 rows=6,259 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 445kB
125. 8.047 8.047 ↑ 1.0 6,259 1

Seq Scan on circuits ci (cost=0.00..113.59 rows=6,259 width=29) (actual time=3.646..8.047 rows=6,259 loops=1)

126. 0.042 0.073 ↑ 1.0 44 1

Hash (cost=1.44..1.44 rows=44 width=18) (actual time=0.073..0.073 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
127. 0.031 0.031 ↑ 1.0 44 1

Seq Scan on justifications ju (cost=0.00..1.44 rows=44 width=18) (actual time=0.006..0.031 rows=44 loops=1)

128. 0.034 0.067 ↑ 1.0 50 1

Hash (cost=1.62..1.62 rows=50 width=15) (actual time=0.067..0.067 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
129. 0.033 0.033 ↑ 1.0 50 1

Seq Scan on states s (cost=0.00..1.62 rows=50 width=15) (actual time=0.007..0.033 rows=50 loops=1)

  • Filter: ((country_id)::text = 'US'::text)
130. 0.111 12.456 ↑ 8.4 105 1

Sort (cost=490.15..492.35 rows=879 width=84) (actual time=12.407..12.456 rows=105 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 31kB
131. 0.100 12.345 ↑ 16.6 53 1

Hash Left Join (cost=85.77..447.17 rows=879 width=84) (actual time=11.500..12.345 rows=53 loops=1)

  • Hash Cond: (mg.user_id = nu.id)
132. 9.159 9.159 ↑ 1.2 53 1

Seq Scan on data_recovery_mg_ignored_dates mg (cost=0.00..330.52 rows=62 width=28) (actual time=8.366..9.159 rows=53 loops=1)

  • Filter: ((ignored_date >= '2017-10-20'::date) AND (ignored_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 13382
133. 1.645 3.086 ↑ 1.0 2,834 1

Hash (cost=50.34..50.34 rows=2,834 width=72) (actual time=3.086..3.086 rows=2,834 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
134. 1.441 1.441 ↑ 1.0 2,834 1

Seq Scan on decrypted_users nu (cost=0.00..50.34 rows=2,834 width=72) (actual time=0.012..1.441 rows=2,834 loops=1)

135. 0.000 0.000 ↓ 0.0 0 8,474

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=8,474)

  • One-Time Filter: false
Planning time : 14.847 ms
Execution time : 16,182.427 ms