explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BxU4

Settings
# exclusive inclusive rows x rows loops node
1. 9.176 119,639.425 ↓ 16.3 63,218 1

Unique (cost=525,386.90..525,406.29 rows=3,878 width=8) (actual time=119,626.194..119,639.425 rows=63,218 loops=1)

2. 21.430 119,630.249 ↓ 16.3 63,218 1

Sort (cost=525,386.90..525,396.60 rows=3,878 width=8) (actual time=119,626.193..119,630.249 rows=63,218 loops=1)

  • Sort Key: s.id
  • Sort Method: quicksort Memory: 4500kB
3. 1,856.807 119,608.819 ↓ 16.3 63,218 1

Hash Join (cost=503,435.43..525,155.75 rows=3,878 width=8) (actual time=54,956.383..119,608.819 rows=63,218 loops=1)

  • Hash Cond: ((s.id_theater = t.theater_id) AND (rr.id_country = t.country_id))
4. 57,962.582 117,395.237 ↓ 111.8 14,008,569 1

Merge Join (cost=501,551.41..522,613.85 rows=125,309 width=18) (actual time=54,598.039..117,395.237 rows=14,008,569 loops=1)

  • Merge Cond: ((s.gid_entity)::ltree = (m.gid)::ltree)
  • Join Filter: ((s.theater_day)::text < rr.released_at)
  • Rows Removed by Join Filter: 62660526
5. 3,984.011 49,124.229 ↑ 1.0 2,521,193 1

Sort (cost=490,909.49..497,212.47 rows=2,521,193 width=59) (actual time=48,149.458..49,124.229 rows=2,521,193 loops=1)

  • Sort Key: s.gid_entity
  • Sort Method: external merge Disk: 182560kB
6. 45,140.218 45,140.218 ↑ 1.0 2,521,193 1

Seq Scan on relevant_showtime s (cost=0.00..167,312.93 rows=2,521,193 width=59) (actual time=2.313..45,140.218 rows=2,521,193 loops=1)

7. 3,933.236 10,308.426 ↓ 2,733.6 76,669,095 1

Sort (cost=10,641.42..10,711.53 rows=28,047 width=51) (actual time=6,444.752..10,308.426 rows=76,669,095 loops=1)

  • Sort Key: m.gid
  • Sort Method: quicksort Memory: 4682kB
8. 14.646 6,375.190 ↑ 1.0 28,047 1

Hash Join (cost=7,725.25..8,569.37 rows=28,047 width=51) (actual time=6,320.852..6,375.190 rows=28,047 loops=1)

  • Hash Cond: (rr.id_movie = m.id)
9. 49.326 49.326 ↑ 1.0 28,047 1

Seq Scan on relevant_release rr (cost=0.00..458.47 rows=28,047 width=18) (actual time=9.381..49.326 rows=28,047 loops=1)

10. 71.246 6,311.218 ↑ 1.0 188,100 1

Hash (cost=5,374.00..5,374.00 rows=188,100 width=45) (actual time=6,311.218..6,311.218 rows=188,100 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 16744kB
11. 6,239.972 6,239.972 ↑ 1.0 188,100 1

Seq Scan on relevant_movie m (cost=0.00..5,374.00 rows=188,100 width=45) (actual time=1,347.715..6,239.972 rows=188,100 loops=1)

12. 13.362 356.775 ↑ 1.0 36,401 1

Hash (cost=1,338.01..1,338.01 rows=36,401 width=14) (actual time=356.775..356.775 rows=36,401 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2219kB
13. 343.413 343.413 ↑ 1.0 36,401 1

Seq Scan on relevant_theater t (cost=0.00..1,338.01 rows=36,401 width=14) (actual time=5.920..343.413 rows=36,401 loops=1)