explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0cKi

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 16,236.715 ↑ 4.0 50 1

Unique (cost=882,426.34..910,563.74 rows=200 width=71) (actual time=15,847.918..16,236.715 rows=50 loops=1)

2. 57.954 16,236.670 ↑ 4.0 57 1

Merge Join (cost=882,426.34..910,563.17 rows=230 width=71) (actual time=15,847.917..16,236.670 rows=57 loops=1)

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

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
5. 2.400 159.786 ↓ 2.4 200 3

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

6. 39.504 157.375 ↓ 2.4 200 3

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

  • Hash Cond: (d2.movie_id = m2.movie_id)
7. 40.002 40.002 ↑ 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.124..40.002 rows=323,672 loops=3)

8. 0.183 77.869 ↓ 2.3 179 3

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

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

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

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

11. 0.076 7.148 ↓ 2.9 555 3

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

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

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

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

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

14. 2.392 2.392 ↓ 6.0 555 3

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

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

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

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

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

  • Index Cond: (director_id = d2.director_id)
  • Filter: (((director_fname)::text ~~* '%'::text) OR ((director_lname)::text ~~* '%'::text))
17. 197.296 15,949.149 ↑ 1.1 970,191 1

Unique (cost=861,170.36..876,515.91 rows=1,023,037 width=71) (actual time=15,618.469..15,949.149 rows=970,191 loops=1)

18. 602.147 15,751.853 ↑ 1.1 970,191 1

Sort (cost=861,170.36..863,727.95 rows=1,023,037 width=71) (actual time=15,618.468..15,751.853 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. 1,024.518 15,149.706 ↑ 1.1 971,047 1

GroupAggregate (cost=646,989.30..675,125.62 rows=1,023,037 width=71) (actual time=13,942.209..15,149.706 rows=971,047 loops=1)

  • Group Key: m.movie_id
  • Filter: (string_agg((g2.genre_name)::text, '/'::text) ~~* '%'::text)
20. 0.000 14,125.188 ↑ 1.0 1,022,016 1

Sort (cost=646,989.30..649,547.14 rows=1,023,139 width=71) (actual time=13,942.178..14,125.188 rows=1,022,016 loops=1)

  • Sort Key: m.movie_id
  • Sort Method: external merge Disk: 59128kB
21. 15,365.082 27,626.075 ↑ 1.0 1,022,016 1

Gather (cost=16,840.92..460,926.64 rows=1,023,139 width=71) (actual time=10,848.729..27,626.075 rows=1,022,016 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
22. 71.352 12,260.993 ↑ 1.3 340,672 3

Hash Join (cost=15,840.92..357,612.74 rows=426,308 width=71) (actual time=10,780.225..12,260.993 rows=340,672 loops=3)

  • Hash Cond: (g.genre_id = g2.genre_id)
23. 5,797.648 12,189.557 ↑ 1.3 340,672 3

Parallel Hash Join (cost=15,839.85..354,911.72 rows=426,308 width=43) (actual time=10,779.989..12,189.557 rows=340,672 loops=3)

  • Hash Cond: (m.movie_id = g.movie_id)
24. 6,167.808 6,167.808 ↑ 1.2 3,316,672 3

Parallel Seq Scan on movie m (cost=0.00..255,310.19 rows=4,145,438 width=39) (actual time=0.094..6,167.808 rows=3,316,672 loops=3)

  • Filter: (((movie_title)::text ~~* '%'::text) AND (movie_year >= 0) AND (movie_year <= 2020))
25. 160.911 224.101 ↑ 1.3 340,672 3

Parallel Hash (cost=8,844.49..8,844.49 rows=426,349 width=8) (actual time=224.101..224.101 rows=340,672 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3552kB
26. 63.190 63.190 ↑ 1.3 340,672 3

Parallel Seq Scan on movie_genres g (cost=0.00..8,844.49 rows=426,349 width=8) (actual time=0.085..63.190 rows=340,672 loops=3)

27. 0.010 0.084 ↑ 1.0 3 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.074 0.074 ↑ 1.0 3 3

Seq Scan on genres g2 (cost=0.00..1.03 rows=3 width=36) (actual time=0.073..0.074 rows=3 loops=3)

Planning time : 1.706 ms
Execution time : 31,446.668 ms