explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WOZ4

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 3,100.746 ↓ 0.0 0 1

Sort (cost=3,709,052.45..3,709,052.56 rows=46 width=87) (actual time=3,100.746..3,100.746 rows=0 loops=1)

  • Sort Key: (((fff.sm + fff.sm1) + fff.sm2)) DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.000 3,100.741 ↓ 0.0 0 1

Subquery Scan on fff (cost=3,410,579.30..3,709,051.18 rows=46 width=87) (actual time=3,100.741..3,100.741 rows=0 loops=1)

3. 0.001 3,100.741 ↓ 0.0 0 1

Unique (cost=3,410,579.30..3,709,050.49 rows=46 width=83) (actual time=3,100.741..3,100.741 rows=0 loops=1)

4. 0.001 3,100.740 ↓ 0.0 0 1

Merge Join (cost=3,410,579.30..3,709,050.37 rows=46 width=83) (actual time=3,100.740..3,100.740 rows=0 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
5. 0.006 3,100.739 ↓ 0.0 0 1

Sort (cost=40,068.71..40,068.72 rows=4 width=16) (actual time=3,100.739..3,100.739 rows=0 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 25kB
6. 0.009 3,100.733 ↓ 0.0 0 1

Hash Join (cost=28,543.78..40,068.67 rows=4 width=16) (actual time=3,100.733..3,100.733 rows=0 loops=1)

  • Hash Cond: (d2.director_id = d33.director_id)
7. 0.001 1,615.649 ↑ 2,018.0 1 1

Nested Loop (cost=14,089.85..25,357.40 rows=2,018 width=16) (actual time=1,615.649..1,615.649 rows=1 loops=1)

8. 0.004 1,615.636 ↑ 1,857.0 1 1

Nested Loop (cost=14,089.42..24,463.40 rows=1,857 width=8) (actual time=1,615.636..1,615.636 rows=1 loops=1)

9. 0.001 1,615.617 ↑ 1,000.0 1 1

Unique (cost=14,089.00..14,101.50 rows=1,000 width=21) (actual time=1,615.617..1,615.617 rows=1 loops=1)

10. 0.844 1,615.616 ↑ 1,000.0 1 1

Sort (cost=14,089.00..14,091.50 rows=1,000 width=21) (actual time=1,615.616..1,615.616 rows=1 loops=1)

  • Sort Key: ac.actor_id, ac.actor_fname, ac.actor_lname, (similarity(concat(ac.actor_fname, ' ', ac.actor_lname), 'chris'::text))
  • Sort Method: quicksort Memory: 145kB
11. 63.937 1,614.772 ↓ 1.2 1,231 1

Gather (cost=1,000.00..14,039.17 rows=1,000 width=21) (actual time=1.577..1,614.772 rows=1,231 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 1,550.835 1,550.835 ↑ 1.0 410 3

Parallel Seq Scan on actor ac (cost=0.00..12,939.17 rows=417 width=21) (actual time=2.564..1,550.835 rows=410 loops=3)

  • Filter: (concat(actor_fname, ' ', actor_lname) % 'CHRIS'::text)
  • Rows Removed by Filter: 332927
13. 0.015 0.015 ↑ 2.0 1 1

Index Scan using movie_cast_actor_id_index on movie_cast m2 (cost=0.42..10.33 rows=2 width=8) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (actor_id = ac.actor_id)
14. 0.012 0.012 ↑ 1.0 1 1

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

  • Index Cond: (movie_id = m2.movie_id)
15. 0.002 1,485.075 ↓ 0.0 0 1

Hash (cost=14,441.44..14,441.44 rows=1,000 width=8) (actual time=1,485.074..1,485.075 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
16. 0.000 1,485.073 ↓ 0.0 0 1

Subquery Scan on d33 (cost=14,418.94..14,441.44 rows=1,000 width=8) (actual time=1,485.073..1,485.073 rows=0 loops=1)

17. 0.001 1,485.073 ↓ 0.0 0 1

Unique (cost=14,418.94..14,431.44 rows=1,000 width=21) (actual time=1,485.073..1,485.073 rows=0 loops=1)

18. 0.000 1,485.072 ↓ 0.0 0 1

Sort (cost=14,418.94..14,421.44 rows=1,000 width=21) (actual time=1,485.072..1,485.072 rows=0 loops=1)

  • Sort Key: d3.director_id, d3.director_fname, d3.director_lname, (similarity(concat(d3.director_fname, ' ', d3.director_lname), 'ahoj'::text))
  • Sort Method: quicksort Memory: 25kB
19. 54.350 1,488.984 ↓ 0.0 0 1

Gather (cost=1,000.00..14,369.11 rows=1,000 width=21) (actual time=1,485.066..1,488.984 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 1,434.634 1,434.634 ↓ 0.0 0 3

Parallel Seq Scan on director d3 (cost=0.00..13,269.11 rows=417 width=21) (actual time=1,434.634..1,434.634 rows=0 loops=3)

  • Filter: (concat(director_fname, ' ', director_lname) % 'ahoj'::text)
  • Rows Removed by Filter: 333335
21. 0.000 0.000 ↓ 0.0 0

Unique (cost=3,370,510.59..3,544,618.44 rows=9,949,020 width=75) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Sort (cost=3,370,510.59..3,395,383.14 rows=9,949,020 width=75) (never executed)

  • Sort Key: m.movie_id, m.movie_title, m.movie_country, m.movie_year, (similarity((m.movie_title)::text, '%'::text)), (string_agg((g2.genre_name)::text, '/'::text))
23. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=11.01..887,916.87 rows=9,949,020 width=75) (never executed)

  • Group Key: m.movie_id
  • Filter: (string_agg((g2.genre_name)::text, '/'::text) ~~* '%'::text)
24. 0.000 0.000 ↓ 0.0 0

Merge Left Join (cost=11.01..614,293.95 rows=9,950,015 width=71) (never executed)

  • Merge Cond: (m.movie_id = g.movie_id)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using movie_pkey on movie m (cost=0.43..490,894.13 rows=9,950,015 width=39) (never executed)

  • Filter: ((movie_year >= 0) AND (movie_year <= 2020))
26. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.42..85,755.98 rows=1,022,016 width=36) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.42..83,200.94 rows=1,022,016 width=36) (never executed)

  • Join Filter: (g.genre_id = g2.genre_id)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using movie_genres_movie_id_index on movie_genres g (cost=0.42..44,874.30 rows=1,022,016 width=8) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.04 rows=3 width=36) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on genres g2 (cost=0.00..1.03 rows=3 width=36) (never executed)