explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bk6b

Settings
# exclusive inclusive rows x rows loops node
1. 343.182 5,102.054 ↑ 1.1 5,416 1

GroupAggregate (cost=111,465.27..163,968.19 rows=5,995 width=541) (actual time=3,683.442..5,102.054 rows=5,416 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 390.447 406.922 ↑ 3.0 113 1

GroupAggregate (cost=1,535.58..1,547.40 rows=339 width=50) (actual time=20.100..406.922 rows=113 loops=1)

  • Group Key: ct_1.theater_id
4. 0.367 16.475 ↓ 1.0 382 1

Sort (cost=1,535.58..1,536.53 rows=379 width=173) (actual time=16.397..16.475 rows=382 loops=1)

  • Sort Key: ct_1.theater_id
  • Sort Method: quicksort Memory: 126kB
5. 0.202 16.108 ↓ 1.0 382 1

Hash Join (cost=534.95..1,519.35 rows=379 width=173) (actual time=12.828..16.108 rows=382 loops=1)

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

Seq Scan on data_recovery_contacted_theaters ct_1 (cost=0.00..983.40 rows=379 width=26) (actual time=8.633..11.735 rows=382 loops=1)

  • Filter: ((contacted_date >= '2018-07-18'::date) AND (contacted_date <= '2018-07-25'::date))
  • Rows Removed by Filter: 39972
7. 1.368 4.171 ↑ 1.0 2,709 1

Hash (cost=501.09..501.09 rows=2,709 width=163) (actual time=4.171..4.171 rows=2,709 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 556kB
8. 2.803 2.803 ↑ 1.0 2,709 1

Seq Scan on users us (cost=0.00..501.09 rows=2,709 width=163) (actual time=0.031..2.803 rows=2,709 loops=1)

9. 103.601 4,758.872 ↓ 2.1 12,610 1

Nested Loop Left Join (cost=109,917.88..161,731.37 rows=5,995 width=1,490) (actual time=3,682.927..4,758.872 rows=12,610 loops=1)

  • Join Filter: (((s.country_id)::text = (th.country_id)::text) AND ((s.id)::text = (th.state_id)::text))
  • Rows Removed by Join Filter: 331052
10. 10.932 4,630.051 ↓ 2.1 12,610 1

Nested Loop Left Join (cost=109,917.88..161,624.83 rows=5,995 width=1,376) (actual time=3,682.440..4,630.051 rows=12,610 loops=1)

  • Join Filter: false
11. 8.809 4,619.119 ↓ 2.1 12,610 1

Merge Left Join (cost=109,917.88..161,564.88 rows=5,995 width=1,376) (actual time=3,682.437..4,619.119 rows=12,610 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
12. 20.381 4,603.482 ↓ 2.1 12,548 1

Nested Loop Left Join (cost=109,363.05..160,994.84 rows=5,995 width=1,209) (actual time=3,675.681..4,603.482 rows=12,548 loops=1)

13. 10.628 4,520.361 ↓ 2.1 12,548 1

Merge Left Join (cost=109,362.91..160,044.13 rows=5,995 width=179) (actual time=3,675.672..4,520.361 rows=12,548 loops=1)

  • Merge Cond: (th.id = ct.theater_id)
14. 19.448 4,102.439 ↓ 2.1 12,548 1

Nested Loop Left Join (cost=109,341.88..160,006.25 rows=5,995 width=137) (actual time=3,268.421..4,102.439 rows=12,548 loops=1)

15. 18.623 3,292.467 ↓ 2.1 12,548 1

Merge Left Join (cost=109,341.32..109,375.18 rows=5,995 width=135) (actual time=3,268.147..3,292.467 rows=12,548 loops=1)

  • Merge Cond: (th.id = ta.theater_id)
16. 16.389 3,200.420 ↓ 2.0 11,961 1

Sort (cost=106,202.98..106,217.97 rows=5,995 width=128) (actual time=3,197.000..3,200.420 rows=11,961 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 3032kB
17. 6.293 3,184.031 ↓ 2.0 11,961 1

Hash Left Join (cost=91,750.71..105,826.81 rows=5,995 width=128) (actual time=3,090.573..3,184.031 rows=11,961 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
18. 8.488 3,171.355 ↓ 2.0 11,961 1

Hash Right Join (cost=91,567.65..105,628.00 rows=5,995 width=107) (actual time=3,084.120..3,171.355 rows=11,961 loops=1)

  • Hash Cond: (dj.theater_id = th.id)
19. 159.860 159.860 ↓ 1.1 8,502 1

Seq Scan on data_recovery_justifications dj (cost=0.00..14,025.92 rows=8,084 width=14) (actual time=81.051..159.860 rows=8,502 loops=1)

  • Filter: ((booking_date >= '2018-07-18'::date) AND (booking_date <= '2018-07-25'::date))
  • Rows Removed by Filter: 641293
20. 4.218 3,003.007 ↑ 1.1 5,416 1

Hash (cost=91,492.71..91,492.71 rows=5,995 width=101) (actual time=3,003.006..3,003.007 rows=5,416 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 718kB
21. 8.179 2,998.789 ↑ 1.1 5,416 1

Hash Right Join (cost=87,224.76..91,492.71 rows=5,995 width=101) (actual time=2,962.679..2,998.789 rows=5,416 loops=1)

  • Hash Cond: (id.theater_id = th.id)
22. 28.217 31.762 ↓ 1.0 13,808 1

Bitmap Heap Scan on theater_aliases id (cost=279.90..4,512.80 rows=13,352 width=15) (actual time=3.776..31.762 rows=13,808 loops=1)

  • Recheck Cond: ((source_id)::text = 'BOE'::text)
  • Heap Blocks: exact=347
23. 3.545 3.545 ↓ 1.0 13,808 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..276.56 rows=13,352 width=0) (actual time=3.545..3.545 rows=13,808 loops=1)

  • Index Cond: ((source_id)::text = 'BOE'::text)
24. 9.691 2,958.848 ↑ 1.1 5,416 1

Hash (cost=86,869.93..86,869.93 rows=5,995 width=94) (actual time=2,958.848..2,958.848 rows=5,416 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 688kB
25. 235.173 2,949.157 ↑ 1.1 5,416 1

Bitmap Heap Scan on theaters th (cost=743.85..86,869.93 rows=5,995 width=94) (actual time=8.012..2,949.157 rows=5,416 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 6948
  • Heap Blocks: exact=3808
26. 6.268 6.268 ↓ 1.0 12,364 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..742.35 rows=11,991 width=0) (actual time=6.268..6.268 rows=12,364 loops=1)

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

SubPlan (for Bitmap Heap Scan)

28. 2,707.716 2,707.716 ↓ 0.0 0 12,364

Index Only Scan using ix_theater_properties_is_reporting on theater_properties (cost=0.56..57.93 rows=13 width=0) (actual time=0.219..0.219 rows=0 loops=12,364)

  • Index Cond: ((theater_id = th.id) AND (as_of_date >= '2017-07-19'::date) AND (is_reporting = true))
  • Filter: is_reporting
  • Heap Fetches: 5416
29. 2.751 6.383 ↑ 1.0 6,047 1

Hash (cost=107.47..107.47 rows=6,047 width=29) (actual time=6.383..6.383 rows=6,047 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 432kB
30. 3.632 3.632 ↑ 1.0 6,047 1

Seq Scan on circuits ci (cost=0.00..107.47 rows=6,047 width=29) (actual time=0.505..3.632 rows=6,047 loops=1)

31. 6.066 73.424 ↓ 13.0 9,203 1

Sort (cost=3,138.34..3,140.11 rows=708 width=15) (actual time=71.139..73.424 rows=9,203 loops=1)

  • Sort Key: ta.theater_id
  • Sort Method: quicksort Memory: 503kB
32. 50.323 67.358 ↓ 8.1 5,748 1

Bitmap Heap Scan on theater_aliases ta (cost=1,197.79..3,104.83 rows=708 width=15) (actual time=17.173..67.358 rows=5,748 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=815
33. 0.108 17.035 ↓ 0.0 0 1

BitmapAnd (cost=1,197.79..1,197.79 rows=708 width=0) (actual time=17.035..17.035 rows=0 loops=1)

34. 9.131 9.131 ↑ 1.2 6,266 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..413.23 rows=7,251 width=0) (actual time=9.131..9.131 rows=6,266 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[]))
35. 7.796 7.796 ↑ 1.0 35,002 1

Bitmap Index Scan on ix_theater_aliases_source_type (cost=0.00..783.96 rows=35,138 width=0) (actual time=7.796..7.796 rows=35,002 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
36. 790.524 790.524 ↑ 1.0 1 12,548

Index Scan using ix_theater_properties_is_reporting on theater_properties pr (cost=0.56..8.45 rows=1 width=10) (actual time=0.063..0.063 rows=1 loops=12,548)

  • Index Cond: ((theater_id = th.id) AND (as_of_date = '2018-07-18'::date))
37. 0.155 407.294 ↑ 1.4 241 1

Sort (cost=21.03..21.87 rows=339 width=50) (actual time=407.244..407.294 rows=241 loops=1)

  • Sort Key: ct.theater_id
  • Sort Method: quicksort Memory: 33kB
38. 407.139 407.139 ↑ 3.0 113 1

CTE Scan on contacted_theaters_cte ct (cost=0.00..6.78 rows=339 width=50) (actual time=20.108..407.139 rows=113 loops=1)

39. 62.740 62.740 ↑ 1.0 1 12,548

Index Scan using pk_justifications on justifications ju (cost=0.14..0.16 rows=1 width=1,032) (actual time=0.005..0.005 rows=1 loops=12,548)

  • Index Cond: ((id)::text = dj.justification_id)
40. 0.135 6.828 ↓ 4.8 196 1

Sort (cost=554.82..554.93 rows=41 width=175) (actual time=6.751..6.828 rows=196 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 38kB
41. 0.073 6.693 ↓ 1.2 51 1

Nested Loop Left Join (cost=0.28..553.73 rows=41 width=175) (actual time=6.345..6.693 rows=51 loops=1)

42. 6.110 6.110 ↓ 1.2 51 1

Seq Scan on data_recovery_mg_ignored_dates mg (cost=0.00..313.52 rows=41 width=28) (actual time=5.932..6.110 rows=51 loops=1)

  • Filter: ((ignored_date >= '2018-07-18'::date) AND (ignored_date <= '2018-07-25'::date))
  • Rows Removed by Filter: 13384
43. 0.510 0.510 ↑ 1.0 1 51

Index Scan using pk_users on users nu (cost=0.28..5.86 rows=1 width=163) (actual time=0.010..0.010 rows=1 loops=51)

  • Index Cond: (id = mg.user_id)
44. 0.000 0.000 ↓ 0.0 0 12,610

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=12,610)

  • One-Time Filter: false
45. 24.757 25.220 ↓ 27.0 27 12,610

Materialize (cost=0.00..1.63 rows=1 width=154) (actual time=0.000..0.002 rows=27 loops=12,610)

46. 0.463 0.463 ↓ 50.0 50 1

Seq Scan on states s (cost=0.00..1.62 rows=1 width=154) (actual time=0.443..0.463 rows=50 loops=1)

  • Filter: ((country_id)::text = 'US'::text)
Planning time : 49.147 ms
Execution time : 5,156.015 ms