explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l0mS

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 8,127.102 ↑ 1.0 11 1

Limit (cost=221.94..356.32 rows=11 width=71) (actual time=923.166..8,127.102 rows=11 loops=1)

2. 0.065 8,127.035 ↑ 31,938.0 16 1

GroupAggregate (cost=160.86..6,242,672.11 rows=511,008 width=71) (actual time=4.734..8,127.035 rows=16 loops=1)

  • Group Key: m.movie_id
3. 0.021 8,126.970 ↑ 28,389.3 18 1

Nested Loop (cost=160.86..6,233,729.47 rows=511,008 width=71) (actual time=4.709..8,126.970 rows=18 loops=1)

  • Join Filter: (mg.genre_id = g.genre_id)
  • Rows Removed by Join Filter: 13
4. 4,293.856 8,126.931 ↑ 28,389.3 18 1

Nested Loop Semi Join (cost=160.86..6,214,565.63 rows=511,008 width=43) (actual time=4.690..8,126.931 rows=18 loops=1)

  • Join Filter: (m.movie_id = m_1.movie_id)
  • Rows Removed by Join Filter: 58159970
5. 77.326 475.031 ↑ 18.9 54,162 1

Merge Join (cost=6.48..523,665.16 rows=1,022,016 width=47) (actual time=0.017..475.031 rows=54,162 loops=1)

  • Merge Cond: (m.movie_id = mg.movie_id)
6. 295.785 295.785 ↑ 18.8 528,317 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..295.785 rows=528,317 loops=1)

7. 101.920 101.920 ↑ 18.9 54,162 1

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

8. 2,436.653 3,358.044 ↓ 2.9 1,074 54,162

Materialize (cost=154.39..18,712.60 rows=370 width=4) (actual time=0.000..0.062 rows=1,074 loops=54,162)

9. 0.096 921.391 ↓ 2.9 1,074 1

Append (cost=154.39..18,710.75 rows=370 width=4) (actual time=4.660..921.391 rows=1,074 loops=1)

10. 0.558 45.314 ↓ 2.9 536 1

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

11. 0.239 28.140 ↓ 2.9 536 1

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

12. 8.345 12.916 ↓ 2.9 555 1

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

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

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

14. 2.717 2.717 ↓ 6.0 555 1

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

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

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

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

  • Index Cond: (actor_id = a.actor_id)
17. 16.616 16.616 ↑ 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.031..0.031 rows=1 loops=536)

  • Index Cond: (movie_id = mc.movie_id)
  • Heap Fetches: 536
18. 57.380 875.981 ↓ 2.9 538 1

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

  • Workers Planned: 2
  • Workers Launched: 2
19. 8.620 818.601 ↓ 2.3 179 3

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

20. 8.908 809.934 ↓ 2.3 179 3

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

21. 800.978 800.978 ↓ 2.2 175 3

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

  • Filter: (((director_fname)::text ~~* 'joe'::text) OR ((director_lname)::text ~~* 'joe'::text))
  • Rows Removed by Filter: 333160
22. 0.048 0.048 ↑ 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.041..0.048 rows=1 loops=525)

  • Index Cond: (director_id = d.director_id)
23. 0.047 0.047 ↑ 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.047..0.047 rows=1 loops=538)

  • Index Cond: (movie_id = md.movie_id)
  • Heap Fetches: 538
24. 0.001 0.018 ↑ 1.5 2 18

Materialize (cost=0.00..1.04 rows=3 width=36) (actual time=0.001..0.001 rows=2 loops=18)

25. 0.017 0.017 ↑ 1.0 3 1

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

Planning time : 2.355 ms
Execution time : 8,127.368 ms