explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4BQA

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 1,250.385 ↑ 1.0 11 1

Limit (cost=12,719.25..12,719.55 rows=11 width=71) (actual time=1,250.360..1,250.385 rows=11 loops=1)

2. 0.011 1,250.364 ↑ 9.0 11 1

GroupAggregate (cost=12,719.25..12,721.97 rows=99 width=71) (actual time=1,250.359..1,250.364 rows=11 loops=1)

  • Group Key: m.movie_id, m.movie_title, m.movie_country, m.movie_year
3. 0.058 1,250.353 ↑ 8.2 12 1

Sort (cost=12,719.25..12,719.50 rows=99 width=71) (actual time=1,250.353..1,250.353 rows=12 loops=1)

  • Sort Key: m.movie_id, m.movie_title, m.movie_country, m.movie_year
  • Sort Method: quicksort Memory: 66kB
4. 0.391 1,250.295 ↓ 4.9 482 1

Unique (cost=12,714.48..12,714.98 rows=99 width=71) (actual time=1,249.878..1,250.295 rows=482 loops=1)

5. 0.818 1,249.904 ↓ 6.0 590 1

Sort (cost=12,714.48..12,714.73 rows=99 width=71) (actual time=1,249.878..1,249.904 rows=590 loops=1)

  • Sort Key: m.movie_id
  • Sort Method: quicksort Memory: 74kB
6. 1.261 1,249.086 ↓ 6.0 590 1

Hash Join (cost=254.68..12,711.20 rows=99 width=71) (actual time=94.401..1,249.086 rows=590 loops=1)

  • Hash Cond: (mg.genre_id = g.genre_id)
7. 5.457 1,247.783 ↓ 6.0 590 1

Nested Loop Left Join (cost=253.60..12,709.50 rows=99 width=43) (actual time=94.344..1,247.783 rows=590 loops=1)

  • Filter: (((d.director_fname)::text ~~* '%'::text) OR ((d.director_lname)::text ~~* '%'::text) OR ((a.actor_fname)::text ~~* 'chris'::text) OR ((a.actor_lname)::text ~~* 'chris'::text))
  • Rows Removed by Filter: 4835
8. 1.845 1,167.160 ↓ 54.2 5,369 1

Nested Loop Left Join (cost=252.75..11,924.18 rows=99 width=56) (actual time=92.585..1,167.160 rows=5,369 loops=1)

9. 5.898 1,143.839 ↓ 54.2 5,369 1

Nested Loop Left Join (cost=252.33..11,876.39 rows=99 width=47) (actual time=92.583..1,143.839 rows=5,369 loops=1)

10. 24.914 1,095.197 ↓ 54.0 5,343 1

Nested Loop (cost=251.90..11,139.32 rows=99 width=43) (actual time=92.574..1,095.197 rows=5,343 loops=1)

11. 776.982 859.575 ↓ 54.6 52,677 1

Bitmap Heap Scan on movie m (cost=251.48..3,910.65 rows=965 width=39) (actual time=92.488..859.575 rows=52,677 loops=1)

  • Recheck Cond: ((movie_title)::text ~~* 'vanity fair'::text)
  • Filter: ((movie_year >= 0) AND (movie_year <= 2020))
  • Heap Blocks: exact=45744
12. 82.593 82.593 ↓ 54.6 52,677 1

Bitmap Index Scan on movie_tit_idx (cost=0.00..251.24 rows=965 width=0) (actual time=82.593..82.593 rows=52,677 loops=1)

  • Index Cond: ((movie_title)::text ~~* 'vanity fair'::text)
13. 210.708 210.708 ↓ 0.0 0 52,677

Index Scan using movie_genres_movie_id_index on movie_genres mg (cost=0.42..7.48 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=52,677)

  • Index Cond: (movie_id = m.movie_id)
14. 42.744 42.744 ↓ 0.0 0 5,343

Index Scan using movie_director_movie_id_index on movie_director md (cost=0.42..7.44 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=5,343)

  • Index Cond: (movie_id = m.movie_id)
15. 21.476 21.476 ↓ 0.0 0 5,369

Index Scan using director_pkey on director d (cost=0.42..0.48 rows=1 width=17) (actual time=0.004..0.004 rows=0 loops=5,369)

  • Index Cond: (md.director_id = director_id)
16. 5.074 75.166 ↓ 0.0 0 5,369

Nested Loop Left Join (cost=0.85..7.91 rows=1 width=17) (actual time=0.014..0.014 rows=0 loops=5,369)

17. 42.952 42.952 ↓ 0.0 0 5,369

Index Scan using movie_cast_movie_id_index on movie_cast mc (cost=0.42..7.43 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=5,369)

  • Index Cond: (movie_id = m.movie_id)
18. 27.140 27.140 ↑ 1.0 1 590

Index Scan using actor_pkey on actor a (cost=0.42..0.48 rows=1 width=17) (actual time=0.046..0.046 rows=1 loops=590)

  • Index Cond: (mc.actor_id = actor_id)
19. 0.000 0.042 ↑ 1.0 3 1

Hash (cost=1.04..1.04 rows=3 width=36) (actual time=0.042..0.042 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.051 0.051 ↑ 1.0 3 1

Seq Scan on genres g (cost=0.00..1.04 rows=3 width=36) (actual time=0.034..0.051 rows=3 loops=1)

  • Filter: ((genre_name)::text ~~* '%'::text)
Planning time : 3.576 ms
Execution time : 1,252.141 ms