explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0nFy

Settings
# exclusive inclusive rows x rows loops node
1. 102.539 92,104.934 ↓ 458.8 640,518 1

Unique (cost=590,995.73..591,002.71 rows=1,396 width=8) (actual time=91,960.813..92,104.934 rows=640,518 loops=1)

2. 238.569 92,002.395 ↓ 458.8 640,518 1

Sort (cost=590,995.73..590,999.22 rows=1,396 width=8) (actual time=91,960.812..92,002.395 rows=640,518 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 54601kB
3. 1,880.414 91,763.826 ↓ 458.8 640,518 1

Hash Join (cost=563,647.62..590,922.81 rows=1,396 width=8) (actual time=3,854.633..91,763.826 rows=640,518 loops=1)

  • Hash Cond: ((s.id_theater = t.theater_id) AND (rr.id_country = t.country_id))
4. 80,546.014 89,856.133 ↓ 298.8 13,450,279 1

Merge Join (cost=561,762.04..588,800.90 rows=45,015 width=18) (actual time=3,827.256..89,856.133 rows=13,450,279 loops=1)

  • Merge Cond: ((s.gid_entity)::ltree = (m.gid)::ltree)
  • Join Filter: ((s.theater_day >= (rr.released_at)::date) AND (s.theater_day <= ((rr.released_at)::date + '7 days'::interval)))
  • Rows Removed by Join Filter: 72450967
5. 3,874.026 4,834.113 ↑ 1.0 2,774,813 1

Sort (cost=551,098.57..558,035.61 rows=2,774,814 width=59) (actual time=3,656.754..4,834.113 rows=2,774,813 loops=1)

  • Sort Key: s.gid_entity
  • Sort Method: external merge Disk: 200920kB
6. 960.087 960.087 ↑ 1.0 2,774,814 1

Seq Scan on relevant_showtime s (cost=0.00..193,032.14 rows=2,774,814 width=59) (actual time=0.009..960.087 rows=2,774,814 loops=1)

7. 4,353.239 4,476.006 ↓ 3,127.5 85,901,246 1

Sort (cost=10,662.92..10,731.58 rows=27,466 width=51) (actual time=167.212..4,476.006 rows=85,901,246 loops=1)

  • Sort Key: m.gid
  • Sort Method: quicksort Memory: 4633kB
8. 8.592 122.767 ↓ 1.0 27,481 1

Hash Join (cost=7,725.66..8,637.94 rows=27,466 width=51) (actual time=70.262..122.767 rows=27,481 loops=1)

  • Hash Cond: (rr.id_movie = m.id)
9. 44.176 44.176 ↓ 1.0 27,481 1

Seq Scan on relevant_release rr (cost=0.00..534.62 rows=27,466 width=18) (actual time=0.022..44.176 rows=27,481 loops=1)

  • Filter: (released_at ~ '^(?:[0-9]{4}-[0-9]{2}-[0-9]{2})$'::text)
  • Rows Removed by Filter: 889
10. 35.028 69.999 ↑ 1.0 188,118 1

Hash (cost=5,374.18..5,374.18 rows=188,118 width=45) (actual time=69.999..69.999 rows=188,118 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 16745kB
11. 34.971 34.971 ↑ 1.0 188,118 1

Seq Scan on relevant_movie m (cost=0.00..5,374.18 rows=188,118 width=45) (actual time=0.008..34.971 rows=188,118 loops=1)

12. 11.201 27.279 ↑ 1.0 36,423 1

Hash (cost=1,339.23..1,339.23 rows=36,423 width=14) (actual time=27.279..27.279 rows=36,423 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2220kB
13. 16.078 16.078 ↑ 1.0 36,423 1

Seq Scan on relevant_theater t (cost=0.00..1,339.23 rows=36,423 width=14) (actual time=0.015..16.078 rows=36,423 loops=1)