explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4ODy

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 18,693.058 ↓ 2.0 2 1

Unique (cost=343,201.70..352,570.20 rows=1 width=71) (actual time=18,693.033..18,693.058 rows=2 loops=1)

2. 0.025 18,693.055 ↓ 3.0 3 1

Merge Join (cost=343,201.70..352,570.20 rows=1 width=71) (actual time=18,693.031..18,693.055 rows=3 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
3. 0.008 9,414.623 ↓ 4.0 4 1

Sort (cost=17,593.37..17,593.37 rows=1 width=8) (actual time=9,414.622..9,414.623 rows=4 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 25kB
4. 1.257 9,414.615 ↓ 4.0 4 1

Hash Join (cost=15,123.77..17,593.36 rows=1 width=8) (actual time=807.802..9,414.615 rows=4 loops=1)

  • Hash Cond: (d2.director_id = d3.director_id)
5. 2.056 8,620.777 ↓ 1.6 600 1

Nested Loop (cost=825.15..3,293.73 rows=386 width=12) (actual time=15.206..8,620.777 rows=600 loops=1)

6. 1.415 46.473 ↓ 1.5 536 1

Nested Loop (cost=824.73..3,122.83 rows=355 width=4) (actual time=15.198..46.473 rows=536 loops=1)

7. 0.759 16.198 ↓ 2.9 555 1

Unique (cost=824.30..825.26 rows=191 width=4) (actual time=15.143..16.198 rows=555 loops=1)

8. 0.411 15.439 ↓ 2.9 555 1

Sort (cost=824.30..824.78 rows=191 width=4) (actual time=15.142..15.439 rows=555 loops=1)

  • Sort Key: ac.actor_id
  • Sort Method: quicksort Memory: 51kB
9. 10.320 15.028 ↓ 2.9 555 1

Bitmap Heap Scan on actor ac (cost=153.53..817.07 rows=191 width=4) (actual time=4.792..15.028 rows=555 loops=1)

  • Recheck Cond: (((actor_fname)::text ~~* 'chris'::text) OR ((actor_lname)::text ~~* 'chris'::text))
  • Heap Blocks: exact=531
10. 0.001 4.708 ↓ 0.0 0 1

BitmapOr (cost=153.53..153.53 rows=191 width=0) (actual time=4.708..4.708 rows=0 loops=1)

11. 2.815 2.815 ↓ 6.0 555 1

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

  • Index Cond: ((actor_fname)::text ~~* 'chris'::text)
12. 1.892 1.892 ↓ 0.0 0 1

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

  • Index Cond: ((actor_lname)::text ~~* 'chris'::text)
13. 28.860 28.860 ↑ 2.0 1 555

Index Scan using movie_cast_actor_id_index on movie_cast m2 (cost=0.42..12.00 rows=2 width=8) (actual time=0.040..0.052 rows=1 loops=555)

  • Index Cond: (actor_id = ac.actor_id)
14. 8,572.248 8,572.248 ↑ 1.0 1 536

Index Scan using movie_director_movie_id_index on movie_director d2 (cost=0.42..0.47 rows=1 width=8) (actual time=15.290..15.993 rows=1 loops=536)

  • Index Cond: (movie_id = m2.movie_id)
15. 0.050 792.581 ↓ 2.7 525 1

Hash (cost=14,296.23..14,296.23 rows=191 width=4) (actual time=792.581..792.581 rows=525 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
16. 0.066 792.531 ↓ 2.7 525 1

Unique (cost=14,293.36..14,294.32 rows=191 width=4) (actual time=792.446..792.531 rows=525 loops=1)

17. 0.231 792.465 ↓ 2.7 525 1

Sort (cost=14,293.36..14,293.84 rows=191 width=4) (actual time=792.445..792.465 rows=525 loops=1)

  • Sort Key: d3.director_id
  • Sort Method: quicksort Memory: 49kB
18. 106.495 792.234 ↓ 2.7 525 1

Gather (cost=1,000.00..14,286.13 rows=191 width=4) (actual time=0.786..792.234 rows=525 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 685.739 685.739 ↓ 2.2 175 3

Parallel Seq Scan on director d3 (cost=0.00..13,267.02 rows=80 width=4) (actual time=1.413..685.739 rows=175 loops=3)

  • Filter: (((director_fname)::text ~~* 'joe'::text) OR ((director_lname)::text ~~* 'joe'::text))
  • Rows Removed by Filter: 333160
20. 0.007 9,278.407 ↑ 113,557.3 3 1

Unique (cost=325,608.33..330,718.41 rows=340,672 width=71) (actual time=9,278.402..9,278.407 rows=3 loops=1)

21. 279.407 9,278.400 ↑ 113,557.3 3 1

Sort (cost=325,608.33..326,460.01 rows=340,672 width=71) (actual time=9,278.399..9,278.400 rows=3 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: 36912kB
22. 242.829 8,998.993 ↓ 1.9 638,939 1

GroupAggregate (cost=273,515.00..280,328.44 rows=340,672 width=71) (actual time=8,630.125..8,998.993 rows=638,939 loops=1)

  • Group Key: m.movie_id
23. 598.770 8,756.164 ↓ 1.9 660,261 1

Sort (cost=273,515.00..274,366.68 rows=340,672 width=71) (actual time=8,630.100..8,756.164 rows=660,261 loops=1)

  • Sort Key: m.movie_id
  • Sort Method: external merge Disk: 38008kB
24. 0.000 8,157.394 ↓ 1.9 660,261 1

Gather (cost=1,001.49..228,235.11 rows=340,672 width=71) (actual time=3.055..8,157.394 rows=660,261 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
25. 7,677.501 8,207.600 ↓ 1.6 220,087 3

Nested Loop (cost=1.49..193,167.91 rows=141,947 width=71) (actual time=0.491..8,207.600 rows=220,087 loops=3)

26. 102.540 530.065 ↓ 1.6 220,087 3

Hash Join (cost=1.05..11,537.44 rows=141,947 width=36) (actual time=0.337..530.065 rows=220,087 loops=3)

  • Hash Cond: (g.genre_id = g2.genre_id)
27. 427.439 427.439 ↑ 1.2 340,672 3

Parallel Seq Scan on movie_genres g (cost=0.00..8,839.40 rows=425,840 width=8) (actual time=0.019..427.439 rows=340,672 loops=3)

28. 0.010 0.086 ↑ 1.0 1 3

Hash (cost=1.04..1.04 rows=1 width=36) (actual time=0.086..0.086 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.076 0.076 ↑ 1.0 1 3

Seq Scan on genres g2 (cost=0.00..1.04 rows=1 width=36) (actual time=0.074..0.076 rows=1 loops=3)

  • Filter: ((genre_name)::text ~~* 'sci-fi'::text)
  • Rows Removed by Filter: 2
30. 0.034 0.034 ↑ 1.0 1 660,261

Index Scan using movie_pkey on movie m (cost=0.43..1.28 rows=1 width=39) (actual time=0.034..0.034 rows=1 loops=660,261)

  • Index Cond: (movie_id = g.movie_id)
  • Filter: ((movie_year >= 0) AND (movie_year <= 2020))