explain.depesz.com

PostgreSQL's explain analyze made readable

Result: emYm

Settings
# exclusive inclusive rows x rows loops node
1. 0.125 24,669.020 ↑ 1.6 123 1

Sort (cost=4,001,044.43..4,001,044.93 rows=200 width=87) (actual time=24,669.014..24,669.020 rows=123 loops=1)

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

Subquery Scan on fff (cost=3,702,027.70..4,001,036.79 rows=200 width=87) (actual time=24,235.848..24,668.895 rows=123 loops=1)

3. 0.104 24,668.804 ↑ 1.6 123 1

Unique (cost=3,702,027.70..4,001,033.79 rows=200 width=83) (actual time=24,235.845..24,668.804 rows=123 loops=1)

4. 58.302 24,668.700 ↑ 327.3 139 1

Merge Join (cost=3,702,027.70..4,000,920.07 rows=45,489 width=83) (actual time=24,235.844..24,668.700 rows=139 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
5. 0.644 6,587.269 ↑ 3.0 1,362 1

Sort (cost=233,273.21..233,283.44 rows=4,090 width=16) (actual time=6,587.017..6,587.269 rows=1,362 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 112kB
6. 58.527 6,586.625 ↑ 3.0 1,362 1

Merge Join (cost=207,966.41..233,027.86 rows=4,090 width=16) (actual time=6,234.787..6,586.625 rows=1,362 loops=1)

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

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

8. 480.585 3,921.188 ↑ 1.0 998,639 1

Sort (cost=142,185.33..144,685.34 rows=1,000,004 width=21) (actual time=3,813.230..3,921.188 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,440.603 3,440.603 ↑ 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.044..3,440.603 rows=1,000,004 loops=1)

10. 0.722 2,421.738 ↑ 1.5 1,362 1

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

  • Sort Key: d2.director_id
  • Sort Method: quicksort Memory: 112kB
11. 82.848 2,421.016 ↑ 1.5 1,362 1

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

  • Hash Cond: (d2.movie_id = m2.movie_id)
12. 89.214 89.214 ↑ 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.040..89.214 rows=971,017 loops=1)

13. 0.395 2,248.954 ↑ 1.5 1,232 1

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

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

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

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

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

16. 0.227 1,725.771 ↓ 1.2 1,231 1

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

17. 1.037 1,725.544 ↓ 1.2 1,231 1

Sort (cost=14,089.00..14,091.50 rows=1,000 width=21) (actual time=1,725.490..1,725.544 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. 58.190 1,724.507 ↓ 1.2 1,231 1

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

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

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

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

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

  • Buckets: 131072 Batches: 16 Memory Usage: 3399kB
21. 139.664 139.664 ↑ 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..139.664 rows=970,020 loops=1)

22. 230.337 18,023.129 ↑ 10.2 970,474 1

Unique (cost=3,468,754.48..3,642,831.11 rows=9,947,236 width=75) (actual time=17,648.821..18,023.129 rows=970,474 loops=1)

23. 721.902 17,792.792 ↑ 10.2 970,474 1

Sort (cost=3,468,754.48..3,493,622.57 rows=9,947,236 width=75) (actual time=17,648.819..17,792.792 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. 9,660.703 17,070.890 ↑ 10.2 971,047 1

GroupAggregate (cost=179,404.15..986,611.99 rows=9,947,236 width=75) (actual time=693.453..17,070.890 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,592.197 7,410.187 ↓ 1.0 10,000,984 1

Merge Left Join (cost=179,404.15..713,038.13 rows=9,948,231 width=71) (actual time=693.430..7,410.187 rows=10,000,984 loops=1)

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

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

Materialize (cost=179,403.20..184,519.39 rows=1,023,238 width=36) (actual time=693.405..1,035.094 rows=1,022,016 loops=1)

28. 638.187 938.220 ↑ 1.0 1,022,016 1

Sort (cost=179,403.20..181,961.30 rows=1,023,238 width=36) (actual time=693.400..938.220 rows=1,022,016 loops=1)

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

Hash Left Join (cost=1.07..21,294.95 rows=1,023,238 width=36) (actual time=0.070..300.033 rows=1,022,016 loops=1)

  • Hash Cond: (g.genre_id = g2.genre_id)
30. 102.314 102.314 ↑ 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.036..102.314 rows=1,022,016 loops=1)

31. 0.007 0.027 ↑ 1.0 3 1

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

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

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

Planning time : 0.848 ms
Execution time : 24,692.689 ms