explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lkA6

Settings
# exclusive inclusive rows x rows loops node
1. 41.031 760.970 ↑ 1.8 3,249 1

GroupAggregate (cost=151,635.53..160,255.61 rows=5,995 width=541) (actual time=657.498..760.970 rows=3,249 loops=1)

  • Group Key: th.id
2.          

CTE contacted_theaters_cte

3. 0.546 13.213 ↑ 2.8 59 1

GroupAggregate (cost=1,283.75..1,321.76 rows=168 width=50) (actual time=12.769..13.213 rows=59 loops=1)

  • Group Key: ct_1.theater_id
4. 0.170 12.667 ↑ 11.4 210 1

Sort (cost=1,283.75..1,289.73 rows=2,394 width=82) (actual time=12.651..12.667 rows=210 loops=1)

  • Sort Key: ct_1.theater_id
  • Sort Method: quicksort Memory: 41kB
5. 0.140 12.497 ↑ 11.4 210 1

Hash Join (cost=81.86..1,149.38 rows=2,394 width=82) (actual time=2.215..12.497 rows=210 loops=1)

  • Hash Cond: (ct_1.user_id = us.id)
6. 10.658 10.658 ↓ 1.2 210 1

Seq Scan on data_recovery_contacted_theaters ct_1 (cost=0.00..983.40 rows=177 width=26) (actual time=0.499..10.658 rows=210 loops=1)

  • Filter: ((contacted_date >= '2018-01-15'::date) AND (contacted_date <= '2018-01-21'::date))
  • Rows Removed by Filter: 40150
7. 0.991 1.699 ↑ 1.0 2,705 1

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

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

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

9. 3.120 719.939 ↑ 15.6 5,204 1

Nested Loop Left Join (cost=150,313.78..151,328.85 rows=81,082 width=1,399) (actual time=657.421..719.939 rows=5,204 loops=1)

  • Join Filter: false
10. 2.686 716.819 ↑ 15.6 5,204 1

