Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select film.film_id, film.title, a.actors
- from film
- left join
- (
- select film_actor.film_id, array_agg(first_name) as actors
- from actor
- inner join film_actor using(actor_id)
- group by film_actor.film_id
- ) as a
- on a.film_id = film.film_id
- where film.title = 'ACADEMY DINOSAUR'
- order by film.title;
- select film.film_id, film.title, a.actors
- from film
- left join lateral
- (
- select array_agg(first_name) as actors
- from actor
- inner join film_actor using(actor_id)
- where film_actor.film_id = film.film_id
- ) as a
- on true
- where film.title = 'ACADEMY DINOSAUR'
- order by film.title;
- Merge Left Join (cost=507.20..573.11 rows=1 width=51) (actual time=15.087..15.089 rows=1 loops=1)
- Merge Cond: (film.film_id = film_actor.film_id)
- -> Sort (cost=8.30..8.31 rows=1 width=19) (actual time=0.075..0.075 rows=1 loops=1)
- Sort Key: film.film_id
- Sort Method: quicksort Memory: 25kB
- -> Index Scan using idx_title on film (cost=0.28..8.29 rows=1 width=19) (actual time=0.044..0.058 rows=1 loops=1)
- Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text)
- -> GroupAggregate (cost=498.90..552.33 rows=997 width=34) (actual time=15.004..15.004 rows=1 loops=1)
- Group Key: film_actor.film_id
- -> Sort (cost=498.90..512.55 rows=5462 width=8) (actual time=14.934..14.937 rows=11 loops=1)
- Sort Key: film_actor.film_id
- Sort Method: quicksort Memory: 449kB
- -> Hash Join (cost=6.50..159.84 rows=5462 width=8) (actual time=0.355..8.359 rows=5462 loops=1)
- Hash Cond: (film_actor.actor_id = actor.actor_id)
- -> Seq Scan on film_actor (cost=0.00..84.62 rows=5462 width=4) (actual time=0.035..2.205 rows=5462 loops=1)
- -> Hash (cost=4.00..4.00 rows=200 width=10) (actual time=0.303..0.303 rows=200 loops=1)
- Buckets: 1024 Batches: 1 Memory Usage: 17kB
- -> Seq Scan on actor (cost=0.00..4.00 rows=200 width=10) (actual time=0.027..0.143 rows=200 loops=1)
- Planning time: 1.495 ms
- Execution time: 15.426 ms
- Nested Loop Left Join (cost=25.11..33.16 rows=1 width=51) (actual time=0.849..0.854 rows=1 loops=1)
- -> Index Scan using idx_title on film (cost=0.28..8.29 rows=1 width=19) (actual time=0.045..0.048 rows=1 loops=1)
- Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text)
- -> Aggregate (cost=24.84..24.85 rows=1 width=32) (actual time=0.797..0.797 rows=1 loops=1)
- -> Hash Join (cost=10.82..24.82 rows=5 width=6) (actual time=0.672..0.764 rows=10 loops=1)
- Hash Cond: (film_actor.actor_id = actor.actor_id)
- -> Bitmap Heap Scan on film_actor (cost=4.32..18.26 rows=5 width=2) (actual time=0.072..0.150 rows=10 loops=1)
- Recheck Cond: (film_id = film.film_id)
- Heap Blocks: exact=10
- -> Bitmap Index Scan on idx_fk_film_id (cost=0.00..4.32 rows=5 width=0) (actual time=0.041..0.041 rows=10 loops=1)
- Index Cond: (film_id = film.film_id)
- -> Hash (cost=4.00..4.00 rows=200 width=10) (actual time=0.561..0.561 rows=200 loops=1)
- Buckets: 1024 Batches: 1 Memory Usage: 17kB
- -> Seq Scan on actor (cost=0.00..4.00 rows=200 width=10) (actual time=0.039..0.275 rows=200 loops=1)
- Planning time: 1.722 ms
- Execution time: 1.087 ms
Add Comment
Please, Sign In to add comment