explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lw2s

Settings
# exclusive inclusive rows x rows loops node
1. 210.070 12,686.317 ↓ 3,194.7 638,939 1

Unique (cost=11.01..863,084.12 rows=200 width=71) (actual time=0.059..12,686.317 rows=638,939 loops=1)

2. 75.248 12,476.247 ↓ 200.7 638,939 1

Subquery Scan on foo (cost=11.01..863,076.16 rows=3,184 width=71) (actual time=0.058..12,476.247 rows=638,939 loops=1)

3. 4,781.197 12,400.999 ↓ 200.7 638,939 1

GroupAggregate (cost=11.01..863,044.32 rows=3,184 width=71) (actual time=0.058..12,400.999 rows=638,939 loops=1)

  • Group Key: m.movie_id
  • Filter: (string_agg((g2.genre_name)::text, '/'::text) ~~* '%sci-fi%'::text)
  • Rows Removed by Filter: 9311076
4. 1,568.623 7,619.802 ↓ 1.0 10,000,984 1

Merge Left Join (cost=11.01..614,293.95 rows=9,950,015 width=71) (actual time=0.044..7,619.802 rows=10,000,984 loops=1)

  • Merge Cond: (m.movie_id = g.movie_id)
5. 4,478.560 4,478.560 ↑ 1.0 9,950,015 1

Index Scan using movie_pkey on movie m (cost=0.43..490,894.13 rows=9,950,015 width=39) (actual time=0.011..4,478.560 rows=9,950,015 loops=1)

  • Filter: ((movie_year >= 0) AND (movie_year <= 2020))
6. 157.608 1,572.619 ↑ 1.0 1,022,016 1

Materialize (cost=0.42..85,755.98 rows=1,022,016 width=36) (actual time=0.030..1,572.619 rows=1,022,016 loops=1)

7. 404.424 1,415.011 ↑ 1.0 1,022,016 1

Nested Loop Left Join (cost=0.42..83,200.94 rows=1,022,016 width=36) (actual time=0.027..1,415.011 rows=1,022,016 loops=1)

  • Join Filter: (g.genre_id = g2.genre_id)
  • Rows Removed by Join Filter: 660269
8. 1,010.587 1,010.587 ↑ 1.0 1,022,016 1

Index Scan using movie_genres_movie_id_index on movie_genres g (cost=0.42..44,874.30 rows=1,022,016 width=8) (actual time=0.005..1,010.587 rows=1,022,016 loops=1)

9. 0.000 0.000 ↑ 1.5 2 1,022,016

Materialize (cost=0.00..1.04 rows=3 width=36) (actual time=0.000..0.000 rows=2 loops=1,022,016)

10. 0.015 0.015 ↑ 1.0 3 1

Seq Scan on genres g2 (cost=0.00..1.03 rows=3 width=36) (actual time=0.015..0.015 rows=3 loops=1)