explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rVqo

Settings
# exclusive inclusive rows x rows loops node
1. 0.425 18,337.038 ↓ 3.2 637 1

Sort (cost=3,692,043.44..3,692,043.94 rows=200 width=83) (actual time=18,337.005..18,337.038 rows=637 loops=1)

  • Sort Key: ((foo2.sm + foo2.sm1)) DESC
  • Sort Method: quicksort Memory: 113kB
2. 0.180 18,336.613 ↓ 3.2 637 1

Subquery Scan on foo2 (cost=3,393,027.33..3,692,035.79 rows=200 width=83) (actual time=17,916.054..18,336.613 rows=637 loops=1)

3. 0.227 18,336.433 ↓ 3.2 637 1

Unique (cost=3,393,027.33..3,692,033.29 rows=200 width=79) (actual time=17,916.051..18,336.433 rows=637 loops=1)

4. 57.432 18,336.206 ↑ 64.6 640 1

Merge Join (cost=3,393,027.33..3,691,929.94 rows=41,340 width=79) (actual time=17,916.046..18,336.206 rows=640 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
5. 3.706 433.396 ↓ 1.7 6,314 1

Sort (cost=22,516.74..22,526.02 rows=3,713 width=8) (actual time=432.112..433.396 rows=6,314 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 488kB
6. 5.381 429.690 ↓ 1.7 6,314 1

Nested Loop (cost=4,515.67..22,296.59 rows=3,713 width=8) (actual time=307.110..429.690 rows=6,314 loops=1)

7. 5.109 308.839 ↓ 3.2 6,415 1

HashAggregate (cost=4,515.25..4,545.23 rows=1,999 width=8) (actual time=307.012..308.839 rows=6,415 loops=1)

  • Group Key: ac.actor_id, similarity(concat(ac.actor_fname, ' ', ac.actor_lname), 'chris'::text)
8. 271.554 303.730 ↓ 3.2 6,415 1

Bitmap Heap Scan on actor ac (cost=168.00..4,505.25 rows=1,999 width=8) (actual time=33.015..303.730 rows=6,415 loops=1)

  • Recheck Cond: (((actor_fname)::text % 'chris'::text) OR ((actor_lname)::text % 'chris'::text))
  • Rows Removed by Index Recheck: 30811
  • Heap Blocks: exact=6666
9. 0.003 32.176 ↓ 0.0 0 1

BitmapOr (cost=168.00..168.00 rows=2,000 width=0) (actual time=32.176..32.176 rows=0 loops=1)

10. 19.924 19.924 ↓ 27.1 27,118 1

Bitmap Index Scan on fname_idx (cost=0.00..83.50 rows=1,000 width=0) (actual time=19.924..19.924 rows=27,118 loops=1)

  • Index Cond: ((actor_fname)::text % 'chris'::text)
11. 12.249 12.249 ↓ 10.4 10,400 1

Bitmap Index Scan on lname_idx (cost=0.00..83.50 rows=1,000 width=0) (actual time=12.249..12.249 rows=10,400 loops=1)

  • Index Cond: ((actor_lname)::text % 'chris'::text)
12. 115.470 115.470 ↑ 2.0 1 6,415

Index Scan using movie_cast_actor_id_index on movie_cast m2 (cost=0.42..8.85 rows=2 width=8) (actual time=0.014..0.018 rows=1 loops=6,415)

  • Index Cond: (actor_id = ac.actor_id)
13. 223.953 17,845.378 ↑ 10.2 971,023 1

Unique (cost=3,370,510.59..3,544,618.44 rows=9,949,020 width=75) (actual time=17,483.925..17,845.378 rows=971,023 loops=1)

14. 742.576 17,621.425 ↑ 10.2 971,023 1

Sort (cost=3,370,510.59..3,395,383.14 rows=9,949,020 width=75) (actual time=17,483.922..17,621.425 rows=971,023 loops=1)

  • Sort Key: m.movie_id, m.movie_title, m.movie_country, m.movie_year, (similarity((m.movie_title)::text, '%'::text)), (string_agg((g2.genre_name)::text, '/'::text))
  • Sort Method: external merge Disk: 60344kB
15. 9,406.066 16,878.849 ↑ 10.2 971,047 1

GroupAggregate (cost=11.01..887,916.87 rows=9,949,020 width=75) (actual time=0.094..16,878.849 rows=971,047 loops=1)

  • Group Key: m.movie_id
  • Filter: (string_agg((g2.genre_name)::text, '/'::text) ~~* '%'::text)
  • Rows Removed by Filter: 8978968
16. 1,564.178 7,472.783 ↓ 1.0 10,000,984 1

Merge Left Join (cost=11.01..614,293.95 rows=9,950,015 width=71) (actual time=0.073..7,472.783 rows=10,000,984 loops=1)

  • Merge Cond: (m.movie_id = g.movie_id)
17. 4,437.532 4,437.532 ↑ 1.0 9,950,015 1

Index Scan using movie_pkey on movie m (cost=0.43..490,894.13 rows=9,950,015 width=39) (actual time=0.016..4,437.532 rows=9,950,015 loops=1)

  • Filter: ((movie_year >= 0) AND (movie_year <= 2020))
18. 155.768 1,471.073 ↑ 1.0 1,022,016 1

Materialize (cost=0.42..85,755.98 rows=1,022,016 width=36) (actual time=0.055..1,471.073 rows=1,022,016 loops=1)

19. 404.215 1,315.305 ↑ 1.0 1,022,016 1

Nested Loop Left Join (cost=0.42..83,200.94 rows=1,022,016 width=36) (actual time=0.053..1,315.305 rows=1,022,016 loops=1)

  • Join Filter: (g.genre_id = g2.genre_id)
  • Rows Removed by Join Filter: 660269
20. 911.090 911.090 ↑ 1.0 1,022,016 1

Index Scan using movie_genres_movie_id_index on movie_genres g (cost=0.42..44,874.30 rows=1,022,016 width=8) (actual time=0.030..911.090 rows=1,022,016 loops=1)

21. 0.000 0.000 ↑ 1.5 2 1,022,016

Materialize (cost=0.00..1.04 rows=3 width=36) (actual time=0.000..0.000 rows=2 loops=1,022,016)

22. 0.018 0.018 ↑ 1.0 3 1

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