Planner — IMDB — JOB-16A

SELECT MIN(an.name) AS cool_actor_pseudonym,
       MIN(t.title) AS series_named_after_char
FROM job.aka_name AS an,
     job.cast_info AS ci,
     job.company_name AS cn,
     job.keyword AS k,
     job.movie_companies AS mc,
     job.movie_keyword AS mk,
     job.name AS n,
     job.title AS t
WHERE cn.country_code ='[us]'
  AND k.keyword ='character-name-in-title'
  AND t.episode_nr >= 50
  AND t.episode_nr < 100
  AND an.person_id = n.id
  AND n.id = ci.person_id
  AND ci.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND t.id = mc.movie_id
  AND mc.company_id = cn.id
  AND an.person_id = ci.person_id
  AND ci.movie_id = mc.movie_id
  AND ci.movie_id = mk.movie_id
  AND mc.movie_id = mk.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 48,446,396 48M Rank		Estimation Error Est Err 48,446,408 48M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 84,973 85K Rank	Estimation Error Est Err 385 385 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 48,514,483 49M Rank		Estimation Error Est Err 48,514,482 49M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 83,880 84K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 47,730,788 48M Rank		Estimation Error Est Err 47,645,997 48M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 169,700 170K Rank	Estimation Error Est Err 386 386 Rank	Estimation Error Est Err 127,502 128K Rank
