explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mPOA

Settings
# exclusive inclusive rows x rows loops node
1. 21.166 3,037.177 ↑ 135.4 5,503 1

GroupAggregate (cost=5,973,690.83..6,908,379.47 rows=744,846 width=508) (actual time=3,012.402..3,037.177 rows=5,503 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.425 9.239 ↑ 6.4 39 1

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

  • Group Key: ct_1.theater_id
4. 0.264 8.814 ↑ 13.9 273 1

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

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

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

  • Hash Cond: (ct_1.user_id = us.id)
6. 5.342 5.342 ↓ 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.254..5.342 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.533 2.941 ↑ 1.0 2,834 1

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

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

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

9. 12.833 3,016.011 ↑ 1,245.8 8,472 1

Sort (cost=5,972,005.02..5,998,391.19 rows=10,554,468 width=270) (actual time=3,012.387..3,016.011 rows=8,472 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 2,570kB
10. 15.126 3,003.178 ↑ 1,245.8 8,472 1

Nested Loop Left Join (cost=3,140,246.57..3,405,984.56 rows=10,554,468 width=270) (actual time=2,966.956..3,003.178 rows=8,472 loops=1)

  • Join Filter: false
11. 9.708 2,988.052 ↑ 1,245.8 8,472 1

Merge Right Join (cost=3,140,246.57..3,300,439.88 rows=10,554,468 width=270) (actual time=2,966.952..2,988.052 rows=8,472 loops=1)

  • Merge Cond: (nu.id = mg.user_id)
12. 2.387 3.778 ↑ 6.0 472 1

Sort (cost=212.85..219.94 rows=2,834 width=72) (actual time=3.531..3.778 rows=472 loops=1)

  • Sort Key: nu.id
  • Sort Method: quicksort Memory: 323kB
13. 1.391 1.391 ↑ 1.0 2,834 1

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

14. 7.491 2,974.566 ↑ 87.9 8,472 1

Materialize (cost=3,140,033.72..3,143,757.95 rows=744,846 width=214) (actual time=2,963.399..2,974.566 rows=8,472 loops=1)

15. 12.350 2,967.075 ↑ 87.9 8,472 1

Sort (cost=3,140,033.72..3,141,895.84 rows=744,846 width=214) (actual time=2,963.395..2,967.075 rows=8,472 loops=1)

  • Sort Key: mg.user_id
  • Sort Method: quicksort Memory: 2,580kB
16. 9.983 2,954.725 ↑ 87.9 8,472 1

Hash Left Join (cost=2,958,985.06..2,991,009.72 rows=744,846 width=214) (actual time=2,866.314..2,954.725 rows=8,472 loops=1)

  • Hash Cond: (((th.country_id)::text = (s.country_id)::text) AND ((th.state_id)::text = (s.id)::text))
17. 9.644 2,944.680 ↑ 87.9 8,472 1

Hash Left Join (cost=2,958,982.68..2,979,663.71 rows=744,846 width=205) (actual time=2,866.235..2,944.680 rows=8,472 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
18. 8.446 2,928.110 ↑ 87.9 8,472 1

Merge Left Join (cost=2,958,790.85..2,970,069.25 rows=744,846 width=184) (actual time=2,859.260..2,928.110 rows=8,472 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
19. 7.954 2,915.511 ↑ 88.6 8,411 1

Merge Left Join (cost=2,958,458.48..2,967,874.42 rows=744,846 width=164) (actual time=2,855.147..2,915.511 rows=8,411 loops=1)

  • Merge Cond: (th.id = ct.theater_id)
20. 16.991 2,898.218 ↑ 88.6 8,411 1

Merge Left Join (cost=2,958,443.59..2,965,996.02 rows=744,846 width=122) (actual time=2,845.827..2,898.218 rows=8,411 loops=1)

  • Merge Cond: (th.id = id.theater_id)
21. 10.757 2,854.943 ↑ 88.6 8,411 1

Merge Left Join (cost=2,951,949.29..2,957,561.18 rows=744,846 width=115) (actual time=2,827.296..2,854.943 rows=8,411 loops=1)

  • Merge Cond: (th.id = ta.theater_id)
22. 9.525 2,511.220 ↑ 92.8 8,023 1

Merge Left Join (cost=2,947,650.36..2,951,396.07 rows=744,846 width=115) (actual time=2,496.933..2,511.220 rows=8,023 loops=1)

  • Merge Cond: (th.id = dj.theater_id)
23. 10.303 2,379.441 ↑ 135.4 5,503 1

Sort (cost=2,930,366.35..2,932,228.47 rows=744,846 width=93) (actual time=2,376.565..2,379.441 rows=5,503 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 974kB
24. 107.859 2,369.138 ↑ 135.4 5,503 1

Bitmap Heap Scan on theaters th (cost=61,979.47..2,857,719.35 rows=744,846 width=93) (actual time=35.937..2,369.138 rows=5,503 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 6,911
  • Heap Blocks: exact=25,651
25. 14.345 14.345 ↑ 12.3 120,664 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..61,793.26 rows=1,489,693 width=0) (actual time=14.345..14.345 rows=120,664 loops=1)

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

SubPlan (for Bitmap Heap Scan)

27. 2,246.934 2,246.934 ↓ 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.181..0.181 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,339
28. 4.632 122.254 ↓ 1.0 4,013 1

Sort (cost=17,284.00..17,293.61 rows=3,844 width=30) (actual time=120.361..122.254 rows=4,013 loops=1)

  • Sort Key: dj.theater_id
  • Sort Method: quicksort Memory: 411kB
29. 4.083 117.622 ↓ 1.0 4,013 1

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

  • Hash Cond: (dj.justification_id = (ju.id)::text)
30. 113.479 113.479 ↓ 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=25.966..113.479 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
31. 0.029 0.060 ↑ 1.0 44 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
32. 0.031 0.031 ↑ 1.0 44 1

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

33. 6.520 332.966 ↓ 7.5 5,493 1

Sort (cost=4,298.92..4,300.75 rows=731 width=8) (actual time=330.351..332.966 rows=5,493 loops=1)

  • Sort Key: ta.theater_id
  • Sort Method: quicksort Memory: 457kB
34. 116.072 326.446 ↓ 8.2 6,018 1

Bitmap Heap Scan on theater_aliases ta (cost=2,222.25..4,264.15 rows=731 width=8) (actual time=210.479..326.446 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=955
35. 0.055 210.374 ↓ 0.0 0 1

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

36. 46.173 46.173 ↑ 1.1 6,537 1

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

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

  • Index Cond: (source_type = 'EXT'::text)
38. 17.013 26.284 ↓ 1.2 16,756 1

Sort (cost=6,494.26..6,529.52 rows=14,106 width=15) (actual time=18.525..26.284 rows=16,756 loops=1)

  • Sort Key: id.theater_id
  • Sort Method: quicksort Memory: 1,034kB
39. 8.340 9.271 ↑ 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.976..9.271 rows=13,860 loops=1)

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

  • Index Cond: ((source_id)::text = 'BOE'::text)
41. 0.051 9.339 ↑ 5.4 46 1

Sort (cost=14.89..15.51 rows=249 width=50) (actual time=9.316..9.339 rows=46 loops=1)

  • Sort Key: ct.theater_id
  • Sort Method: quicksort Memory: 28kB
42. 9.288 9.288 ↑ 6.4 39 1

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

43. 0.074 4.153 ↓ 1.7 105 1

Sort (cost=332.37..332.53 rows=62 width=28) (actual time=4.109..4.153 rows=105 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 29kB
44. 4.079 4.079 ↑ 1.2 53 1

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

  • Filter: ((ignored_date >= '2017-10-20'::date) AND (ignored_date <= '2017-10-26'::date))
  • Rows Removed by Filter: 13,382
45. 3.502 6.926 ↑ 1.0 6,259 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 445kB
46. 3.424 3.424 ↑ 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.424 rows=6,259 loops=1)

47. 0.031 0.062 ↑ 1.0 50 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
48. 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.007..0.031 rows=50 loops=1)

  • Filter: ((country_id)::text = 'US'::text)
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
Planning time : 9.752 ms
Execution time : 3,046.903 ms