explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cuBj

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 17,684.146 ↑ 30.3 6 1

Unique (cost=3,406,339.49..3,679,890.80 rows=182 width=71) (actual time=17,277.529..17,684.146 rows=6 loops=1)

2. 62.027 17,684.138 ↑ 30.3 6 1

Merge Join (cost=3,406,339.49..3,679,890.34 rows=182 width=71) (actual time=17,277.527..17,684.138 rows=6 loops=1)

  • Merge Cond: (m2.movie_id = m.movie_id)
3. 0.041 4,430.357 ↓ 3.7 59 1

Sort (cost=64,450.10..64,450.14 rows=16 width=8) (actual time=4,430.334..4,430.357 rows=59 loops=1)

  • Sort Key: m2.movie_id
  • Sort Method: quicksort Memory: 27kB
4. 0.848 4,430.316 ↓ 3.7 59 1

Hash Join (cost=64,402.14..64,449.78 rows=16 width=8) (actual time=4,428.205..4,430.316 rows=59 loops=1)

  • Hash Cond: (d3.director_id = d2.director_id)
5. 1.122 2,042.829 ↓ 3.3 6,590 1

Unique (cost=14,576.52..14,596.51 rows=1,999 width=17) (actual time=2,041.449..2,042.829 rows=6,590 loops=1)

6. 9.918 2,041.707 ↓ 3.3 6,590 1

Sort (cost=14,576.52..14,581.52 rows=1,999 width=17) (actual time=2,041.447..2,041.707 rows=6,590 loops=1)

  • Sort Key: d3.director_id, d3.director_fname, d3.director_lname
  • Sort Method: quicksort Memory: 699kB
7. 75.070 2,031.789 ↓ 3.3 6,590 1

Gather (cost=1,000.00..14,466.92 rows=1,999 width=17) (actual time=1.259..2,031.789 rows=6,590 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 1,956.719 1,956.719 ↓ 2.6 2,197 3

Parallel Seq Scan on director d3 (cost=0.00..13,267.02 rows=833 width=17) (actual time=0.533..1,956.719 rows=2,197 loops=3)

  • Filter: (((director_fname)::text % 'chris'::text) OR ((director_lname)::text % 'CHRIS'::text))
  • Rows Removed by Filter: 331138
9. 2.082 2,386.639 ↓ 1.7 6,925 1

Hash (cost=49,775.24..49,775.24 rows=4,031 width=12) (actual time=2,386.639..2,386.639 rows=6,925 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 362kB
10. 132.185 2,384.557 ↓ 1.7 6,925 1

Gather (cost=39,444.20..49,775.24 rows=4,031 width=12) (actual time=2,241.028..2,384.557 rows=6,925 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 43.290 2,252.372 ↓ 1.4 2,308 3

Hash Join (cost=38,444.20..48,372.14 rows=1,680 width=12) (actual time=2,164.173..2,252.372 rows=2,308 loops=3)

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

13. 1.319 2,163.987 ↓ 1.7 6,314 3

Hash (cost=38,397.79..38,397.79 rows=3,713 width=4) (actual time=2,163.987..2,163.987 rows=6,314 loops=3)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 286kB
14. 347.154 2,162.668 ↓ 1.7 6,314 3

Hash Join (cost=34,500.68..38,397.79 rows=3,713 width=4) (actual time=1,820.433..2,162.668 rows=6,314 loops=3)

  • Hash Cond: (ac2.actor_id = m2.actor_id)
15. 0.503 1,451.152 ↓ 3.2 6,415 3

Subquery Scan on ac2 (cost=4,510.25..4,550.23 rows=1,999 width=4) (actual time=1,449.260..1,451.152 rows=6,415 loops=3)

16. 7.169 1,450.649 ↓ 3.2 6,415 3

HashAggregate (cost=4,510.25..4,530.24 rows=1,999 width=17) (actual time=1,449.260..1,450.649 rows=6,415 loops=3)

  • Group Key: ac.actor_id, ac.actor_fname, ac.actor_lname
17. 292.472 1,443.480 ↓ 3.2 6,415 3

Bitmap Heap Scan on actor ac (cost=168.00..4,495.26 rows=1,999 width=17) (actual time=1,152.469..1,443.480 rows=6,415 loops=3)

  • Recheck Cond: (((actor_fname)::text % 'chris'::text) OR ((actor_lname)::text % 'CHRIS'::text))
  • Rows Removed by Index Recheck: 30811
  • Heap Blocks: exact=6666
18. 0.002 1,151.008 ↓ 0.0 0 3

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

19. 829.334 829.334 ↓ 27.1 27,118 3

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

  • Index Cond: ((actor_fname)::text % 'chris'::text)
20. 321.672 321.672 ↓ 10.4 10,400 3

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

  • Index Cond: ((actor_lname)::text % 'CHRIS'::text)
21. 202.807 364.362 ↑ 1.0 970,020 3

Hash (cost=14,057.86..14,057.86 rows=971,086 width=8) (actual time=364.362..364.362 rows=970,020 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3399kB
22. 161.555 161.555 ↑ 1.0 970,020 3

Seq Scan on movie_cast m2 (cost=0.00..14,057.86 rows=971,086 width=8) (actual time=0.104..161.555 rows=970,020 loops=3)

23. 226.120 13,191.754 ↑ 10.5 942,870 1

Unique (cost=3,341,889.39..3,491,097.93 rows=9,947,236 width=71) (actual time=12,816.901..13,191.754 rows=942,870 loops=1)

24. 704.316 12,965.634 ↑ 10.5 942,870 1

Sort (cost=3,341,889.39..3,366,757.48 rows=9,947,236 width=71) (actual time=12,816.900..12,965.634 rows=942,870 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
25. 4,672.924 12,261.318 ↑ 10.2 971,047 1

GroupAggregate (cost=179,404.15..961,743.90 rows=9,947,236 width=71) (actual time=790.245..12,261.318 rows=971,047 loops=1)

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

Merge Left Join (cost=179,404.15..713,038.13 rows=9,948,231 width=71) (actual time=790.221..7,588.394 rows=10,000,984 loops=1)

  • Merge Cond: (m.movie_id = g.movie_id)
27. 4,836.216 4,836.216 ↓ 1.0 9,950,015 1

Index Scan using movie_pkey on movie m (cost=0.43..490,858.45 rows=9,948,231 width=39) (actual time=0.025..4,836.216 rows=9,950,015 loops=1)

  • Filter: ((movie_year >= 0) AND (movie_year <= 2020))
28. 104.460 1,130.443 ↑ 1.0 1,022,016 1

Materialize (cost=179,403.20..184,519.39 rows=1,023,238 width=36) (actual time=790.191..1,130.443 rows=1,022,016 loops=1)

29. 686.565 1,025.983 ↑ 1.0 1,022,016 1

Sort (cost=179,403.20..181,961.30 rows=1,023,238 width=36) (actual time=790.185..1,025.983 rows=1,022,016 loops=1)

  • Sort Key: g.movie_id
  • Sort Method: external merge Disk: 21352kB
30. 218.779 339.418 ↑ 1.0 1,022,016 1

Hash Left Join (cost=1.07..21,294.95 rows=1,023,238 width=36) (actual time=0.073..339.418 rows=1,022,016 loops=1)

  • Hash Cond: (g.genre_id = g2.genre_id)
31. 120.615 120.615 ↑ 1.0 1,022,016 1

Seq Scan on movie_genres g (cost=0.00..14,813.38 rows=1,023,238 width=8) (actual time=0.042..120.615 rows=1,022,016 loops=1)

32. 0.003 0.024 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.021 0.021 ↑ 1.0 3 1

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