explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UnNb

Settings
# exclusive inclusive rows x rows loops node
1. 162.623 10,393.385 ↑ 79.7 5,469 1

GroupAggregate (cost=3,583,744.88..3,640,385.98 rows=435,699 width=541) (actual time=10,185.195..10,393.385 rows=5,469 loops=1)

  • Group Key: th.id
  • Buffers: shared hit=45843 read=243918 dirtied=2455 written=1021
2.          

CTE contacted_theaters_cte

3. 235.121 244.712 ↑ 2.4 113 1

GroupAggregate (cost=3,361.67..3,370.85 rows=270 width=50) (actual time=10.504..244.712 rows=113 loops=1)

  • Group Key: ct_1.theater_id
  • Buffers: shared hit=1683
4. 0.670 9.591 ↓ 1.3 382 1

Sort (cost=3,361.67..3,362.40 rows=290 width=173) (actual time=9.313..9.591 rows=382 loops=1)

  • Sort Key: ct_1.theater_id
  • Sort Method: quicksort Memory: 126kB
  • Buffers: shared hit=1675
5. 0.396 8.921 ↓ 1.3 382 1

Nested Loop (cost=0.28..3,349.81 rows=290 width=173) (actual time=3.504..8.921 rows=382 loops=1)

  • Buffers: shared hit=1675
6. 7.761 7.761 ↓ 1.3 382 1

Seq Scan on data_recovery_contacted_theaters ct_1 (cost=0.00..1,359.52 rows=290 width=26) (actual time=3.477..7.761 rows=382 loops=1)

  • Filter: ((contacted_date >= '2018-07-18'::date) AND (contacted_date <= '2018-07-25'::date))
  • Rows Removed by Filter: 55007
  • Buffers: shared hit=529
7. 0.764 0.764 ↑ 1.0 1 382

Index Scan using pk_users on users us (cost=0.28..6.86 rows=1 width=163) (actual time=0.002..0.002 rows=1 loops=382)

  • Index Cond: (id = ct_1.user_id)
  • Buffers: shared hit=1146
8. 24.041 10,230.762 ↑ 34.3 12,702 1

Nested Loop Left Join (cost=3,580,374.04..3,586,909.75 rows=435,699 width=369) (actual time=10,185.152..10,230.762 rows=12,702 loops=1)

  • Join Filter: false
  • Buffers: shared hit=45843 read=243918 dirtied=2455 written=1021
9. 15.248 10,206.721 ↑ 34.3 12,702 1

Merge Left Join (cost=3,580,374.04..3,582,552.76 rows=435,699 width=369) (actual time=10,185.148..10,206.721 rows=12,702 loops=1)

  • Merge Cond: (th.id = mg.theater_id)
  • Buffers: shared hit=45843 read=243918 dirtied=2455 written=1021
10. 22.198 10,186.833 ↑ 34.5 12,641 1

Sort (cost=3,579,709.97..3,580,799.22 rows=435,699 width=202) (actual time=10,180.601..10,186.833 rows=12,641 loops=1)

  • Sort Key: th.id
  • Sort Method: quicksort Memory: 3722kB
  • Buffers: shared hit=45688 read=243791 dirtied=2455 written=1021
11. 16.501 10,164.635 ↑ 34.5 12,641 1

Hash Left Join (cost=2,633,730.60..3,538,900.28 rows=435,699 width=202) (actual time=5,532.710..10,164.635 rows=12,641 loops=1)

  • Hash Cond: (((th.country_id)::text = (s.country_id)::text) AND ((th.state_id)::text = (s.id)::text))
  • Buffers: shared hit=45688 read=243791 dirtied=2455 written=1021
12. 19.740 10,148.001 ↑ 34.5 12,641 1

Hash Right Join (cost=2,633,728.22..3,532,261.98 rows=435,699 width=193) (actual time=5,532.551..10,148.001 rows=12,641 loops=1)

  • Hash Cond: (pr.theater_id = th.id)
  • Buffers: shared hit=45687 read=243791 dirtied=2455 written=1021
