Planner — IMDB — JOB-9D

SELECT MIN(an.name) AS alternative_name,
       MIN(chn.name) AS voiced_char_name,
       MIN(n.name) AS voicing_actress,
       MIN(t.title) AS american_movie
FROM job.aka_name AS an,
     job.char_name AS chn,
     job.cast_info AS ci,
     job.company_name AS cn,
     job.movie_companies AS mc,
     job.name AS n,
     job.role_type AS rt,
     job.title AS t
WHERE ci.note IN ('(voice)',
                  '(voice: Japanese version)',
                  '(voice) (uncredited)',
                  '(voice: English version)')
  AND cn.country_code ='[us]'
  AND n.gender ='f'
  AND rt.role ='actress'
  AND ci.movie_id = t.id
  AND t.id = mc.movie_id
  AND ci.movie_id = mc.movie_id
  AND mc.company_id = cn.id
  AND ci.role_id = rt.id
  AND n.id = ci.person_id
  AND chn.id = ci.person_role_id
  AND an.person_id = n.id
  AND an.person_id = ci.person_id;

Engine Compare

Accuracy chart, rows processed ?
Scan Scan	Seek Seek	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
Estimation Error Est Err 10,061,208 10M Rank		Estimation Error Est Err 10,409,442 10M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,194,318 2.2M Rank	Estimation Error Est Err 483,082 483K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 46,385,023 46M Rank		Estimation Error Est Err 66,978,018 67M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 48,819,711 49M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 10,455,950 10M Rank		Estimation Error Est Err 11,524,904 12M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,040,472 4M Rank	Estimation Error Est Err 483,084 483K Rank	Estimation Error Est Err 2,488,280 2.5M Rank
Estimation Error Est Err 18,054,314 18M Rank		Estimation Error Est Err 11,170,548 11M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,295,696 4.3M Rank	Estimation Error Est Err 483,092 483K Rank	Estimation Error Est Err 9,966,460 10M Rank
Estimation Error Est Err 8,202,036 8.2M Rank		Estimation Error Est Err 4,991,132 5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,495,866 4.5M Rank	Estimation Error Est Err 483,082 483K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,833,738 4.8M Rank	Estimation Error Est Err 2,224,608 2.2M Rank	Estimation Error Est Err 5,037,909 5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,533,354 3.5M Rank	Estimation Error Est Err 483,086 483K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 573,460 573K Rank	Estimation Error Est Err 47,857 48K Rank	Estimation Error Est Err 2,100,890 2.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,203,504 2.2M Rank	Estimation Error Est Err 483,148 483K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,046,357 9M Rank		Estimation Error Est Err 3,422,895 3.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 9,651,627 9.7M Rank	Estimation Error Est Err 483,098 483K Rank	Estimation Error Est Err 8,770,207 8.8M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min, min 3496 483082 INNER JOIN HASH ON person_id72 = id16 13824 173567 │└INNER JOIN HASH ON movie_id = id56 13250 173567 │ │└INNER JOIN HASH ON id46 = person_role_id 13868 173567 │ │ │└INNER JOIN HASH ON company_id = id36 37783 426400 │ │ │ │└INNER JOIN HASH ON movie_id = movie_id29 18433 255493 │ │ │ │ │└INNER JOIN HASH ON id16 = person_id 74487 255651 │ │ │ │ │ │└INNER JOIN HASH ON role_id = id 1 1 │ │ │ │ │ │ │└TABLE SCAN role_type WHERE role = actress 707897 801259 │ │ │ │ │ │ TABLE SCAN cast_info WHERE note IN(voice,voice uncredited,(voice : English version),(voice : Japanese version)) 931987 71641 │ │ │ │ │ TABLE SCAN name WHERE gender = f 2609129 2609129 │ │ │ │ TABLE SCAN movie_companies 90648 9184 │ │ │ TABLE SCAN company_name WHERE country_code = us 3140339 3140339 │ │ TABLE SCAN char_name 2528312 2528312 │ TABLE SCAN title 901343 901343 TABLE SCAN aka_name
	Estimate Actual Operator - 1 PROJECT a1 AS alternative_name, a2 AS voiced_char_name, a3 AS voicing_actress, a4 AS american_movie - 1 AGGREGATE MIN(name_right) AS a1, MIN(name_right_2) AS a2, MIN(name_left) AS a3, MIN(title) AS a4 - 0 PROJECT name, name, name, title - 0 PROJECT name, name, name, title - 0 INNER JOIN HASH ON tuple(PROJECTION_5800.id,PROJECTION_5800.id) = tuple(PROJECTION_5761.person_id,PROJECTION_5761.person_id) - 0 │└PROJECT person_id, person_id, name_right, name AS name_right_2, title - 0 │ PROJECT name, person_id, person_id, name, title - 0 │ INNER JOIN HASH ON tuple(PROJECTION_5797.id,PROJECTION_5797.id) = tuple(PROJECTION_5764.movie_id,PROJECTION_5764.movie_id) - 0 │ │└PROJECT movie_id, movie_id, name, person_id, person_id, name - 0 │ │ PROJECT name, person_id, person_id, movie_id, name, movie_id - 0 │ │ INNER JOIN HASH ON PROJECTION_5794.id = PROJECTION_5767.company_id - 12522525 │ │ │└PROJECT company_id, name, person_id, person_id, movie_id, name, movie_id - 12522525 │ │ │ PROJECT name, person_id, person_id, movie_id, name, movie_id, company_id - 12522525 │ │ │ INNER JOIN HASH ON PROJECTION_5791.movie_id = PROJECTION_5770.movie_id - 6559938 │ │ │ │└PROJECT movie_id AS movie_id_right, name, person_id, person_id, name - 6559938 │ │ │ │ PROJECT name, person_id, person_id, movie_id, name - 6559938 │ │ │ │ INNER JOIN HASH ON PROJECTION_5788.id = PROJECTION_5773.person_role_id - 8161344 │ │ │ │ │└PROJECT person_role_id, name AS name_right, person_id, person_id, movie_id - 8161344 │ │ │ │ │ PROJECT name, person_id, person_id, person_role_id, movie_id - 8161344 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_5785.person_id = PROJECTION_5776.person_id - 7451973 │ │ │ │ │ │└PROJECT person_id AS person_id_right, person_role_id, movie_id - 7451973 │ │ │ │ │ │ PROJECT person_id, person_role_id, movie_id - 7451973 │ │ │ │ │ │ INNER JOIN HASH ON PROJECTION_5782.role_id = PROJECTION_5779.id - 1 │ │ │ │ │ │ │└PROJECT id - 1 │ │ │ │ │ │ │ PROJECT id - 1 │ │ │ │ │ │ │ TABLE SCAN role_type WHERE role = 'actress' - 36244344 │ │ │ │ │ │ PROJECT role_id, person_id, person_role_id, movie_id - 36244344 │ │ │ │ │ │ PROJECT person_id, person_role_id, movie_id, role_id - 36244344 │ │ │ │ │ │ TABLE SCAN cast_info WHERE TRUE - 901343 │ │ │ │ │ PROJECT person_id AS person_id_left, name - 901343 │ │ │ │ │ PROJECT name, person_id - 901343 │ │ │ │ │ TABLE SCAN aka_name - 3140339 │ │ │ │ PROJECT id, name AS name_left - 3140339 │ │ │ │ PROJECT name, id - 3140339 │ │ │ │ TABLE SCAN char_name - 2609129 │ │ │ PROJECT movie_id AS movie_id_left, company_id - 2609129 │ │ │ PROJECT movie_id, company_id - 2609129 │ │ │ TABLE SCAN movie_companies - 0 │ │ PROJECT id - 0 │ │ PROJECT id - 0 │ │ TABLE SCAN company_name WHERE country_code = 'us' - 2528312 │ PROJECT id, title - 2528312 │ PROJECT title, id - 2528312 │ TABLE SCAN title - 961555 PROJECT id, name AS name_left - 961555 PROJECT id, name - 961555 TABLE SCAN name WHERE gender = 'f'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2), MIN(#3) 657 483082 PROJECT name, name, name, title 657 483082 INNER JOIN HASH ON id = movie_id 645 483082 │└INNER JOIN HASH ON id = person_role_id 645 505134 │ │└INNER JOIN HASH ON id = person_id 1113 505250 │ │ │└INNER JOIN HASH ON person_id = person_id 4439 189554 │ │ │ │└INNER JOIN HASH ON role_id = id 1 1 │ │ │ │ │└FILTER id <= 11 1 1 │ │ │ │ │ TABLE SCAN role_type WHERE role = 'actress' 53270 189554 │ │ │ │ INNER JOIN HASH ON movie_id = movie_id 18253 1153798 │ │ │ │ │└INNER JOIN HASH ON company_id = id 1644 84843 │ │ │ │ │ │└TABLE SCAN company_name WHERE country_code = 'us' 2609129 2609129 │ │ │ │ │ TABLE SCAN movie_companies 7248868 215901 │ │ │ │ FILTER (person_id >= 4) AND (movie_id BETWEEN 2 AND 2525745) 7248868 215901 │ │ │ │ FILTER (note = '(voice)') OR (note = '(voice: Japanese version)') OR (note = '(voice) (uncredited)') OR (note = '(voice: English version)') 36244344 5459458 │ │ │ │ TABLE SCAN cast_info WHERE note IN('(voice)','(voice: Japanese version)','(voice) (uncredited)','(voice: English version)') 901343 10451 │ │ │ TABLE SCAN aka_name WHERE person_id <= 4061926 2083746 5329 │ │ FILTER id BETWEEN 4 AND 4061926 2083746 5329 │ │ TABLE SCAN "name" WHERE gender = 'f' 3140339 15967 │ TABLE SCAN char_name 2528312 16858 TABLE SCAN title WHERE id >= 2 AND id <= 2525745
	Estimate Actual Operator 1 1 PROJECT alternative_name, voiced_char_name, voicing_actress, american_movie 1 1 AGGREGATE MIN(name), MIN(name), MIN(name), MIN(title) 90635 10 DISTRIBUTE GATHER 90635 10 AGGREGATE MIN(name), MIN(name), MIN(name), MIN(title) 90635 483082 INNER JOIN HASH ON movie_id = id AND movie_id = id 90635 483082 │└DISTRIBUTE GATHER 90635 483082 │ INNER JOIN HASH ON id = role_id 3 1 │ │└DISTRIBUTE GATHER 3 1 │ │ FILTER role = 'actress' 12 12 │ │ DISTRIBUTE ROUND ROBIN 12 12 │ │ TABLE SCAN role_type WHERE role = 'actress' 332330 483082 │ INNER JOIN HASH ON person_id = id AND person_id = id 332330 490029 │ │└DISTRIBUTE HASH ON person_id, person_id 332330 490029 │ │ INNER JOIN HASH ON id = company_id 47000 84843 │ │ │└DISTRIBUTE GATHER 47000 84843 │ │ │ FILTER country_code = 'us' 234997 234997 │ │ │ TABLE SCAN company_name WHERE country_code = 'us' 1661629 930328 │ │ INNER JOIN HASH ON movie_id = movie_id 1608526 497319 │ │ │└DISTRIBUTE HASH ON movie_id 1608526 497319 │ │ │ INNER JOIN HASH ON person_role_id = id 1608526 517803 │ │ │ │└DISTRIBUTE HASH ON person_role_id 1608526 517803 │ │ │ │ INNER JOIN HASH ON person_id = person_id 901343 901343 │ │ │ │ │└DISTRIBUTE HASH ON person_id 901343 901343 │ │ │ │ │ TABLE SCAN aka_name 7248869 280995 │ │ │ │ DISTRIBUTE HASH ON person_id 7248869 280995 │ │ │ │ FILTER note IN('(voice)','(voice: Japanese version)','(voice) (uncredited)','(voice: English version)') 36244344 7656546 │ │ │ │ TABLE SCAN cast_info WHERE (note IN('(voice)','(voice: Japanese version)','(voice) (uncredited)','(voice: English version)') AND CASE MOD(HASH_REPARTITION person_id,10) WHEN 0 THEN (((person_id >= 5) AND (person_id <= 4167489)) AND TRUE) WHEN 1 THEN (((person_id >= 15) AND (person_id <= 4167473)) AND TRUE) WHEN 2 THEN (((person_id >= 69) AND (person_id <= 4167478)) AND TRUE) WHEN 3 THEN (((person_id >= 161) AND (person_id <= 4167352)) AND TRUE) WHEN 4 THEN ... 3140339 3140339 │ │ │ DISTRIBUTE HASH ON id 3140339 3140339 │ │ │ TABLE SCAN char_name WHERE CASE MOD(HASH_REPARTITION id,10) WHEN 0 THEN (((id >= 119) AND (id <= 3139884)) AND TRUE) WHEN 1 THEN (((id >= 63) AND (id <= 3139863)) AND TRUE) WHEN 2 THEN (((id >= 53) AND (id <= 3139889)) AND TRUE) WHEN 3 THEN (((id >= 1) AND (id <= 3139888)) AND TRUE) WHEN 4 THEN (((id >= 21) AND (id <= 3139890)) AND TRUE) WHEN 5 THEN (((id >= 57) AND (id <= 3139914)) AND TRUE) WHEN 6 THEN (((id >= 142) AND (id <= 3139883)) AND TRUE) WHEN 7 THEN ... 2609129 2609129 │ │ DISTRIBUTE HASH ON movie_id 2609129 2609129 │ │ TABLE SCAN movie_companies WHERE CASE MOD(HASH_REPARTITION movie_id,10) WHEN 0 THEN (((movie_id >= 920) AND (movie_id <= 2525688)) AND TRUE) WHEN 1 THEN (((movie_id >= 1126) AND (movie_id <= 2525697)) AND TRUE) WHEN 2 THEN (((movie_id >= 921) AND (movie_id <= 2525701)) AND TRUE) WHEN 3 THEN (((movie_id >= 912) AND (movie_id <= 2525700)) AND TRUE) WHEN 4 THEN (((movie_id >= 925) AND (movie_id <= 2525205)) AND TRUE) WHEN 5 THEN (((movie_id >= 908) AND (movie_id <= 25... 833499 961555 │ DISTRIBUTE HASH ON id, id 833499 961555 │ FILTER gender = 'f' 4167491 983636 │ TABLE SCAN name WHERE (gender = 'f') AND CASE MOD(HASH_REPARTITION(id,id),10) WHEN 1 THEN ((((id >= 169332) AND (id <= 2700427)) AND ((id >= 169332) AND (id <= 2700427))) AND TRUE) WHEN 3 THEN ((((id >= 4453) AND (id <= 2698015)) AND ((id >= 4453) AND (id <= 2698015))) AND TRUE) WHEN 5 THEN ((((id >= 1728587) AND (id <= 2700464)) AND ((id >= 1728587) AND (id <= 2700464))) AND TRUE) WHEN 7 THEN ((((id >= 1728872) AND (id <= 2700366)) AND ((id >= 1728872) AND (id <= 2700... 2528312 2528312 TABLE SCAN title WHERE (((id >= 908) AND (id <= 2525702)) AND ((id >= 908) AND (id <= 2525702))) AND TRUE
	Estimate Actual Operator 1 1 AGGREGATE MIN(an.name), MIN(chn.name), MIN(n.name), MIN(t.title) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE MIN(an.name), MIN(chn.name), MIN(n.name), MIN(t.title) 608 483082 INNER JOIN HASH ON ci.movie_id = mc.movie_id 608 482841 │└DISTRIBUTE GATHER 150 482841 │ INNER JOIN HASH ON ci.person_id = an.person_id 150 255493 │ │└DISTRIBUTE GATHER 142 255493 │ │ INNER JOIN HASH ON ci.person_id = n.id 142 255651 │ │ │└DISTRIBUTE GATHER 142 255651 │ │ │ INNER JOIN HASH ON ci.person_role_id = chn.id 142 255651 │ │ │ │└DISTRIBUTE GATHER 12100 255651 │ │ │ │ INNER JOIN HASH ON ci.role_id = rt.id 12100 1 │ │ │ │ │└DISTRIBUTE GATHER 235000 1 │ │ │ │ │ TABLE SCAN role_type WHERE rt.role = 'actress' 2610000 255651 │ │ │ │ TABLE SCAN cast_info WHERE (ci.note IN('(voice)','(voice: Japanese version)','(voice) (uncredited)','(voice: English version)')) AND (ci.person_role_id IS NOT NULL) 901000 3132421 │ │ │ TABLE SCAN char_name 4170000 956453 │ │ TABLE SCAN name WHERE (n.gender IS NOT NULL) AND (n.gender = 'f') 12 897301 │ TABLE SCAN aka_name 238 1153798 INNER JOIN HASH ON mc.movie_id = t.id 238 1153798 │└DISTRIBUTE GATHER 142 1153798 │ INNER JOIN HASH ON mc.company_id = cn.id 142 84843 │ │└DISTRIBUTE GATHER 2530000 84843 │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 3140000 2606542 │ TABLE SCAN movie_companies 36200000 2522738 TABLE SCAN title
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1047) AS Expr1016, MIN(partialagg1048) AS Expr1017, MIN(partialagg1049) AS Expr1018, MIN(partialagg1050) AS Expr1019 5 10 AGGREGATE MIN(name as name) AS partialagg1047, MIN(name as name) AS partialagg1048, MIN(name as name) AS partialagg1049, MIN(title as title) AS partialagg1050 30101 483138 INNER JOIN HASH ON ci.role_id = rt.id 1 1 │└FILTER role as role = 'actress' 12 12 │ INNER JOIN LOOP ON Bmk1012 = Bmk1012 1 12 │ │└TABLE SEEK role_type AS rt 12 12 │ TABLE SEEK role_type AS rt 33111 483138 INNER JOIN HASH ON an.person_id = n.id 6824 173655 │└INNER JOIN HASH ON mc.company_id = cn.id 84576 84843 │ │└TABLE SCAN company_name AS cn WHERE country_code as country_code = 'us' 18974 173655 │ INNER JOIN HASH ON mc.movie_id = t.id 8722 255592 │ │└INNER JOIN HASH ON t.id = ci.movie_id 8722 255592 │ │ │└INNER JOIN HASH ON id as id = person_role_id as person_role_id 19388 276108 │ │ │ │└INNER JOIN HASH ON n.id = ci.person_id 86757 276269 │ │ │ │ │└TABLE SCAN cast_info AS ci WHERE (note as note = '(voice)' OR note as note = '(voice) (uncredited)' OR note as note = '(voice: English version)' OR note as note = '(voice: Japanese version)') AND BLOOM(role_id as role_id) 931 31372 │ │ │ │ TABLE SCAN name AS n WHERE gender as gender = 'f' AND BLOOM(id as id) 31403 47378 │ │ │ TABLE SCAN char_name AS chn WHERE BLOOM(id as id) 2528310 76034 │ │ TABLE SCAN title AS t WHERE BLOOM(id as id) 26091 47833 │ TABLE SEEK movie_companies AS mc WHERE BLOOM(company_id as company_id) AND BLOOM(movie_id as movie_id) 901 9707 TABLE SCAN aka_name AS an WHERE BLOOM(person_id as person_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS alternative_name, min_50 AS voiced_char_name, min_51 AS voicing_actress, min_52 AS american_movie 1 1 AGGREGATE MIN(min_53) AS min, MIN(min_54) AS min_50, MIN(min_55) AS min_51, MIN(min_56) AS min_52 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name) AS min_53, MIN(name_1) AS min_54, MIN(name_29) AS min_55, MIN(title) AS min_56 - 483082 INNER JOIN HASH ON movie_id = id_43 2528312 2528312 │└DISTRIBUTE HASH ON id_43 2528312 2528312 │ PROJECT id AS id_43, title 2528312 2528312 │ TABLE SCAN title - 483082 INNER JOIN HASH ON role_id = id_39 12 1 │└DISTRIBUTE GATHER 12 1 │ PROJECT id AS id_39 12 1 │ FILTER role = 'actress' 12 1 │ TABLE SCAN role_type - 483082 INNER JOIN HASH ON person_id_10 = id_28 4167491 961555 │└DISTRIBUTE HASH ON id_28 4167491 961555 │ PROJECT id AS id_28, name AS name_29 4167491 961555 │ FILTER gender = 'f' 4167491 961555 │ TABLE SCAN name - 483198 INNER JOIN HASH ON company_id = id_14 234997 84843 │└DISTRIBUTE GATHER 234997 84843 │ PROJECT id AS id_14 234997 84843 │ FILTER country_code = 'us' 234997 84843 │ TABLE SCAN company_name - 483198 INNER JOIN HASH ON movie_id = movie_id_23 2609129 1153798 │└DISTRIBUTE HASH ON movie_id_23 2609129 1153798 │ PROJECT movie_id AS movie_id_23, company_id 2609129 1153798 │ TABLE SCAN movie_companies - 483319 INNER JOIN HASH ON person_role_id = id_0 3140339 3140339 │└DISTRIBUTE HASH ON id_0 3140339 3140339 │ PROJECT id AS id_0, name AS name_1 3140339 3140339 │ TABLE SCAN char_name - 503508 INNER JOIN HASH ON person_id_10 = person_id 901343 901343 │└DISTRIBUTE GATHER 901343 901343 │ TABLE SCAN aka_name 32619910 276166 PROJECT person_id AS person_id_10, movie_id, person_role_id, role_id 32619910 276166 FILTER note IN('(voice)','(voice) (uncredited)','(voice: English version)','(voice: Japanese version)') 32619910 276166 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(name), MIN(name), MIN(title) 4 4 AGGREGATE PARTIAL MIN(name), PARTIAL MIN(name), PARTIAL MIN(name), PARTIAL MIN(title) 54336 483082 INNER JOIN LOOP ON person_id = id 22368 173567 │└INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 22368 173567 │ │└INNER JOIN LOOP ON id = person_role_id 22368 189464 │ │ │└INNER JOIN LOOP ON id = person_id 24292 47388 │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 289280 1104664 │ │ │ │ │└INNER JOIN LOOP ON role_id = id 1 1 │ │ │ │ │ │└TABLE SCAN role_type AS rt WHERE rt.role = 'actress' 315580 1104664 │ │ │ │ │ TABLE SEEK cast_info AS ci WHERE ci.note IN('(voice)','(voice: Japanese version)','(voice) (uncredited)','(voice: English version)') 307315 288449 │ │ │ │ INNER JOIN HASH ON company_id = id 201896 84843 │ │ │ │ │└TABLE SEEK company_name AS cn 3366620 2609129 │ │ │ │ TABLE SCAN movie_companies AS mc 189554 189554 │ │ │ TABLE SEEK name AS n WHERE n.gender = 'f' 189464 189464 │ │ TABLE SEEK char_name AS chn 173567 173567 │ TABLE SEEK title AS t 347134 482516 TABLE SEEK aka_name AS an