explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YHwVN

Settings
# exclusive inclusive rows x rows loops node
1. 21.187 3,324.843 ↑ 89.4 5,505 1

GroupAggregate (cost=4,083,945.99..4,701,796.22 rows=492,360 width=508) (actual time=3,299.477..3,324.843 rows=5,505 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.777 11.216 ↑ 6.4 39 1

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

  • Group Key: ct_1.theater_id
4. 0.326 10.439 ↑ 13.9 273 1

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

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

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

  • Hash Cond: (ct_1.user_id = us.id)
6. 6.627 6.627 ↓ 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.289..6.627 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.698 3.189 ↑ 1.0 2,834 1

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

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

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

9. 13.060 3,303.656 ↑ 823.3 8,474 1

Sort (cost=4,082,260.18..4,099,702.03 rows=6,976,741 width=270) (actual time=3,299.455..3,303.656 rows=8,474 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 2570kB
10. 15.055 3,290.596 ↑ 823.3 8,474 1

Nested Loop Left Join (cost=2,231,231.19..2,406,894.78 rows=6,976,741 width=270) (actual time=3,254.247..3,290.596 rows=8,474 loops=1)

  • Join Filter: false
11. 9.474 3,275.541 ↑ 823.3 8,474 1

Merge Right Join (cost=2,231,231.19..2,337,127.37 rows=6,976,741 width=270) (actual time=3,254.241..3,275.541 rows=8,474 loops=1)

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

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

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

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

14. 7.740 3,261.907 ↑ 58.1 8,474 1

Materialize (cost=2,231,018.33..2,233,480.13 rows=492,360 width=214) (actual time=3,250.299..3,261.907 rows=8,474 loops=1)

15. 13.341 3,254.167 ↑ 58.1 8,474 1

Sort (cost=2,231,018.33..2,232,249.23 rows=492,360 width=214) (actual time=3,250.294..3,254.167 rows=8,474 loops=1)

  • Sort Key: mg.user_id
  • Sort Method: quicksort Memory: 2580kB
16. 9.345 3,240.826 ↑ 58.1 8,474 1

Hash Left Join (cost=2,103,195.68..2,133,979.79 rows=492,360 width=214) (actual time=3,145.464..3,240.826 rows=8,474 loops=1)

  • Hash Cond: (((th.country_id)::text = (s.country_id)::text) AND ((th.state_id)::text = (s.id)::text))
17. 9.094 3,231.354 ↑ 58.1 8,474 1

Hash Left Join (cost=2,103,193.31..2,126,477.92 rows=492,360 width=205) (actual time=3,145.317..3,231.354 rows=8,474 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
18. 12.960 3,212.758 ↑ 58.1 8,474 1

Hash Right Join (cost=2,103,001.48..2,120,070.74 rows=492,360 width=184) (actual time=3,135.761..3,212.758 rows=8,474 loops=1)

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
22. 0.032 0.032 ↑ 1.0 44 1

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

23. 5.614 3,115.052 ↑ 83.6 5,891 1

Hash (cost=2,096,844.99..2,096,844.99 rows=492,360 width=162) (actual time=3,115.052..3,115.052 rows=5,891 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4857kB
24. 17.115 3,109.438 ↑ 83.6 5,891 1

Hash Right Join (cost=2,091,749.52..2,096,844.99 rows=492,360 width=162) (actual time=3,086.481..3,109.438 rows=5,891 loops=1)

  • Hash Cond: (id.theater_id = th.id)
25. 8.473 9.375 ↑ 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.951..9.375 rows=13,860 loops=1)

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

  • Index Cond: ((source_id)::text = 'BOE'::text)
27. 5.230 3,082.948 ↑ 83.6 5,891 1

Hash (cost=2,085,109.27..2,085,109.27 rows=492,360 width=155) (actual time=3,082.948..3,082.948 rows=5,891 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4824kB
28. 11.130 3,077.718 ↑ 83.6 5,891 1

Hash Right Join (cost=2,083,064.31..2,085,109.27 rows=492,360 width=155) (actual time=3,062.802..3,077.718 rows=5,891 loops=1)

  • Hash Cond: (ta.theater_id = th.id)
29. 6.326 12.115 ↓ 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.891..12.115 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.046 5.789 ↓ 0.0 0 1

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

31. 1.469 1.469 ↑ 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.469..1.469 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. 4.274 4.274 ↑ 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.274..4.274 rows=35,309 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
33. 5.019 3,054.473 ↑ 88.9 5,537 1

Hash (cost=2,074,687.56..2,074,687.56 rows=492,360 width=155) (actual time=3,054.473..3,054.473 rows=5,537 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4779kB
34. 7.394 3,049.454 ↑ 88.9 5,537 1

Hash Right Join (cost=2,074,681.54..2,074,687.56 rows=492,360 width=155) (actual time=3,043.868..3,049.454 rows=5,537 loops=1)

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

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

36. 5.098 3,030.790 ↑ 88.9 5,537 1

Hash (cost=2,068,527.04..2,068,527.04 rows=492,360 width=113) (actual time=3,030.790..3,030.790 rows=5,537 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4777kB
37. 8.060 3,025.692 ↑ 88.9 5,537 1

Hash Right Join (cost=2,068,195.73..2,068,527.04 rows=492,360 width=113) (actual time=3,019.466..3,025.692 rows=5,537 loops=1)

  • Hash Cond: (mg.theater_id = th.id)
38. 1.918 1.918 ↑ 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.191..1.918 rows=53 loops=1)

  • Filter: ((ignored_date >= '2017-10-20'::date) AND (ignored_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 13382
39. 8.590 3,015.714 ↑ 89.4 5,505 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 4730kB
40. 48.550 3,007.124 ↑ 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=5.871..3,007.124 rows=5,505 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 6909
  • Heap Blocks: exact=3724
41. 4.042 4.042 ↑ 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=4.042..4.042 rows=12,666 loops=1)

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

SubPlan (for Bitmap Heap Scan)

43. 2,954.532 2,954.532 ↓ 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.238..0.238 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: 1747
44. 3.772 9.502 ↑ 1.0 6,259 1

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

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

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

46. 0.039 0.127 ↑ 1.0 50 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
47. 0.088 0.088 ↑ 1.0 50 1

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

  • Filter: ((country_id)::text = 'US'::text)
48. 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 : 6.400 ms
Execution time : 3,337.413 ms