explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mO7S

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 7,416.103 ↑ 1.0 11 1

Limit (cost=94,145.88..94,315.00 rows=11 width=162) (actual time=7,415.278..7,416.103 rows=11 loops=1)

2.          

CTE actors_ids

3. 1,034.263 1,034.263 ↓ 1.0 1,000,013 1

Seq Scan on actor a (cost=0.00..19,187.16 rows=999,915 width=4) (actual time=0.029..1,034.263 rows=1,000,013 loops=1)

  • Filter: ((actor_lname)::text ~~* '%'::text)
4.          

CTE directors_ids

5. 959.165 959.165 ↓ 1.0 1,000,004 1

Seq Scan on director d (cost=0.00..19,517.05 rows=999,911 width=4) (actual time=0.023..959.165 rows=1,000,004 loops=1)

  • Filter: ((director_fname)::text ~~* '%'::text)
6. 0.008 7,416.098 ↑ 1,479.1 16 1

Nested Loop (cost=55,364.79..419,219.63 rows=23,666 width=162) (actual time=7,411.221..7,416.098 rows=16 loops=1)

7. 5.691 7,415.994 ↑ 16,471.8 16 1

Hash Join (cost=55,364.36..71,697.18 rows=263,548 width=48) (actual time=7,411.206..7,415.994 rows=16 loops=1)

  • Hash Cond: (mc.movie_id = md.movie_id)
8. 0.360 1,814.826 ↑ 1,206.5 402 1

Nested Loop (cost=22,498.51..24,900.06 rows=485,010 width=40) (actual time=1,813.079..1,814.826 rows=402 loops=1)

9. 511.965 1,813.209 ↓ 2.1 419 1

HashAggregate (cost=22,498.09..22,500.09 rows=200 width=4) (actual time=1,813.064..1,813.209 rows=419 loops=1)

  • Group Key: actors_ids.actor_id
10. 1,301.244 1,301.244 ↓ 1.0 1,000,013 1

CTE Scan on actors_ids (cost=0.00..19,998.30 rows=999,915 width=4) (actual time=0.032..1,301.244 rows=1,000,013 loops=1)

11. 1.257 1.257 ↑ 2.0 1 419

Index Scan using movie_cast_actor_id_index on movie_cast mc (cost=0.42..11.98 rows=2 width=40) (actual time=0.003..0.003 rows=1 loops=419)

  • Index Cond: (actor_id = actors_ids.actor_id)
12. 273.628 5,595.477 ↓ 2.0 971,017 1

Hash (cost=24,900.00..24,900.00 rows=485,508 width=8) (actual time=5,595.477..5,595.477 rows=971,017 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 16 (originally 8) Memory Usage: 3403kB
13. 338.056 5,321.849 ↓ 2.0 971,017 1

Nested Loop (cost=22,498.42..24,900.00 rows=485,508 width=8) (actual time=1,683.064..5,321.849 rows=971,017 loops=1)

14. 756.976 1,983.781 ↓ 5,000.0 1,000,004 1

HashAggregate (cost=22,498.00..22,500.00 rows=200 width=4) (actual time=1,683.048..1,983.781 rows=1,000,004 loops=1)

  • Group Key: directors_ids.did
15. 1,226.805 1,226.805 ↓ 1.0 1,000,004 1

CTE Scan on directors_ids (cost=0.00..19,998.22 rows=999,911 width=4) (actual time=0.024..1,226.805 rows=1,000,004 loops=1)

16. 3,000.012 3,000.012 ↑ 2.0 1 1,000,004

Index Scan using movie_director_director_id_index on movie_director md (cost=0.42..11.98 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=1,000,004)

  • Index Cond: (director_id = directors_ids.did)
17. 0.096 0.096 ↑ 1.0 1 16

Index Scan using movie_pkey on movie m (cost=0.43..1.32 rows=1 width=114) (actual time=0.006..0.006 rows=1 loops=16)

  • Index Cond: (movie_id = mc.movie_id)