explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0bRS

Settings
# exclusive inclusive rows x rows loops node
1. 79.101 1,125.461 ↑ 1.1 5,427 1

GroupAggregate (cost=87,022.28..147,513.95 rows=6,115 width=541) (actual time=829.892..1,125.461 rows=5,427 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.578 16.252 ↑ 6.0 39 1

GroupAggregate (cost=1,384.98..1,439.03 rows=234 width=50) (actual time=15.777..16.252 rows=39 loops=1)

  • Group Key: ct_1.theater_id
4. 0.155 15.674 ↑ 12.5 273 1

Sort (cost=1,384.98..1,393.50 rows=3,408 width=82) (actual time=15.645..15.674 rows=273 loops=1)

  • Sort Key: ct_1.theater_id
  • Sort Method: quicksort Memory: 46kB
5. 0.189 15.519 ↑ 12.5 273 1

Hash Join (cost=81.86..1,185.02 rows=3,408 width=82) (actual time=2.371..15.519 rows=273 loops=1)

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

Seq Scan on data_recovery_contacted_theaters ct_1 (cost=0.00..983.40 rows=252 width=26) (actual time=0.456..13.433 rows=273 loops=1)

  • Filter: ((contacted_date >= '2017-10-20'::date) AND (contacted_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 40087
7. 1.073 1.897 ↑ 1.0 2,705 1

Hash (cost=48.05..48.05 rows=2,705 width=72) (actual time=1.897..1.897 rows=2,705 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
8. 0.824 0.824 ↑ 1.0 2,705 1

Seq Scan on decrypted_users us (cost=0.00..48.05 rows=2,705 width=72) (actual time=0.014..0.824 rows=2,705 loops=1)

9. 5.791 1,046.360 ↑ 10.0 8,342 1

Nested Loop Left Join (cost=85,583.26..138,260.07 rows=83,328 width=1,398) (actual time=829.728..1,046.360 rows=8,342 loops=1)

  • Join Filter: false
10. 5.336 1,040.569 ↑ 10.0 8,342 1

Merge Left Join (cost=85,583.26..137,426.79 rows=83,328 width=1,398) (actual time=829.724..1,040.569 rows=8,342 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
11. 61.063 1,027.689 ↓ 1.3 8,281 1

Nested Loop Left Join (cost=85,117.66..136,941.16 rows=6,161 width=1,322) (actual time=822.190..1,027.689 rows=8,281 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: 217444
12. 9.079 950.064 ↓ 1.3 8,281 1

Nested Loop Left Join (cost=85,117.66..136,831.71 rows=6,161 width=1,208) (actual time=822.136..950.064 rows=8,281 loops=1)

13. 3.925 841.613 ↓ 1.4 8,281 1

Merge Left Join (cost=85,117.09..85,202.44 rows=6,115 width=1,206) (actual time=822.104..841.613 rows=8,281 loops=1)

  • Merge Cond: (th.id = ct.theater_id)
14. 6.484 821.343 ↓ 1.4 8,281 1

Merge Left Join (cost=85,103.20..85,171.97 rows=6,115 width=1,164) (actual time=805.766..821.343 rows=8,281 loops=1)

  • Merge Cond: (th.id = ta.theater_id)
15. 6.422 789.728 ↓ 1.3 7,937 1

Merge Left Join (cost=81,964.86..82,014.46 rows=6,115 width=1,157) (actual time=781.862..789.728 rows=7,937 loops=1)

  • Merge Cond: (th.id = dj.theater_id)
16. 6.659 623.199 ↑ 1.1 5,427 1

Sort (cost=67,724.43..67,739.72 rows=6,115 width=121) (actual time=622.334..623.199 rows=5,427 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 1223kB
17. 3.278 616.540 ↑ 1.1 5,427 1

Hash Left Join (cost=63,055.84..67,339.85 rows=6,115 width=121) (actual time=600.113..616.540 rows=5,427 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
18. 7.743 609.317 ↑ 1.1 5,427 1

Hash Right Join (cost=62,872.78..67,140.73 rows=6,115 width=100) (actual time=596.108..609.317 rows=5,427 loops=1)

  • Hash Cond: (id.theater_id = th.id)
19. 5.593 7.694 ↓ 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=2.173..7.694 rows=13,808 loops=1)

  • Recheck Cond: ((source_id)::text = 'BOE'::text)
  • Heap Blocks: exact=347
20. 2.101 2.101 ↓ 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=2.101..2.101 rows=13,808 loops=1)

  • Index Cond: ((source_id)::text = 'BOE'::text)
21. 6.689 593.880 ↑ 1.1 5,427 1

Hash (cost=62,516.45..62,516.45 rows=6,115 width=93) (actual time=593.880..593.880 rows=5,427 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 689kB
22. 64.954 587.191 ↑ 1.1 5,427 1

Bitmap Heap Scan on theaters th (cost=229.66..62,516.45 rows=6,115 width=93) (actual time=3.550..587.191 rows=5,427 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 6937
  • Heap Blocks: exact=2276
23. 2.949 2.949 ↓ 1.0 12,364 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..228.13 rows=12,229 width=0) (actual time=2.949..2.949 rows=12,364 loops=1)

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

SubPlan (for Bitmap Heap Scan)

25. 519.288 519.288 ↓ 0.0 0 12,364

Index Only Scan using ix_theater_properties_is_reporting on theater_properties (cost=0.57..90.85 rows=21 width=0) (actual time=0.042..0.042 rows=0 loops=12,364)

  • 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: 5427
26. 2.199 3.945 ↑ 1.0 6,047 1

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

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

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

28. 2.757 160.107 ↓ 1.2 4,013 1

Sort (cost=14,240.43..14,249.05 rows=3,448 width=1,044) (actual time=159.521..160.107 rows=4,013 loops=1)

  • Sort Key: dj.theater_id
  • Sort Method: quicksort Memory: 411kB
29. 1.972 157.350 ↓ 1.2 4,013 1

Hash Left Join (cost=1.99..14,037.83 rows=3,448 width=1,044) (actual time=37.914..157.350 rows=4,013 loops=1)

  • Hash Cond: (dj.justification_id = (ju.id)::text)
30. 155.320 155.320 ↓ 1.2 4,013 1

Seq Scan on data_recovery_justifications dj (cost=0.00..14,025.92 rows=3,448 width=14) (actual time=37.822..155.320 rows=4,013 loops=1)

  • Filter: ((booking_date >= '2017-10-20'::date) AND (booking_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 645782
31. 0.024 0.058 ↑ 1.0 44 1

Hash (cost=1.44..1.44 rows=44 width=1,032) (actual time=0.058..0.058 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
32. 0.034 0.034 ↑ 1.0 44 1

Seq Scan on justifications ju (cost=0.00..1.44 rows=44 width=1,032) (actual time=0.022..0.034 rows=44 loops=1)

33. 5.282 25.131 ↓ 7.5 5,332 1

Sort (cost=3,138.34..3,140.11 rows=708 width=15) (actual time=23.898..25.131 rows=5,332 loops=1)

  • Sort Key: ta.theater_id
  • Sort Method: quicksort Memory: 503kB
34. 10.185 19.849 ↓ 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=9.807..19.849 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
35. 0.106 9.664 ↓ 0.0 0 1

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

36. 3.258 3.258 ↑ 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=3.258..3.258 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[]))
37. 6.300 6.300 ↑ 1.0 34,997 1

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

  • Index Cond: (source_type = 'EXT'::text)
38. 0.040 16.345 ↑ 5.1 46 1

Sort (cost=13.89..14.47 rows=234 width=50) (actual time=16.334..16.345 rows=46 loops=1)

  • Sort Key: ct.theater_id
  • Sort Method: quicksort Memory: 28kB
39. 16.305 16.305 ↑ 6.0 39 1

CTE Scan on contacted_theaters_cte ct (cost=0.00..4.68 rows=234 width=50) (actual time=15.785..16.305 rows=39 loops=1)

40. 99.372 99.372 ↑ 1.0 1 8,281

Index Scan using ix_theater_properties_is_reporting on theater_properties pr (cost=0.56..8.44 rows=1 width=10) (actual time=0.012..0.012 rows=1 loops=8,281)

  • Index Cond: ((theater_id = th.id) AND (as_of_date = '2017-10-20'::date))
41. 16.523 16.562 ↓ 27.0 27 8,281

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

42. 0.039 0.039 ↓ 50.0 50 1

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

  • Filter: ((country_id)::text = 'US'::text)
43. 0.144 7.544 ↑ 8.0 105 1

Sort (cost=465.60..467.70 rows=839 width=84) (actual time=7.529..7.544 rows=105 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 31kB
44. 0.098 7.400 ↑ 15.8 53 1

Hash Left Join (cost=81.86..424.86 rows=839 width=84) (actual time=4.908..7.400 rows=53 loops=1)

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

Seq Scan on data_recovery_mg_ignored_dates mg (cost=0.00..313.52 rows=62 width=28) (actual time=1.782..4.196 rows=53 loops=1)

  • Filter: ((ignored_date >= '2017-10-20'::date) AND (ignored_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 13382
46. 1.927 3.106 ↑ 1.0 2,705 1

Hash (cost=48.05..48.05 rows=2,705 width=72) (actual time=3.106..3.106 rows=2,705 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
47. 1.179 1.179 ↑ 1.0 2,705 1

Seq Scan on decrypted_users nu (cost=0.00..48.05 rows=2,705 width=72) (actual time=0.014..1.179 rows=2,705 loops=1)

48. 0.000 0.000 ↓ 0.0 0 8,342

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

  • One-Time Filter: false