13. 0.000 6,438.636 ↑ 6.1 3,819 1

Gather (cost=1,000.00..899,435.17 rows=23,254 width=10) (actual time=1,840.498..6,438.636 rows=3,819 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=569 read=224618 dirtied=2412 written=1021
14. 6,452.777 6,452.777 ↑ 7.6 1,273 3 / 3

Parallel Seq Scan on theater_properties pr (cost=0.00..896,109.77 rows=9,689 width=10) (actual time=1,822.720..6,452.777 rows=1,273 loops=3)

  • Filter: (as_of_date = '2018-07-18'::date)
  • Rows Removed by Filter: 11982191
  • Buffers: shared hit=2825 read=691790 dirtied=7076 written=3078
15. 12.468 3,689.625 ↑ 34.5 12,641 1

Hash (cost=2,627,281.98..2,627,281.98 rows=435,699 width=191) (actual time=3,689.625..3,689.625 rows=12,641 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 6190kB
  • Buffers: shared hit=45118 read=19173 dirtied=43
16. 18.270 3,677.157 ↑ 34.5 12,641 1

Hash Right Join (cost=2,611,395.58..2,627,281.98 rows=435,699 width=191) (actual time=3,588.791..3,677.157 rows=12,641 loops=1)

  • Hash Cond: (dj.theater_id = th.id)
  • Buffers: shared hit=45118 read=19173 dirtied=43
17. 6.722 107.837 ↓ 1.1 8,502 1

Gather (cost=1,002.01..16,856.74 rows=7,472 width=30) (actual time=35.266..107.837 rows=8,502 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2 read=2939
18. 3.203 101.115 ↑ 1.1 2,834 3 / 3

Hash Left Join (cost=2.01..15,109.54 rows=3,113 width=30) (actual time=27.075..101.115 rows=2,834 loops=3)

  • Hash Cond: (dj.justification_id = (ju.id)::text)
  • Buffers: shared hit=49 read=7666
19. 97.828 97.828 ↑ 1.1 2,834 3 / 3

Parallel Seq Scan on data_recovery_justifications dj (cost=0.00..15,068.84 rows=3,113 width=14) (actual time=26.907..97.828 rows=2,834 loops=3)

  • Filter: ((booking_date >= '2018-07-18'::date) AND (booking_date <= '2018-07-25'::date))
  • Rows Removed by Filter: 391024
  • Buffers: shared hit=18 read=7666
20. 0.039 0.084 ↑ 1.0 45 3 / 3

Hash (cost=1.45..1.45 rows=45 width=18) (actual time=0.084..0.084 rows=45 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=3
21. 0.045 0.045 ↑ 1.0 45 3 / 3

Seq Scan on justifications ju (cost=0.00..1.45 rows=45 width=18) (actual time=0.021..0.045 rows=45 loops=3)

  • Buffers: shared hit=3
22. 6.288 3,551.050 ↑ 74.7 5,831 1

Hash (cost=2,604,947.33..2,604,947.33 rows=435,699 width=169) (actual time=3,551.050..3,551.050 rows=5,831 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4983kB
  • Buffers: shared hit=45116 read=16234 dirtied=43
23. 15.996 3,544.762 ↑ 74.7 5,831 1

Hash Right Join (cost=2,599,855.54..2,604,947.33 rows=435,699 width=169) (actual time=3,522.875..3,544.762 rows=5,831 loops=1)

  • Hash Cond: (id.theater_id = th.id)
  • Buffers: shared hit=45116 read=16234 dirtied=43
24. 8.347 9.215 ↓ 1.0 13,875 1

Bitmap Heap Scan on theater_aliases id (cost=475.74..5,508.84 rows=13,848 width=15) (actual time=0.917..9.215 rows=13,875 loops=1)

  • Recheck Cond: ((source_id)::text = 'BOE'::text)
  • Heap Blocks: exact=384
  • Buffers: shared hit=426
25. 0.868 0.868 ↓ 1.0 13,875 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..472.28 rows=13,848 width=0) (actual time=0.868..0.868 rows=13,875 loops=1)

  • Index Cond: ((source_id)::text = 'BOE'::text)
  • Buffers: shared hit=42
26. 5.724 3,519.551 ↑ 74.7 5,831 1

Hash (cost=2,593,933.55..2,593,933.55 rows=435,699 width=162) (actual time=3,519.551..3,519.551 rows=5,831 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4951kB
  • Buffers: shared hit=44690 read=16234 dirtied=43
27. 7.930 3,513.827 ↑ 74.7 5,831 1

Hash Right Join (cost=2,593,927.01..2,593,933.55 rows=435,699 width=162) (actual time=3,273.964..3,513.827 rows=5,831 loops=1)

  • Hash Cond: (ct.theater_id = th.id)
  • Buffers: shared hit=44690 read=16234 dirtied=43
28. 244.887 244.887 ↑ 2.4 113 1

CTE Scan on contacted_theaters_cte ct (cost=0.00..5.40 rows=270 width=50) (actual time=10.507..244.887 rows=113 loops=1)

  • Buffers: shared hit=1683
29. 5.699 3,261.010 ↑ 74.7 5,831 1

Hash (cost=2,588,480.77..2,588,480.77 rows=435,699 width=120) (actual time=3,261.010..3,261.010 rows=5,831 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4914kB
  • Buffers: shared hit=43007 read=16234 dirtied=43
30. 12.792 3,255.311 ↑ 74.7 5,831 1

Hash Right Join (cost=2,586,587.99..2,588,480.77 rows=435,699 width=120) (actual time=3,238.248..3,255.311 rows=5,831 loops=1)

  • Hash Cond: (ta.theater_id = th.id)
  • Buffers: shared hit=43007 read=16234 dirtied=43
31. 6.837 245.868 ↓ 9.6 6,336 1

Bitmap Heap Scan on theater_aliases ta (cost=2,232.64..4,122.63 rows=660 width=15) (actual time=239.140..245.868 rows=6,336 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=965
  • Buffers: shared hit=1166 read=1009
32. 0.106 239.031 ↓ 0.0 0 1

BitmapAnd (cost=2,232.64..2,232.64 rows=660 width=0) (actual time=239.031..239.031 rows=0 loops=1)

  • Buffers: shared hit=201 read=1009
33. 52.091 52.091 ↑ 1.0 6,848 1

Bitmap Index Scan on ix_theater_aliases_source_id (cost=0.00..414.74 rows=6,881 width=0) (actual time=52.091..52.091 rows=6,848 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[]))
  • Buffers: shared hit=201 read=150
34. 186.834 186.834 ↓ 1.0 35,647 1

Bitmap Index Scan on ix_theater_aliases_source_type (cost=0.00..1,817.32 rows=35,320 width=0) (actual time=186.834..186.834 rows=35,647 loops=1)

  • Index Cond: (source_type = 'EXT'::text)
  • Buffers: shared read=859
35. 9.451 2,996.651 ↑ 79.7 5,469 1

Hash (cost=2,578,909.11..2,578,909.11 rows=435,699 width=113) (actual time=2,996.651..2,996.651 rows=5,469 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 4825kB
  • Buffers: shared hit=41841 read=15225 dirtied=43
36. 10.034 2,987.200 ↑ 79.7 5,469 1

Hash Left Join (cost=73,341.83..2,578,909.11 rows=435,699 width=113) (actual time=12.952..2,987.200 rows=5,469 loops=1)

  • Hash Cond: (th.circuit_id = ci.id)
  • Buffers: shared hit=41841 read=15225 dirtied=43
37. 54.757 2,969.921 ↑ 79.7 5,469 1

Bitmap Heap Scan on theaters th (cost=73,148.97..2,573,216.16 rows=435,699 width=92) (actual time=5.695..2,969.921 rows=5,469 loops=1)

  • Recheck Cond: ((country_id)::text = 'US'::text)
  • Filter: (alternatives: SubPlan 2 or hashed SubPlan 3)
  • Rows Removed by Filter: 6975
  • Heap Blocks: exact=3264
  • Buffers: shared hit=41790 read=15225 dirtied=43
38. 3.268 3.268 ↑ 70.0 12,446 1

Bitmap Index Scan on ix_theaters_country_49 (cost=0.00..73,040.04 rows=871,398 width=0) (actual time=3.268..3.268 rows=12,446 loops=1)

  • Index Cond: ((country_id)::text = 'US'::text)
  • Buffers: shared hit=363
39.          

SubPlan (for Bitmap Heap Scan)

40. 2,911.896 2,911.896 ↓ 0.0 0 12,444

Index Only Scan using ix_theater_properties_is_reporting on theater_properties (cost=0.57..5.03 rows=4 width=0) (actual time=0.234..0.234 rows=0 loops=12,444)

  • Index Cond: ((theater_id = th.id) AND (as_of_date >= ('2018-07-18'::date - ('180 DAYS'::cstring)::interval)) AND (is_reporting = true))
  • Filter: is_reporting
  • Heap Fetches: 2397
  • Buffers: shared hit=38163 read=15225 dirtied=43
41. 0.000 0.000 ↓ 0.0 0

Gather (cost=1,000.00..1,055,371.13 rows=373,645 width=8) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
42. 0.000 0.000 ↓ 0.0 0

Parallel Seq Scan on theater_properties theater_properties_1 (cost=0.00..1,017,006.63 rows=155,685 width=8) (never executed)

  • Filter: (is_reporting AND (as_of_date >= ('2018-07-18'::date - ('180 DAYS'::cstring)::interval)))
43. 3.731 7.245 ↑ 1.0 6,305 1

Hash (cost=114.05..114.05 rows=6,305 width=29) (actual time=7.245..7.245 rows=6,305 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 448kB
  • Buffers: shared hit=51
44. 3.514 3.514 ↑ 1.0 6,305 1

Seq Scan on circuits ci (cost=0.00..114.05 rows=6,305 width=29) (actual time=0.015..3.514 rows=6,305 loops=1)

  • Buffers: shared hit=51
45. 0.096 0.133 ↑ 1.0 50 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
46. 0.037 0.037 ↑ 1.0 50 1

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

  • Filter: ((country_id)::text = 'US'::text)
  • Buffers: shared hit=1
47. 0.155 4.640 ↓ 4.7 194 1

Sort (cost=664.07..664.17 rows=41 width=175) (actual time=4.539..4.640 rows=194 loops=1)

  • Sort Key: mg.theater_id
  • Sort Method: quicksort Memory: 38kB
  • Buffers: shared hit=155 read=127
48. 0.080 4.485 ↓ 1.2 51 1

Nested Loop Left Join (cost=0.28..662.97 rows=41 width=175) (actual time=4.103..4.485 rows=51 loops=1)

  • Buffers: shared hit=155 read=127
49. 4.303 4.303 ↓ 1.2 51 1

Seq Scan on data_recovery_mg_ignored_dates mg (cost=0.00..330.76 rows=41 width=28) (actual time=4.074..4.303 rows=51 loops=1)

  • Filter: ((ignored_date >= '2018-07-18'::date) AND (ignored_date <= '2018-07-25'::date))
  • Rows Removed by Filter: 13400
  • Buffers: shared hit=2 read=127
50. 0.102 0.102 ↑ 1.0 1 51

Index Scan using pk_users on users nu (cost=0.28..8.10 rows=1 width=163) (actual time=0.002..0.002 rows=1 loops=51)

  • Index Cond: (id = mg.user_id)
  • Buffers: shared hit=153
51. 0.000 0.000 ↓ 0.0 0 12,702

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=12,702)

  • One-Time Filter: false
Planning time : 13.357 ms
Execution time : 10,400.461 ms