explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PR8w

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 75,159.869 ↑ 33,157.3 3 1

Unique (cost=3,811,346.72..5,013,301.97 rows=99,472 width=71) (actual time=72,018.132..75,159.869 rows=3 loops=1)

2. 0.002 75,159.866 ↑ 331,574.7 3 1

Unique (cost=3,811,346.72..4,990,920.68 rows=994,724 width=71) (actual time=72,018.132..75,159.866 rows=3 loops=1)

3. 24.208 75,159.864 ↑ 3,315,745.3 3 1

Merge Join (cost=3,811,346.72..4,866,580.23 rows=9,947,236 width=71) (actual time=72,018.131..75,159.864 rows=3 loops=1)

  • Merge Cond: (m.movie_id = m_1.movie_id)
4. 82.764 60,370.880 ↑ 24.0 415,201 1

Unique (cost=3,366,759.97..3,515,968.51 rows=9,947,236 width=71) (actual time=60,230.704..60,370.880 rows=415,201 loops=1)

5. 593.216 60,288.116 ↑ 24.0 415,201 1

Sort (cost=3,366,759.97..3,391,628.06 rows=9,947,236 width=71) (actual time=60,230.702..60,288.116 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,918.091 59,694.900 ↑ 10.2 971,047 1

GroupAggregate (cost=179,404.15..986,614.48 rows=9,947,236 width=71) (actual time=855.518..59,694.900 rows=971,047 loops=1)

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

Merge Left Join (cost=179,404.15..737,908.70 rows=9,948,231 width=71) (actual time=855.488..54,776.809 rows=10,000,984 loops=1)

  • Merge Cond: (m.movie_id = g.movie_id)
8. 51,907.709 51,907.709 ↓ 1.0 9,950,015 1

Index Scan using movie_pkey on movie m (cost=0.43..515,729.02 rows=9,948,231 width=39) (actual time=0.021..51,907.709 rows=9,950,015 loops=1)

  • Filter: (((movie_title)::text % '%'::text) OR ((movie_year >= 0) AND (movie_year <= 2020)))
9. 95.107 1,203.704 ↑ 1.0 1,022,016 1

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

10. 746.640 1,108.597 ↑ 1.0 1,022,016 1

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

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

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

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

13. 0.009 0.023 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.014 0.014 ↑ 1.0 3 1

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

15. 0.018 14,764.776 ↑ 2,487.0 4 1

Materialize (cost=444,586.75..1,077,087.61 rows=9,948 width=8) (actual time=11,787.419..14,764.776 rows=4 loops=1)

16. 268.506 14,764.758 ↑ 2,487.0 4 1

Merge Join (cost=444,586.75..1,077,062.74 rows=9,948 width=8) (actual time=11,787.402..14,764.758 rows=4 loops=1)

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

Unique (cost=320,684.47..320,734.21 rows=9,948 width=4) (actual time=11,272.853..11,272.855 rows=4 loops=1)

18. 0.000 11,272.852 ↑ 2,487.0 4 1

Sort (cost=320,684.47..320,709.34 rows=9,948 width=4) (actual time=11,272.852..11,272.852 rows=4 loops=1)

  • Sort Key: m_1.movie_id
  • Sort Method: quicksort Memory: 25kB
19. 1,151.511 11,821.264 ↑ 2,487.0 4 1

Gather (cost=41,992.17..320,023.91 rows=9,948 width=4) (actual time=6,609.023..11,821.264 rows=4 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 8,491.714 10,669.753 ↑ 4,145.0 1 3

Parallel Hash Left Join (cost=40,992.17..318,029.11 rows=4,145 width=4) (actual time=8,226.060..10,669.753 rows=1 loops=3)

  • Hash Cond: (m_1.movie_id = m2.movie_id)
  • Filter: (concat(ac.actor_fname, ' ', ac.actor_lname) % 'pratt'::text)
  • Rows Removed by Filter: 3331823
21. 1,158.745 1,158.745 ↑ 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.183..1,158.745 rows=3,316,672 loops=3)

22. 213.275 1,019.294 ↑ 1.3 323,340 3

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

  • Buckets: 65536 Batches: 16 Memory Usage: 3776kB
23. 450.081 806.019 ↑ 1.3 323,340 3

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

  • Hash Cond: (m2.actor_id = ac.actor_id)
24. 66.524 66.524 ↑ 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.126..66.524 rows=323,340 loops=3)

25. 210.087 289.414 ↑ 1.2 333,338 3

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

  • Buckets: 65536 Batches: 16 Memory Usage: 3904kB
26. 79.327 79.327 ↑ 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.100..79.327 rows=333,338 loops=3)

27. 618.696 3,223.397 ↑ 2.3 4,254,089 1

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

28. 500.746 2,604.701 ↑ 2.3 4,273,464 1

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

  • Merge Cond: (m_2.movie_id = d2.movie_id)
29. 1,477.758 1,477.758 ↑ 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.094..1,477.758 rows=4,254,089 loops=1)

  • Heap Fetches: 4254089
30. 35.142 626.197 ↑ 2.3 415,334 1

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

31. 450.967 591.055 ↑ 2.3 415,334 1

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

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