explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G5y4

Settings
# exclusive inclusive rows x rows loops node
1. 94.273 125,268.479 ↓ 458.8 640,518 1

Unique (cost=595,785.00..595,791.98 rows=1,396 width=8) (actual time=125,135.858..125,268.479 rows=640,518 loops=1)

2. 197.804 125,174.206 ↓ 458.8 640,518 1

Sort (cost=595,785.00..595,788.49 rows=1,396 width=8) (actual time=125,135.857..125,174.206 rows=640,518 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 54601kB
3. 1,828.258 124,976.402 ↓ 458.8 640,518 1

Merge Join (cost=593,421.78..595,712.08 rows=1,396 width=8) (actual time=121,675.460..124,976.402 rows=640,518 loops=1)

  • Merge Cond: ((t.theater_id = s.id_theater) AND (t.country_id = rr.id_country))
4. 19.009 19.009 ↑ 1.0 36,405 1

Index Only Scan using relevant_theater_id_country_idx on relevant_theater t (cost=0.29..1,758.82 rows=36,423 width=14) (actual time=0.021..19.009 rows=36,405 loops=1)

  • Heap Fetches: 36405
5. 33,175.568 123,129.135 ↓ 298.8 13,450,279 1

Sort (cost=593,421.49..593,534.03 rows=45,015 width=18) (actual time=121,675.356..123,129.135 rows=13,450,279 loops=1)

  • Sort Key: s.id_theater, rr.id_country
  • Sort Method: external sort Disk: 447528kB
6. 80,608.049 89,953.567 ↓ 298.8 13,450,279 1

Merge Join (cost=562,903.40..589,942.26 rows=45,015 width=18) (actual time=3,891.767..89,953.567 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
7. 3,914.155 4,889.643 ↑ 1.0 2,774,813 1

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

  • Sort Key: s.gid_entity
  • Sort Method: external merge Disk: 200920kB
8. 975.488 975.488 ↑ 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.014..975.488 rows=2,774,814 loops=1)

9. 4,328.056 4,455.875 ↓ 3,127.5 85,901,246 1

Sort (cost=11,804.27..11,872.94 rows=27,466 width=51) (actual time=170.030..4,455.875 rows=85,901,246 loops=1)

  • Sort Key: m.gid
  • Sort Method: quicksort Memory: 4633kB
10. 16.142 127.819 ↓ 1.0 27,481 1

Merge Join (cost=0.80..9,779.29 rows=27,466 width=51) (actual time=6.006..127.819 rows=27,481 loops=1)

  • Merge Cond: (m.id = rr.id_movie)
11. 47.673 47.673 ↑ 1.0 188,070 1

Index Only Scan using relevant_movie_idx on relevant_movie m (cost=0.42..8,185.17 rows=188,118 width=45) (actual time=0.027..47.673 rows=188,070 loops=1)

  • Heap Fetches: 188070
12. 64.004 64.004 ↓ 1.0 27,481 1

Index Scan using relevant_release_idx on relevant_release rr (cost=0.29..782.76 rows=27,466 width=18) (actual time=5.974..64.004 rows=27,481 loops=1)

  • Filter: (released_at ~ '^(?:[0-9]{4}-[0-9]{2}-[0-9]{2})$'::text)
  • Rows Removed by Filter: 889