explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A2VK

Settings
# exclusive inclusive rows x rows loops node
1. 80.078 1,060.887 ↑ 1.1 5,427 1

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

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.488 11.307 ↑ 6.0 39 1

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

  • Group Key: ct_1.theater_id
4. 0.173 10.819 ↑ 12.5 273 1

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

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

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

  • Hash Cond: (ct_1.user_id = us.id)
6. 8.790 8.790 ↓ 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.446..8.790 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.032 1.714 ↑ 1.0 2,705 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
8. 0.682 0.682 ↑ 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.682 rows=2,705 loops=1)

9. 5.901 980.809 ↑ 10.0 8,342 1

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

  • Join Filter: false
10. 5.421 974.908 ↑ 10.0 8,342 1

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

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

Nested Loop Left Join (cost=85,117.66..136,941.16 rows=6,161 width=1,322) (actual time=759.430..964.818 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. 7.521 885.745 ↓ 1.3 8,281 1

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

13. 3.736 778.852 ↓ 1.4 8,281 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Bitmap Heap Scan on theaters th (cost=229.66..62,516.45 rows=6,115 width=93) (actual time=6.922..559.618 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. 5.584 5.584 ↓ 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=5.584..5.584 rows=12,364 loops=1)

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

SubPlan (for Bitmap Heap Scan)

25. 494.560 494.560 ↓ 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.040..0.040 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. 1.903 3.639 ↑ 1.0 6,047 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 432kB
27. 1.736 1.736 ↑ 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.736 rows=6,047 loops=1)

28. 2.437 140.547 ↓ 1.2 4,013 1

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

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

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

  • Hash Cond: (dj.justification_id = (ju.id)::text)
30. 136.171 136.171 ↓ 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.003..136.171 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.027 0.055 ↑ 1.0 44 1

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

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

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

33. 3.968 19.635 ↓ 7.5 5,332 1

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

  • Sort Key: ta.theater_id
  • Sort Method: quicksort Memory: 503kB
34. 6.574 15.667 ↓ 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.227..15.667 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.104 9.093 ↓ 0.0 0 1

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

36. 2.925 2.925 ↑ 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=2.924..2.925 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.064 6.064 ↑ 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.064..6.064 rows=34,997 loops=1)

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

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

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

CTE Scan on contacted_theaters_cte ct (cost=0.00..4.68 rows=234 width=50) (actual time=10.861..11.345 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.020..0.039 rows=50 loops=1)

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

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

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

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

  • Hash Cond: (mg.user_id = nu.id)
45. 2.835 2.835 ↑ 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.784..2.835 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.002 1.716 ↑ 1.0 2,705 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 209kB
47. 0.714 0.714 ↑ 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..0.714 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