explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A3h

Settings
# exclusive inclusive rows x rows loops node
1. 0.059 21,140.712 ↑ 4.0 50 1

Unique (cost=843,634.72..871,741.31 rows=200 width=71) (actual time=20,767.601..21,140.712 rows=50 loops=1)

2. 55.031 21,140.653 ↑ 4.0 57 1

Merge Join (cost=843,634.72..871,740.74 rows=230 width=71) (actual time=20,767.600..21,140.653 rows=57 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
3. 0.408 47.086 ↓ 3.0 600 1

Sort (cost=3,307.51..3,308.01 rows=201 width=8) (actual time=46.972..47.086 rows=600 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 53kB
4. 0.718 46.678 ↓ 3.0 600 1

Nested Loop (cost=154.80..3,299.82 rows=201 width=8) (actual time=3.750..46.678 rows=600 loops=1)

5. 0.364 42.360 ↓ 3.0 600 1

Nested Loop (cost=154.38..3,201.79 rows=201 width=12) (actual time=3.740..42.360 rows=600 loops=1)

6. 0.596 22.700 ↓ 2.9 536 1

Nested Loop (cost=153.95..3,112.72 rows=185 width=4) (actual time=3.733..22.700 rows=536 loops=1)

7. 1.791 5.454 ↓ 2.9 555 1

Bitmap Heap Scan on actor ac (cost=153.53..817.07 rows=191 width=4) (actual time=3.723..5.454 rows=555 loops=1)

  • Recheck Cond: (((actor_fname)::text ~~* 'chris'::text) OR ((actor_lname)::text ~~* 'chris'::text))
  • Heap Blocks: exact=531
8. 0.001 3.663 ↓ 0.0 0 1

BitmapOr (cost=153.53..153.53 rows=191 width=0) (actual time=3.663..3.663 rows=0 loops=1)

9. 2.074 2.074 ↓ 6.0 555 1

Bitmap Index Scan on fname_idx (cost=0.00..76.70 rows=93 width=0) (actual time=2.074..2.074 rows=555 loops=1)

  • Index Cond: ((actor_fname)::text ~~* 'chris'::text)
10. 1.588 1.588 ↓ 0.0 0 1

Bitmap Index Scan on lname_idx (cost=0.00..76.73 rows=98 width=0) (actual time=1.588..1.588 rows=0 loops=1)

  • Index Cond: ((actor_lname)::text ~~* 'chris'::text)
11. 16.650 16.650 ↑ 2.0 1 555

Index Scan using movie_cast_actor_id_index on movie_cast m2 (cost=0.42..12.00 rows=2 width=8) (actual time=0.025..0.030 rows=1 loops=555)

  • Index Cond: (actor_id = ac.actor_id)
12. 19.296 19.296 ↑ 1.0 1 536

Index Scan using movie_director_movie_id_index on movie_director d2 (cost=0.42..0.47 rows=1 width=8) (actual time=0.033..0.036 rows=1 loops=536)

  • Index Cond: (movie_id = m2.movie_id)
13. 3.600 3.600 ↑ 1.0 1 600

Index Scan using director_pkey on director d3 (cost=0.42..0.49 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=600)

  • Index Cond: (director_id = d2.director_id)
  • Filter: (((director_fname)::text ~~* '%'::text) OR ((director_lname)::text ~~* '%'::text))
14. 190.507 21,038.536 ↑ 1.1 970,191 1

Unique (cost=840,327.21..855,655.96 rows=1,021,917 width=71) (actual time=20,720.623..21,038.536 rows=970,191 loops=1)

15. 609.526 20,848.029 ↑ 1.1 970,191 1

Sort (cost=840,327.21..842,882.00 rows=1,021,917 width=71) (actual time=20,720.621..20,848.029 rows=970,191 loops=1)

  • Sort Key: m.movie_id, m.movie_title, m.movie_country, m.movie_year, (string_agg((g2.genre_name)::text, '/'::text))
  • Sort Method: external merge Disk: 56544kB
16. 609.820 20,238.503 ↑ 1.1 971,047 1

GroupAggregate (cost=6.48..654,493.35 rows=1,021,917 width=71) (actual time=0.056..20,238.503 rows=971,047 loops=1)

  • Group Key: m.movie_id
17. 366.281 19,628.683 ↓ 1.0 1,022,016 1

Nested Loop (cost=6.48..636,609.80 rows=1,021,917 width=71) (actual time=0.044..19,628.683 rows=1,022,016 loops=1)

  • Join Filter: (g.genre_id = g2.genre_id)
  • Rows Removed by Join Filter: 660269
18. 840.446 19,262.402 ↓ 1.0 1,022,016 1

Merge Join (cost=6.48..598,286.87 rows=1,021,917 width=43) (actual time=0.019..19,262.402 rows=1,022,016 loops=1)

  • Merge Cond: (m.movie_id = g.movie_id)
19. 17,489.258 17,489.258 ↓ 1.0 9,950,014 1

Index Scan using movie_pkey on movie m (cost=0.43..515,769.16 rows=9,949,050 width=39) (actual time=0.011..17,489.258 rows=9,950,014 loops=1)

  • Filter: (((movie_title)::text ~~* '%'::text) AND (movie_year >= 0) AND (movie_year <= 2020))
20. 932.698 932.698 ↑ 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.006..932.698 rows=1,022,016 loops=1)

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

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

22. 0.019 0.019 ↑ 1.0 3 1

Seq Scan on genres g2 (cost=0.00..1.04 rows=3 width=36) (actual time=0.017..0.019 rows=3 loops=1)

  • Filter: ((genre_name)::text ~~* '%'::text)