explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S1wV

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 13,776.285 ↑ 1.0 1 1

Unique (cost=880,525.52..880,559.98 rows=1 width=71) (actual time=13,522.478..13,776.285 rows=1 loops=1)

2. 0.919 13,776.284 ↑ 1.0 1 1

Nested Loop (cost=880,525.52..880,559.97 rows=1 width=71) (actual time=13,522.477..13,776.284 rows=1 loops=1)

  • Join Filter: (d2.director_id = d3.director_id)
  • Rows Removed by Join Filter: 13650
3. 0.067 13,061.350 ↓ 27.0 27 1

Nested Loop (cost=866,232.16..866,261.36 rows=1 width=75) (actual time=12,810.481..13,061.350 rows=27 loops=1)

4. 36.288 13,060.108 ↓ 25.0 25 1

Merge Join (cost=866,231.73..866,260.88 rows=1 width=75) (actual time=12,810.404..13,060.108 rows=25 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
5. 0.302 571.035 ↓ 1.5 536 1

Sort (cost=3,137.86..3,138.75 rows=355 width=4) (actual time=570.944..571.035 rows=536 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 50kB
6. 0.533 570.733 ↓ 1.5 536 1

Nested Loop (cost=824.73..3,122.83 rows=355 width=4) (actual time=555.652..570.733 rows=536 loops=1)

7. 0.123 555.770 ↓ 2.9 555 1

Unique (cost=824.30..825.26 rows=191 width=4) (actual time=555.594..555.770 rows=555 loops=1)

8. 0.850 555.647 ↓ 2.9 555 1

Sort (cost=824.30..824.78 rows=191 width=4) (actual time=555.592..555.647 rows=555 loops=1)

  • Sort Key: ac.actor_id
  • Sort Method: quicksort Memory: 51kB
9. 549.361 554.797 ↓ 2.9 555 1

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

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

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

11. 3.362 3.362 ↓ 6.0 555 1

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

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

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

  • Index Cond: ((actor_lname)::text ~~* 'chris'::text)
13. 14.430 14.430 ↑ 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.021..0.026 rows=1 loops=555)

  • Index Cond: (actor_id = ac.actor_id)
14. 129.656 12,452.785 ↓ 598.2 595,190 1

Unique (cost=863,093.87..863,108.79 rows=995 width=71) (actual time=12,239.453..12,452.785 rows=595,190 loops=1)

15. 373.130 12,323.129 ↓ 598.2 595,190 1

Sort (cost=863,093.87..863,096.35 rows=995 width=71) (actual time=12,239.451..12,323.129 rows=595,190 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: 34280kB
16. 4,553.620 11,949.999 ↓ 598.7 595,736 1

GroupAggregate (cost=11.01..863,044.32 rows=995 width=71) (actual time=0.063..11,949.999 rows=595,736 loops=1)

  • Group Key: m.movie_id
  • Filter: (string_agg((g2.genre_name)::text, '/'::text) ~~* 'sci-fi'::text)
  • Rows Removed by Filter: 9354279
17. 1,560.556 7,396.379 ↓ 1.0 10,000,984 1

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

  • Merge Cond: (m.movie_id = g.movie_id)
18. 4,376.074 4,376.074 ↑ 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,376.074 rows=9,950,015 loops=1)

  • Filter: ((movie_year >= 0) AND (movie_year <= 2020))
19. 156.939 1,459.749 ↑ 1.0 1,022,016 1

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

20. 393.591 1,302.810 ↑ 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.033..1,302.810 rows=1,022,016 loops=1)

  • Join Filter: (g.genre_id = g2.genre_id)
  • Rows Removed by Join Filter: 660269
21. 909.219 909.219 ↑ 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.011..909.219 rows=1,022,016 loops=1)

22. 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)

23. 0.017 0.017 ↑ 1.0 3 1

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

24. 1.175 1.175 ↑ 1.0 1 25

Index Scan using movie_director_movie_id_index on movie_director d2 (cost=0.42..0.47 rows=1 width=8) (actual time=0.045..0.047 rows=1 loops=25)

  • Index Cond: (movie_id = m2.movie_id)
25. 1.620 714.015 ↓ 2.6 506 27

Unique (cost=14,293.36..14,294.32 rows=191 width=4) (actual time=26.364..26.445 rows=506 loops=27)

26. 0.933 712.395 ↓ 2.6 506 27

Sort (cost=14,293.36..14,293.84 rows=191 width=4) (actual time=26.364..26.385 rows=506 loops=27)

  • Sort Key: d3.director_id
  • Sort Method: quicksort Memory: 49kB
27. 76.874 711.462 ↓ 2.7 525 1

Gather (cost=1,000.00..14,286.13 rows=191 width=4) (actual time=1.274..711.462 rows=525 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
28. 634.588 634.588 ↓ 2.2 175 3

Parallel Seq Scan on director d3 (cost=0.00..13,267.02 rows=80 width=4) (actual time=4.766..634.588 rows=175 loops=3)

  • Filter: (((director_fname)::text ~~* 'joe'::text) OR ((director_lname)::text ~~* 'joe'::text))
  • Rows Removed by Filter: 333160