Planner — IMDB — JOB-16D

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 >= 5
  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,452,098 48M Rank		Estimation Error Est Err 48,458,388 48M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 769,185 769K Rank	Estimation Error Est Err 249,455 249K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 49,138,794 49M Rank		Estimation Error Est Err 49,138,793 49M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 101,002 101K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 49,183,139 49M Rank		Estimation Error Est Err 49,183,138 49M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 689,003 689K Rank	Estimation Error Est Err 249,456 249K Rank	Estimation Error Est Err 401,200 401K Rank
Estimation Error Est Err 239,382 239K Rank		Estimation Error Est Err 786,304 786K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 769,181 769K Rank	Estimation Error Est Err 249,455 249K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 688,401 688K Rank	Estimation Error Est Err 688,401 688K Rank	Estimation Error Est Err 689,024 689K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 689,003 689K Rank	Estimation Error Est Err 249,455 249K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 531,335 531K Rank	Estimation Error Est Err 504,095 504K Rank	Estimation Error Est Err 790,005 790K Rank	Estimation Error Est Err 15,427 15K Rank	Estimation Error Est Err 753,550 754K Rank	Estimation Error Est Err 249,455 249K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,703,875 6.7M Rank		Estimation Error Est Err 23,251,265 23M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 20,690,128 21M Rank	Estimation Error Est Err 249,471 249K Rank	Estimation Error Est Err 5,956,365 6M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 1009 249455 INNER JOIN HASH ON id67 = person_id 788 249455 │└INNER JOIN HASH ON person_id57 = person_id 864 169273 │ │└INNER JOIN HASH ON movie_id48 = movie_id29 30 6766 │ │ │└INNER JOIN HASH ON company_id = id36 83 8661 │ │ │ │└INNER JOIN HASH ON movie_id29 = movie_id 9 5385 │ │ │ │ │└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 706149 5382 │ │ │ │ │ TABLE SCAN title WHERE episode_nr BETWEEN 5 AND 99 2609129 2609129 │ │ │ │ TABLE SCAN movie_companies 90648 478 │ │ │ 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_1208.id,PROJECTION_1208.id) = tuple(PROJECTION_1169.person_id,PROJECTION_1169.person_id) - 0 │└PROJECT person_id, person_id, name, title - 0 │ PROJECT name, person_id, person_id, title - 0 │ INNER JOIN HASH ON PROJECTION_1205.person_id = PROJECTION_1172.person_id - 0 │ │└PROJECT person_id AS person_id_right, title - 0 │ │ PROJECT person_id, title - 0 │ │ INNER JOIN HASH ON tuple(PROJECTION_1202.movie_id,PROJECTION_1202.movie_id,PROJECTION_1202.movie_id) = tuple(PROJECTION_1175.movie_id,PROJECTION_1175.movie_id,PROJECTION_1175.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_1199.id = PROJECTION_1178.company_id - 8661 │ │ │ │└PROJECT company_id, movie_id, movie_id, title, id AS id_right - 8661 │ │ │ │ PROJECT movie_id, company_id, movie_id, title, id - 8661 │ │ │ │ INNER JOIN HASH ON tuple(PROJECTION_1196.movie_id,PROJECTION_1196.movie_id) = tuple(PROJECTION_1181.movie_id,PROJECTION_1181.id) - 5385 │ │ │ │ │└PROJECT movie_id AS movie_id_right, id, title - 5385 │ │ │ │ │ PROJECT movie_id, title, id - 5385 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_1193.id = PROJECTION_1184.movie_id - 41840 │ │ │ │ │ │└PROJECT movie_id - 41840 │ │ │ │ │ │ PROJECT movie_id - 41840 │ │ │ │ │ │ INNER JOIN HASH ON PROJECTION_1190.keyword_id = PROJECTION_1187.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 - 692556 │ │ │ │ │ PROJECT id, title - 692556 │ │ │ │ │ PROJECT id, title - 692556 │ │ │ │ │ TABLE SCAN title WHERE (episode_nr >= 5) 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 249455 PROJECT name, title 0 249455 INNER JOIN HASH ON id = person_id 0 249455 │└INNER JOIN HASH ON person_id = person_id 1 169273 │ │└INNER JOIN HASH ON movie_id = id 0 6766 │ │ │└INNER JOIN HASH ON id = company_id 14 8661 │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 14 5385 │ │ │ │ │└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 12362 │ │ │ │ │ FILTER id BETWEEN 2 AND 2525745 505662 12366 │ │ │ │ │ TABLE SCAN title WHERE episode_nr >= 5 AND episode_nr < 100 2609129 19077 │ │ │ │ TABLE SCAN movie_companies 1644 44844 │ │ │ TABLE SCAN company_name WHERE country_code = 'us' 36244344 80685 │ │ FILTER person_id >= 4 36244344 80685 │ │ TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525745 901343 27297 │ TABLE SCAN aka_name WHERE person_id <= 4061926 4167491 13274 TABLE SCAN "name" WHERE id >= 4 AND id <= 4061926
	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 249455 INNER JOIN HASH ON ci.person_id = an.person_id 5200 169273 │└DISTRIBUTE GATHER 3280 169273 │ INNER JOIN HASH ON ci.person_id = n.id 3280 169273 │ │└DISTRIBUTE GATHER 3280 169273 │ │ INNER JOIN HASH ON mc.movie_id = ci.movie_id 3280 6766 │ │ │└DISTRIBUTE GATHER 199 6766 │ │ │ INNER JOIN HASH ON mc.company_id = cn.id 199 8661 │ │ │ │└DISTRIBUTE GATHER 192 8661 │ │ │ │ INNER JOIN HASH ON mk.movie_id = mc.movie_id 192 5385 │ │ │ │ │└DISTRIBUTE GATHER 75 5385 │ │ │ │ │ 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 690163 │ │ │ │ │ TABLE SCAN title WHERE (t.episode_nr IS NOT NULL) AND (t.episode_nr >= 5L) AND (t.episode_nr < 100L) 235000 2605039 │ │ │ │ TABLE SCAN movie_companies 2610000 83357 │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 2530000 36228035 │ │ TABLE SCAN cast_info 134000 4159335 │ TABLE SCAN name 4520000 897348 TABLE SCAN aka_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1016, MIN(title as title) AS Expr1017 12998 249455 INNER JOIN HASH ON an.person_id = ci.person_id 2678 169273 │└INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 169273 │ │└TABLE SEEK cast_info AS ci 2678 169273 │ PROJECT BmkToPage Bmk1002 AS Expr1152 2678 169273 │ INNER JOIN LOOP ON t.id = ci.movie_id 30 169273 │ │└TABLE SEEK cast_info AS ci 88 6766 │ SORT movie_id 88 6766 │ FILTER country_code as country_code = 'us' 246 8661 │ INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 8661 │ │└TABLE SEEK company_name AS cn 246 8661 │ INNER JOIN LOOP ON mc.company_id = cn.id 1 8661 │ │└TABLE SEEK company_name AS cn 246 8661 │ INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 8661 │ │└TABLE SEEK movie_companies AS mc 246 8661 │ SORT Expr1033 246 8661 │ PROJECT BmkToPage Bmk1008 AS Expr1033 246 8661 │ INNER JOIN LOOP ON t.id = mc.movie_id 9 8661 │ │└TABLE SEEK movie_companies AS mc 24 5385 │ INNER JOIN LOOP ON Bmk1014 = Bmk1014 0 5385 │ │└TABLE SEEK title AS t WHERE episode_nr as episode_nr >= 5 AND episode_nr as episode_nr < 100 90 41840 │ PROJECT BmkToPage Bmk1014 AS Expr1146 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' 901343 27239 TABLE SCAN aka_name AS an WHERE BLOOM(person_id as person_id)
	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 - 249455 INNER JOIN HASH ON movie_id = id_38 1141 692556 │└DISTRIBUTE HASH ON id_38 1141 692556 │ PROJECT id AS id_38, title 1141 692556 │ FILTER (episode_nr >= 5) AND (episode_nr < 100) 1141 692556 │ TABLE SCAN title - 3710592 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 - 3710592 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 - 3710592 INNER JOIN HASH ON movie_id = movie_id_23 4523930 41840 │└DISTRIBUTE HASH ON movie_id_23 4523930 41840 │ PROJECT movie_id AS movie_id_23, keyword_id 4523930 41840 │ TABLE SCAN movie_keyword - 3709435 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 - 3709435 INNER JOIN HASH ON movie_id = movie_id_17 2609129 68275 │└DISTRIBUTE HASH ON movie_id_17 2609129 68275 │ PROJECT movie_id AS movie_id_17, company_id 2609129 68275 │ TABLE SCAN movie_companies - 990027 INNER JOIN HASH ON person_id_1 = person_id 901343 901343 │└DISTRIBUTE GATHER 901343 901343 │ TABLE SCAN aka_name 36244344 747526 PROJECT person_id AS person_id_1, movie_id 36244344 747526 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 920 249455 INNER JOIN LOOP ON person_id = id 379 169273 │└INNER JOIN LOOP ON id = person_id 379 169273 │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 17 6766 │ │ │└INNER JOIN LOOP ON id = company_id 47 8661 │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 9 5385 │ │ │ │ │└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 >= 5) AND (t.episode_nr < 100) 26925 8669 │ │ │ │ TABLE SEEK movie_companies AS mc 8661 8661 │ │ │ TABLE SEEK company_name AS cn WHERE cn.country_code = 'us' 263874 169285 │ │ TABLE SEEK cast_info AS ci 169273 169273 │ TABLE SEEK name AS n 338546 248831 TABLE SEEK aka_name AS an