explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bz5Q

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 10,651.461 ↓ 2.0 2 1

Unique (cost=343,201.70..352,570.20 rows=1 width=71) (actual time=10,651.452..10,651.461 rows=2 loops=1)

2. 0.009 10,651.458 ↓ 3.0 3 1

Merge Join (cost=343,201.70..352,570.20 rows=1 width=71) (actual time=10,651.450..10,651.458 rows=3 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
3. 0.009 948.797 ↓ 4.0 4 1

Sort (cost=17,593.37..17,593.37 rows=1 width=8) (actual time=948.796..948.797 rows=4 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 25kB
4. 0.164 948.788 ↓ 4.0 4 1

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

  • Hash Cond: (d2.director_id = d3.director_id)
5. 0.250 46.574 ↓ 1.6 600 1

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

6. 0.500 29.172 ↓ 1.5 536 1

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

7. 0.140 13.687 ↓ 2.9 555 1

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

8. 0.225 13.547 ↓ 2.9 555 1

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

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

Bitmap Heap Scan on actor ac (cost=153.53..817.07 rows=191 width=4) (actual time=4.639..13.322 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.555 ↓ 0.0 0 1

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

11. 2.419 2.419 ↓ 6.0 555 1

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

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

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

  • Index Cond: ((actor_lname)::text ~~* 'chris'::text)
13. 14.985 14.985 ↑ 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.022..0.027 rows=1 loops=555)

  • Index Cond: (actor_id = ac.actor_id)
14. 17.152 17.152 ↑ 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=0.030..0.032 rows=1 loops=536)

  • Index Cond: (movie_id = m2.movie_id)
15. 0.066 902.050 ↓ 2.7 525 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
16. 0.093 901.984 ↓ 2.7 525 1

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

17. 0.432 901.891 ↓ 2.7 525 1

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

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

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

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

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

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

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

21. 312.025 9,702.646 ↑ 113,557.3 3 1

Sort (cost=325,608.33..326,460.01 rows=340,672 width=71) (actual time=9,702.646..9,702.646 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. 265.178 9,390.621 ↓ 1.9 638,939 1

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

  • Group Key: m.movie_id
23. 660.562 9,125.443 ↓ 1.9 660,261 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
25. 8,239.491 8,529.685 ↓ 1.6 220,087 3

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

26. 114.146 290.157 ↓ 1.6 220,087 3

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

  • Hash Cond: (g.genre_id = g2.genre_id)
27. 175.901 175.901 ↑ 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.076..175.901 rows=340,672 loops=3)

28. 0.011 0.110 ↑ 1.0 1 3

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

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

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

  • Filter: ((genre_name)::text ~~* 'sci-fi'::text)
  • Rows Removed by Filter: 2
30. 0.037 0.037 ↑ 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.037..0.037 rows=1 loops=660,261)

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