19a === Aggregate (cost=1299738.67..1299738.68 rows=1 width=64) -> Nested Loop (cost=67007.01..1299738.67 rows=1 width=32) -> Nested Loop (cost=67006.58..1299737.77 rows=1 width=36) Join Filter: (n.id = an.person_id) -> Nested Loop (cost=67006.58..1268061.55 rows=1 width=44) Join Filter: (mi.movie_id = t.id) -> Nested Loop (cost=67006.15..1268060.11 rows=1 width=39) Join Filter: (mi.movie_id = mc.movie_id) -> Nested Loop (cost=67003.73..880481.11 rows=1 width=35) -> Hash Join (cost=67003.30..869156.34 rows=7265 width=16) Hash Cond: (ci.role_id = rt.id) -> Hash Join (cost=67002.13..868845.51 rows=87179 width=20) Hash Cond: (ci.movie_id = mc.movie_id) -> Seq Scan on cast_info ci (cost=0.00..796740.36 rows=846245 width=16) Filter: (note = ANY ('{(voice),"(voice: Japanese version)","(voice) (uncredited)","(voice: English version)"}'::text[])) -> Hash (cost=65684.46..65684.46 rows=105414 width=4) -> Hash Join (cost=6985.94..65684.46 rows=105414 width=4) Hash Cond: (mc.company_id = cn.id) -> Seq Scan on movie_companies mc (cost=0.00..57926.93 rows=293933 width=8) Filter: ((note IS NOT NULL) AND ((note ~~ '%(USA)%'::text) OR (note ~~ '%(worldwide)%'::text))) -> Hash (cost=5932.46..5932.46 rows=84278 width=4) -> Seq Scan on company_name cn (cost=0.00..5932.46 rows=84278 width=4) Filter: ((country_code)::text = '[us]'::text) -> Hash (cost=1.15..1.15 rows=1 width=4) -> Seq Scan on role_type rt (cost=0.00..1.15 rows=1 width=4) Filter: ((role)::text = 'actress'::text) -> Index Scan using name_pkey on name n (cost=0.43..1.56 rows=1 width=19) Index Cond: (id = ci.person_id) Filter: ((name ~~ '%Ang%'::text) AND ((gender)::text = 'f'::text)) -> Hash Join (cost=2.43..387511.21 rows=5424 width=4) Hash Cond: (mi.info_type_id = it.id) -> Seq Scan on movie_info mi (cost=0.00..385839.60 rows=612892 width=8) Filter: ((info IS NOT NULL) AND ((info ~~ 'Japan:%200%'::text) OR (info ~~ 'USA:%200%'::text))) -> Hash (cost=2.41..2.41 rows=1 width=4) -> Seq Scan on info_type it (cost=0.00..2.41 rows=1 width=4) Filter: ((info)::text = 'release dates'::text) -> Index Scan using title_pkey on title t (cost=0.43..1.43 rows=1 width=21) Index Cond: (id = ci.movie_id) Filter: ((production_year >= 2005) AND (production_year <= 2009)) -> Seq Scan on aka_name an (cost=0.00..20409.43 rows=901343 width=4) -> Index Only Scan using char_name_pkey on char_name chn (cost=0.43..0.90 rows=1 width=4) Index Cond: (id = ci.person_role_id) 23b === Aggregate (cost=532215.67..532215.68 rows=1 width=64) -> Nested Loop (cost=6198.10..532215.67 rows=1 width=27) -> Nested Loop (cost=6197.97..532215.49 rows=1 width=21) Join Filter: (mi.movie_id = t.id) -> Nested Loop (cost=6197.54..532214.99 rows=1 width=16) -> Nested Loop (cost=6197.41..532214.84 rows=1 width=20) -> Nested Loop (cost=6196.99..532214.39 rows=1 width=24) Join Filter: (mc.movie_id = mi.movie_id) -> Nested Loop (cost=2961.60..470379.20 rows=1 width=8) Join Filter: (mi.movie_id = mk.movie_id) -> Hash Join (cost=2961.60..84530.59 rows=135 width=4) Hash Cond: (mk.keyword_id = k.id) -> Seq Scan on movie_keyword mk (cost=0.00..69693.30 rows=4523930 width=8) -> Hash (cost=2961.55..2961.55 rows=4 width=4) -> Seq Scan on keyword k (cost=0.00..2961.55 rows=4 width=4) Filter: (keyword = ANY ('{nerd,loner,alienation,dignity}'::text[])) -> Materialize (cost=0.00..385844.56 rows=2 width=4) -> Nested Loop (cost=0.00..385844.55 rows=2 width=4) Join Filter: (it1.id = mi.info_type_id) -> Seq Scan on info_type it1 (cost=0.00..2.41 rows=1 width=4) Filter: ((info)::text = 'release dates'::text) -> Seq Scan on movie_info mi (cost=0.00..385839.60 rows=203 width=8) Filter: ((note ~~ '%internet%'::text) AND (info ~~ 'USA:% 200%'::text)) -> Hash Join (cost=3235.39..59649.48 rows=174857 width=16) Hash Cond: (mc.movie_id = cc.movie_id) -> Seq Scan on movie_companies mc (cost=0.00..44881.29 rows=2609129 width=12) -> Hash (cost=2813.24..2813.24 rows=33772 width=4) -> Hash Join (cost=1.06..2813.24 rows=33772 width=4) Hash Cond: (cc.status_id = cct1.id) -> Seq Scan on complete_cast cc (cost=0.00..2081.86 rows=135086 width=8) -> Hash (cost=1.05..1.05 rows=1 width=4) -> Seq Scan on comp_cast_type cct1 (cost=0.00..1.05 rows=1 width=4) Filter: ((kind)::text = 'complete+verified'::text) -> Index Scan using company_name_pkey on company_name cn (cost=0.42..0.45 rows=1 width=4) Index Cond: (id = mc.company_id) Filter: ((country_code)::text = '[us]'::text) -> Index Only Scan using company_type_pkey on company_type ct (cost=0.13..0.15 rows=1 width=4) Index Cond: (id = mc.company_type_id) -> Index Scan using title_pkey on title t (cost=0.43..0.49 rows=1 width=25) Index Cond: (id = mk.movie_id) Filter: (production_year > 2000) -> Index Scan using kind_type_pkey on kind_type kt (cost=0.13..0.15 rows=1 width=14) Index Cond: (id = t.kind_id) Filter: ((kind)::text = 'movie'::text) 29a === Aggregate (cost=1402289.45..1402289.46 rows=1 width=96) -> Nested Loop (cost=1374724.97..1402289.44 rows=1 width=48) -> Nested Loop (cost=1374724.84..1402289.27 rows=1 width=52) -> Nested Loop (cost=1374724.70..1402289.10 rows=1 width=56) Join Filter: (ci.person_id = n.id) -> Nested Loop (cost=1374724.27..1402286.87 rows=1 width=53) -> Nested Loop (cost=1374724.14..1402286.70 rows=1 width=57) Join Filter: (t.id = cc.movie_id) -> Nested Loop (cost=1374724.14..1398516.26 rows=1 width=69) Join Filter: (mi.movie_id = t.id) -> Nested Loop (cost=1374723.71..1398514.10 rows=1 width=48) -> Hash Join (cost=1374723.57..1398513.92 rows=1 width=52) Hash Cond: (pi.info_type_id = it3.id) -> Hash Join (cost=1374721.15..1398511.30 rows=69 width=56) Hash Cond: (an.person_id = ci.person_id) -> Seq Scan on aka_name an (cost=0.00..20409.43 rows=901343 width=4) -> Hash (cost=1374720.77..1374720.77 rows=30 width=52) -> Nested Loop (cost=1319884.18..1374720.77 rows=30 width=52) -> Hash Join (cost=1319883.76..1374550.11 rows=83 width=56) Hash Cond: (mc.movie_id = mi.movie_id) -> Seq Scan on movie_companies mc (cost=0.00..44881.29 rows=2609129 width=8) -> Hash (cost=1319883.56..1319883.56 rows=16 width=48) -> Hash Join (cost=931745.45..1319883.56 rows=16 width=48) Hash Cond: (mi.movie_id = ci.movie_id) -> Seq Scan on movie_info mi (cost=0.00..385839.60 rows=612892 width=8) Filter: ((info IS NOT NULL) AND ((info ~~ 'Japan:%200%'::text) OR (info ~~ 'USA:%200%'::text))) -> Hash (cost=931745.13..931745.13 rows=26 width=40) -> Hash Join (cost=838741.37..931745.13 rows=26 width=40) Hash Cond: (pi.person_id = ci.person_id) -> Seq Scan on person_info pi (cost=0.00..81889.18 rows=2963818 width=8) -> Hash (cost=838741.36..838741.36 rows=1 width=32) -> Nested Loop (cost=84196.00..838741.36 rows=1 width=32) -> Hash Join (cost=84195.56..838679.87 rows=27 width=20) Hash Cond: (ci.movie_id = mk.movie_id) -> Seq Scan on cast_info ci (cost=0.00..751405.83 rows=820857 width=16) Filter: (note = ANY ('{(voice),"(voice) (uncredited)","(voice: English version)"}'::text[])) -> Hash (cost=84195.13..84195.13 rows=34 width=4) -> Hash Join (cost=2626.14..84195.13 rows=34 width=4) Hash Cond: (mk.keyword_id = k.id) -> Seq Scan on movie_keyword mk (cost=0.00..69693.30 rows=4523930 width=8) -> Hash (cost=2626.12..2626.12 rows=1 width=4) -> Seq Scan on keyword k (cost=0.00..2626.12 rows=1 width=4) Filter: (keyword = 'computer-animation'::text) -> Memoize (cost=0.44..2.26 rows=1 width=20) Cache Key: ci.person_role_id Cache Mode: logical -> Index Scan using char_name_pkey on char_name chn (cost=0.43..2.25 rows=1 width=20) Index Cond: (id = ci.person_role_id) Filter: (name = 'Queen'::text) -> Index Scan using company_name_pkey on company_name cn (cost=0.42..2.06 rows=1 width=4) Index Cond: (id = mc.company_id) Filter: ((country_code)::text = '[us]'::text) -> Hash (cost=2.41..2.41 rows=1 width=4) -> Seq Scan on info_type it3 (cost=0.00..2.41 rows=1 width=4) Filter: ((info)::text = 'trivia'::text) -> Index Scan using info_type_pkey on info_type it (cost=0.14..0.16 rows=1 width=4) Index Cond: (id = mi.info_type_id) Filter: ((info)::text = 'release dates'::text) -> Index Scan using title_pkey on title t (cost=0.43..2.15 rows=1 width=21) Index Cond: (id = mk.movie_id) Filter: ((production_year >= 2000) AND (production_year <= 2010) AND (title = 'Shrek 2'::text)) -> Seq Scan on complete_cast cc (cost=0.00..2081.86 rows=135086 width=12) -> Index Scan using comp_cast_type_pkey on comp_cast_type cct1 (cost=0.13..0.15 rows=1 width=4) Index Cond: (id = cc.subject_id) Filter: ((kind)::text = 'cast'::text) -> Index Scan using name_pkey on name n (cost=0.43..2.21 rows=1 width=19) Index Cond: (id = pi.person_id) Filter: ((name ~~ '%An%'::text) AND ((gender)::text = 'f'::text)) -> Index Scan using role_type_pkey on role_type rt (cost=0.14..0.16 rows=1 width=4) Index Cond: (id = ci.role_id) Filter: ((role)::text = 'actress'::text) -> Index Scan using comp_cast_type_pkey on comp_cast_type cct2 (cost=0.13..0.15 rows=1 width=4) Index Cond: (id = cc.status_id) Filter: ((kind)::text = 'complete+verified'::text) (74 rows) 29c === Aggregate (cost=1447617.23..1447617.24 rows=1 width=96) -> Nested Loop (cost=1350841.09..1447617.22 rows=1 width=48) -> Nested Loop (cost=1350840.96..1447617.05 rows=1 width=52) Join Filter: (ci.person_id = n.id) -> Nested Loop (cost=1350840.53..1447614.83 rows=1 width=49) -> Nested Loop (cost=1350840.40..1447614.66 rows=1 width=53) Join Filter: (t.id = cc.movie_id) -> Hash Join (cost=1350840.40..1443844.22 rows=1 width=65) Hash Cond: (pi.info_type_id = it3.id) -> Hash Join (cost=1350837.97..1443841.73 rows=26 width=69) Hash Cond: (pi.person_id = ci.person_id) -> Seq Scan on person_info pi (cost=0.00..81889.18 rows=2963818 width=8) -> Hash (cost=1350837.96..1350837.96 rows=1 width=61) -> Nested Loop (cost=1296170.75..1350837.96 rows=1 width=61) -> Nested Loop (cost=1296170.61..1350837.78 rows=1 width=65) -> Hash Join (cost=1296170.19..1350835.73 rows=1 width=69) Hash Cond: (mc.movie_id = t.id) -> Seq Scan on movie_companies mc (cost=0.00..44881.29 rows=2609129 width=8) -> Hash (cost=1296170.18..1296170.18 rows=1 width=61) -> Nested Loop (cost=908031.84..1296170.18 rows=1 width=61) -> Hash Join (cost=908031.70..1296169.67 rows=3 width=65) Hash Cond: (mi.movie_id = t.id) -> Seq Scan on movie_info mi (cost=0.00..385839.60 rows=612892 width=8) Filter: ((info IS NOT NULL) AND ((info ~~ 'Japan:%200%'::text) OR (info ~~ 'USA:%200%'::text))) -> Hash (cost=908031.54..908031.54 rows=13 width=57) -> Nested Loop (cost=884173.12..908031.54 rows=13 width=57) Join Filter: (ci.movie_id = t.id) -> Hash Join (cost=884172.69..907962.48 rows=32 width=36) Hash Cond: (an.person_id = ci.person_id) -> Seq Scan on aka_name an (cost=0.00..20409.43 rows=901343 width=4) -> Hash (cost=884172.52..884172.52 rows=14 width=32) -> Nested Loop (cost=84195.99..884172.52 rows=14 width=32) -> Hash Join (cost=84195.56..884109.62 rows=28 width=20) Hash Cond: (ci.movie_id = mk.movie_id) -> Seq Scan on cast_info ci (cost=0.00..796740.36 rows=846245 width=16) Filter: (note = ANY ('{(voice),"(voice: Japanese version)","(voice) (uncredited)","(voice: English version)"}'::text[])) -> Hash (cost=84195.13..84195.13 rows=34 width=4) -> Hash Join (cost=2626.14..84195.13 rows=34 width=4) Hash Cond: (mk.keyword_id = k.id) -> Seq Scan on movie_keyword mk (cost=0.00..69693.30 rows=4523930 width=8) -> Hash (cost=2626.12..2626.12 rows=1 width=4) -> Seq Scan on keyword k (cost=0.00..2626.12 rows=1 width= 4) Filter: (keyword = 'computer-animation'::text) -> Index Scan using char_name_pkey on char_name chn (cost=0.43..2.25 rows=1 width= 20) Index Cond: (id = ci.person_role_id) -> Index Scan using title_pkey on title t (cost=0.43..2.15 rows=1 width=21) Index Cond: (id = mk.movie_id) Filter: ((production_year >= 2000) AND (production_year <= 2010)) -> Index Scan using info_type_pkey on info_type it (cost=0.14..0.16 rows=1 width=4) Index Cond: (id = mi.info_type_id) Filter: ((info)::text = 'release dates'::text) -> Index Scan using company_name_pkey on company_name cn (cost=0.42..2.06 rows=1 width=4) Index Cond: (id = mc.company_id) Filter: ((country_code)::text = '[us]'::text) -> Index Scan using role_type_pkey on role_type rt (cost=0.14..0.16 rows=1 width=4) Index Cond: (id = ci.role_id) Filter: ((role)::text = 'actress'::text) -> Hash (cost=2.41..2.41 rows=1 width=4) -> Seq Scan on info_type it3 (cost=0.00..2.41 rows=1 width=4) Filter: ((info)::text = 'trivia'::text) -> Seq Scan on complete_cast cc (cost=0.00..2081.86 rows=135086 width=12) -> Index Scan using comp_cast_type_pkey on comp_cast_type cct1 (cost=0.13..0.15 rows=1 width=4) Index Cond: (id = cc.subject_id) Filter: ((kind)::text = 'cast'::text) -> Index Scan using name_pkey on name n (cost=0.43..2.21 rows=1 width=19) Index Cond: (id = pi.person_id) Filter: ((name ~~ '%An%'::text) AND ((gender)::text = 'f'::text)) -> Index Scan using comp_cast_type_pkey on comp_cast_type cct2 (cost=0.13..0.15 rows=1 width=4) Index Cond: (id = cc.status_id) Filter: ((kind)::text = 'complete+verified'::text) (70 rows) 30a === Aggregate (cost=1310740.22..1310740.23 rows=1 width=128) -> Nested Loop (cost=1224081.08..1310740.21 rows=1 width=79) -> Nested Loop (cost=1224080.95..1310740.04 rows=1 width=83) -> Hash Join (cost=1224080.52..1310738.58 rows=2 width=87) Hash Cond: (mk.movie_id = t.id) -> Seq Scan on movie_keyword mk (cost=0.00..69693.30 rows=4523930 width=8) -> Hash (cost=1224080.51..1224080.51 rows=1 width=103) -> Nested Loop (cost=377420.09..1224080.51 rows=1 width=103) Join Filter: (mi.movie_id = t.id) -> Nested Loop (cost=377419.66..1224079.58 rows=1 width=82) -> Hash Join (cost=377419.23..1224077.46 rows=2 width=71) Hash Cond: (ci.movie_id = mi.movie_id) -> Seq Scan on cast_info ci (cost=0.00..842074.89 rows=1222219 width=8) Filter: (note = ANY ('{(writer),"(head writer)","(written by)",(story),"(story editor)"}'::text[])) -> Hash (cost=377419.20..377419.20 rows=2 width=63) -> Hash Join (cost=28319.40..377419.20 rows=2 width=63) Hash Cond: (mi.info_type_id = it1.id) -> Hash Join (cost=28316.98..377416.14 rows=234 width=67) Hash Cond: (mi.movie_id = mi_idx.movie_id) -> Seq Scan on movie_info mi (cost=0.00..348803.00 rows=58765 width=50) Filter: (info = ANY ('{Horror,Thriller}'::text[])) -> Hash (cost=28294.30..28294.30 rows=1814 width=17) -> Hash Join (cost=25649.97..28294.30 rows=1814 width=17) Hash Cond: (cc.subject_id = cct1.id) -> Hash Join (cost=25648.90..28273.61 rows=3628 width=21) Hash Cond: (cc.movie_id = mi_idx.movie_id) -> Seq Scan on complete_cast cc (cost=0.00..2081.86 rows=135086 width=12) -> Hash (cost=25496.24..25496.24 rows=12213 width=9) -> Hash Join (cost=2.43..25496.24 rows=12213 width=9) Hash Cond: (mi_idx.info_type_id = it2.id) -> Seq Scan on movie_info_idx mi_idx (cost=0.00..21735.35 rows=1380035 width=13) -> Hash (cost=2.41..2.41 rows=1 width=4) -> Seq Scan on info_type it2 (cost=0.00..2.41 rows=1 width=4) Filter: ((info)::text = 'votes'::text) -> Hash (cost=1.05..1.05 rows=2 width=4) -> Seq Scan on comp_cast_type cct1 (cost=0.00..1.05 rows=2 width=4) Filter: ((kind)::text = ANY ('{cast,crew}'::text[])) -> Hash (cost=2.41..2.41 rows=1 width=4) -> Seq Scan on info_type it1 (cost=0.00..2.41 rows=1 width=4) Filter: ((info)::text = 'genres'::text) -> Index Scan using name_pkey on name n (cost=0.43..1.06 rows=1 width=19) Index Cond: (id = ci.person_id) Filter: ((gender)::text = 'm'::text) -> Index Scan using title_pkey on title t (cost=0.43..0.92 rows=1 width=21) Index Cond: (id = mi_idx.movie_id) Filter: (production_year > 2000) -> Memoize (cost=0.43..0.72 rows=1 width=4) Cache Key: mk.keyword_id Cache Mode: logical -> Index Scan using keyword_pkey on keyword k (cost=0.42..0.71 rows=1 width=4) Index Cond: (id = mk.keyword_id) Filter: (keyword = ANY ('{murder,violence,blood,gore,death,female-nudity,hospital}'::text[])) -> Index Scan using comp_cast_type_pkey on comp_cast_type cct2 (cost=0.13..0.15 rows=1 width=4) Index Cond: (id = cc.status_id) Filter: ((kind)::text = 'complete+verified'::text) (55 rows) 7b == Aggregate (cost=879018.65..879018.66 rows=1 width=64) -> Nested Loop (cost=7562.36..879018.64 rows=1 width=32) Join Filter: (n.id = an.person_id) -> Nested Loop (cost=7562.36..848301.63 rows=1 width=44) Join Filter: (pi.person_id = n.id) -> Nested Loop (cost=7561.93..848301.16 rows=1 width=25) Join Filter: (pi.person_id = ci.person_id) -> Nested Loop (cost=0.00..89301.53 rows=1 width=4) Join Filter: (it.id = pi.info_type_id) -> Seq Scan on info_type it (cost=0.00..2.41 rows=1 width=4) Filter: ((info)::text = 'mini biography'::text) -> Seq Scan on person_info pi (cost=0.00..89298.73 rows=31 width=8) Filter: (note = 'Volker Boehm'::text) -> Hash Join (cost=7561.93..758975.51 rows=1930 width=21) Hash Cond: (ci.movie_id = t.id) -> Seq Scan on cast_info ci (cost=0.00..615402.24 rows=36267624 width=8) -> Hash (cost=7561.25..7561.25 rows=54 width=25) -> Nested Loop (cost=1.67..7561.25 rows=54 width=25) -> Hash Join (cost=1.24..561.48 rows=1666 width=4) Hash Cond: (ml.link_type_id = lt.id) -> Seq Scan on movie_link ml (cost=0.00..462.97 rows=29997 width=8) -> Hash (cost=1.23..1.23 rows=1 width=4) -> Seq Scan on link_type lt (cost=0.00..1.23 rows=1 width=4) Filter: ((link)::text = 'features'::text) -> Index Scan using title_pkey on title t (cost=0.43..4.20 rows=1 width=21) Index Cond: (id = ml.linked_movie_id) Filter: ((production_year >= 1980) AND (production_year <= 1984)) -> Index Scan using name_pkey on name n (cost=0.43..0.46 rows=1 width=19) Index Cond: (id = ci.person_id) Filter: (((name_pcode_cf)::text ~~ 'D%'::text) AND ((gender)::text = 'm'::text)) -> Seq Scan on aka_name an (cost=0.00..22662.79 rows=644338 width=4) Filter: (name ~~ '%a%'::text)