explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g9MA

Settings
# exclusive inclusive rows x rows loops node
1. 23.885 4,438.433 ↑ 116.0 4,245 1

GroupAggregate (cost=50,874,740.70..51,600,939.54 rows=492,360 width=541) (actual time=4,395.404..4,438.433 rows=4,245 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.414 9.742 ↑ 6.4 39 1

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

  • Group Key: ct_1.theater_id
4. 0.304 9.328 ↑ 13.9 273 1

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

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

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

  • Hash Cond: (ct_1.user_id = us.id)
6. 5.573 5.573 ↓ 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.228..5.573 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.691 3.133 ↑ 1.0 2,834 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 218kB
8. 1.442 1.442 ↑ 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.442 rows=2,834 loops=1)

9. 9.957 4,414.548 ↑ 1,331.4 5,240 1

Nested Loop Left Join (cost=50,873,054.90..50,945,288.89 rows=6,976,741 width=279) (actual time=4,395.341..4,414.548 rows=5,240 loops=1)

  • Join Filter: false
10. 6.398 4,404.591 ↑ 1,331.4 5,240 1

Merge Left Join (cost=50,873,054.90..50,875,521.48 rows=6,976,741 width=279) (actual time=4,395.337..4,404.591 rows=5,240 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
11. 9.264 4,393.345 ↑ 95.1 5,179 1

Sort (cost=50,872,564.75..50,873,795.65 rows=492,360 width=203) (actual time=4,390.527..4,393.345 rows=5,179 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 1555kB
12. 6.102 4,384.081 ↑ 95.1 5,179 1

Hash Left Join (cost=49,519,588.72..50,777,209.70 rows=492,360 width=203) (actual time=289.869..4,384.081 rows=5,179 loops=1)

  • Hash Cond: (((th.country_id)::text = (s.country_id)::text) AND ((th.state_id)::text = (s.id)::text))
13. 5.206 4,377.910 ↑ 95.1 5,179 1

Hash Left Join (cost=49,519,586.34..50,769,707.83 rows=492,360 width=194) (actual time=289.788..4,377.910 rows=5,179 loops=1)

  • Hash Cond: (th.id = ct.theater_id)
14. 68.733 4,362.881 ↑ 95.1 5,179 1

Hash Right Join (cost=49,519,578.25..50,753,082.48 rows=492,360 width=152) (actual time=279.933..4,362.881 rows=5,179 loops=1)

  • Hash Cond: (pr.theater_id = th.id)
15. 4,072.285 4,072.285 ↓ 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=3.622..4,072.285 rows=107,568 loops=1)

  • Filter: (as_of_date = '2017-10-20'::date)
  • Rows Removed by Filter: 35631845
16. 5.018 221.863 ↑ 95.1 5,179 1

Hash (cost=49,513,423.75..49,513,423.75 rows=492,360 width=150) (actual time=221.863..221.863 rows=5,179 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4907kB
17. 12.067 216.845 ↑ 95.1 5,179 1

Merge Left Join (cost=49,508,398.87..49,513,423.75 rows=492,360 width=150) (actual time=177.379..216.845 rows=5,179 loops=1)

  • Merge Cond: (th.id = id.theater_id)
18. 7.541 180.191 ↑ 95.1 5,179 1

Merge Left Join (cost=49,501,904.56..49,505,622.15 rows=492,360 width=143) (actual time=159.053..180.191 rows=5,179 loops=1)

  • Merge Cond: (th.id = ta.theater_id)
19. 7.080 155.896 ↑ 102.8 4,791 1

Merge Left Join (cost=49,497,605.63..49,500,088.36 rows=492,360 width=136) (actual time=144.723..155.896 rows=4,791 loops=1)

  • Merge Cond: (th.id = dj.theater_id)
20. 6.119 57.323 ↑ 116.0 4,245 1

Sort (cost=49,480,321.63..49,481,552.53 rows=492,360 width=114) (actual time=55.142..57.323 rows=4,245 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 929kB
21. 4.259 51.204 ↑ 116.0 4,245 1

Hash Left Join (cost=45,960.88..49,433,770.58 rows=492,360 width=114) (actual time=26.675..51.204 rows=4,245 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
22. 24.829 39.786 ↑ 116.0 4,245 1

Bitmap Heap Scan on theaters th (cost=45,769.05..49,427,363.40 rows=492,360 width=93) (actual time=19.470..39.786 rows=4,245 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (alternatives: SubPlan 2 or hashed SubPlan 3)
  • Rows Removed by Filter: 8169
  • Heap Blocks: exact=3723
23. 4.366 4.366 ↑ 77.8 12,665 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..45,645.96 rows=984,720 width=0) (actual time=4.366..4.366 rows=12,665 loops=1)

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

SubPlan (for Bitmap Heap Scan)

25. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_theater_aliases_theater_id on theater_aliases ta_1 (cost=0.42..49.44 rows=1 width=0) (never executed)

  • Index Cond: (theater_id = th.id)
  • Filter: ((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[]))
26. 8.862 10.591 ↑ 1.1 6,535 1

Bitmap Heap Scan on theater_aliases ta_2 (cost=420.97..6,354.36 rows=7,502 width=8) (actual time=1.848..10.591 rows=6,535 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[]))
  • Heap Blocks: exact=1000
27. 1.729 1.729 ↑ 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=1.729..1.729 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[]))
28. 3.785 7.159 ↑ 1.0 6,259 1

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

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

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

30. 4.762 91.493 ↓ 1.0 4,013 1

Sort (cost=17,284.00..17,293.61 rows=3,844 width=30) (actual time=89.574..91.493 rows=4,013 loops=1)

  • Sort Key: dj.theater_id
  • Sort Method: quicksort Memory: 411kB
31. 4.225 86.731 ↓ 1.0 4,013 1

Hash Left Join (cost=1.99..17,055.12 rows=3,844 width=30) (actual time=17.071..86.731 rows=4,013 loops=1)

  • Hash Cond: (dj.justification_id = (ju.id)::text)
32. 82.433 82.433 ↓ 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=16.974..82.433 rows=4,013 loops=1)

  • Filter: ((booking_date >= '2017-10-20'::date) AND (booking_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 784709
33. 0.043 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
34. 0.030 0.030 ↑ 1.0 44 1

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

35. 6.369 16.754 ↓ 7.5 5,501 1

Sort (cost=4,298.92..4,300.75 rows=731 width=15) (actual time=14.325..16.754 rows=5,501 loops=1)

  • Sort Key: ta.theater_id
  • Sort Method: quicksort Memory: 518kB
36. 5.206 10.385 ↓ 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=5.273..10.385 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
37. 0.041 5.179 ↓ 0.0 0 1

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

38. 1.078 1.078 ↑ 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=1.078..1.078 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[]))
39. 4.060 4.060 ↑ 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=4.060..4.060 rows=35,309 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
40. 15.280 24.587 ↓ 1.0 14,783 1

