explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t4Zz

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 26,429.006 ↑ 33,157.3 3 1

Unique (cost=3,808,441.65..5,010,396.90 rows=99,472 width=71) (actual time=23,177.019..26,429.006 rows=3 loops=1)

2. 0.004 26,429.004 ↑ 331,574.7 3 1

Unique (cost=3,808,441.65..4,988,015.61 rows=994,724 width=71) (actual time=23,177.019..26,429.004 rows=3 loops=1)

3. 26.804 26,429.000 ↑ 3,315,745.3 3 1

Merge Join (cost=3,808,441.65..4,863,675.16 rows=9,947,236 width=71) (actual time=23,177.018..26,429.000 rows=3 loops=1)

  • Merge Cond: (m.movie_id = m_1.movie_id)
4. 88.721 12,474.163 ↑ 24.0 415,201 1

Unique (cost=3,363,011.94..3,512,220.48 rows=9,947,236 width=71) (actual time=12,325.042..12,474.163 rows=415,201 loops=1)

5. 563.292 12,385.442 ↑ 24.0 415,201 1

Sort (cost=3,363,011.94..3,387,880.03 rows=9,947,236 width=71) (actual time=12,325.039..12,385.442 rows=415,201 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: 56544kB
6. 4,765.065 11,822.150 ↑ 10.2 971,047 1

GroupAggregate (cost=175,656.12..982,866.45 rows=9,947,236 width=71) (actual time=784.894..11,822.150 rows=971,047 loops=1)

  • Group Key: m.movie_id
  • Filter: (lower(string_agg((g2.genre_name)::text, '/'::text)) ~~* '%'::text)
  • Rows Removed by Filter: 8978968
7. 1,515.192 7,057.085 ↓ 1.0 10,000,984 1

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

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

  • Filter: ((movie_year >= 0) AND (movie_year <= 2020))
9. 94.970 1,094.975 ↑ 1.0 1,022,016 1

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

10. 617.121 1,000.005 ↑ 1.0 1,022,016 1

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

  • Sort Key: g.movie_id
  • Sort Method: external merge Disk: 21352kB
11. 283.406 382.884 ↑ 1.0 1,022,016 1

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

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

13. 0.008 0.033 ↑ 423.3 3 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
14. 0.025 0.025 ↑ 423.3 3 1

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

15. 0.004 13,928.033 ↑ 2,487.0 4 1

Materialize (cost=445,429.71..1,077,930.56 rows=9,948 width=8) (actual time=10,851.966..13,928.033 rows=4 loops=1)

16. 291.301 13,928.029 ↑ 2,487.0 4 1

Merge Join (cost=445,429.71..1,077,905.69 rows=9,948 width=8) (actual time=10,851.963..13,928.029 rows=4 loops=1)

  • Merge Cond: (m_1.movie_id = m_2.movie_id)
17. 0.003 10,333.526 ↑ 2,487.0 4 1

Unique (cost=321,527.42..321,577.16 rows=9,948 width=4) (actual time=10,333.524..10,333.526 rows=4 loops=1)

18. 0.000 10,333.523 ↑ 2,487.0 4 1

Sort (cost=321,527.42..321,552.29 rows=9,948 width=4) (actual time=10,333.522..10,333.523 rows=4 loops=1)

  • Sort Key: m_1.movie_id
  • Sort Method: quicksort Memory: 25kB
19. 4,104.736 14,198.533 ↑ 2,487.0 4 1

Gather (cost=41,992.17..320,866.87 rows=9,948 width=4) (actual time=7,650.552..14,198.533 rows=4 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 7,883.922 10,093.797 ↑ 4,145.0 1 3

Parallel Hash Left Join (cost=40,992.17..318,872.07 rows=4,145 width=4) (actual time=7,369.187..10,093.797 rows=1 loops=3)

  • Hash Cond: (m_1.movie_id = m2.movie_id)
  • Filter: (concat(lower((ac.actor_fname)::text), ' ', lower((ac.actor_lname)::text)) % 'pratt'::text)
  • Rows Removed by Filter: 3331823
21. 1,165.208 1,165.208 ↑ 1.2 3,316,672 3

Parallel Seq Scan on movie m_1 (cost=0.00..224,208.96 rows=4,145,096 width=4) (actual time=0.139..1,165.208 rows=3,316,672 loops=3)

22. 361.405 1,044.667 ↑ 1.3 323,340 3

Parallel Hash (cost=33,563.44..33,563.44 rows=404,619 width=17) (actual time=1,044.667..1,044.667 rows=323,340 loops=3)

  • Buckets: 65536 Batches: 16 Memory Usage: 3808kB
23. 385.424 683.262 ↑ 1.3 323,340 3

Parallel Hash Left Join (cost=18,504.12..33,563.44 rows=404,619 width=17) (actual time=473.440..683.262 rows=323,340 loops=3)

  • Hash Cond: (m2.actor_id = ac.actor_id)
24. 55.936 55.936 ↑ 1.3 323,340 3

Parallel Seq Scan on movie_cast m2 (cost=0.00..8,393.19 rows=404,619 width=8) (actual time=0.119..55.936 rows=323,340 loops=3)

25. 171.932 241.902 ↑ 1.2 333,338 3

Parallel Hash (cost=10,853.72..10,853.72 rows=416,672 width=17) (actual time=241.902..241.902 rows=333,338 loops=3)

  • Buckets: 65536 Batches: 16 Memory Usage: 3904kB
26. 69.970 69.970 ↑ 1.2 333,338 3

Parallel Seq Scan on actor ac (cost=0.00..10,853.72 rows=416,672 width=17) (actual time=0.083..69.970 rows=333,338 loops=3)

27. 631.548 3,303.202 ↑ 2.3 4,254,089 1

Unique (cost=123,902.28..631,751.81 rows=9,948,231 width=4) (actual time=518.413..3,303.202 rows=4,254,089 loops=1)

28. 512.123 2,671.654 ↑ 2.3 4,273,464 1

Merge Left Join (cost=123,902.28..606,881.23 rows=9,948,231 width=4) (actual time=518.411..2,671.654 rows=4,273,464 loops=1)

  • Merge Cond: (m_2.movie_id = d2.movie_id)
29. 1,527.252 1,527.252 ↑ 2.3 4,254,089 1

Index Only Scan using movie_pkey on movie m_2 (cost=0.43..441,117.29 rows=9,948,231 width=4) (actual time=0.196..1,527.252 rows=4,254,089 loops=1)

  • Heap Fetches: 4254089
30. 35.957 632.279 ↑ 2.3 415,334 1

Materialize (cost=123,901.20..128,756.31 rows=971,022 width=8) (actual time=518.209..632.279 rows=415,334 loops=1)

31. 458.657 596.322 ↑ 2.3 415,334 1

Sort (cost=123,901.20..126,328.76 rows=971,022 width=8) (actual time=518.203..596.322 rows=415,334 loops=1)

  • Sort Key: d2.movie_id
  • Sort Method: external merge Disk: 17184kB
32. 137.665 137.665 ↑ 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.038..137.665 rows=971,017 loops=1)