Planner — IMDB — JOB-8A

SELECT MIN(an1.name) AS actress_pseudonym,
       MIN(t.title) AS japanese_movie_dubbed
FROM job.aka_name AS an1,
     job.cast_info AS ci,
     job.company_name AS cn,
     job.movie_companies AS mc,
     job.name AS n1,
     job.role_type AS rt,
     job.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;

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 3,522,829 3.5M Rank		Estimation Error Est Err 3,523,221 3.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 70,229 70K Rank	Estimation Error Est Err 62 62 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,592,559 3.6M Rank		Estimation Error Est Err 120,515 121K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,506,995 3.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,054,295 3.1M Rank		Estimation Error Est Err 3,047,581 3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 77,298 77K Rank	Estimation Error Est Err 63 63 Rank	Estimation Error Est Err 42,428 42K Rank
Estimation Error Est Err 16,023,456 16M Rank		Estimation Error Est Err 2,463,519 2.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 985,244 985K Rank	Estimation Error Est Err 72 72 Rank	Estimation Error Est Err 1,085,350 1.1M Rank
Estimation Error Est Err 40,904 41K Rank		Estimation Error Est Err 50,442 50K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 84,790 85K Rank	Estimation Error Est Err 62 62 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 159,253 159K Rank	Estimation Error Est Err 159,252 159K Rank	Estimation Error Est Err 172,100 172K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 152,484 152K Rank	Estimation Error Est Err 62 62 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,337,168 9.3M Rank	Estimation Error Est Err 9,282,076 9.3M Rank	Estimation Error Est Err 16,782,377 17M Rank	Estimation Error Est Err 104 104 Rank	Estimation Error Est Err 10,881,714 11M Rank	Estimation Error Est Err 104 104 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,608,929 2.6M Rank		Estimation Error Est Err 354 354 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,608,965 2.6M Rank	Estimation Error Est Err 78 78 Rank	Estimation Error Est Err 2,608,920 2.6M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 1 62 INNER JOIN HASH ON person_id62 = id16 1 38 │└INNER JOIN HASH ON movie_id = id46 1 38 │ │└INNER JOIN HASH ON company_id = id36 1 39 │ │ │└INNER JOIN HASH ON movie_id = movie_id29 1 394 │ │ │ │└INNER JOIN HASH ON id16 = person_id 7 34848 │ │ │ │ │└INNER JOIN HASH ON role_id = id 1 1 │ │ │ │ │ │└TABLE SCAN role_type WHERE role = actress 59 93095 │ │ │ │ │ TABLE SCAN cast_info WHERE note = (voice : English version) 8139 15 │ │ │ │ TABLE SCAN name WHERE name LIKE '%Yo%' AND NOT (name LIKE '%Yu%') 38219 37 │ │ │ TABLE SCAN movie_companies WHERE note32 LIKE '%(Japan)%' AND NOT (note32 LIKE '%(USA)%') 6655 26 │ │ TABLE SCAN company_name WHERE country_code = jp 2528312 2528312 │ TABLE SCAN title 901343 901343 TABLE SCAN aka_name
	Estimate Actual Operator - 1 PROJECT a1 AS actress_pseudonym, a2 AS japanese_movie_dubbed - 1 AGGREGATE MIN(name) AS a1, MIN(title) AS a2 - 0 PROJECT name, title - 0 PROJECT name, title - 0 INNER JOIN HASH ON tuple(PROJECTION_5459.movie_id,PROJECTION_5459.movie_id) = tuple(PROJECTION_5456.id,PROJECTION_5456.id) - 2528312 │└PROJECT id, title - 2528312 │ PROJECT title, id - 2528312 │ TABLE SCAN title - 0 PROJECT movie_id, movie_id, name - 0 PROJECT name, movie_id, movie_id - 0 INNER JOIN HASH ON PROJECTION_5465.company_id = PROJECTION_5462.id - 0 │└PROJECT id - 0 │ PROJECT id - 0 │ TABLE SCAN company_name WHERE country_code = 'jp' - 0 PROJECT company_id, name, movie_id, movie_id - 0 PROJECT name, movie_id, movie_id, company_id - 0 INNER JOIN HASH ON tuple(PROJECTION_5471.person_id,PROJECTION_5471.id) = tuple(PROJECTION_5468.person_id,PROJECTION_5468.person_id) - 901343 │└PROJECT person_id AS person_id_right, name - 901343 │ PROJECT name, person_id - 901343 │ TABLE SCAN aka_name - 39 PROJECT person_id AS person_id_left, id, movie_id, movie_id, company_id - 39 PROJECT person_id, movie_id, movie_id, company_id, id - 39 INNER JOIN HASH ON PROJECTION_5477.person_id = PROJECTION_5474.id - 20911 │└PROJECT id - 20911 │ PROJECT id - 20911 │ TABLE SCAN name WHERE name LIKE '%Yo%' AND notLike(name,'%Yu%') - 7532 PROJECT person_id, movie_id, movie_id, company_id - 7532 PROJECT person_id, movie_id, movie_id, company_id - 7532 INNER JOIN HASH ON PROJECTION_5483.role_id = PROJECTION_5480.id - 1 │└PROJECT id - 1 │ PROJECT id - 1 │ TABLE SCAN role_type WHERE role = 'actress' - 19849 PROJECT role_id, person_id, movie_id, movie_id, company_id - 19849 PROJECT person_id, movie_id, role_id, movie_id, company_id - 19849 INNER JOIN HASH ON PROJECTION_5489.movie_id = PROJECTION_5486.movie_id - 48897 │└PROJECT movie_id AS movie_id_right, company_id - 48897 │ PROJECT movie_id, company_id - 48897 │ TABLE SCAN movie_companies WHERE note LIKE '%(Japan)%' AND notLike(note,'%(USA)%') - 93095 PROJECT movie_id AS movie_id_left, person_id, role_id - 93095 PROJECT person_id, movie_id, role_id - 93095 TABLE SCAN cast_info WHERE note = '(voice: English version)'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 0 62 PROJECT name, title 0 62 INNER JOIN HASH ON id = movie_id 0 62 │└INNER JOIN HASH ON person_id = id 0 38 │ │└INNER JOIN HASH ON id = id 0 7438 │ │ │└INNER JOIN HASH ON id = company_id 5 7532 │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 26 34848 │ │ │ │ │└INNER JOIN HASH ON role_id = id 1 1 │ │ │ │ │ │└FILTER id <= 11 1 1 │ │ │ │ │ │ TABLE SCAN role_type WHERE role = 'actress' 323 34848 │ │ │ │ │ FILTER (id >= 4) AND (movie_id BETWEEN 2 AND 2525745) 323 34848 │ │ │ │ │ TABLE SCAN cast_info WHERE note = '(voice: English version)' 521825 1544 │ │ │ │ TABLE SCAN movie_companies WHERE contains(note,'(Japan)') AND ( NOT contains(note,'(USA)')) 1644 3976 │ │ │ TABLE SCAN company_name WHERE country_code = 'jp' 833498 5 │ │ FILTER id BETWEEN 4 AND 4061926 833498 5 │ │ TABLE SCAN "name" WHERE contains(name,'Yo') AND ( NOT contains(name,'Yu')) 901343 6 │ TABLE SCAN aka_name WHERE person_id <= 4061926 2528312 524 TABLE SCAN title WHERE id >= 2 AND id <= 2525745
	Estimate Actual Operator 1 1 PROJECT actress_pseudonym, japanese_movie_dubbed 1 1 AGGREGATE MIN(name), MIN(title) 28463 10 DISTRIBUTE GATHER 28463 10 AGGREGATE MIN(name), MIN(title) 28463 62 INNER JOIN HASH ON movie_id = id AND movie_id = id 28463 62 │└DISTRIBUTE GATHER 28463 62 │ 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' 104366 62 │ INNER JOIN HASH ON person_id = id AND person_id = id 104366 17440 │ │└DISTRIBUTE GATHER 104366 17440 │ │ INNER JOIN HASH ON id = company_id 47000 6752 │ │ │└DISTRIBUTE GATHER 47000 6752 │ │ │ FILTER country_code = 'jp' 234997 234997 │ │ │ TABLE SCAN company_name WHERE country_code = 'jp' 521826 17599 │ │ PROJECT person_id, name, person_id, movie_id, role_id, movie_id, company_id 521826 17599 │ │ INNER JOIN HASH ON movie_id = movie_id 521826 48897 │ │ │└DISTRIBUTE HASH ON movie_id 521826 48897 │ │ │ FILTER note LIKE '%(Japan)%' AND note NOT LIKE '%(USA)%' 2609129 2609129 │ │ │ TABLE SCAN movie_companies WHERE (note LIKE '%(Japan)%' AND note NOT LIKE '%(USA)%') AND (((company_id >= 74) AND (company_id <= 234945)) AND TRUE) 1608526 75828 │ │ DISTRIBUTE HASH ON movie_id 1608526 75828 │ │ INNER JOIN HASH ON person_id = person_id 901343 901343 │ │ │└DISTRIBUTE HASH ON person_id 901343 901343 │ │ │ TABLE SCAN aka_name 7248869 35005 │ │ DISTRIBUTE HASH ON person_id 7248869 35005 │ │ FILTER note = '(voice: English version)' 36244344 7533666 │ │ TABLE SCAN cast_info WHERE (((note = '(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 (((person_id >= 94) AND (person_id <= 4167420)) AND TRUE) WHEN 5 THE... 833499 11650 │ FILTER name LIKE '%Yo%' AND name NOT LIKE '%Yu%' 4167491 2461757 │ TABLE SCAN name WHERE (name LIKE '%Yo%' AND name NOT LIKE '%Yu%') AND ((((id >= 293010) AND (id <= 2697033)) AND ((id >= 293010) AND (id <= 2697033))) AND TRUE) 2528312 2282552 TABLE SCAN title WHERE (((id >= 443156) AND (id <= 2487182)) AND ((id >= 443156) AND (id <= 2487182))) AND struct(id,id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , ...
	Estimate Actual Operator 1 1 AGGREGATE MIN(an1.name), MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(an1.name), MIN(t.title) 220 62 INNER JOIN HASH ON ci.person_id = an1.person_id 220 38 │└DISTRIBUTE GATHER 86 38 │ INNER JOIN HASH ON mc.company_id = cn.id 86 6752 │ │└DISTRIBUTE GATHER 4170000 6752 │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'jp') 81 39 │ INNER JOIN HASH ON ci.movie_id = mc.movie_id 81 394 │ │└DISTRIBUTE GATHER 901000 394 │ │ INNER JOIN HASH ON ci.movie_id = t.id 901000 394 │ │ │└DISTRIBUTE GATHER 51 394 │ │ │ INNER JOIN HASH ON ci.person_id = n1.id 51 34848 │ │ │ │└DISTRIBUTE GATHER 12100 34848 │ │ │ │ INNER JOIN HASH ON ci.role_id = rt.id 12100 1 │ │ │ │ │└DISTRIBUTE GATHER 235000 1 │ │ │ │ │ TABLE SCAN role_type WHERE rt.role = 'actress' 2610000 34848 │ │ │ │ TABLE SCAN cast_info WHERE (ci.note IS NOT NULL) AND (ci.note = '(voice: English version)') 2530000 9844 │ │ │ TABLE SCAN name WHERE contains(n1.name,'Yo') AND ( NOT contains(n1.name,'Yu')) 12 2475066 │ │ TABLE SCAN title 36200000 44232 │ TABLE SCAN movie_companies WHERE (mc.note IS NOT NULL) AND contains(mc.note,'(Japan)') AND ( NOT contains(mc.note,'(USA)')) 901000 483552 TABLE SCAN aka_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1014, MIN(title as title) AS Expr1015 87 104 INNER JOIN LOOP ON Bmk1012 = Bmk1012 1 104 │└TABLE SEEK title AS t 87 104 SORT Expr1059 87 104 PROJECT BmkToPage Bmk1012 AS Expr1059 87 104 INNER JOIN LOOP ON mc.movie_id = t.id 1 104 │└TABLE SEEK title AS t 87 104 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 104 │└TABLE SEEK aka_name AS an1 87 104 INNER JOIN LOOP ON n1.id = an1.person_id 4 104 │└TABLE SEEK aka_name AS an1 17 71 FILTER name as name LIKE '%Yo%' AND NOT name as name LIKE '%Yu%' 30 7441 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 7441 │└TABLE SEEK name AS n1 30 7441 PROJECT BmkToPage Bmk1008 AS Expr1072 30 7441 INNER JOIN LOOP ON ci.person_id = n1.id 1 7441 │└TABLE SEEK name AS n1 30 7441 FILTER note as note = '(voice: English version)' 15795 263495 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 263495 │└TABLE SEEK cast_info AS ci 15795 263495 PROJECT BmkToPage Bmk1002 AS Expr1069 15795 263495 INNER JOIN HASH ON Bmk1002 = Bmk1002 173750 1551310 │└INNER JOIN LOOP ON mc.movie_id = ci.movie_id 30 1551310 │ │└TABLE SEEK cast_info AS ci 5749 48328 │ INNER JOIN HASH ON mc.company_id = cn.id 6802 6752 │ │└TABLE SCAN company_name AS cn WHERE country_code as country_code = 'jp' 49157 48328 │ TABLE SCAN movie_companies AS mc WHERE (PROBE(Bitmap1066,company_id as company_id,N'IN ROW')) AND (note as note LIKE '%(Japan)%' AND NOT note as note LIKE '%(USA)%') 3294940 7451973 INNER JOIN LOOP ON rt.id = ci.role_id 3294940 7451973 │└TABLE SEEK cast_info AS ci 1 1 FILTER role as role = 'actress' 12 12 TABLE SCAN role_type AS rt
	Estimate Actual Operator 1 ∞ PROJECT min AS actress_pseudonym, min_40 AS japanese_movie_dubbed 1 1 AGGREGATE MIN(min_41) AS min, MIN(min_42) AS min_40 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name) AS min_41, MIN(title) AS min_42 - 62 INNER JOIN HASH ON movie_id = id_33 2528312 2528312 │└DISTRIBUTE HASH ON id_33 2528312 2528312 │ PROJECT id AS id_33, title 2528312 2528312 │ TABLE SCAN title - 62 INNER JOIN HASH ON role_id = id_29 12 1 │└DISTRIBUTE GATHER 12 1 │ PROJECT id AS id_29 12 1 │ FILTER role = 'actress' 12 1 │ TABLE SCAN role_type - 62 INNER JOIN HASH ON person_id_1 = id_18 4167491 20911 │└DISTRIBUTE HASH ON id_18 4167491 20911 │ PROJECT id AS id_18 4167491 20911 │ FILTER (name LIKE '%Yo%') AND NOT (name LIKE '%Yu%') 4167491 20911 │ TABLE SCAN name - 62 INNER JOIN HASH ON company_id = id_5 234997 6752 │└DISTRIBUTE GATHER 234997 6752 │ PROJECT id AS id_5 234997 6752 │ FILTER country_code = 'jp' 234997 6752 │ TABLE SCAN company_name - 64 INNER JOIN HASH ON movie_id = movie_id_13 2348216 48897 │└DISTRIBUTE HASH ON movie_id_13 2348216 48897 │ PROJECT movie_id AS movie_id_13, company_id 2348216 48897 │ FILTER (note LIKE '%(Japan)%') AND NOT (note LIKE '%(USA)%') 2348216 48897 │ TABLE SCAN movie_companies - 40 INNER JOIN HASH ON person_id_1 = person_id 901343 4031 │└DISTRIBUTE GATHER 901343 4031 │ TABLE SCAN aka_name 32619910 25 PROJECT person_id AS person_id_1, movie_id, role_id 32619910 25 FILTER note = '(voice: English version)' 32619910 25 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 1 62 INNER JOIN LOOP ON person_id = person_id AND person_id = id AND (person_id = id) 2 38 │└INNER JOIN LOOP ON id = person_id 1 3719 │ │└INNER JOIN HASH ON role_id = id 1 1 │ │ │└TABLE SCAN role_type AS rt WHERE rt.role = 'actress' 32 19600 │ │ INNER JOIN LOOP ON movie_id = id 334 48328 │ │ │└INNER JOIN LOOP ON id = movie_id 334 48328 │ │ │ │└INNER JOIN LOOP ON company_id = id 7870 6752 │ │ │ │ │└TABLE SEEK company_name AS cn 6752 48344 │ │ │ │ TABLE SEEK movie_companies AS mc WHERE (mc.note LIKE '%(Japan)%') AND (mc.note NOT LIKE '%(USA)%') 48328 48328 │ │ │ TABLE SEEK title AS t 48328 48328 │ │ TABLE SEEK cast_info AS ci WHERE ci.note = '(voice: English version)' 7438 7438 │ TABLE SEEK name AS n1 WHERE (n1.name LIKE '%Yo%') AND (n1.name NOT LIKE '%Yu%') 76 61 TABLE SEEK aka_name AS an1