explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kJQZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 9,656.509 ↑ 100.0 2 1

Unique (cost=343,982.06..357,239.26 rows=200 width=71) (actual time=9,656.255..9,656.509 rows=2 loops=1)

2. 0.006 9,656.507 ↑ 145,744.0 2 1

Merge Join (cost=343,982.06..356,510.54 rows=291,488 width=71) (actual time=9,656.254..9,656.507 rows=2 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
3. 0.073 808.212 ↑ 61.7 3 1

Merge Join (cost=18,373.72..18,382.05 rows=185 width=8) (actual time=807.962..808.212 rows=3 loops=1)

  • Merge Cond: (m2.movie_id = d2.movie_id)
4. 0.059 31.142 ↓ 2.9 534 1

Unique (cost=3,119.69..3,120.62 rows=185 width=4) (actual time=31.059..31.142 rows=534 loops=1)

5. 0.257 31.083 ↓ 2.9 536 1

Sort (cost=3,119.69..3,120.15 rows=185 width=4) (actual time=31.058..31.083 rows=536 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 50kB
6. 0.131 30.826 ↓ 2.9 536 1

Nested Loop (cost=153.95..3,112.72 rows=185 width=4) (actual time=5.400..30.826 rows=536 loops=1)

7. 8.869 14.045 ↓ 2.9 555 1

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

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

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

9. 2.799 2.799 ↓ 6.0 555 1

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

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

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

  • Index Cond: ((actor_lname)::text ~~* 'chris'::text)
11. 16.650 16.650 ↑ 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.024..0.030 rows=1 loops=555)

  • Index Cond: (actor_id = ac.actor_id)
12. 0.061 776.997 ↓ 2.9 538 1

Unique (cost=15,254.03..15,254.96 rows=185 width=4) (actual time=776.900..776.997 rows=538 loops=1)

13. 0.413 776.936 ↓ 2.9 538 1

Sort (cost=15,254.03..15,254.49 rows=185 width=4) (actual time=776.899..776.936 rows=538 loops=1)

  • Sort Key: d2.movie_id
  • Sort Method: quicksort Memory: 50kB
14. 65.624 776.523 ↓ 2.9 538 1

Gather (cost=1,000.42..15,247.07 rows=185 width=4) (actual time=1.161..776.523 rows=538 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 9.889 710.899 ↓ 2.3 179 3

Nested Loop (cost=0.42..14,228.57 rows=77 width=4) (actual time=6.439..710.899 rows=179 loops=3)

16. 700.955 700.955 ↓ 2.2 175 3

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

  • Filter: (((director_fname)::text ~~* 'joe'::text) OR ((director_lname)::text ~~* 'joe'::text))
  • Rows Removed by Filter: 333160
17. 0.055 0.055 ↑ 2.0 1 525

Index Scan using movie_director_director_id_index on movie_director d2 (cost=0.42..12.00 rows=2 width=8) (actual time=0.046..0.055 rows=1 loops=525)

  • Index Cond: (director_id = d3.director_id)
18. 0.004 8,848.289 ↑ 113,557.3 3 1

Unique (cost=325,608.33..330,718.41 rows=340,672 width=71) (actual time=8,848.286..8,848.289 rows=3 loops=1)

19. 273.968 8,848.285 ↑ 113,557.3 3 1

Sort (cost=325,608.33..326,460.01 rows=340,672 width=71) (actual time=8,848.285..8,848.285 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
20. 247.239 8,574.317 ↓ 1.9 638,939 1

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

  • Group Key: m.movie_id
21. 602.600 8,327.078 ↓ 1.9 660,261 1

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

  • Sort Key: m.movie_id
  • Sort Method: external merge Disk: 38016kB
22. 0.000 7,724.478 ↓ 1.9 660,261 1

Gather (cost=1,001.49..228,235.11 rows=340,672 width=71) (actual time=1.121..7,724.478 rows=660,261 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
23. 7,636.272 7,802.172 ↓ 1.6 220,087 3

Nested Loop (cost=1.49..193,167.91 rows=141,947 width=71) (actual time=0.582..7,802.172 rows=220,087 loops=3)

24. 104.745 165.866 ↓ 1.6 220,087 3

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

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

26. 0.011 0.089 ↑ 1.0 1 3

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

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

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

  • Filter: ((genre_name)::text ~~* 'sci-fi'::text)
  • Rows Removed by Filter: 2
28. 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))