explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ay3V

Settings
# exclusive inclusive rows x rows loops node
1. 0.043 25,161.355 ↑ 4.0 50 1

Unique (cost=3,388,273.23..3,661,873.70 rows=200 width=71) (actual time=24,778.714..25,161.355 rows=50 loops=1)

2. 56.684 25,161.312 ↑ 39.3 57 1

Merge Join (cost=3,388,273.23..3,661,868.10 rows=2,240 width=71) (actual time=24,778.713..25,161.312 rows=57 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
3. 0.241 251.422 ↓ 3.0 600 1

Sort (cost=21,255.99..21,256.49 rows=201 width=8) (actual time=251.298..251.422 rows=600 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 53kB
4. 80.993 251.181 ↓ 3.0 600 1

Gather (cost=11,276.16..21,248.30 rows=201 width=8) (actual time=156.374..251.181 rows=600 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 19.235 170.188 ↓ 2.4 200 3

Nested Loop (cost=10,276.16..20,228.20 rows=84 width=8) (actual time=89.912..170.188 rows=200 loops=3)

6. 37.633 150.857 ↓ 2.4 200 3

Parallel Hash Join (cost=10,275.74..20,187.23 rows=84 width=12) (actual time=76.426..150.857 rows=200 loops=3)

  • Hash Cond: (d2.movie_id = m2.movie_id)
7. 37.454 37.454 ↑ 1.3 323,672 3

Parallel Seq Scan on movie_director d2 (cost=0.00..8,393.92 rows=404,592 width=8) (actual time=0.122..37.454 rows=323,672 loops=3)

8. 0.179 75.770 ↓ 2.3 179 3

Parallel Hash (cost=10,274.77..10,274.77 rows=77 width=4) (actual time=75.770..75.770 rows=179 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 104kB
9. 28.727 75.591 ↓ 2.3 179 3

Hash Join (cost=819.46..10,274.77 rows=77 width=4) (actual time=9.394..75.591 rows=179 loops=3)

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

11. 0.091 8.740 ↓ 2.9 555 3

Hash (cost=817.07..817.07 rows=191 width=4) (actual time=8.740..8.740 rows=555 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
12. 4.350 8.649 ↓ 2.9 555 3

Bitmap Heap Scan on actor ac (cost=153.53..817.07 rows=191 width=4) (actual time=4.388..8.649 rows=555 loops=3)

  • Recheck Cond: (((actor_fname)::text ~~* 'chris'::text) OR ((actor_lname)::text ~~* 'chris'::text))
  • Heap Blocks: exact=531
13. 0.002 4.299 ↓ 0.0 0 3

BitmapOr (cost=153.53..153.53 rows=191 width=0) (actual time=4.299..4.299 rows=0 loops=3)

14. 2.439 2.439 ↓ 6.0 555 3

Bitmap Index Scan on fname_idx (cost=0.00..76.70 rows=93 width=0) (actual time=2.439..2.439 rows=555 loops=3)

  • Index Cond: ((actor_fname)::text ~~* 'chris'::text)
15. 1.858 1.858 ↓ 0.0 0 3

Bitmap Index Scan on lname_idx (cost=0.00..76.73 rows=98 width=0) (actual time=1.858..1.858 rows=0 loops=3)

  • Index Cond: ((actor_lname)::text ~~* 'chris'::text)
16. 0.096 0.096 ↑ 1.0 1 600

Index Scan using director_pkey on director d3 (cost=0.42..0.49 rows=1 width=4) (actual time=0.096..0.096 rows=1 loops=600)

  • Index Cond: (director_id = d2.director_id)
  • Filter: (((director_fname)::text ~~* '%'::text) OR ((director_lname)::text ~~* '%'::text))
17. 193.899 24,853.206 ↑ 10.3 970,191 1

Unique (cost=3,367,017.24..3,516,238.07 rows=9,948,055 width=71) (actual time=24,527.410..24,853.206 rows=970,191 loops=1)

18. 674.751 24,659.307 ↑ 10.3 970,191 1

Sort (cost=3,367,017.24..3,391,887.38 rows=9,948,055 width=71) (actual time=24,527.395..24,659.307 rows=970,191 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
19. 4,108.568 23,984.556 ↑ 10.2 971,047 1

GroupAggregate (cost=179,404.15..986,676.15 rows=9,948,055 width=71) (actual time=745.900..23,984.556 rows=971,047 loops=1)

  • Group Key: m.movie_id
  • Filter: (string_agg((g2.genre_name)::text, '/'::text) ~~* '%'::text)
  • Rows Removed by Filter: 8978968
20. 1,448.188 19,875.988 ↓ 1.0 10,000,984 1

Merge Left Join (cost=179,404.15..737,949.90 rows=9,949,050 width=71) (actual time=745.866..19,875.988 rows=10,000,984 loops=1)

  • Merge Cond: (m.movie_id = g.movie_id)
21. 17,397.250 17,397.250 ↓ 1.0 9,950,015 1

Index Scan using movie_pkey on movie m (cost=0.43..515,769.16 rows=9,949,050 width=39) (actual time=0.017..17,397.250 rows=9,950,015 loops=1)

  • Filter: (((movie_title)::text ~~* '%'::text) AND (movie_year >= 0) AND (movie_year <= 2020))
22. 82.426 1,030.550 ↑ 1.0 1,022,016 1

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

23. 654.948 948.124 ↑ 1.0 1,022,016 1

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

  • Sort Key: g.movie_id
  • Sort Method: external merge Disk: 21352kB
24. 192.315 293.176 ↑ 1.0 1,022,016 1

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

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

26. 0.003 0.041 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.038 0.038 ↑ 1.0 3 1

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