Estimation Error Est Err 50,605,698 51M Rank		Estimation Error Est Err 2,367,362,972 2.4G Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,246,748,216 1.2G Rank	Estimation Error Est Err 395 395 Rank	Estimation Error Est Err 130,169,187 130M Rank
Estimation Error Est Err 72,622 73K Rank		Estimation Error Est Err 72,643 73K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 84,969 85K Rank	Estimation Error Est Err 385 385 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 84,911 85K Rank	Estimation Error Est Err 84,911 85K Rank	Estimation Error Est Err 84,911 85K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 84,911 85K Rank	Estimation Error Est Err 385 385 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 133,139 133K Rank	Estimation Error Est Err 133,138 133K Rank	Estimation Error Est Err 174,916 175K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 133,138 133K Rank	Estimation Error Est Err 385 385 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,222,356 5.2M Rank		Estimation Error Est Err 2,618 2.6K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,223,077 5.2M Rank	Estimation Error Est Err 401 401 Rank	Estimation Error Est Err 5,222,110 5.2M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 248 385 INNER JOIN HASH ON id67 = person_id 194 385 │└INNER JOIN HASH ON person_id57 = person_id 213 323 │ │└INNER JOIN HASH ON movie_id48 = movie_id29 7 25 │ │ │└INNER JOIN HASH ON company_id = id36 9 54 │ │ │ │└INNER JOIN HASH ON movie_id29 = movie_id 1 146 │ │ │ │ │└INNER JOIN HASH ON id13 = movie_id 32 41840 │ │ │ │ │ │└INNER JOIN HASH ON keyword_id = id 1 1 │ │ │ │ │ │ │└TABLE SCAN keyword WHERE keyword = character - name - in - title 4523930 4523930 │ │ │ │ │ │ TABLE SCAN movie_keyword 56788 146 │ │ │ │ │ TABLE SCAN title WHERE episode_nr BETWEEN 50 AND 99 2609129 2609129 │ │ │ │ TABLE SCAN movie_companies 90648 12 │ │ │ TABLE SCAN company_name WHERE country_code = us 36244344 36244344 │ │ TABLE SCAN cast_info 901343 901343 │ TABLE SCAN aka_name 4167491 4167491 TABLE SCAN name
	Estimate Actual Operator - 1 PROJECT a1 AS cool_actor_pseudonym, a2 AS series_named_after_char - 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_1067.id,PROJECTION_1067.id) = tuple(PROJECTION_1028.person_id,PROJECTION_1028.person_id) - 0 │└PROJECT person_id, person_id, name, title - 0 │ PROJECT name, person_id, person_id, title - 0 │ INNER JOIN HASH ON PROJECTION_1064.person_id = PROJECTION_1031.person_id - 0 │ │└PROJECT person_id AS person_id_right, title - 0 │ │ PROJECT person_id, title - 0 │ │ INNER JOIN HASH ON tuple(PROJECTION_1061.movie_id,PROJECTION_1061.movie_id,PROJECTION_1061.movie_id) = tuple(PROJECTION_1034.movie_id,PROJECTION_1034.movie_id,PROJECTION_1034.id) - 0 │ │ │└PROJECT movie_id_right, movie_id AS movie_id_right_2, id, title - 0 │ │ │ PROJECT movie_id, movie_id, title, id - 0 │ │ │ INNER JOIN HASH ON PROJECTION_1058.id = PROJECTION_1037.company_id - 54 │ │ │ │└PROJECT company_id, movie_id, movie_id, title, id AS id_right - 54 │ │ │ │ PROJECT movie_id, company_id, movie_id, title, id - 54 │ │ │ │ INNER JOIN HASH ON tuple(PROJECTION_1055.movie_id,PROJECTION_1055.movie_id) = tuple(PROJECTION_1040.movie_id,PROJECTION_1040.id) - 146 │ │ │ │ │└PROJECT movie_id AS movie_id_right, id, title - 146 │ │ │ │ │ PROJECT movie_id, title, id - 146 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_1052.id = PROJECTION_1043.movie_id - 41840 │ │ │ │ │ │└PROJECT movie_id - 41840 │ │ │ │ │ │ PROJECT movie_id - 41840 │ │ │ │ │ │ INNER JOIN HASH ON PROJECTION_1049.keyword_id = PROJECTION_1046.id - 1 │ │ │ │ │ │ │└PROJECT id - 1 │ │ │ │ │ │ │ PROJECT id - 1 │ │ │ │ │ │ │ TABLE SCAN keyword WHERE keyword = 'character-name-in-title' - 4523930 │ │ │ │ │ │ PROJECT keyword_id, movie_id - 4523930 │ │ │ │ │ │ PROJECT movie_id, keyword_id - 4523930 │ │ │ │ │ │ TABLE SCAN movie_keyword - 68245 │ │ │ │ │ PROJECT id, title - 68245 │ │ │ │ │ PROJECT id, title - 68245 │ │ │ │ │ TABLE SCAN title WHERE (episode_nr >= 50) AND (episode_nr < 100) - 2609129 │ │ │ │ PROJECT movie_id AS movie_id_left, company_id - 2609129 │ │ │ │ PROJECT movie_id, company_id - 2609129 │ │ │ │ TABLE SCAN movie_companies - 0 │ │ │ PROJECT id AS id_left - 0 │ │ │ PROJECT id - 0 │ │ │ TABLE SCAN company_name WHERE country_code = 'us' - 36244344 │ │ PROJECT movie_id AS movie_id_left, person_id - 36244344 │ │ PROJECT person_id, movie_id - 36244344 │ │ TABLE SCAN cast_info - 901343 │ PROJECT person_id AS person_id_left, name - 901343 │ PROJECT name, person_id - 901343 │ TABLE SCAN aka_name - 4167491 PROJECT id - 4167491 PROJECT id - 4167491 TABLE SCAN name
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 0 385 PROJECT name, title 0 385 INNER JOIN HASH ON id = person_id 0 385 │└INNER JOIN HASH ON person_id = person_id 1 323 │ │└INNER JOIN HASH ON movie_id = id 0 25 │ │ │└INNER JOIN HASH ON id = company_id 14 54 │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 14 146 │ │ │ │ │└INNER JOIN HASH ON id = movie_id 69 41838 │ │ │ │ │ │└INNER JOIN HASH ON keyword_id = id 2 1 │ │ │ │ │ │ │└TABLE SCAN keyword WHERE keyword = 'character-name-in-title' 4523930 41838 │ │ │ │ │ │ TABLE SCAN movie_keyword WHERE movie_id <= 2525745 505662 865 │ │ │ │ │ FILTER id BETWEEN 2 AND 2525745 505662 865 │ │ │ │ │ TABLE SCAN title WHERE episode_nr >= 50 AND episode_nr < 100 2609129 7422 │ │ │ │ TABLE SCAN movie_companies 1644 40 │ │ │ TABLE SCAN company_name WHERE country_code = 'us' 36244344 20452 │ │ FILTER person_id >= 4 36244344 20452 │ │ TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525745 901343 1641 │ TABLE SCAN aka_name WHERE person_id <= 4061926 4167491 363 TABLE SCAN "name" WHERE id >= 4 AND id <= 4061926
	Estimate Actual Operator 1 1 PROJECT cool_actor_pseudonym, series_named_after_char 1 1 AGGREGATE MIN(name), MIN(title) 1377 10 DISTRIBUTE GATHER 1377 10 AGGREGATE MIN(name), MIN(title) 1377 385 PROJECT name, title 1377 385 INNER JOIN HASH ON id = movie_id AND id = movie_id AND id = movie_id 1377 68245 │└DISTRIBUTE GATHER 1377 68245 │ FILTER (episode_nr >= 50) AND (episode_nr < 100) 2528312 1790294 │ TABLE SCAN title WHERE (episode_nr >= 50) AND (episode_nr < 100) 595138 3710592 INNER JOIN HASH ON person_id = id AND person_id = id 595138 3710592 │└DISTRIBUTE HASH ON person_id, person_id 595138 3710592 │ INNER JOIN HASH ON id = keyword_id 26834 1 │ │└DISTRIBUTE GATHER 26834 1 │ │ FILTER keyword = 'character-name-in-title' 134170 134170 │ │ DISTRIBUTE ROUND ROBIN 134170 134170 │ │ TABLE SCAN keyword WHERE keyword = 'character-name-in-title' 2975692 1074M │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 1661500 41307596 │ │└DISTRIBUTE HASH ON movie_id, movie_id 1661500 41307596 │ │ 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' 8307394 87239860 │ │ PROJECT person_id, name, person_id, movie_id, movie_id, company_id 8307394 87239860 │ │ INNER JOIN HASH ON movie_id = movie_id 2609129 2609129 │ │ │└DISTRIBUTE HASH ON movie_id 2609129 2609129 │ │ │ TABLE SCAN movie_companies WHERE (((company_id >= 1) AND (company_id <= 234997)) AND TRUE) AND (((((movie_id >= 1634) AND (movie_id <= 2527959)) AND ((movie_id >= 1634) AND (movie_id <= 2527959))) AND ((movie_id >= 1634) AND (movie_id <= 2527959))) AND TRUE) 8042634 36417493 │ │ DISTRIBUTE HASH ON movie_id 8042634 36417493 │ │ INNER JOIN HASH ON person_id = person_id 901343 901343 │ │ │└DISTRIBUTE HASH ON person_id 901343 901343 │ │ │ TABLE SCAN aka_name 36244344 36244344 │ │ DISTRIBUTE HASH ON person_id 36244344 36244344 │ │ TABLE SCAN cast_info WHERE 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 THEN (((person_id >= 113) AND (person_id <= 4... 4523930 4523930 │ DISTRIBUTE HASH ON movie_id, movie_id 4523930 4523930 │ TABLE SCAN movie_keyword WHERE CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 57) AND (movie_id <= 2525741)) AND ((movie_id >= 57) AND (movie_id <= 2525741))) AND TRUE) WHEN 3 THEN ((((movie_id >= 45) AND (movie_id <= 2525737)) AND ((movie_id >= 45) AND (movie_id <= 2525737))) AND TRUE) WHEN 5 THEN ((((movie_id >= 46) AND (movie_id <= 2525744)) AND ((movie_id >= 46) AND (movie_id <= 2525744))) AND TRUE) WHEN 7 THEN ((((movie_id >= 54) AND ... 4167491 4167491 DISTRIBUTE HASH ON id, id 4167491 4167491 TABLE SCAN name WHERE CASE MOD(HASH_REPARTITION(id,id),10) WHEN 1 THEN ((((id >= 4) AND (id <= 4061913)) AND ((id >= 4) AND (id <= 4061913))) AND TRUE) WHEN 3 THEN ((((id >= 621) AND (id <= 4061220)) AND ((id >= 621) AND (id <= 4061220))) AND TRUE) WHEN 5 THEN ((((id >= 56) AND (id <= 4061868)) AND ((id >= 56) AND (id <= 4061868))) AND TRUE) WHEN 7 THEN ((((id >= 233) AND (id <= 4061684)) AND ((id >= 233) AND (id <= 4061684))) AND TRUE) WHEN 9 THEN ((((id >= 836) AND (id <...
	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) 5200 385 INNER JOIN HASH ON ci.person_id = an.person_id 5200 323 │└DISTRIBUTE GATHER 3280 323 │ INNER JOIN HASH ON ci.person_id = n.id 3280 323 │ │└DISTRIBUTE GATHER 3280 323 │ │ INNER JOIN HASH ON mc.movie_id = ci.movie_id 3280 25 │ │ │└DISTRIBUTE GATHER 199 25 │ │ │ INNER JOIN HASH ON mc.company_id = cn.id 199 84843 │ │ │ │└DISTRIBUTE GATHER 235000 84843 │ │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 192 53 │ │ │ INNER JOIN HASH ON mk.movie_id = mc.movie_id 192 146 │ │ │ │└DISTRIBUTE GATHER 75 146 │ │ │ │ INNER JOIN HASH ON mk.movie_id = t.id 75 41840 │ │ │ │ │└DISTRIBUTE GATHER 71 41840 │ │ │ │ │ INNER JOIN HASH ON mk.keyword_id = k.id 71 1 │ │ │ │ │ │└DISTRIBUTE GATHER 901000 1 │ │ │ │ │ │ TABLE SCAN keyword WHERE k.keyword = 'character-name-in-title' 4170000 4519861 │ │ │ │ │ TABLE SCAN movie_keyword 36200000 68021 │ │ │ │ TABLE SCAN title WHERE (t.episode_nr IS NOT NULL) AND (t.episode_nr >= 50L) AND (t.episode_nr < 100L) 2610000 2584554 │ │ │ TABLE SCAN movie_companies 2530000 35421055 │ │ TABLE SCAN cast_info 134000 4155205 │ TABLE SCAN name 4520000 897248 TABLE SCAN aka_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1016, MIN(title as title) AS Expr1017 1268 385 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 385 │└TABLE SEEK aka_name AS an 1268 385 INNER JOIN LOOP ON ci.person_id = an.person_id 4 385 │└TABLE SEEK aka_name AS an 261 323 FILTER country_code as country_code = 'us' 726 1089 INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 1089 │└TABLE SEEK company_name AS cn 726 1089 INNER JOIN LOOP ON mc.company_id = cn.id 1 1089 │└TABLE SEEK company_name AS cn 727 1089 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 1089 │└TABLE SEEK movie_companies AS mc 727 1089 PROJECT BmkToPage Bmk1008 AS Expr1046 727 1089 INNER JOIN LOOP ON t.id = mc.movie_id 9 1089 │└TABLE SEEK movie_companies AS mc 73 1173 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 1173 │└TABLE SEEK cast_info AS ci 73 1173 PROJECT BmkToPage Bmk1002 AS Expr1043 73 1173 INNER JOIN LOOP ON t.id = ci.movie_id 30 1173 │└TABLE SEEK cast_info AS ci 2 146 INNER JOIN LOOP ON Bmk1014 = Bmk1014 0 146 │└TABLE SEEK title AS t WHERE episode_nr as episode_nr >= 50 AND episode_nr as episode_nr < 100 90 41840 PROJECT BmkToPage Bmk1014 AS Expr1041 90 41840 INNER JOIN LOOP ON mk.movie_id = t.id 1 41840 │└TABLE SEEK title AS t 90 41840 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 41840 │└TABLE SEEK movie_keyword AS mk 90 41840 INNER JOIN LOOP ON k.id = mk.keyword_id 90 41840 │└TABLE SEEK movie_keyword AS mk 1 1 TABLE SCAN keyword AS k WHERE keyword as keyword = 'character-name-in-title'
	Estimate Actual Operator 1 ∞ PROJECT min AS cool_actor_pseudonym, min_46 AS series_named_after_char 1 1 AGGREGATE MIN(min_47) AS min, MIN(min_48) AS min_46 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name) AS min_47, MIN(title) AS min_48 - 385 INNER JOIN HASH ON movie_id = id_38 600 68245 │└DISTRIBUTE HASH ON id_38 600 68245 │ PROJECT id AS id_38, title 600 68245 │ FILTER (episode_nr >= 50) AND (episode_nr < 100) 600 68245 │ TABLE SCAN title - 385 INNER JOIN HASH ON person_id_1 = id_27 4167491 4167491 │└DISTRIBUTE HASH ON id_27 4167491 4167491 │ PROJECT id AS id_27 4167491 4167491 │ TABLE SCAN name - 385 INNER JOIN HASH ON keyword_id = id_12 134170 1 │└DISTRIBUTE GATHER 134170 1 │ PROJECT id AS id_12 134170 1 │ FILTER keyword = 'character-name-in-title' 134170 1 │ TABLE SCAN keyword - 385 INNER JOIN HASH ON movie_id = movie_id_23 4523930 146 │└DISTRIBUTE HASH ON movie_id_23 4523930 146 │ PROJECT movie_id AS movie_id_23, keyword_id 4523930 146 │ TABLE SCAN movie_keyword - 385 INNER JOIN HASH ON company_id = id_5 234997 84843 │└DISTRIBUTE GATHER 234997 84843 │ PROJECT id AS id_5 234997 84843 │ FILTER country_code = 'us' 234997 84843 │ TABLE SCAN company_name - 385 INNER JOIN HASH ON movie_id = movie_id_17 2609129 25 │└DISTRIBUTE HASH ON movie_id_17 2609129 25 │ PROJECT movie_id AS movie_id_17, company_id 2609129 25 │ TABLE SCAN movie_companies - 308 INNER JOIN HASH ON person_id_1 = person_id 901343 901343 │└DISTRIBUTE GATHER 901343 901343 │ TABLE SCAN aka_name 36244344 262 PROJECT person_id AS person_id_1, movie_id 36244344 262 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 92 385 INNER JOIN LOOP ON person_id = id 38 323 │└INNER JOIN LOOP ON id = person_id 38 323 │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 2 25 │ │ │└INNER JOIN LOOP ON id = company_id 5 54 │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 146 │ │ │ │ │└INNER JOIN LOOP ON id = movie_id 34 41840 │ │ │ │ │ │└INNER JOIN LOOP ON keyword_id = id 1 1 │ │ │ │ │ │ │└TABLE SEEK keyword AS k 305 41840 │ │ │ │ │ │ TABLE SEEK movie_keyword AS mk 41840 41840 │ │ │ │ │ TABLE SEEK title AS t WHERE (t.episode_nr >= 50) AND (t.episode_nr < 100) 730 146 │ │ │ │ TABLE SEEK movie_companies AS mc 54 54 │ │ │ TABLE SEEK company_name AS cn WHERE cn.country_code = 'us' 1000 323 │ │ TABLE SEEK cast_info AS ci 323 323 │ TABLE SEEK name AS n 646 384 TABLE SEEK aka_name AS an