explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SL3W

Settings
# exclusive inclusive rows x rows loops node
1. 97.522 1,341.444 ↑ 13.9 4,491 1

GroupAggregate (cost=644,287.46..733,858.31 rows=62,240 width=541) (actual time=1,033.158..1,341.444 rows=4,491 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.605 13.793 ↑ 3.8 38 1

GroupAggregate (cost=1,248.01..1,280.23 rows=143 width=50) (actual time=13.467..13.793 rows=38 loops=1)

  • Group Key: ct_1.theater_id
4. 0.119 13.188 ↑ 11.9 170 1

Sort (cost=1,248.01..1,253.08 rows=2,029 width=82) (actual time=13.176..13.188 rows=170 loops=1)

  • Sort Key: ct_1.theater_id
  • Sort Method: quicksort Memory: 38kB
5. 0.136 13.069 ↑ 11.9 170 1

Hash Join (cost=81.86..1,136.55 rows=2,029 width=82) (actual time=10.238..13.069 rows=170 loops=1)

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

Seq Scan on data_recovery_contacted_theaters ct_1 (cost=0.00..983.40 rows=150 width=26) (actual time=8.534..11.257 rows=170 loops=1)

  • Filter: ((contacted_date >= '2018-09-05'::date) AND (contacted_date <= '2018-09-11'::date))
  • Rows Removed by Filter: 40190
7. 0.989 1.676 ↑ 1.0 2,705 1

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

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

9. 9.256 1,243.922 ↑ 57.0 14,776 1

Nested Loop Left Join (cost=643,007.23..653,622.75 rows=841,796 width=1,398) (actual time=1,033.070..1,243.922 rows=14,776 loops=1)

  • Join Filter: false
10. 8.027 1,234.666 ↑ 57.0 14,776 1

Merge Left Join (cost=643,007.23..645,204.79 rows=841,796 width=1,398) (actual time=1,033.065..1,234.666 rows=14,776 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
11. 114.612 1,220.669 ↑ 4.2 14,702 1

Nested Loop Left Join (cost=642,541.63..644,578.96 rows=62,240 width=1,322) (actual time=1,027.124..1,220.669 rows=14,702 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: 398229
12. 6.572 1,076.653 ↑ 4.2 14,702 1

Merge Left Join (cost=642,541.63..643,488.13 rows=62,240 width=1,208) (actual time=1,027.072..1,076.653 rows=14,702 loops=1)

  • Merge Cond: (th.id = ct.theater_id)
13. 12.469 1,056.197 ↑ 4.2 14,702 1

Merge Left Join (cost=642,533.65..643,323.77 rows=62,240 width=1,166) (actual time=1,013.200..1,056.197 rows=14,702 loops=1)

  • Merge Cond: (th.id = id.theater_id)
14. 9.907 1,023.831 ↑ 4.2 14,702 1

Merge Left Join (cost=637,105.92..637,667.07 rows=62,240 width=1,159) (actual time=995.803..1,023.831 rows=14,702 loops=1)

  • Merge Cond: (th.id = ta.theater_id)
15. 10.468 980.840 ↑ 4.4 14,092 1

Merge Left Join (cost=633,967.58..634,369.25 rows=62,240 width=1,152) (actual time=964.372..980.840 rows=14,092 loops=1)

  • Merge Cond: (th.id = dj.theater_id)
16. 8.261 776.850 ↑ 13.9 4,491 1

Sort (cost=613,978.17..614,133.77 rows=62,240 width=116) (actual time=775.895..776.850 rows=4,491 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 1016kB
17. 4.171 768.589 ↑ 13.9 4,491 1

Hash Left Join (cost=3,777.83..605,189.63 rows=62,240 width=116) (actual time=356.350..768.589 rows=4,491 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
18. 5.279 759.616 ↑ 13.9 4,491 1

Hash Left Join (cost=3,594.77..604,843.09 rows=62,240 width=95) (actual time=351.511..759.616 rows=4,491 loops=1)

  • Hash Cond: (th.id = pr.theater_id)
19. 483.803 724.645 ↑ 13.9 4,491 1

Bitmap Heap Scan on theaters th (cost=1,401.60..602,486.53 rows=62,240 width=93) (actual time=321.749..724.645 rows=4,491 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (alternatives: SubPlan 2 or hashed SubPlan 3)
  • Rows Removed by Filter: 7873
  • Heap Blocks: exact=24463
20. 77.766 77.766 ↓ 1.7 208,958 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..1,386.04 rows=124,481 width=0) (actual time=77.766..77.766 rows=208,958 loops=1)

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

SubPlan (for Bitmap Heap Scan)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using uq_theater_properties_20180901_1 on theater_properties_20180901 (cost=0.42..96.60 rows=23 width=0) (never executed)

  • Index Cond: ((theater_id = th.id) AND (as_of_date >= ('2018-09-05'::date - ('180 DAYS'::cstring)::interval)))
  • Filter: is_reporting
23. 163.076 163.076 ↑ 1.0 113,761 1

Seq Scan on theater_properties_20180901 theater_properties_20180901_1 (cost=0.00..3,000.22 rows=113,761 width=8) (actual time=0.144..163.076 rows=113,761 loops=1)

  • Filter: (is_reporting AND (as_of_date >= ('2018-09-05'::date - ('180 DAYS'::cstring)::interval)))
24. 1.496 29.692 ↑ 1.0 3,591 1

Hash (cost=2,147.01..2,147.01 rows=3,693 width=10) (actual time=29.692..29.692 rows=3,591 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 201kB
25. 28.196 28.196 ↑ 1.0 3,591 1

Seq Scan on theater_properties_20180901 pr (cost=0.00..2,147.01 rows=3,693 width=10) (actual time=2.340..28.196 rows=3,591 loops=1)

  • Filter: (as_of_date = '2018-09-05'::date)
  • Rows Removed by Filter: 110170
26. 2.390 4.802 ↑ 1.0 6,047 1

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

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

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

28. 3.144 193.522 ↓ 1.1 12,350 1

Materialize (cost=19,989.41..20,045.83 rows=11,284 width=1,044) (actual time=188.465..193.522 rows=12,350 loops=1)

29. 9.619 190.378 ↓ 1.1 12,350 1

Sort (cost=19,989.41..20,017.62 rows=11,284 width=1,044) (actual time=188.441..190.378 rows=12,350 loops=1)

  • Sort Key: dj.theater_id
  • Sort Method: quicksort Memory: 1350kB
30. 6.186 180.759 ↓ 1.1 12,350 1

Hash Left Join (cost=1.99..14,060.38 rows=11,284 width=1,044) (actual time=105.378..180.759 rows=12,350 loops=1)

  • Hash Cond: (dj.justification_id = (ju.id)::text)
31. 174.523 174.523 ↓ 1.1 12,350 1

Seq Scan on data_recovery_justifications dj (cost=0.00..14,025.92 rows=11,284 width=14) (actual time=105.293..174.523 rows=12,350 loops=1)

  • Filter: ((booking_date >= '2018-09-05'::date) AND (booking_date <= '2018-09-11'::date))
  • Rows Removed by Filter: 637445
32. 0.022 0.050 ↑ 1.0 44 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
33. 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)

34. 5.315 33.084 ↓ 17.8 12,583 1

Sort (cost=3,138.34..3,140.11 rows=708 width=15) (actual time=31.424..33.084 rows=12,583 loops=1)

  • Sort Key: ta.theater_id
  • Sort Method: quicksort Memory: 503kB
35. 13.699 27.769 ↓ 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=14.272..27.769 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
36. 0.160 14.070 ↓ 0.0 0 1

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

37. 6.686 6.686 ↑ 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=6.686..6.686 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[]))
38. 7.224 7.224 ↑ 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=7.224..7.224 rows=34,997 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
39. 10.865 19.897 ↓ 1.8 23,919 1

Sort (cost=5,427.73..5,461.11 rows=13,352 width=15) (actual time=17.388..19.897 rows=23,919 loops=1)

  • Sort Key: id.theater_id
  • Sort Method: quicksort Memory: 1032kB
40. 6.877 9.032 ↓ 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.237..9.032 rows=13,808 loops=1)

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

  • Index Cond: ((source_id)::text = 'BOE'::text)
42. 0.053 13.884 ↑ 1.5 97 1

Sort (cost=7.98..8.34 rows=143 width=50) (actual time=13.867..13.884 rows=97 loops=1)

  • Sort Key: ct.theater_id
  • Sort Method: quicksort Memory: 27kB
43. 13.831 13.831 ↑ 3.8 38 1

CTE Scan on contacted_theaters_cte ct (cost=0.00..2.86 rows=143 width=50) (actual time=13.472..13.831 rows=38 loops=1)

44. 29.362 29.404 ↓ 28.0 28 14,702

Materialize (cost=0.00..1.63 rows=1 width=154) (actual time=0.000..0.002 rows=28 loops=14,702)

45. 0.042 0.042 ↓ 50.0 50 1

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

  • Filter: ((country_id)::text = 'US'::text)
46. 0.087 5.970 ↑ 4.0 212 1

Sort (cost=465.60..467.70 rows=839 width=84) (actual time=5.936..5.970 rows=212 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 33kB
47. 0.079 5.883 ↑ 13.3 63 1

Hash Left Join (cost=81.86..424.86 rows=839 width=84) (actual time=5.736..5.883 rows=63 loops=1)

  • Hash Cond: (mg.user_id = nu.id)
48. 3.612 3.612 ↓ 1.0 63 1

Seq Scan on data_recovery_mg_ignored_dates mg (cost=0.00..313.52 rows=62 width=28) (actual time=3.492..3.612 rows=63 loops=1)

  • Filter: ((ignored_date >= '2018-09-05'::date) AND (ignored_date <= '2018-09-11'::date))
  • Rows Removed by Filter: 13372
49. 1.310 2.192 ↑ 1.0 2,705 1

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

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

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

51. 0.000 0.000 ↓ 0.0 0 14,776

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=14,776)

  • One-Time Filter: false
Planning time : 41.663 ms
Execution time : 1,364.524 ms