explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5qI2

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 885.281 ↑ 1.0 11 1

GroupAggregate (cost=18,817.38..18,817.69 rows=11 width=71) (actual time=885.276..885.281 rows=11 loops=1)

  • Group Key: m.movie_id, m.movie_title, m.movie_year, m.movie_country
2. 0.024 885.270 ↑ 1.1 11 1

Sort (cost=18,817.38..18,817.41 rows=12 width=71) (actual time=885.269..885.270 rows=11 loops=1)

  • Sort Key: m.movie_id, m.movie_title, m.movie_year, m.movie_country
  • Sort Method: quicksort Memory: 25kB
3. 0.025 885.246 ↑ 1.1 11 1

Hash Left Join (cost=18,723.39..18,817.16 rows=12 width=71) (actual time=868.135..885.246 rows=11 loops=1)

  • Hash Cond: (mg.genre_id = g.genre_id)
4. 0.077 885.197 ↑ 1.1 11 1

Nested Loop Left Join (cost=18,722.32..18,816.02 rows=12 width=43) (actual time=868.096..885.197 rows=11 loops=1)

5. 0.056 885.076 ↑ 1.0 11 1

Limit (cost=18,721.90..18,722.93 rows=11 width=39) (actual time=868.084..885.076 rows=11 loops=1)

6. 3.235 885.020 ↑ 310,938.0 16 1

Merge Semi Join (cost=18,721.43..484,755.35 rows=4,975,008 width=39) (actual time=868.072..885.020 rows=16 loops=1)

  • Merge Cond: (m.movie_id = m_1.movie_id)
7. 13.713 13.713 ↑ 162.0 61,421 1

Index Scan using movie_pkey on movie m (cost=0.43..441,144.05 rows=9,950,015 width=39) (actual time=0.008..13.713 rows=61,421 loops=1)

8. 0.006 868.072 ↑ 19.5 19 1

Merge Append (cost=18,721.00..18,727.93 rows=370 width=4) (actual time=868.060..868.072 rows=19 loops=1)

  • Sort Key: m_1.movie_id
9. 0.005 8.781 ↑ 18.5 10 1

Unique (cost=3,363.64..3,364.56 rows=185 width=4) (actual time=8.774..8.781 rows=10 loops=1)

10. 0.150 8.776 ↑ 15.4 12 1

Sort (cost=3,363.64..3,364.10 rows=185 width=4) (actual time=8.773..8.776 rows=12 loops=1)

  • Sort Key: m_1.movie_id
  • Sort Method: quicksort Memory: 50kB
11. 0.000 8.626 ↓ 2.9 536 1

Nested Loop (cost=154.39..3,356.67 rows=185 width=4) (actual time=3.792..8.626 rows=536 loops=1)

12. 0.086 6.582 ↓ 2.9 536 1

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

13. 1.135 4.831 ↓ 2.9 555 1

Bitmap Heap Scan on actor a (cost=153.53..817.07 rows=191 width=4) (actual time=3.773..4.831 rows=555 loops=1)

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

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

15. 2.013 2.013 ↓ 6.0 555 1

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

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

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

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

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

  • Index Cond: (actor_id = a.actor_id)
18. 2.144 2.144 ↑ 1.0 1 536

Index Only Scan using movie_pkey on movie m_1 (cost=0.43..1.32 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=536)

  • Index Cond: (movie_id = mc.movie_id)
  • Heap Fetches: 536
19. 0.225 859.285 ↑ 18.5 10 1

Sort (cost=15,357.35..15,357.81 rows=185 width=4) (actual time=859.284..859.285 rows=10 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 50kB
20. 76.178 859.060 ↓ 2.9 538 1

Gather (cost=1,000.86..15,348.53 rows=185 width=4) (actual time=12.302..859.060 rows=538 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
21. 2.679 782.882 ↓ 2.3 179 3

Nested Loop (cost=0.86..14,330.03 rows=77 width=4) (actual time=4.240..782.882 rows=179 loops=3)

22. 2.874 780.189 ↓ 2.3 179 3

Nested Loop (cost=0.42..14,228.57 rows=77 width=4) (actual time=4.160..780.189 rows=179 loops=3)

23. 777.301 777.301 ↓ 2.2 175 3

Parallel Seq Scan on director d (cost=0.00..13,267.02 rows=80 width=4) (actual time=3.104..777.301 rows=175 loops=3)

  • Filter: (((director_fname)::text ~~* 'joe'::text) OR ((director_lname)::text ~~* 'joe'::text))
  • Rows Removed by Filter: 333160
24. 0.014 0.014 ↑ 2.0 1 525

Index Scan using movie_director_director_id_index on movie_director md (cost=0.42..12.00 rows=2 width=8) (actual time=0.012..0.014 rows=1 loops=525)

  • Index Cond: (director_id = d.director_id)
25. 0.014 0.014 ↑ 1.0 1 538

Index Only Scan using movie_pkey on movie m2 (cost=0.43..1.32 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=538)

  • Index Cond: (movie_id = md.movie_id)
  • Heap Fetches: 538
26. 0.044 0.044 ↑ 1.0 1 11

Index Scan using movie_genres_movie_id_index on movie_genres mg (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=11)

  • Index Cond: (m.movie_id = movie_id)
27. 0.004 0.024 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=36) (actual time=0.024..0.024 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.020 0.020 ↑ 1.0 3 1

Seq Scan on genres g (cost=0.00..1.03 rows=3 width=36) (actual time=0.019..0.020 rows=3 loops=1)