Skip to content

Instantly share code, notes, and snippets.

@jamii

jamii/29a

Created October 6, 2016 10:37
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jamii/1716f918bc17eaac8b61ba5d04720c88 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jamii/1716f918bc17eaac8b61ba5d04720c88 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
29a
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------
Aggregate (cost=266.51..266.52 rows=1 width=48) (actual time=397.078..397.078 rows=1 loops=1)
-> Nested Loop (cost=5.41..266.50 rows=1 width=48) (actual time=0.653..396.609 rows=1620 loops=1)
Join Filter: (cct2.id = cc.status_id)
-> Seq Scan on comp_cast_type cct2 (cost=0.00..1.05 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)
Filter: ((kind)::text = 'complete+verified'::text)
Rows Removed by Filter: 3
-> Nested Loop (cost=5.41..265.44 rows=1 width=52) (actual time=0.647..396.338 rows=1620 loops=1)
Join Filter: (cct1.id = cc.subject_id)
-> Seq Scan on comp_cast_type cct1 (cost=0.00..1.05 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)
Filter: ((kind)::text = 'cast'::text)
Rows Removed by Filter: 3
-> Nested Loop (cost=5.41..264.38 rows=1 width=56) (actual time=0.646..396.083 rows=1620 loops=1)
Join Filter: (t.id = cc.movie_id)
-> Nested Loop (cost=4.99..261.57 rows=1 width=68) (actual time=0.635..393.668 rows=1620 loops=1)
-> Nested Loop (cost=4.57..259.53 rows=1 width=72) (actual time=0.627..383.225 rows=7128 loops=1)
Join Filter: (it.id = mi.info_type_id)
-> Seq Scan on info_type it (cost=0.00..2.41 rows=1 width=4) (actual time=0.003..0.009 rows=1 loops=1)
Filter: ((info)::text = 'release dates'::text)
Rows Removed by Filter: 112
-> Nested Loop (cost=4.57..257.10 rows=1 width=76) (actual time=0.623..382.329 rows=7128 loops=1)
Join Filter: (t.id = mi.movie_id)
-> Nested Loop (cost=4.13..215.07 rows=1 width=68) (actual time=0.587..13.849 rows=3564 loops=1)
-> Nested Loop (cost=3.99..214.89 rows=1 width=72) (actual time=0.571..4.885 rows=10758 loops=1)
Join Filter: (n.id = pi.person_id)
-> Nested Loop (cost=3.56..195.16 rows=1 width=80) (actual time=0.556..1.564 rows=66 loops=1)
Join Filter: (t.id = mc.movie_id)
-> Nested Loop (cost=3.13..190.32 rows=1 width=72) (actual time=0.541..1.504 rows=3 loops=1)
Join Filter: (n.id = an.person_id)
-> Nested Loop (cost=2.71..189.03 rows=1 width=68) (actual time=0.495..1.456 rows=1 loops=1)
-> Nested Loop (cost=2.28..186.79 rows=1 width=56) (actual time=0.479..1.438 rows=1 loops=1)
Join Filter: (rt.id = ci.role_id)
-> Seq Scan on role_type rt (cost=0.00..1.15 rows=1 width=4) (actual time=0.001..0.003 rows=1 loops=1)
Filter: ((role)::text = 'actress'::text)
Rows Removed by Filter: 11
-> Nested Loop (cost=2.28..185.63 rows=1 width=60) (actual time=0.477..1.434 rows=1 loops=1)
-> Nested Loop (cost=1.85..183.31 rows=1 width=41) (actual time=0.103..1.081 rows=38 loops=1)
Join Filter: (t.id = ci.movie_id)
-> Nested Loop (cost=1.28..135.68 rows=1 width=25) (actual time=0.086..0.129 rows=1 loops=1)
Join Filter: (mk.keyword_id = k.id)
Rows Removed by Join Filter: 135
-> Index Scan using index_keyword_keyword on keyword k (cost=0.42..8.44 rows=1 width=4) (actual time=0.031..0.031 rows=1 loops=1)
Index Cond: (keyword = 'computer-animation'::text)
-> Nested Loop (cost=0.86..127.08 rows=13 width=29) (actual time=0.049..0.088 rows=136 loops=1)
-> Index Scan using index_title_title on title t (cost=0.43..58.99 rows=7 width=21) (actual time=0.021..0.033 rows=3 loops=1)
Index Cond: (title = 'Shrek 2'::text)
Filter: ((production_year >= 2000) AND (production_year <= 2010))
-> Index Scan using index_movie_keyword_movie_id on movie_keyword mk (cost=0.43..9.28 rows=45 width=8) (actual time=0.008..0.015 rows=45 loops=3)
Index Cond: (movie_id = t.id)
-> Index Scan using index_cast_info_movie_id on cast_info ci (cost=0.56..47.61 rows=1 width=16) (actual time=0.016..0.943 rows=38 loops=1)
Index Cond: (movie_id = mk.movie_id)
Filter: (note = ANY ('{(voice),"(voice) (uncredited)","(voice: English version)"}'::text[]))
Rows Removed by Filter: 191
-> Index Scan using index_name_id on name n (cost=0.43..2.31 rows=1 width=19) (actual time=0.009..0.009 rows=0 loops=38)
Index Cond: (id = ci.person_id)
Filter: ((name ~~ '%An%'::text) AND ((gender)::text = 'f'::text))
Rows Removed by Filter: 1
-> Index Scan using index_char_name_id on char_name chn (cost=0.43..2.23 rows=1 width=20) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (id = ci.person_role_id)
Filter: (name = 'Queen'::text)
-> Index Only Scan using index_aka_name_person_id on aka_name an (cost=0.42..1.27 rows=2 width=4) (actual time=0.044..0.046 rows=3 loops=1)
Index Cond: (person_id = ci.person_id)
Heap Fetches: 0
-> Index Scan using index_movie_companies_movie_id on movie_companies mc (cost=0.43..4.78 rows=5 width=8) (actual time=0.009..0.013 rows=22 loops=3)
Index Cond: (movie_id = mk.movie_id)
-> Index Scan using index_person_info_person_id on person_info pi (cost=0.43..19.42 rows=25 width=8) (actual time=0.002..0.025 rows=163 loops=66)
Index Cond: (person_id = an.person_id)
-> Index Scan using index_info_type_id on info_type it3 (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=10758)
Index Cond: (id = pi.info_type_id)
Filter: ((info)::text = 'trivia'::text)
Rows Removed by Filter: 1
-> Index Scan using index_movie_info_movie_id on movie_info mi (cost=0.43..42.03 rows=1 width=8) (actual time=0.009..0.103 rows=2 loops=3564)
Index Cond: (movie_id = mk.movie_id)
Filter: ((info IS NOT NULL) AND ((info ~~ 'Japan:%200%'::text) OR (info ~~ 'USA:%200%'::text)))
Rows Removed by Filter: 455
-> Index Scan using index_company_name_id on company_name cn (cost=0.42..2.03 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=7128)
Index Cond: (id = mc.company_id)
Filter: ((country_code)::text = '[us]'::text)
Rows Removed by Filter: 1
-> Index Scan using index_complete_cast_movie_id on complete_cast cc (cost=0.42..2.79 rows=2 width=12) (actual time=0.001..0.001 rows=1 loops=1620)
Index Cond: (movie_id = mk.movie_id)
Planning time: 75.629 ms
Execution time: 397.271 ms
(82 rows)
Raw
29a.sql
SELECT MIN(chn.name) AS voiced_char,
MIN(n.name) AS voicing_actress,
MIN(t.title) AS voiced_animation
FROM aka_name AS an,
complete_cast AS cc,
comp_cast_type AS cct1,
comp_cast_type AS cct2,
char_name AS chn,
cast_info AS ci,
company_name AS cn,
info_type AS it,
info_type AS it3,
keyword AS k,
movie_companies AS mc,
movie_info AS mi,
movie_keyword AS mk,
name AS n,
person_info AS pi,
role_type AS rt,
title AS t
WHERE cct1.kind ='cast'
AND cct2.kind ='complete+verified'
AND chn.name = 'Queen'
AND ci.note in ('(voice)',
'(voice) (uncredited)',
'(voice: English version)')
AND cn.country_code ='[us]'
AND it.info = 'release dates'
AND it3.info = 'trivia'
AND k.keyword = 'computer-animation'
AND mi.info is not null
and (mi.info like 'Japan:%200%'
or mi.info like 'USA:%200%')
AND n.gender ='f'
and n.name like '%An%'
AND rt.role ='actress'
AND t.title = 'Shrek 2'
AND t.production_year between 2000 and 2010
AND t.id = mi.movie_id
AND t.id = mc.movie_id
AND t.id = ci.movie_id
AND t.id = mk.movie_id
AND t.id = cc.movie_id
AND mc.movie_id = ci.movie_id
AND mc.movie_id = mi.movie_id
AND mc.movie_id = mk.movie_id
AND mc.movie_id = cc.movie_id
AND mi.movie_id = ci.movie_id
AND mi.movie_id = mk.movie_id
AND mi.movie_id = cc.movie_id
AND ci.movie_id = mk.movie_id
AND ci.movie_id = cc.movie_id
AND mk.movie_id = cc.movie_id
AND cn.id = mc.company_id
AND it.id = mi.info_type_id
AND n.id = ci.person_id
AND rt.id = ci.role_id
AND n.id = an.person_id
AND ci.person_id = an.person_id
AND chn.id = ci.person_role_id
AND n.id = pi.person_id
AND ci.person_id = pi.person_id
AND it3.id = pi.info_type_id
AND k.id = mk.keyword_id
AND cct1.id = cc.subject_id
AND cct2.id = cc.status_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment