Planner — IMDB — JOB-16B

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 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 50,983,986 51M Rank		Estimation Error Est Err 55,034,394 55M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 14,303,275 14M Rank	Estimation Error Est Err 3,710,592 3.7M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 50,974,550 51M Rank		Estimation Error Est Err 50,974,549 51M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 380,784 381K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 51,015,151 51M Rank		Estimation Error Est Err 53,827,591 54M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 9,756,486 9.8M Rank	Estimation Error Est Err 3,710,593 3.7M Rank	Estimation Error Est Err 5,965,660 6M Rank
Estimation Error Est Err 1,981,854 2M Rank		Estimation Error Est Err 10,622,349 11M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 10,660,997 11M Rank	Estimation Error Est Err 3,710,592 3.7M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,756,348 9.8M Rank	Estimation Error Est Err 9,756,348 9.8M Rank	Estimation Error Est Err 9,756,541 9.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 9,756,486 9.8M Rank	Estimation Error Est Err 3,710,592 3.7M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,366,178 6.4M Rank	Estimation Error Est Err 6,129,574 6.1M Rank	Estimation Error Est Err 9,908,482 9.9M Rank	Estimation Error Est Err 2,900,871 2.9M Rank	Estimation Error Est Err 9,988,718 10M Rank	Estimation Error Est Err 3,710,602 3.7M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 8,539,631 8.5M Rank		Estimation Error Est Err 23,251,265 23M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 23,708,164 24M Rank	Estimation Error Est Err 3,710,608 3.7M Rank	Estimation Error Est Err 7,792,121 7.8M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 3989 3710592 INNER JOIN HASH ON id67 = person_id 3115 3710592 │└INNER JOIN HASH ON id52 = movie_id14 2986 3710592 │ │└INNER JOIN HASH ON person_id42 = person_id 3275 2832555 │ │ │└INNER JOIN HASH ON movie_id33 = movie_id14 104 68316 │ │ │ │└INNER JOIN HASH ON company_id = id21 284 148552 │ │ │ │ │└INNER JOIN HASH ON movie_id14 = 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 2609129 2609129 │ │ │ │ │ TABLE SCAN movie_companies 90648 9436 │ │ │ │ TABLE SCAN company_name WHERE country_code = us 36244344 36244344 │ │ │ TABLE SCAN cast_info 901343 901343 │ │ TABLE SCAN aka_name 2528312 2528312 │ TABLE SCAN title 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_1114.id,PROJECTION_1114.id) = tuple(PROJECTION_1075.person_id,PROJECTION_1075.person_id) - 0 │└PROJECT person_id, person_id, name, title - 0 │ PROJECT name, person_id, person_id, title - 0 │ INNER JOIN HASH ON PROJECTION_1111.person_id = PROJECTION_1078.person_id - 0 │ │└PROJECT person_id AS person_id_right, title - 0 │ │ PROJECT person_id, title - 0 │ │ INNER JOIN HASH ON tuple(PROJECTION_1108.movie_id,PROJECTION_1108.movie_id,PROJECTION_1108.movie_id) = tuple(PROJECTION_1081.movie_id,PROJECTION_1081.movie_id,PROJECTION_1081.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_1105.id = PROJECTION_1084.company_id - 148552 │ │ │ │└PROJECT company_id, movie_id, movie_id, title, id AS id_right - 148552 │ │ │ │ PROJECT movie_id, company_id, movie_id, title, id - 148552 │ │ │ │ INNER JOIN HASH ON tuple(PROJECTION_1102.movie_id,PROJECTION_1102.movie_id) = tuple(PROJECTION_1087.movie_id,PROJECTION_1087.id) - 41840 │ │ │ │ │└PROJECT movie_id AS movie_id_right, id, title - 41840 │ │ │ │ │ PROJECT movie_id, title, id - 41840 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_1099.id = PROJECTION_1090.movie_id - 41840 │ │ │ │ │ │└PROJECT movie_id - 41840 │ │ │ │ │ │ PROJECT movie_id - 41840 │ │ │ │ │ │ INNER JOIN HASH ON PROJECTION_1096.keyword_id = PROJECTION_1093.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 - 2528312 │ │ │ │ │ PROJECT id, title - 2528312 │ │ │ │ │ PROJECT title, id - 2528312 │ │ │ │ │ TABLE SCAN title - 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) 2 3710592 PROJECT name, title 2 3710592 INNER JOIN HASH ON id = person_id 1 3710592 │└INNER JOIN HASH ON person_id = person_id 7 2832555 │ │└INNER JOIN HASH ON movie_id = id 0 68316 │ │ │└INNER JOIN HASH ON id = movie_id 0 68316 │ │ │ │└INNER JOIN HASH ON id = company_id 72 148552 │ │ │ │ │└INNER JOIN HASH ON movie_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 2609129 173613 │ │ │ │ │ TABLE SCAN movie_companies 1644 84843 │ │ │ │ TABLE SCAN company_name WHERE country_code = 'us' 2528312 25740 │ │ │ TABLE SCAN title WHERE id >= 2 AND id <= 2525745 36244344 764634 │ │ FILTER person_id >= 4 36244344 764634 │ │ TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525745 901343 772581 │ TABLE SCAN aka_name WHERE person_id <= 4061926 4167491 118604 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 3710592 INNER JOIN HASH ON ci.person_id = an.person_id 5200 2832555 │└DISTRIBUTE GATHER 3280 2832555 │ INNER JOIN HASH ON ci.person_id = n.id 3280 2832555 │ │└DISTRIBUTE GATHER 3280 2832555 │ │ INNER JOIN HASH ON mc.movie_id = ci.movie_id 3280 68316 │ │ │└DISTRIBUTE GATHER 199 68316 │ │ │ INNER JOIN HASH ON mc.company_id = cn.id 199 148552 │ │ │ │└DISTRIBUTE GATHER 192 148552 │ │ │ │ INNER JOIN HASH ON mk.movie_id = mc.movie_id 192 41840 │ │ │ │ │└DISTRIBUTE GATHER 75 41840 │ │ │ │ │ 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 2520276 │ │ │ │ │ TABLE SCAN title 235000 2605059 │ │ │ │ TABLE SCAN movie_companies 2610000 83604 │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 2530000 36228386 │ │ TABLE SCAN cast_info 134000 4159813 │ TABLE SCAN name 4520000 898151 TABLE SCAN aka_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1030) AS Expr1016, MIN(partialagg1031) AS Expr1017 5 10 AGGREGATE MIN(name as name) AS partialagg1030, MIN(title as title) AS partialagg1031 47409 3710592 INNER JOIN HASH ON an.person_id = ci.person_id 9770 2832555 │└INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 2832555 │ │└TABLE SEEK cast_info AS ci 9770 2832555 │ SORT Expr1051 9770 2832555 │ PROJECT BmkToPage Bmk1002 AS Expr1051 9770 2832555 │ INNER JOIN LOOP ON mc.movie_id = ci.movie_id 30 2832555 │ │└TABLE SEEK cast_info AS ci 323 68316 │ SORT movie_id 323 68316 │ INNER JOIN HASH ON cn.id = mc.company_id 899 148552 │ │└INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 148552 │ │ │└TABLE SEEK movie_companies AS mc 899 148552 │ │ PROJECT BmkToPage Bmk1008 AS Expr1275 899 148552 │ │ INNER JOIN LOOP ON t.id = mc.movie_id 9 148552 │ │ │└TABLE SEEK movie_companies AS mc 90 41840 │ │ INNER JOIN LOOP ON Bmk1014 = Bmk1014 1 41840 │ │ │└TABLE SEEK title AS t 90 41840 │ │ PROJECT BmkToPage Bmk1014 AS Expr1272 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' 845 9436 │ TABLE SCAN company_name AS cn WHERE country_code as country_code = 'us' AND BLOOM(id as id) 9013 227167 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 - 3710592 INNER JOIN HASH ON movie_id = id_38 2528312 2528312 │└DISTRIBUTE HASH ON id_38 2528312 2528312 │ PROJECT id AS id_38, title 2528312 2528312 │ 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) 3376 3710592 INNER JOIN LOOP ON person_id = id 1390 2832555 │└INNER JOIN LOOP ON id = person_id 1390 2832555 │ │└INNER JOIN LOOP ON movie_id = id 63 68316 │ │ │└INNER JOIN LOOP ON id = company_id 173 148552 │ │ │ │└INNER JOIN LOOP ON movie_id = id 34 41840 │ │ │ │ │└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 209200 148532 │ │ │ │ TABLE SEEK movie_companies AS mc 148552 148552 │ │ │ TABLE SEEK company_name AS cn WHERE cn.country_code = 'us' 2732640 2832381 │ │ TABLE SEEK cast_info AS ci 2832555 2832555 │ TABLE SEEK name AS n 5665110 3710647 TABLE SEEK aka_name AS an