explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eiKP

Settings
# exclusive inclusive rows x rows loops node
1. 35.874 11,301.426 ↑ 119.2 5,503 1

GroupAggregate (cost=4,112,124.24..5,079,395.19 rows=655,807 width=541) (actual time=11,234.832..11,301.426 rows=5,503 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.374 8.996 ↑ 6.4 39 1

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

  • Group Key: ct_1.theater_id
4. 0.267 8.622 ↑ 13.9 273 1

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

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

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

  • Hash Cond: (ct_1.user_id = us.id)
6. 5.073 5.073 ↓ 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.214..5.073 rows=273 loops=1)

  • Filter: ((contacted_date >= '2017-10-20'::date) AND (contacted_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 47,822
7. 1.607 2.981 ↑ 1.0 2,834 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 218kB
8. 1.374 1.374 ↑ 1.0 2,834 1

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

9. 15.855 11,265.552 ↑ 1,096.9 8,472 1

Nested Loop Left Join (cost=4,110,438.43..4,206,650.15 rows=9,292,785 width=281) (actual time=11,234.794..11,265.552 rows=8,472 loops=1)

  • Join Filter: false
10. 10.145 11,249.697 ↑ 1,096.9 8,472 1

Merge Left Join (cost=4,110,438.43..4,113,722.30 rows=9,292,785 width=281) (actual time=11,234.789..11,249.697 rows=8,472 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
11. 14.684 11,232.878 ↑ 78.0 8,411 1

Sort (cost=4,109,948.28..4,111,587.80 rows=655,807 width=205) (actual time=11,228.156..11,232.878 rows=8,411 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 2,598kB
12. 9.742 11,218.194 ↑ 78.0 8,411 1

Hash Left Join (cost=2,695,854.55..3,981,582.28 rows=655,807 width=205) (actual time=4,161.070..11,218.194 rows=8,411 loops=1)

  • Hash Cond: (((th.country_id)::text = (s.country_id)::text) AND ((th.state_id)::text = (s.id)::text))
13. 8.316 11,208.392 ↑ 78.0 8,411 1

Hash Left Join (cost=2,695,852.17..3,971,590.77 rows=655,807 width=196) (actual time=4,160.998..11,208.392 rows=8,411 loops=1)

  • Hash Cond: (th.id = ct.theater_id)
14. 92.600 11,191.015 ↑ 78.0 8,411 1

Hash Right Join (cost=2,695,844.08..3,949,449.07 rows=655,807 width=154) (actual time=4,151.920..11,191.015 rows=8,411 loops=1)

  • Hash Cond: (pr.theater_id = th.id)
15. 7,215.397 7,215.397 ↓ 3.5 107,568 1

Seq Scan on theater_properties pr (cost=0.00..1,239,081.95 rows=30,870 width=10) (actual time=262.563..7,215.397 rows=107,568 loops=1)

  • Filter: (as_of_date = '2017-10-20'::date)
  • Rows Removed by Filter: 35,631,593
16. 9.132 3,883.018 ↑ 78.0 8,411 1

Hash (cost=2,673,556.49..2,673,556.49 rows=655,807 width=152) (actual time=3,883.018..3,883.018 rows=8,411 loops=1)

  • Buckets: 1,048,576 Batches: 2 Memory Usage: 8,879kB
17. 8.956 3,873.886 ↑ 78.0 8,411 1

Hash Left Join (cost=2,637,266.40..2,673,556.49 rows=655,807 width=152) (actual time=3,756.108..3,873.886 rows=8,411 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
18. 23.520 3,857.684 ↑ 78.0 8,411 1

Hash Right Join (cost=2,637,074.58..2,665,086.02 rows=655,807 width=131) (actual time=3,748.816..3,857.684 rows=8,411 loops=1)

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

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

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

  • Filter: ((booking_date >= '2017-10-20'::date) AND (booking_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 784,709
21. 0.036 0.066 ↑ 1.0 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
22. 0.030 0.030 ↑ 1.0 44 1

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

23. 5.611 3,714.407 ↑ 112.1 5,849 1

Hash (cost=2,617,987.00..2,617,987.00 rows=655,807 width=109) (actual time=3,714.407..3,714.407 rows=5,849 loops=1)

  • Buckets: 1,048,576 Batches: 2 Memory Usage: 8,580kB
24. 19.524 3,708.796 ↑ 112.1 5,849 1

Hash Right Join (cost=2,612,890.83..2,617,987.00 rows=655,807 width=109) (actual time=3,686.265..3,708.796 rows=5,849 loops=1)

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

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

  • Index Cond: ((source_id)::text = 'BOE'::text)
27. 5.387 3,680.542 ↑ 112.1 5,849 1

Hash (cost=2,604,207.49..2,604,207.49 rows=655,807 width=102) (actual time=3,680.542..3,680.542 rows=5,849 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 8,906kB
28. 15.724 3,675.155 ↑ 112.1 5,849 1

Hash Right Join (cost=2,602,156.36..2,604,207.49 rows=655,807 width=102) (actual time=3,627.149..3,675.155 rows=5,849 loops=1)

  • Hash Cond: (ta.theater_id = th.id)
29. 37.354 237.174 ↓ 8.2 6,018 1

Bitmap Heap Scan on theater_aliases ta (cost=2,222.25..4,264.15 rows=731 width=15) (actual time=199.925..237.174 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=954
30. 0.057 199.820 ↓ 0.0 0 1

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

31. 34.647 34.647 ↑ 1.1 6,535 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..419.09 rows=7,502 width=0) (actual time=34.647..34.647 rows=6,535 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. 165.116 165.116 ↑ 1.0 35,307 1

Bitmap Index Scan on ix_theater_aliases_source_type (cost=0.00..1,802.54 rows=35,482 width=0) (actual time=165.116..165.116 rows=35,307 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
33. 9.129 3,422.257 ↑ 119.2 5,503 1

Hash (cost=2,591,736.53..2,591,736.53 rows=655,807 width=95) (actual time=3,422.257..3,422.257 rows=5,503 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 8,826kB
34. 45.525 3,413.128 ↑ 119.2 5,503 1

Bitmap Heap Scan on theaters th (cost=52,529.62..2,591,736.53 rows=655,807 width=95) (actual time=5.393..3,413.128 rows=5,503 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 6,911
  • Heap Blocks: exact=3,398
35. 3.409 3.409 ↑ 105.6 12,415 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..52,365.66 rows=1,311,614 width=0) (actual time=3.409..3.409 rows=12,415 loops=1)

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

SubPlan (for Bitmap Heap Scan)

37. 3,364.194 3,364.194 ↓ 0.0 0 12,414

Index Only Scan using ix_theater_properties_is_reporting on theater_properties (cost=0.57..33.08 rows=38 width=0) (actual time=0.271..0.271 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: 5,308
38. 3.734 7.246 ↑ 1.0 6,259 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 445kB
39. 3.512 3.512 ↑ 1.0 6,259 1

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

40. 0.023 9.061 ↑ 6.4 39 1

Hash (cost=4.98..4.98 rows=249 width=50) (actual time=9.061..9.061 rows=39 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
41. 9.038 9.038 ↑ 6.4 39 1

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

42. 0.029 0.060 ↑ 1.0 50 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
43. 0.031 0.031 ↑ 1.0 50 1

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

  • Filter: ((country_id)::text = 'US'::text)
44. 0.098 6.674 ↑ 8.4 105 1

Sort (cost=490.15..492.35 rows=879 width=84) (actual time=6.625..6.674 rows=105 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 31kB
45. 0.069 6.576 ↑ 16.6 53 1

Hash Left Join (cost=85.77..447.17 rows=879 width=84) (actual time=5.814..6.576 rows=53 loops=1)

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

Seq Scan on data_recovery_mg_ignored_dates mg (cost=0.00..330.52 rows=62 width=28) (actual time=2.723..3.437 rows=53 loops=1)

  • Filter: ((ignored_date >= '2017-10-20'::date) AND (ignored_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 13,382
47. 1.553 3.070 ↑ 1.0 2,834 1

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

  • Buckets: 4,096 Batches: 1 Memory Usage: 218kB
48. 1.517 1.517 ↑ 1.0 2,834 1

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

49. 0.000 0.000 ↓ 0.0 0 8,472

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

  • One-Time Filter: false