explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KqFv

Settings
# exclusive inclusive rows x rows loops node
1. 0.107 30,530.367 ↑ 1.6 123 1

Sort (cost=3,997,296.40..3,997,296.90 rows=200 width=87) (actual time=30,530.361..30,530.367 rows=123 loops=1)

  • Sort Key: (((fff.sm + fff.sm1) + fff.sm2)) DESC
  • Sort Method: quicksort Memory: 42kB
2. 0.082 30,530.260 ↑ 1.6 123 1

Subquery Scan on fff (cost=3,698,279.67..3,997,288.76 rows=200 width=87) (actual time=30,113.705..30,530.260 rows=123 loops=1)

3. 0.077 30,530.178 ↑ 1.6 123 1

Unique (cost=3,698,279.67..3,997,285.76 rows=200 width=83) (actual time=30,113.703..30,530.178 rows=123 loops=1)

4. 59.170 30,530.101 ↑ 327.3 139 1

Merge Join (cost=3,698,279.67..3,997,172.04 rows=45,489 width=83) (actual time=30,113.702..30,530.101 rows=139 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
5. 0.618 11,322.035 ↑ 3.0 1,362 1

Sort (cost=233,273.21..233,283.44 rows=4,090 width=16) (actual time=11,321.839..11,322.035 rows=1,362 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 112kB
6. 60.934 11,321.417 ↑ 3.0 1,362 1

Merge Join (cost=207,966.41..233,027.86 rows=4,090 width=16) (actual time=10,956.855..11,321.417 rows=1,362 loops=1)

  • Merge Cond: (d3.director_id = d2.director_id)
7. 190.995 4,319.586 ↑ 1.0 998,639 1

Unique (cost=142,185.33..154,685.38 rows=1,000,004 width=21) (actual time=4,016.133..4,319.586 rows=998,639 loops=1)

8. 498.842 4,128.591 ↑ 1.0 998,639 1

Sort (cost=142,185.33..144,685.34 rows=1,000,004 width=21) (actual time=4,016.131..4,128.591 rows=998,639 loops=1)

  • Sort Key: d3.director_id, d3.director_fname, d3.director_lname, (similarity(concat(d3.director_fname, ' ', d3.director_lname), '%'::text))
  • Sort Method: external merge Disk: 33192kB
9. 3,629.749 3,629.749 ↑ 1.0 1,000,004 1

Seq Scan on director d3 (cost=0.00..22,017.06 rows=1,000,004 width=21) (actual time=0.050..3,629.749 rows=1,000,004 loops=1)

10. 1.426 6,940.897 ↑ 1.5 1,362 1

Sort (cost=65,781.08..65,786.12 rows=2,016 width=16) (actual time=6,940.716..6,940.897 rows=1,362 loops=1)

  • Sort Key: d2.director_id
  • Sort Method: quicksort Memory: 112kB
11. 107.235 6,939.471 ↑ 1.5 1,362 1

Hash Join (cost=47,950.71..65,670.43 rows=2,016 width=16) (actual time=2,017.189..6,939.471 rows=1,362 loops=1)

  • Hash Cond: (d2.movie_id = m2.movie_id)
12. 4,815.101 4,815.101 ↑ 1.0 971,017 1

Seq Scan on movie_director d2 (cost=0.00..14,058.22 rows=971,022 width=8) (actual time=0.039..4,815.101 rows=971,017 loops=1)

13. 0.312 2,017.135 ↑ 1.5 1,232 1

Hash (cost=47,927.50..47,927.50 rows=1,857 width=8) (actual time=2,017.135..2,017.135 rows=1,232 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 65kB
14. 193.831 2,016.823 ↑ 1.5 1,232 1

Hash Join (cost=44,079.43..47,927.50 rows=1,857 width=8) (actual time=1,830.297..2,016.823 rows=1,232 loops=1)

  • Hash Cond: (ac2.actor_id = m2.actor_id)
15. 0.145 1,522.563 ↓ 1.2 1,231 1

Subquery Scan on ac2 (cost=14,089.00..14,111.50 rows=1,000 width=8) (actual time=1,522.148..1,522.563 rows=1,231 loops=1)

16. 0.219 1,522.418 ↓ 1.2 1,231 1

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

17. 0.745 1,522.199 ↓ 1.2 1,231 1

Sort (cost=14,089.00..14,091.50 rows=1,000 width=21) (actual time=1,522.145..1,522.199 rows=1,231 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
18. 71.385 1,521.454 ↓ 1.2 1,231 1

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

  • Workers Planned: 2
  • Workers Launched: 2
19. 1,450.069 1,450.069 ↑ 1.0 410 3

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

  • Filter: (concat(actor_fname, ' ', actor_lname) % 'CHRIS'::text)
  • Rows Removed by Filter: 332927
20. 167.371 300.429 ↑ 1.0 970,020 1

Hash (cost=14,057.86..14,057.86 rows=971,086 width=8) (actual time=300.429..300.429 rows=970,020 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 3399kB
21. 133.058 133.058 ↑ 1.0 970,020 1

Seq Scan on movie_cast m2 (cost=0.00..14,057.86 rows=971,086 width=8) (actual time=0.035..133.058 rows=970,020 loops=1)

22. 222.579 19,148.896 ↑ 10.2 970,474 1

Unique (cost=3,465,006.45..3,639,083.08 rows=9,947,236 width=75) (actual time=18,791.858..19,148.896 rows=970,474 loops=1)

23. 658.246 18,926.317 ↑ 10.2 970,474 1

Sort (cost=3,465,006.45..3,489,874.54 rows=9,947,236 width=75) (actual time=18,791.857..18,926.317 rows=970,474 loops=1)

  • 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))
  • Sort Method: external merge Disk: 60344kB
24. 8,978.075 18,268.071 ↑ 10.2 971,047 1

GroupAggregate (cost=175,656.12..982,863.96 rows=9,947,236 width=75) (actual time=3,098.863..18,268.071 rows=971,047 loops=1)

  • Group Key: m.movie_id
  • Filter: (string_agg((g2.genre_name)::text, '/'::text) ~~* '%'::text)
  • Rows Removed by Filter: 8978968
25. 1,476.004 9,289.996 ↓ 1.0 10,000,984 1

Merge Left Join (cost=175,656.12..709,290.10 rows=9,948,231 width=71) (actual time=3,098.840..9,289.996 rows=10,000,984 loops=1)

  • Merge Cond: (m.movie_id = g.movie_id)
26. 4,416.790 4,416.790 ↓ 1.0 9,950,015 1

Index Scan using movie_pkey on movie m (cost=0.43..490,858.45 rows=9,948,231 width=39) (actual time=0.021..4,416.790 rows=9,950,015 loops=1)

  • Filter: ((movie_year >= 0) AND (movie_year <= 2020))
27. 86.539 3,397.202 ↑ 1.0 1,022,016 1

Materialize (cost=175,655.17..180,771.36 rows=1,023,238 width=36) (actual time=3,098.814..3,397.202 rows=1,022,016 loops=1)

28. 623.354 3,310.663 ↑ 1.0 1,022,016 1

Sort (cost=175,655.17..178,213.27 rows=1,023,238 width=36) (actual time=3,098.808..3,310.663 rows=1,022,016 loops=1)

  • Sort Key: g.movie_id
  • Sort Method: external merge Disk: 21352kB
29. 208.456 2,687.309 ↑ 1.0 1,022,016 1

Hash Left Join (cost=38.58..17,546.92 rows=1,023,238 width=36) (actual time=11.732..2,687.309 rows=1,022,016 loops=1)

  • Hash Cond: (g.genre_id = g2.genre_id)
30. 2,467.172 2,467.172 ↑ 1.0 1,022,016 1

Seq Scan on movie_genres g (cost=0.00..14,813.38 rows=1,023,238 width=8) (actual time=0.035..2,467.172 rows=1,022,016 loops=1)

31. 0.007 11.681 ↑ 423.3 3 1

Hash (cost=22.70..22.70 rows=1,270 width=36) (actual time=11.681..11.681 rows=3 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
32. 11.674 11.674 ↑ 423.3 3 1

Seq Scan on genres g2 (cost=0.00..22.70 rows=1,270 width=36) (actual time=11.673..11.674 rows=3 loops=1)

Planning time : 1.271 ms
Execution time : 30,553.762 ms