Sort (cost=6,494.26..6,529.52 rows=14,106 width=15) (actual time=18.322..24.587 rows=14,783 loops=1)

  • Sort Key: id.theater_id
  • Sort Method: quicksort Memory: 1034kB
41. 8.446 9.307 ↑ 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=0.907..9.307 rows=13,860 loops=1)

  • Recheck Cond: ((source_id)::text = 'BOE'::text)
  • Heap Blocks: exact=381
42. 0.861 0.861 ↑ 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=0.861..0.861 rows=13,860 loops=1)

  • Index Cond: ((source_id)::text = 'BOE'::text)
43. 0.030 9.823 ↑ 6.4 39 1

Hash (cost=4.98..4.98 rows=249 width=50) (actual time=9.823..9.823 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
44. 9.793 9.793 ↑ 6.4 39 1

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

45. 0.034 0.069 ↑ 1.0 50 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
46. 0.035 0.035 ↑ 1.0 50 1

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

  • Filter: ((country_id)::text = 'US'::text)
47. 0.105 4.848 ↑ 8.4 105 1

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

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 31kB
48. 0.084 4.743 ↑ 16.6 53 1

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

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

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

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

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

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

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

52. 0.000 0.000 ↓ 0.0 0 5,240

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=5,240)

  • One-Time Filter: false
Planning time : 7.050 ms
Execution time : 4,447.684 ms