Merge Left Join (cost=150,313.78..150,518.03 rows=81,082 width=1,399) (actual time=657.419..716.819 rows=5,204 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
11. 35.213 708.636 ↑ 1.2 5,146 1

Nested Loop Left Join (cost=149,868.83..150,054.75 rows=5,995 width=1,323) (actual time=651.935..708.636 rows=5,146 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: 130055
12. 2.157 663.131 ↑ 1.2 5,146 1

Merge Left Join (cost=149,868.83..149,948.21 rows=5,995 width=1,209) (actual time=651.476..663.131 rows=5,146 loops=1)

  • Merge Cond: (th.id = ct.theater_id)
13. 3.898 647.651 ↑ 1.2 5,146 1

Merge Left Join (cost=149,859.26..149,922.72 rows=5,995 width=1,167) (actual time=638.165..647.651 rows=5,146 loops=1)

  • Merge Cond: (th.id = ta.theater_id)
14. 4.017 583.695 ↑ 1.2 4,851 1

Merge Left Join (cost=146,720.92..146,765.50 rows=5,995 width=1,160) (actual time=578.895..583.695 rows=4,851 loops=1)

  • Merge Cond: (th.id = dj.theater_id)
15. 3.769 435.404 ↑ 1.8 3,249 1

Sort (cost=132,534.57..132,549.56 rows=5,995 width=124) (actual time=434.985..435.404 rows=3,249 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 738kB
16. 1.871 431.635 ↑ 1.8 3,249 1

Hash Left Join (cost=127,852.93..132,158.40 rows=5,995 width=124) (actual time=408.947..431.635 rows=3,249 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
17. 5.831 425.043 ↑ 1.8 3,249 1

Hash Right Join (cost=127,669.87..131,959.59 rows=5,995 width=103) (actual time=404.155..425.043 rows=3,249 loops=1)

  • Hash Cond: (id.theater_id = th.id)
18. 15.658 19.301 ↓ 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=4.140..19.301 rows=13,808 loops=1)

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

  • Index Cond: ((source_id)::text = 'BOE'::text)
20. 2.390 399.911 ↑ 1.8 3,249 1

Hash (cost=127,315.04..127,315.04 rows=5,995 width=96) (actual time=399.911..399.911 rows=3,249 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 439kB
21. 2.578 397.521 ↑ 1.8 3,249 1

Hash Right Join (cost=125,454.46..127,315.04 rows=5,995 width=96) (actual time=373.913..397.521 rows=3,249 loops=1)

  • Hash Cond: (pr.theater_id = th.id)
22. 0.349 21.473 ↓ 1.0 3,174 1

Append (cost=0.00..1,852.60 rows=3,039 width=10) (actual time=0.200..21.473 rows=3,174 loops=1)

23. 21.124 21.124 ↓ 1.0 3,174 1

Seq Scan on theater_properties_20180101 pr (cost=0.00..1,852.60 rows=3,039 width=10) (actual time=0.200..21.124 rows=3,174 loops=1)

  • Filter: (as_of_date = '2018-01-15'::date)
  • Rows Removed by Filter: 94954
24. 3.867 373.470 ↑ 1.8 3,249 1

Hash (cost=125,379.52..125,379.52 rows=5,995 width=94) (actual time=373.470..373.470 rows=3,249 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 430kB
25. 64.841 369.603 ↑ 1.8 3,249 1

Bitmap Heap Scan on theaters th (cost=743.85..125,379.52 rows=5,995 width=94) (actual time=9.737..369.603 rows=3,249 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (SubPlan 2)
  • Rows Removed by Filter: 9115
  • Heap Blocks: exact=3808
26. 8.026 8.026 ↓ 1.0 12,364 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..742.35 rows=11,991 width=0) (actual time=8.026..8.026 rows=12,364 loops=1)

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

SubPlan (for Bitmap Heap Scan)

28. 74.544 296.736 ↓ 0.0 0 12,364

Append (cost=4.71..189.02 rows=53 width=0) (actual time=0.024..0.024 rows=0 loops=12,364)

29. 49.456 148.368 ↓ 0.0 0 12,364

Bitmap Heap Scan on theater_properties_20180101 (cost=4.71..99.58 rows=28 width=0) (actual time=0.012..0.012 rows=0 loops=12,364)

  • Recheck Cond: ((theater_id = th.id) AND (as_of_date >= ('2018-01-15'::date - ('180 DAYS'::cstring)::interval)))
  • Filter: is_reporting
  • Heap Blocks: exact=3136
30. 98.912 98.912 ↑ 4.0 7 12,364

Bitmap Index Scan on uq_theater_properties_test_20180101 (cost=0.00..4.71 rows=28 width=0) (actual time=0.008..0.008 rows=7 loops=12,364)

  • Index Cond: ((theater_id = th.id) AND (as_of_date >= ('2018-01-15'::date - ('180 DAYS'::cstring)::interval)))
31. 18.456 73.824 ↓ 0.0 0 9,228

Bitmap Heap Scan on theater_properties_20180201 (cost=4.68..89.44 rows=25 width=0) (actual time=0.008..0.008 rows=0 loops=9,228)

  • Recheck Cond: ((theater_id = th.id) AND (as_of_date >= ('2018-01-15'::date - ('180 DAYS'::cstring)::interval)))
  • Filter: is_reporting
  • Heap Blocks: exact=113
32. 55.368 55.368 ↓ 0.0 0 9,228

Bitmap Index Scan on uq_theater_properties_test_20180201 (cost=0.00..4.68 rows=25 width=0) (actual time=0.006..0.006 rows=0 loops=9,228)

  • Index Cond: ((theater_id = th.id) AND (as_of_date >= ('2018-01-15'::date - ('180 DAYS'::cstring)::interval)))
33. 2.284 4.721 ↑ 1.0 6,047 1

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

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

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

35. 2.438 144.274 ↓ 1.3 3,535 1

Sort (cost=14,186.35..14,192.98 rows=2,652 width=1,044) (actual time=143.904..144.274 rows=3,535 loops=1)

  • Sort Key: dj.theater_id
  • Sort Method: quicksort Memory: 373kB
36. 1.921 141.836 ↓ 1.3 3,535 1

Hash Left Join (cost=1.99..14,035.54 rows=2,652 width=1,044) (actual time=48.325..141.836 rows=3,535 loops=1)

  • Hash Cond: (dj.justification_id = (ju.id)::text)
37. 138.393 138.393 ↓ 1.3 3,535 1

Seq Scan on data_recovery_justifications dj (cost=0.00..14,025.92 rows=2,652 width=14) (actual time=46.765..138.393 rows=3,535 loops=1)

  • Filter: ((booking_date >= '2018-01-15'::date) AND (booking_date <= '2018-01-21'::date))
  • Rows Removed by Filter: 646260
38. 0.045 1.522 ↑ 1.0 44 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
39. 1.477 1.477 ↑ 1.0 44 1

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

40. 4.216 60.058 ↓ 7.1 5,004 1

Sort (cost=3,138.34..3,140.11 rows=708 width=15) (actual time=59.262..60.058 rows=5,004 loops=1)

  • Sort Key: ta.theater_id
  • Sort Method: quicksort Memory: 503kB
41. 37.832 55.842 ↓ 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=18.148..55.842 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
42. 0.128 18.010 ↓ 0.0 0 1

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

43. 10.560 10.560 ↑ 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=10.560..10.560 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[]))
44. 7.322 7.322 ↑ 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.322..7.322 rows=34,997 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
45. 0.049 13.323 ↑ 1.9 87 1

Sort (cost=9.57..9.99 rows=168 width=50) (actual time=13.307..13.323 rows=87 loops=1)

  • Sort Key: ct.theater_id
  • Sort Method: quicksort Memory: 29kB
46. 13.274 13.274 ↑ 2.8 59 1

CTE Scan on contacted_theaters_cte ct (cost=0.00..3.36 rows=168 width=50) (actual time=12.777..13.274 rows=59 loops=1)

47. 9.847 10.292 ↓ 26.0 26 5,146

Materialize (cost=0.00..1.63 rows=1 width=154) (actual time=0.000..0.002 rows=26 loops=5,146)

48. 0.445 0.445 ↓ 50.0 50 1

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

  • Filter: ((country_id)::text = 'US'::text)
49. 0.050 5.497 ↑ 4.3 141 1

Sort (cost=444.94..446.47 rows=609 width=84) (actual time=5.478..5.497 rows=141 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 31kB
50. 0.054 5.447 ↑ 13.8 44 1

Hash Left Join (cost=81.86..416.78 rows=609 width=84) (actual time=4.204..5.447 rows=44 loops=1)

  • Hash Cond: (mg.user_id = nu.id)
51. 3.620 3.620 ↑ 1.0 44 1

Seq Scan on data_recovery_mg_ignored_dates mg (cost=0.00..313.52 rows=45 width=28) (actual time=2.409..3.620 rows=44 loops=1)

  • Filter: ((ignored_date >= '2018-01-15'::date) AND (ignored_date <= '2018-01-21'::date))
  • Rows Removed by Filter: 13391
52. 1.082 1.773 ↑ 1.0 2,705 1

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

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

54. 0.000 0.000 ↓ 0.0 0 5,204

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=5,204)

  • One-Time Filter: false
Planning time : 41.096 ms
Execution time : 773.415 ms