explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5zDe

Settings
# exclusive inclusive rows x rows loops node
1. 101.406 897,855.882 ↓ 693,511.0 693,511 1

Unique (cost=6,965.01..6,965.02 rows=1 width=8) (actual time=897,715.071..897,855.882 rows=693,511 loops=1)

  • Planning time: 100.897 ms
  • Execution time: 897883.082 ms
2. 333.359 897,754.476 ↓ 693,511.0 693,511 1

Sort (cost=6,965.01..6,965.02 rows=1 width=8) (actual time=897,715.070..897,754.476 rows=693,511 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 57085kB
3. 19,380.118 897,421.117 ↓ 693,511.0 693,511 1

Nested Loop (cost=588.61..6,965.00 rows=1 width=8) (actual time=1,027.135..897,421.117 rows=693,511 loops=1)

  • 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: 1731926
4. 9,484.113 10,517.661 ↓ 14,698.3 32,130,494 1

Hash Join (cost=588.06..2,085.76 rows=2,186 width=75) (actual time=1,011.434..10,517.661 rows=32,130,494 loops=1)

  • Hash Cond: (t.country_id = rr.id_country)
5. 26.241 26.241 ↑ 1.0 36,425 1

Seq Scan on relevant_theater t (cost=0.00..1,339.25 rows=36,425 width=14) (actual time=4.114..26.241 rows=36,425 loops=1)

6. 11.367 1,007.307 ↓ 2,377.8 28,534 1

Hash (cost=587.91..587.91 rows=12 width=73) (actual time=1,007.307..1,007.307 rows=28,534 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2681kB
7. 6.013 995.940 ↓ 2,377.8 28,534 1

Nested Loop (cost=0.42..587.91 rows=12 width=73) (actual time=9.808..995.940 rows=28,534 loops=1)

8. 76.839 76.839 ↓ 2,377.8 28,534 1

Seq Scan on relevant_release rr (cost=0.00..556.14 rows=12 width=40) (actual time=0.017..76.839 rows=28,534 loops=1)

  • Filter: (released_at ~ '^(?:[0-9]{4}-[0-9]{2}-[0-9]{2})$'::text)
  • Rows Removed by Filter: 997
9. 913.088 913.088 ↑ 1.0 1 28,534

Index Only Scan using relevant_movie_idx on relevant_movie m (cost=0.42..2.64 rows=1 width=45) (actual time=0.031..0.032 rows=1 loops=28,534)

  • Index Cond: (id = rr.id_movie)
  • Heap Fetches: 28534
10. 867,523.338 867,523.338 ↓ 0.0 0 32,130,494

Index Scan using relevant_showtime_idx on relevant_showtime s (cost=0.56..2.20 rows=1 width=59) (actual time=0.018..0.027 rows=0 loops=32,130,494)

  • Index Cond: ((id_theater = t.theater_id) AND ((gid_entity)::ltree = (m.gid)::ltree))