Planner — IMDB — JOB-8B

SELECT MIN(an.name) AS acress_pseudonym,
       MIN(t.title) AS japanese_anime_movie
FROM job.aka_name AS an,
     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 ='(voice: English version)'
  AND cn.country_code ='[jp]'
  AND mc.note LIKE '%(Japan)%'
  AND mc.note NOT LIKE '%(USA)%'
  AND (mc.note LIKE '%(2006)%'
       OR mc.note LIKE '%(2007)%')
  AND n.name LIKE '%Yo%'
  AND n.name NOT LIKE '%Yu%'
  AND rt.role ='actress'
  AND t.production_year BETWEEN 2006 AND 2007
  AND (t.title LIKE 'One Piece%'
       OR t.title LIKE 'Dragon Ball Z%')
  AND an.person_id = n.id
  AND n.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 an.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 994,451 994K Rank		Estimation Error Est Err 994,509 995K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 69,769 70K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,023,926 1M Rank		Estimation Error Est Err 1,023,974 1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 69,781 70K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 254,896 255K Rank		Estimation Error Est Err 248,144 248K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 76,855 77K Rank	Estimation Error Est Err 7 7 Rank	Estimation Error Est Err 42,007 42K Rank
Estimation Error Est Err 14,791,095 15M Rank		Estimation Error Est Err 158,725 159K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 919,468 919K Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 1,030,299 1M Rank
Estimation Error Est Err 35,175 35K Rank		Estimation Error Est Err 37,036 37K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 71,880 72K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 24,206 24K Rank	Estimation Error Est Err 24,206 24K Rank	Estimation Error Est Err 17,454 17K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 17,442 17K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 742 742 Rank	Estimation Error Est Err 734 734 Rank	Estimation Error Est Err 734 734 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 734 734 Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 39,492 39K Rank		Estimation Error Est Err 36 36 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 39,493 39K Rank	Estimation Error Est Err 22 22 Rank	Estimation Error Est Err 39,504 40K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 1 6 INNER JOIN HASH ON person_id62 = person_id 1 4 │└INNER JOIN HASH ON company_id = id51 1 4 │ │└INNER JOIN HASH ON movie_id = movie_id44 1 7 │ │ │└INNER JOIN HASH ON id31 = person_id 1 56 │ │ │ │└INNER JOIN HASH ON movie_id = id16 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) 950 5 │ │ │ │ TABLE SCAN title WHERE production_year BETWEEN 2006 AND 2007 8139 2 │ │ │ TABLE SCAN name WHERE name LIKE '%Yo%' AND NOT (name LIKE '%Yu%') 2547 3 │ │ TABLE SCAN movie_companies WHERE note47 LIKE '%(Japan)%' AND note47 LIKE '%(2006)%' OR note47 LIKE '%(2007)%' AND NOT (note47 LIKE '%(USA)%') 6655 2 │ TABLE SCAN company_name WHERE country_code = jp 901343 901343 TABLE SCAN aka_name
	Estimate Actual Operator - 1 PROJECT a1 AS acress_pseudonym, a2 AS japanese_anime_movie - 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_5530.person_id,PROJECTION_5530.person_id) = tuple(PROJECTION_5497.person_id,PROJECTION_5497.id) - 0 │└PROJECT person_id AS person_id_right, id, title - 0 │ PROJECT person_id, id, title - 0 │ INNER JOIN HASH ON PROJECTION_5527.id = PROJECTION_5500.company_id - 4 │ │└PROJECT company_id, person_id, id AS id_right, title - 4 │ │ PROJECT person_id, company_id, id, title - 4 │ │ INNER JOIN HASH ON PROJECTION_5524.id = PROJECTION_5503.person_id - 25 │ │ │└PROJECT person_id, company_id, title - 25 │ │ │ PROJECT person_id, company_id, title - 25 │ │ │ INNER JOIN HASH ON tuple(PROJECTION_5521.movie_id,PROJECTION_5521.movie_id) = tuple(PROJECTION_5506.movie_id,PROJECTION_5506.id) - 56 │ │ │ │└PROJECT movie_id AS movie_id_right, id, person_id, title - 56 │ │ │ │ PROJECT person_id, movie_id, title, id - 56 │ │ │ │ INNER JOIN HASH ON PROJECTION_5518.id = PROJECTION_5509.movie_id - 34848 │ │ │ │ │└PROJECT movie_id, person_id - 34848 │ │ │ │ │ PROJECT person_id, movie_id - 34848 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_5515.role_id = PROJECTION_5512.id - 1 │ │ │ │ │ │└PROJECT id - 1 │ │ │ │ │ │ PROJECT id - 1 │ │ │ │ │ │ TABLE SCAN role_type WHERE role = 'actress' - 93095 │ │ │ │ │ PROJECT role_id, person_id, movie_id - 93095 │ │ │ │ │ PROJECT person_id, movie_id, role_id - 93095 │ │ │ │ │ TABLE SCAN cast_info WHERE note = '(voice: English version)' - 7 │ │ │ │ PROJECT id, title - 7 │ │ │ │ PROJECT id, title - 7 │ │ │ │ TABLE SCAN title WHERE (production_year >= 2006) AND (production_year <= 2007) AND (startsWith(title,'One Piece') OR startsWith(title,'Dragon Ball Z')) - 8569 │ │ │ PROJECT movie_id AS movie_id_left, company_id - 8569 │ │ │ PROJECT movie_id, company_id - 8569 │ │ │ TABLE SCAN movie_companies WHERE note LIKE '%(Japan)%' AND notLike(note,'%(USA)%') AND (note LIKE '%(2006)%' OR note LIKE '%(2007)%') - 20911 │ │ PROJECT id - 20911 │ │ PROJECT id - 20911 │ │ TABLE SCAN name WHERE name LIKE '%Yo%' AND notLike(name,'%Yu%') - 0 │ PROJECT id AS id_left - 0 │ PROJECT id - 0 │ TABLE SCAN company_name WHERE country_code = 'jp' - 901343 PROJECT person_id AS person_id_left, name - 901343 PROJECT name, person_id - 901343 TABLE SCAN aka_name
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 0 6 PROJECT name, title 0 6 INNER JOIN HASH ON person_id = id 0 4 │└INNER JOIN HASH ON id = id 0 25 │ │└INNER JOIN HASH ON id = movie_id 0 1074 │ │ │└INNER JOIN HASH ON id = company_id 5 1079 │ │ │ │└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 259 │ │ │ │ TABLE SCAN movie_companies WHERE contains(note,'(Japan)') AND ( NOT contains(note,'(USA)')) AND (contains(note,'(2006)') OR contains(note,'(2007)')) 1644 58 │ │ │ TABLE SCAN company_name WHERE country_code = 'jp' 505662 3 │ │ FILTER id BETWEEN 2 AND 2525745 505662 3 │ │ TABLE SCAN title WHERE production_year >= 2006 AND production_year <= 2007 AND (prefix(title,'One Piece') OR prefix(title,'Dragon Ball Z')) 833498 3 │ FILTER id BETWEEN 4 AND 4061926 833498 3 │ TABLE SCAN "name" WHERE contains(name,'Yo') AND ( NOT contains(name,'Yu')) 901343 3 TABLE SCAN aka_name WHERE person_id <= 4061926
	Estimate Actual Operator 1 1 PROJECT acress_pseudonym, japanese_anime_movie 1 1 AGGREGATE MIN(name), MIN(title) 28463 10 DISTRIBUTE GATHER 28463 10 AGGREGATE MIN(name), MIN(title) 28463 6 INNER JOIN HASH ON movie_id = id AND movie_id = id 28463 25 │└DISTRIBUTE GATHER 28463 25 │ 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 25 │ INNER JOIN HASH ON person_id = id AND person_id = id 104366 2754 │ │└DISTRIBUTE GATHER 104366 2754 │ │ 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 2760 │ │ PROJECT person_id, name, person_id, movie_id, role_id, movie_id, company_id 521826 2760 │ │ INNER JOIN HASH ON movie_id = movie_id 521826 8569 │ │ │└DISTRIBUTE HASH ON movie_id 521826 8569 │ │ │ FILTER (note LIKE '%(Japan)%' AND note NOT LIKE '%(USA)%') AND (note LIKE '%(2006)%' OR note LIKE '%(2007)%') 2609129 2609129 │ │ │ TABLE SCAN movie_companies WHERE ((note LIKE '%(Japan)%' AND note NOT LIKE '%(USA)%') AND (note LIKE '%(2006)%' OR note LIKE '%(2007)%')) 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 4277 │ FILTER name LIKE '%Yo%' AND name NOT LIKE '%Yu%' 4167491 983636 │ TABLE SCAN name WHERE (name LIKE '%Yo%' AND name NOT LIKE '%Yu%') AND ((((id >= 1733848) AND (id <= 2689872)) AND ((id >= 1733848) AND (id <= 2689872))) AND TRUE) 505663 7 FILTER ((production_year >= 2006) AND (production_year <= 2007)) AND (title LIKE 'One Piece%' OR title LIKE 'Dragon Ball Z%') 2528312 2528312 TABLE SCAN title WHERE (((production_year >= 2006) AND (production_year <= 2007)) AND (title LIKE 'One Piece%' OR title LIKE 'Dragon Ball Z%')) AND ((((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...
	Estimate Actual Operator 1 1 AGGREGATE MIN(an.name), MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(an.name), MIN(t.title) 218 6 INNER JOIN HASH ON ci.person_id = an.person_id 218 4 │└DISTRIBUTE GATHER 137 4 │ INNER JOIN HASH ON ci.movie_id = mc.movie_id 137 394 │ │└DISTRIBUTE GATHER 137 394 │ │ INNER JOIN HASH ON ci.person_id = n.id 137 34848 │ │ │└DISTRIBUTE GATHER 44300 34848 │ │ │ INNER JOIN HASH ON ci.role_id = rt.id 44300 1 │ │ │ │└DISTRIBUTE GATHER 901000 1 │ │ │ │ TABLE SCAN role_type WHERE rt.role = 'actress' 4170000 34848 │ │ │ TABLE SCAN cast_info WHERE (ci.note IS NOT NULL) AND (ci.note = '(voice: English version)') 2530000 9844 │ │ TABLE SCAN name WHERE contains(n.name,'Yo') AND ( NOT contains(n.name,'Yu')) 137 4 │ INNER JOIN HASH ON mc.company_id = cn.id 137 6752 │ │└DISTRIBUTE GATHER 235000 6752 │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'jp') 12100 4 │ INNER JOIN HASH ON mc.movie_id = t.id 12100 7 │ │└DISTRIBUTE GATHER 12 7 │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year >= 2006L) AND (t.production_year <= 2007L) AND (startswith(t.title,'One Piece') OR startswith(t.title,'Dragon Ball Z')) 36200000 2514 │ TABLE SCAN movie_companies WHERE (mc.note IS NOT NULL) AND contains(mc.note,'(Japan)') AND ( NOT contains(mc.note,'(USA)')) AND (contains(mc.note,'(2006)') OR contains(mc.note,'(2007)')) 2610000 200930 TABLE SCAN aka_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1014, MIN(title as title) AS Expr1015 1 6 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 6 │└TABLE SEEK aka_name AS an 1 6 INNER JOIN LOOP ON n.id = an.person_id 1 6 │└TABLE SEEK aka_name AS an 1 4 FILTER name as name LIKE '%Yo%' AND NOT name as name LIKE '%Yu%' 1 25 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 25 │└TABLE SEEK name AS n 1 25 PROJECT BmkToPage Bmk1008 AS Expr1063 1 25 INNER JOIN LOOP ON ci.person_id = n.id 1 25 │└TABLE SEEK name AS n 1 25 FILTER country_code as country_code = 'jp' 1 25 INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 25 │└TABLE SEEK company_name AS cn 1 25 INNER JOIN LOOP ON mc.company_id = cn.id 1 25 │└TABLE SEEK company_name AS cn 1 25 FILTER role as role = 'actress' 1 104 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 104 │└TABLE SEEK role_type AS rt 1 104 INNER JOIN LOOP ON ci.role_id = rt.id 1 104 │└TABLE SEEK role_type AS rt 1 104 FILTER note as note = '(voice: English version)' 32 188 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 188 │└TABLE SEEK cast_info AS ci 32 188 PROJECT BmkToPage Bmk1002 AS Expr1061 32 188 INNER JOIN LOOP ON t.id = ci.movie_id 30 188 │└TABLE SEEK cast_info AS ci 1 5 FILTER note as note LIKE '%(Japan)%' AND NOT note as note LIKE '%(USA)%' AND (note as note LIKE '%(2006)%' OR note as note LIKE '%(2007)%') 267 19 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 19 │└TABLE SEEK movie_companies AS mc 267 19 PROJECT BmkToPage Bmk1006 AS Expr1059 267 19 INNER JOIN LOOP ON t.id = mc.movie_id 2 19 │└TABLE SEEK movie_companies AS mc 26 8 TABLE SCAN title AS t WHERE (production_year as production_year >= 2006 AND production_year as production_year <= 2007) AND (title as title LIKE 'One Piece%' OR title as title LIKE 'Dragon Ball Z%')
	Estimate Actual Operator 1 ∞ PROJECT min AS acress_pseudonym, min_40 AS japanese_anime_movie 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 - 6 INNER JOIN HASH ON movie_id = id_33 15904 7 │└DISTRIBUTE HASH ON id_33 15904 7 │ PROJECT id AS id_33, title 15904 7 │ FILTER (production_year BETWEEN 2006 AND 2007) AND (((title >= 'Dragon Ball Z') AND (title < 'Dragon Ball ')) OR ((title >= 'One Piece') AND (title < 'One Piecf'))) AND ((title LIKE 'One Piece%') OR (title LIKE 'Dragon Ball Z%')) 15904 7 │ TABLE SCAN title - 6 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 - 6 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 - 6 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 - 6 INNER JOIN HASH ON movie_id = movie_id_13 2348216 7786 │└DISTRIBUTE HASH ON movie_id_13 2348216 7786 │ PROJECT movie_id AS movie_id_13, company_id 2348216 7786 │ FILTER (note LIKE '%(Japan)%') AND NOT (note LIKE '%(USA)%') AND ((note LIKE '%(2006)%') OR (note LIKE '%(2007)%')) 2348216 7786 │ TABLE SCAN movie_companies - 6 INNER JOIN HASH ON person_id_1 = person_id 901343 4031 │└DISTRIBUTE GATHER 901343 4031 │ TABLE SCAN aka_name 32619910 4 PROJECT person_id AS person_id_1, movie_id, role_id 32619910 4 FILTER note = '(voice: English version)' 32619910 4 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 1 6 INNER JOIN LOOP ON id = role_id 1 6 │└INNER JOIN LOOP ON id = person_id 1 171 │ │└INNER JOIN LOOP ON person_id = person_id 1 104 │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 2 4 │ │ │ │└INNER JOIN LOOP ON id = movie_id 14 8495 │ │ │ │ │└INNER JOIN LOOP ON company_id = id 7870 6752 │ │ │ │ │ │└TABLE SEEK company_name AS cn 6752 8507 │ │ │ │ │ TABLE SEEK movie_companies AS mc WHERE (mc.note LIKE '%(Japan)%') AND (mc.note NOT LIKE '%(USA)%') AND ((mc.note LIKE '%(2006)%') OR (mc.note LIKE '%(2007)%')) 8495 8495 │ │ │ │ TABLE SEEK title AS t WHERE (t.production_year >= 2006) AND (t.production_year <= 2007) AND ((t.title LIKE 'One Piece%') OR (t.title LIKE 'Dragon Ball Z%')) 4 104 │ │ │ TABLE SEEK cast_info AS ci WHERE ci.note = '(voice: English version)' 208 170 │ │ TABLE SEEK aka_name AS an 171 171 │ TABLE SEEK name AS n WHERE (n.name LIKE '%Yo%') AND (n.name NOT LIKE '%Yu%') 6 6 TABLE SEEK role_type AS rt WHERE rt.role = 'actress'