8a.sql

SELECT MIN(an1.name) AS actress_pseudonym,
       MIN(t.title) AS japanese_movie_dubbed
FROM aka_name AS an1,
     cast_info AS ci,
     company_name AS cn,
     movie_companies AS mc,
     name AS n1,
     role_type AS rt,
     title AS t
WHERE ci.note ='(voice: English version)'
  AND cn.country_code ='[jp]'
  AND mc.note like '%(Japan)%'
  and mc.note not like '%(USA)%'
  AND n1.name like '%Yo%'
  and n1.name not like '%Yu%'
  AND rt.role ='actress'
  AND an1.person_id = n1.id
  AND n1.id = ci.person_id
  AND ci.movie_id = t.id
  AND t.id = mc.movie_id
  AND mc.company_id = cn.id
  AND ci.role_id = rt.id
  AND an1.person_id = ci.person_id
  AND ci.movie_id = mc.movie_id;

gistfile1.txt

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=69444.90..69444.91 rows=1 width=33) (actual time=20643.334..20643.334 rows=1 loops=1)
   ->  Nested Loop  (cost=3280.79..69444.89 rows=1 width=33) (actual time=2858.264..20643.223 rows=62 loops=1)
         ->  Nested Loop  (cost=3280.36..69422.85 rows=29 width=41) (actual time=22.928..20497.326 rows=17379 loops=1)
               ->  Hash Join  (cost=3279.93..69409.47 rows=12 width=21) (actual time=22.556..20357.283 rows=7438 loops=1)
                     Hash Cond: (ci.role_id = rt.id)
                     ->  Nested Loop  (cost=3278.77..69407.65 rows=145 width=25) (actual time=22.520..20352.222 rows=19600 loops=1)
                           ->  Nested Loop  (cost=3278.20..66436.91 rows=1587 width=25) (actual time=20.891..1333.619 rows=48328 loops=1)
                                 ->  Hash Join  (cost=3277.77..61438.29 rows=1587 width=4) (actual time=20.846..524.266 rows=48328 loops=1)
                                       Hash Cond: (mc.company_id = cn.id)
                                       ->  Seq Scan on movie_companies mc  (cost=0.00..57926.93 rows=58056 width=8) (actual time=0.355..470.707 rows=48897 loops=1)
                                             Filter: ((note ~~ '%(Japan)%'::text) AND (note !~~ '%(USA)%'::text))
                                             Rows Removed by Filter: 2560232
                                       ->  Hash  (cost=3197.49..3197.49 rows=6423 width=4) (actual time=20.478..20.478 rows=6752 loops=1)
                                             Buckets: 8192  Batches: 1  Memory Usage: 302kB
                                             ->  Bitmap Heap Scan on company_name cn  (cost=122.20..3197.49 rows=6423 width=4) (actual time=2.563..19.128 rows=6752 loops=1)
                                                   Recheck Cond: ((country_code)::text = '[jp]'::text)
                                                   Heap Blocks: exact=2648
                                                   ->  Bitmap Index Scan on index_company_name_country_code  (cost=0.00..120.59 rows=6423 width=0) (actual time=2.127..2.127 rows=6752 loops=1)
                                                         Index Cond: ((country_code)::text = '[jp]'::text)
                                 ->  Index Scan using index_title_id on title t  (cost=0.43..3.14 rows=1 width=21) (actual time=0.016..0.016 rows=1 loops=48328)
                                       Index Cond: (id = mc.movie_id)
                           ->  Index Scan using index_cast_info_movie_id on cast_info ci  (cost=0.56..1.86 rows=1 width=12) (actual time=0.382..0.393 rows=0 loops=48328)
                                 Index Cond: (movie_id = t.id)
                                 Filter: (note = '(voice: English version)'::text)
                                 Rows Removed by Filter: 32
                     ->  Hash  (cost=1.15..1.15 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                           ->  Seq Scan on role_type rt  (cost=0.00..1.15 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1)
                                 Filter: ((role)::text = 'actress'::text)
                                 Rows Removed by Filter: 11
               ->  Index Scan using index_aka_name_person_id on aka_name an1  (cost=0.42..1.09 rows=2 width=20) (actual time=0.015..0.018 rows=2 loops=7438)
                     Index Cond: (person_id = ci.person_id)
         ->  Index Scan using index_name_id on name n1  (cost=0.43..0.75 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=17379)
               Index Cond: (id = an1.person_id)
               Filter: ((name ~~ '%Yo%'::text) AND (name !~~ '%Yu%'::text))
               Rows Removed by Filter: 1
 Planning time: 6.771 ms
 Execution time: 20643.407 ms
(38 rows)