Planner — IMDB — JOB-8D

SELECT MIN(an1.name) AS costume_designer_pseudo,
       MIN(t.title) AS movie_with_costumes
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 cn.country_code ='[us]'
  AND rt.role ='costume designer'
  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 46,535,463 47M Rank		Estimation Error Est Err 46,157,819 46M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,540,483 5.5M Rank	Estimation Error Est Err 323,005 323K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 46,450,620 46M Rank		Estimation Error Est Err 46,450,619 46M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,277,410 2.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 17,439,508 17M Rank		Estimation Error Est Err 17,677,669 18M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,915,543 4.9M Rank	Estimation Error Est Err 323,021 323K Rank	Estimation Error Est Err 7,737,693 7.7M Rank
Estimation Error Est Err 10,813,332 11M Rank		Estimation Error Est Err 9,323,134 9.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,969,048 5M Rank	Estimation Error Est Err 323,015 323K Rank	Estimation Error Est Err 11,685,724 12M Rank
Estimation Error Est Err 2,858,586 2.9M Rank		Estimation Error Est Err 4,149,220 4.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,562,149 3.6M Rank	Estimation Error Est Err 323,005 323K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 7,536,860 7.5M Rank	Estimation Error Est Err 1,498,075 1.5M Rank	Estimation Error Est Err 7,321,291 7.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,412,430 4.4M Rank	Estimation Error Est Err 64,606 65K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,333,577 2.3M Rank	Estimation Error Est Err 1,253,995 1.3M Rank	Estimation Error Est Err 2,333,429 2.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,780,027 3.8M Rank	Estimation Error Est Err 323,015 323K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,195,426 9.2M Rank		Estimation Error Est Err 2,257,399 2.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 9,467,638 9.5M Rank	Estimation Error Est Err 323,021 323K Rank	Estimation Error Est Err 8,835,804 8.8M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 15576107 323005 INNER JOIN HASH ON person_id = person_id63 1053984 901343 │└INNER JOIN HASH ON person_id = id12 901343 901343 │ │└TABLE SCAN aka_name 4167491 4167491 │ TABLE SCAN name 15697930 285538 INNER JOIN HASH ON movie_id64 = movie_id 999148 1153798 │└INNER JOIN HASH ON id42 = movie_id 957641 1153798 │ │└INNER JOIN HASH ON company_id = id24 90648 84843 │ │ │└TABLE SCAN company_name WHERE country_code = us 2609129 2609129 │ │ TABLE SCAN movie_companies 2528312 2528312 │ TABLE SCAN title 3339341 276403 INNER JOIN HASH ON role_id = id57 1 1 │└TABLE SCAN role_type WHERE role = costume designer 36244344 36244344 TABLE SCAN cast_info
	Estimate Actual Operator - 1 PROJECT a1 AS costume_designer_pseudo, a2 AS movie_with_costumes - 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_5612.id,PROJECTION_5612.id) = tuple(PROJECTION_5579.person_id,PROJECTION_5579.person_id) - 0 │└PROJECT person_id, person_id, name, title - 0 │ PROJECT name, person_id, person_id, title - 0 │ INNER JOIN HASH ON PROJECTION_5609.id = PROJECTION_5582.company_id - 752819 │ │└PROJECT company_id, name, person_id, person_id, title - 752819 │ │ PROJECT name, person_id, person_id, company_id, title - 752819 │ │ INNER JOIN HASH ON tuple(PROJECTION_5606.movie_id,PROJECTION_5606.movie_id) = tuple(PROJECTION_5585.movie_id,PROJECTION_5585.id) - 218966 │ │ │└PROJECT movie_id AS movie_id_right, id, name, person_id, person_id, title - 218966 │ │ │ PROJECT name, person_id, person_id, movie_id, title, id - 218966 │ │ │ INNER JOIN HASH ON PROJECTION_5603.id = PROJECTION_5588.movie_id - 218966 │ │ │ │└PROJECT movie_id, name, person_id, person_id - 218966 │ │ │ │ PROJECT name, person_id, person_id, movie_id - 218966 │ │ │ │ INNER JOIN HASH ON PROJECTION_5600.person_id = PROJECTION_5591.person_id - 276403 │ │ │ │ │└PROJECT person_id AS person_id_right, movie_id - 276403 │ │ │ │ │ PROJECT person_id, movie_id - 276403 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_5597.role_id = PROJECTION_5594.id - 1 │ │ │ │ │ │└PROJECT id - 1 │ │ │ │ │ │ PROJECT id - 1 │ │ │ │ │ │ TABLE SCAN role_type WHERE role = 'costume designer' - 36244344 │ │ │ │ │ PROJECT role_id, person_id, movie_id - 36244344 │ │ │ │ │ PROJECT person_id, movie_id, role_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 - 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 - 0 │ PROJECT id - 0 │ TABLE SCAN company_name WHERE country_code = 'us' - 4167491 PROJECT id - 4167491 PROJECT id - 4167491 TABLE SCAN name
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 6570 323005 PROJECT name, title 6570 323005 INNER JOIN HASH ON id = person_id 5666 323005 │└INNER JOIN HASH ON id = movie_id 5566 323005 │ │└INNER JOIN HASH ON person_id = person_id 22196 285538 │ │ │└INNER JOIN HASH ON role_id = id 1 1 │ │ │ │└FILTER id <= 11 1 1 │ │ │ │ TABLE SCAN role_type WHERE role = 'costume designer' 266352 285538 │ │ │ INNER JOIN HASH ON movie_id = movie_id 18253 1153798 │ │ │ │└INNER JOIN HASH ON company_id = id 1644 84843 │ │ │ │ │└TABLE SCAN company_name WHERE country_code = 'us' 2609129 2609129 │ │ │ │ TABLE SCAN movie_companies 36244344 100382 │ │ │ FILTER person_id >= 4 36244344 100382 │ │ │ TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525745 901343 7991 │ │ TABLE SCAN aka_name WHERE person_id <= 4061926 2528312 51796 │ TABLE SCAN title WHERE id >= 2 AND id <= 2525745 4167491 4444 TABLE SCAN "name" WHERE id >= 4 AND id <= 4061926
	Estimate Actual Operator 1 1 PROJECT costume_designer_pseudo, movie_with_costumes 1 1 AGGREGATE MIN(name), MIN(title) 453136 10 DISTRIBUTE GATHER 453136 10 AGGREGATE MIN(name), MIN(title) 453136 323005 INNER JOIN HASH ON movie_id = id AND movie_id = id 453136 323005 │└DISTRIBUTE HASH ON movie_id, movie_id 453136 323005 │ INNER JOIN HASH ON id = role_id 3 1 │ │└DISTRIBUTE GATHER 3 1 │ │ FILTER role = 'costume designer' 12 12 │ │ DISTRIBUTE ROUND ROBIN 12 12 │ │ TABLE SCAN role_type WHERE role = 'costume designer' 1661500 406283 │ INNER JOIN HASH ON person_id = id AND person_id = id 1661500 406283 │ │└DISTRIBUTE HASH ON person_id, person_id 1661500 406283 │ │ 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 924500 │ │ PROJECT person_id, name, person_id, movie_id, role_id, movie_id, company_id 8307394 924500 │ │ 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 8042634 293247 │ │ DISTRIBUTE HASH ON movie_id 8042634 293247 │ │ INNER JOIN HASH ON person_id = person_id 901343 901343 │ │ │└DISTRIBUTE HASH ON person_id 901343 901343 │ │ │ TABLE SCAN aka_name 36244344 372048 │ │ DISTRIBUTE HASH ON person_id 36244344 372048 │ │ 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 <= ... 4167491 4167491 │ DISTRIBUTE HASH ON id, id 4167491 4167491 │ TABLE SCAN name WHERE CASE MOD(HASH_REPARTITION(id,id),10) WHEN 1 THEN ((((id >= 862) AND (id <= 3444656)) AND ((id >= 862) AND (id <= 3444656))) AND TRUE) WHEN 3 THEN ((((id >= 161) AND (id <= 3444506)) AND ((id >= 161) AND (id <= 3444506))) AND TRUE) WHEN 5 THEN ((((id >= 1549) AND (id <= 3444494)) AND ((id >= 1549) AND (id <= 3444494))) AND TRUE) WHEN 7 THEN ((((id >= 339) AND (id <= 3444737)) AND ((id >= 339) AND (id <= 3444737))) AND TRUE) WHEN 9 THEN ((((id >= 83... 2528312 2528312 DISTRIBUTE HASH ON id, id 2528312 2528312 TABLE SCAN title WHERE CASE MOD(HASH_REPARTITION(id,id),10) WHEN 1 THEN ((((id >= 57) AND (id <= 2525611)) AND ((id >= 57) AND (id <= 2525611))) AND TRUE) WHEN 3 THEN ((((id >= 51) AND (id <= 2525593)) AND ((id >= 51) AND (id <= 2525593))) AND TRUE) WHEN 5 THEN ((((id >= 52) AND (id <= 2525362)) AND ((id >= 52) AND (id <= 2525362))) AND TRUE) WHEN 7 THEN ((((id >= 54) AND (id <= 2523962)) AND ((id >= 54) AND (id <= 2523962))) AND TRUE) WHEN 9 THEN ((((id >= 53) AND (id <= ...
	Estimate Actual Operator 1 1 AGGREGATE MIN(an1.name), MIN(t.title) 1 16 DISTRIBUTE GATHER 1 16 AGGREGATE MIN(an1.name), MIN(t.title) 209000 323005 INNER JOIN HASH ON ci.movie_id = mc.movie_id 209000 1153798 │└DISTRIBUTE GATHER 3290000 1153798 │ INNER JOIN HASH ON mc.movie_id = t.id 3290000 1153798 │ │└DISTRIBUTE GATHER 12700 1153798 │ │ INNER JOIN HASH ON mc.company_id = cn.id 12700 84843 │ │ │└DISTRIBUTE GATHER 235000 84843 │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 12 2606542 │ │ TABLE SCAN movie_companies 901000 2522738 │ TABLE SCAN title 901000 218966 INNER JOIN HASH ON an1.person_id = ci.person_id 901000 276403 │└DISTRIBUTE HASH ON ci.person_id 12100 276403 │ INNER JOIN HASH ON ci.role_id = rt.id 12100 1 │ │└DISTRIBUTE GATHER 2530000 1 │ │ TABLE SCAN role_type WHERE rt.role = 'costume designer' 2610000 7156550 │ TABLE SCAN cast_info 3290000 901343 INNER JOIN HASH ON an1.person_id = n1.id 3290000 901343 │└DISTRIBUTE HASH ON an1.person_id 4170000 901343 │ TABLE SCAN aka_name 3290000 4167491 DISTRIBUTE HASH ON n1.id 36200000 4167491 TABLE SCAN name
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1030) AS Expr1014, MIN(partialagg1031) AS Expr1015 5 10 AGGREGATE MIN(name as name) AS partialagg1030, MIN(title as title) AS partialagg1031 12508000 323005 INNER JOIN HASH ON ci.person_id = an1.person_id 901343 901343 │└TABLE SCAN aka_name AS an1 2577820 285538 INNER JOIN HASH ON t.id = mc.movie_id 2577810 285538 │└INNER JOIN HASH ON ci.role_id = rt.id 1 1 │ │└FILTER role as role = 'costume designer' 12 12 │ │ INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 12 │ │ │└TABLE SEEK role_type AS rt 12 12 │ │ TABLE SEEK role_type AS rt 2835590 285538 │ INNER JOIN HASH ON ci.movie_id = mc.movie_id 938352 1153798 │ │└INNER JOIN HASH ON mc.company_id = cn.id 84576 84843 │ │ │└TABLE SCAN company_name AS cn WHERE country_code as country_code = 'us' 2609130 1153798 │ │ TABLE SEEK movie_companies AS mc WHERE BLOOM(company_id as company_id) 362443 100173 │ TABLE SEEK cast_info AS ci WHERE BLOOM(role_id as role_id) AND BLOOM(movie_id as movie_id) 2528310 93396 TABLE SCAN title AS t WHERE BLOOM(id as id)
	Estimate Actual Operator 1 ∞ PROJECT min AS costume_designer_pseudo, min_40 AS movie_with_costumes 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 - 323005 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 - 323005 INNER JOIN HASH ON role_id = id_29 12 1 │└DISTRIBUTE GATHER 12 1 │ PROJECT id AS id_29 12 1 │ FILTER role = 'costume designer' 12 1 │ TABLE SCAN role_type - 393689 INNER JOIN HASH ON person_id_1 = id_18 4167491 4167491 │└DISTRIBUTE HASH ON id_18 4167491 4167491 │ PROJECT id AS id_18 4167491 4167491 │ TABLE SCAN name - 393689 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 - 393689 INNER JOIN HASH ON movie_id = movie_id_13 2609129 1153798 │└DISTRIBUTE HASH ON movie_id_13 2609129 1153798 │ PROJECT movie_id AS movie_id_13, company_id 2609129 1153798 │ TABLE SCAN movie_companies - 285226 INNER JOIN HASH ON person_id_1 = person_id 901343 901343 │└DISTRIBUTE GATHER 901343 901343 │ TABLE SCAN aka_name 36244344 359638 PROJECT person_id AS person_id_1, movie_id, role_id 36244344 359638 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 5 5 AGGREGATE PARTIAL MIN(name), PARTIAL MIN(title) 1894980 64601 INNER JOIN HASH ON id = person_id 1453780 901343 │└TABLE SCAN aka_name AS an1 3900520 285538 INNER JOIN LOOP ON id = person_id 780104 57107 │└INNER JOIN HASH ON id = movie_id 14983525 1382015 │ │└INNER JOIN LOOP ON role_id = id 1 1 │ │ │└TABLE SCAN role_type AS rt WHERE rt.role = 'costume designer' 16345660 1382015 │ │ TABLE SEEK cast_info AS ci 238169 230759 │ INNER JOIN HASH ON id = movie_id 307315 230759 │ │└INNER JOIN HASH ON company_id = id 252370 84843 │ │ │└TABLE SEEK company_name AS cn 4208275 2609129 │ │ TABLE SCAN movie_companies AS mc 3160390 2528312 │ TABLE SCAN title AS t 31217 31217 TABLE SEEK name AS n1