explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zY4r1

Settings
# exclusive inclusive rows x rows loops node
1. 13.657 308.002 ↑ 116.0 4,245 1

GroupAggregate (cost=51,546,196.54..52,164,046.78 rows=492,360 width=508) (actual time=291.998..308.002 rows=4,245 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.412 9.861 ↑ 6.4 39 1

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

  • Group Key: ct_1.theater_id
4. 0.306 9.449 ↑ 13.9 273 1

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

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

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

  • Hash Cond: (ct_1.user_id = us.id)
6. 5.786 5.786 ↓ 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.786 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.628 3.087 ↑ 1.0 2,834 1

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

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

9. 7.614 294.345 ↑ 1,331.4 5,240 1

Sort (cost=51,544,510.73..51,561,952.58 rows=6,976,741 width=270) (actual time=291.982..294.345 rows=5,240 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 1526kB
10. 9.293 286.731 ↑ 1,331.4 5,240 1

Nested Loop Left Join (cost=49,693,481.74..49,869,145.33 rows=6,976,741 width=270) (actual time=264.412..286.731 rows=5,240 loops=1)

  • Join Filter: false
11. 5.896 277.438 ↑ 1,331.4 5,240 1

Merge Right Join (cost=49,693,481.74..49,799,377.92 rows=6,976,741 width=270) (actual time=264.408..277.438 rows=5,240 loops=1)

  • Merge Cond: (nu.id = mg.user_id)
12. 2.529 4.088 ↑ 6.0 472 1

Sort (cost=212.85..219.94 rows=2,834 width=72) (actual time=3.850..4.088 rows=472 loops=1)

  • Sort Key: nu.id
  • Sort Method: quicksort Memory: 323kB
13. 1.559 1.559 ↑ 1.0 2,834 1

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

14. 4.605 267.454 ↑ 94.0 5,240 1

Materialize (cost=49,693,268.89..49,695,730.69 rows=492,360 width=214) (actual time=260.536..267.454 rows=5,240 loops=1)

15. 7.539 262.849 ↑ 94.0 5,240 1

Sort (cost=49,693,268.89..49,694,499.79 rows=492,360 width=214) (actual time=260.532..262.849 rows=5,240 loops=1)

  • Sort Key: mg.user_id
  • Sort Method: quicksort Memory: 1538kB
16. 8.181 255.310 ↑ 94.0 5,240 1

Nested Loop Left Join (cost=49,508,746.27..49,596,230.34 rows=492,360 width=214) (actual time=186.681..255.310 rows=5,240 loops=1)

17. 5.160 236.649 ↑ 94.0 5,240 1

Merge Left Join (cost=49,508,746.13..49,516,234.51 rows=492,360 width=205) (actual time=186.659..236.649 rows=5,240 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
18. 4.796 229.782 ↑ 95.1 5,179 1

Merge Left Join (cost=49,508,413.76..49,514,670.90 rows=492,360 width=185) (actual time=184.994..229.782 rows=5,179 loops=1)

  • Merge Cond: (th.id = ct.theater_id)
19. 12.446 215.019 ↑ 95.1 5,179 1

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

  • Merge Cond: (th.id = id.theater_id)
20. 7.645 177.935 ↑ 95.1 5,179 1

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

  • Merge Cond: (th.id = ta.theater_id)
21. 6.695 153.823 ↑ 102.8 4,791 1

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

  • Merge Cond: (th.id = dj.theater_id)
22. 6.469 57.053 ↑ 116.0 4,245 1

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

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 929kB
23. 4.524 50.584 ↑ 116.0 4,245 1

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

  • Hash Cond: (th.circuit_id = ci.id)
24. 25.844 38.708 ↑ 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=17.283..38.708 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=3724
25. 3.732 3.732 ↑ 77.7 12,666 1

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

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

SubPlan (for Bitmap Heap Scan)

27. 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[]))
28. 7.713 9.132 ↑ 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.514..9.132 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
29. 1.419 1.419 ↑ 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.419..1.419 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[]))
30. 3.759 7.352 ↑ 1.0 6,259 1

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

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

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

32. 4.496 90.075 ↓ 1.0 4,013 1

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

  • Sort Key: dj.theater_id
  • Sort Method: quicksort Memory: 411kB
33. 4.001 85.579 ↓ 1.0 4,013 1

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

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

  • Filter: ((booking_date >= '2017-10-20'::date) AND (booking_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 784709
35. 0.033 0.062 ↑ 1.0 44 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
36. 0.029 0.029 ↑ 1.0 44 1

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

37. 6.099 16.467 ↓ 7.5 5,501 1

Sort (cost=4,298.92..4,300.75 rows=731 width=8) (actual time=13.909..16.467 rows=5,501 loops=1)

  • Sort Key: ta.theater_id
  • Sort Method: quicksort Memory: 457kB
38. 4.927 10.368 ↓ 8.2 6,018 1

Bitmap Heap Scan on theater_aliases ta (cost=2,222.25..4,264.15 rows=731 width=8) (actual time=5.547..10.368 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
39. 0.045 5.441 ↓ 0.0 0 1

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

40. 1.135 1.135 ↑ 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.135..1.135 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[]))
41. 4.261 4.261 ↑ 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.261..4.261 rows=35,309 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
42. 15.517 24.638 ↓ 1.0 14,783 1

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

  • Sort Key: id.theater_id
  • Sort Method: quicksort Memory: 1034kB
43. 8.287 9.121 ↑ 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.881..9.121 rows=13,860 loops=1)

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

  • Index Cond: ((source_id)::text = 'BOE'::text)
45. 0.054 9.967 ↑ 5.4 46 1

Sort (cost=14.89..15.51 rows=249 width=50) (actual time=9.946..9.967 rows=46 loops=1)

  • Sort Key: ct.theater_id
  • Sort Method: quicksort Memory: 28kB
46. 9.913 9.913 ↑ 6.4 39 1

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

47. 0.085 1.707 ↓ 1.7 105 1

Sort (cost=332.37..332.53 rows=62 width=28) (actual time=1.660..1.707 rows=105 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 29kB
48. 1.622 1.622 ↑ 1.2 53 1

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

  • Filter: ((ignored_date >= '2017-10-20'::date) AND (ignored_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 13382
49. 10.480 10.480 ↑ 1.0 1 5,240

Index Scan using pk_states on states s (cost=0.14..0.16 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=5,240)

  • Index Cond: (((id)::text = (th.state_id)::text) AND ((country_id)::text = (th.country_id)::text) AND ((country_id)::text = 'US'::text))
50. 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 : 6.223 ms
Execution time : 316.203 ms