/*
25. produksjons?r og tittel for alle filmer som b?de Angelina Jolie og Antonio Banderas har deltatt i sammen.*/
-- Kan ogs? l?ses f.eks. med join og subsp?rring eller IN og subsp?rring
select f.title, f.prodyear
from film f natural join filmparticipation fp natural join person p
where p.firstname = 'Angelina' and p.lastname = 'Jolie'
  and exists (
      select *
      from filmparticipation fp2 natural join person p
      where fp2.filmid = fp.filmid
        and p.firstname = 'Antonio' and p.lastname = 'Banderas'
  );


QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=8.44..30.13 rows=1 width=24)
   ->  Nested Loop Semi Join  (cost=8.15..29.83 rows=1 width=8)
         ->  Nested Loop  (cost=0.84..20.93 rows=3 width=4)
               ->  Index Scan using personlastnameindex on person p  (cost=0.42..6.87 rows=1 width=4)
                     Index Cond: (lastname = 'Jolie'::text)
                     Filter: (firstname = 'Angelina'::text)
               ->  Index Scan using filmparticipationpersonidindex on filmparticipation fp  (cost=0.42..13.93 rows=13 width=8)
                     Index Cond: (personid = p.personid)
         ->  Hash Join  (cost=7.31..7.83 rows=1 width=4)
               Hash Cond: (fp2.personid = p_1.personid)
               ->  Index Scan using filmparticipationfilmidindex on filmparticipation fp2  (cost=0.42..0.88 rows=25 width=8)
                     Index Cond: (filmid = fp.filmid)
               ->  Hash  (cost=6.87..6.87 rows=1 width=4)
                     ->  Index Scan using personlastnameindex on person p_1  (cost=0.42..6.87 rows=1 width=4)
                           Index Cond: (lastname = 'Banderas'::text)
                           Filter: (firstname = 'Antonio'::text)
   ->  Index Scan using filmpkey on film f  (cost=0.29..0.31 rows=1 width=28)
         Index Cond: (filmid = fp.filmid)
(18 rows)

                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=8.44..30.13 rows=1 width=24) (actual time=558.837..563.661 rows=1 loops=1)
   Buffers: shared hit=14 read=123
   ->  Nested Loop Semi Join  (cost=8.15..29.83 rows=1 width=8) (actual time=548.753..553.577 rows=1 loops=1)
         Buffers: shared hit=12 read=122
         ->  Nested Loop  (cost=0.84..20.93 rows=3 width=4) (actual time=64.470..69.296 rows=2 loops=1)
               Buffers: shared hit=1 read=8
               ->  Index Scan using personlastnameindex on person p  (cost=0.42..6.87 rows=1 width=4) (actual time=39.172..43.989 rows=1 loops=1)
                     Index Cond: (lastname = 'Jolie'::text)
                     Filter: (firstname = 'Angelina'::text)
                     Rows Removed by Filter: 2
                     Buffers: shared read=5
               ->  Index Scan using filmparticipationpersonidindex on filmparticipation fp  (cost=0.42..13.93 rows=13 width=8) (actual time=25.284..25.290 rows=2 loops=1)
                     Index Cond: (personid = p.personid)
                     Buffers: shared hit=1 read=3
         ->  Hash Join  (cost=7.31..7.83 rows=1 width=4) (actual time=242.133..242.133 rows=0 loops=2)
               Hash Cond: (fp2.personid = p_1.personid)
               Buffers: shared hit=11 read=114
               ->  Index Scan using filmparticipationfilmidindex on filmparticipation fp2  (cost=0.42..0.88 rows=25 width=8) (actual time=12.518..228.539 rows=56 loops=2)
                     Index Cond: (filmid = fp.filmid)
                     Buffers: shared hit=6 read=111
               ->  Hash  (cost=6.87..6.87 rows=1 width=4) (actual time=26.775..26.776 rows=1 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     Buffers: shared hit=2 read=3
                     ->  Index Scan using personlastnameindex on person p_1  (cost=0.42..6.87 rows=1 width=4) (actual time=11.443..26.755 rows=1 loops=1)
                           Index Cond: (lastname = 'Banderas'::text)
                           Filter: (firstname = 'Antonio'::text)
                           Rows Removed by Filter: 1
                           Buffers: shared hit=2 read=3
   ->  Index Scan using filmpkey on film f  (cost=0.29..0.31 rows=1 width=28) (actual time=10.071..10.071 rows=1 loops=1)
         Index Cond: (filmid = fp.filmid)
         Buffers: shared hit=2 read=1
 Planning Time: 1.168 ms
 Execution Time: 563.781 ms
(33 rows)