Planner — IMDB — JOB-8C

SELECT MIN(a1.name) AS writer_pseudo_name,
       MIN(t.title) AS movie_title
FROM job.aka_name AS a1,
     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 ='writer'
  AND a1.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 a1.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 50,765,830 51M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 10,396,151 10M Rank	Estimation Error Est Err 2,487,611 2.5M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 46,450,620 46M Rank		Estimation Error Est Err 50,245,298 50M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 17,724,464 18M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 21,840,514 22M Rank		Estimation Error Est Err 25,685,662 26M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 11,154,436 11M Rank	Estimation Error Est Err 2,487,630 2.5M Rank	Estimation Error Est Err 10,190,236 10M Rank
Estimation Error Est Err 13,282,752 13M Rank		Estimation Error Est Err 22,509,224 23M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 17,770,922 18M Rank	Estimation Error Est Err 2,487,621 2.5M Rank	Estimation Error Est Err 20,633,650 21M Rank
Estimation Error Est Err 5,375,301 5.4M Rank		Estimation Error Est Err 14,013,278 14M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 12,557,947 13M Rank	Estimation Error Est Err 2,487,611 2.5M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 20,044,207 20M Rank	Estimation Error Est Err 14,005,422 14M Rank	Estimation Error Est Err 23,117,603 23M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 30,622,950 31M Rank	Estimation Error Est Err 497,527 498K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,337,723 3.3M Rank	Estimation Error Est Err 1,989,390 2M Rank	Estimation Error Est Err 9,553,395 9.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 9,553,395 9.6M Rank	Estimation Error Est Err 2,487,621 2.5M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 11,618,166 12M Rank		Estimation Error Est Err 15,456,509 15M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 16,664,072 17M Rank	Estimation Error Est Err 2,487,627 2.5M 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 2487611 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 1970658 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 2728943 INNER JOIN HASH ON role_id = id57 1 1 │└TABLE SCAN role_type WHERE role = writer 36244344 36244344 TABLE SCAN cast_info
	Estimate Actual Operator - 1 PROJECT a1 AS writer_pseudo_name, a2 AS movie_title - 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_5571.id,PROJECTION_5571.id) = tuple(PROJECTION_5538.person_id,PROJECTION_5538.person_id) - 0 │└PROJECT person_id, person_id, name, title - 0 │ PROJECT name, person_id, person_id, title - 0 │ INNER JOIN HASH ON PROJECTION_5568.id = PROJECTION_5541.company_id - 4961427 │ │└PROJECT company_id, name, person_id, person_id, title - 4961427 │ │ PROJECT name, person_id, person_id, company_id, title - 4961427 │ │ INNER JOIN HASH ON tuple(PROJECTION_5565.movie_id,PROJECTION_5565.movie_id) = tuple(PROJECTION_5544.movie_id,PROJECTION_5544.id) - 2343724 │ │ │└PROJECT movie_id AS movie_id_right, id, name, person_id, person_id, title - 2343724 │ │ │ PROJECT name, person_id, person_id, movie_id, title, id - 2343724 │ │ │ INNER JOIN HASH ON PROJECTION_5562.id = PROJECTION_5547.movie_id - 2343724 │ │ │ │└PROJECT movie_id, name, person_id, person_id - 2343724 │ │ │ │ PROJECT name, person_id, person_id, movie_id - 2343724 │ │ │ │ INNER JOIN HASH ON PROJECTION_5559.person_id = PROJECTION_5550.person_id - 2728943 │ │ │ │ │└PROJECT person_id AS person_id_right, movie_id - 2728943 │ │ │ │ │ PROJECT person_id, movie_id - 2728943 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_5556.role_id = PROJECTION_5553.id - 1 │ │ │ │ │ │└PROJECT id - 1 │ │ │ │ │ │ PROJECT id - 1 │ │ │ │ │ │ TABLE SCAN role_type WHERE role = 'writer' - 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 2487611 PROJECT name, title 6570 2487611 INNER JOIN HASH ON id = person_id 5666 2487611 │└INNER JOIN HASH ON id = movie_id 5566 2487611 │ │└INNER JOIN HASH ON person_id = person_id 22196 1970658 │ │ │└INNER JOIN HASH ON role_id = id 1 1 │ │ │ │└FILTER id <= 11 1 1 │ │ │ │ TABLE SCAN role_type WHERE role = 'writer' 266352 1970658 │ │ │ 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 1692377 │ │ │ FILTER person_id >= 4 36244344 1692377 │ │ │ TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525745 901343 711691 │ │ TABLE SCAN aka_name WHERE person_id <= 4061926 2528312 233845 │ TABLE SCAN title WHERE id >= 2 AND id <= 2525745 4167491 43415 TABLE SCAN "name" WHERE id >= 4 AND id <= 4061926
	Estimate Actual Operator 1 1 PROJECT writer_pseudo_name, movie_title 1 1 AGGREGATE MIN(name), MIN(title) 453136 10 DISTRIBUTE GATHER 453136 10 AGGREGATE MIN(name), MIN(title) 453136 2487611 INNER JOIN HASH ON movie_id = id AND movie_id = id 453136 2487611 │└DISTRIBUTE HASH ON movie_id, movie_id 453136 2487611 │ INNER JOIN HASH ON id = role_id 3 1 │ │└DISTRIBUTE GATHER 3 1 │ │ FILTER role = 'writer' 12 12 │ │ DISTRIBUTE ROUND ROBIN 12 12 │ │ TABLE SCAN role_type WHERE role = 'writer' 1661500 2592587 │ INNER JOIN HASH ON person_id = id AND person_id = id 1661500 2592587 │ │└DISTRIBUTE HASH ON person_id, person_id 1661500 2592587 │ │ 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 5189683 │ │ PROJECT person_id, name, person_id, movie_id, role_id, movie_id, company_id 8307394 5189683 │ │ 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 2420843 │ │ DISTRIBUTE HASH ON movie_id 8042634 2420843 │ │ INNER JOIN HASH ON person_id = person_id 901343 901343 │ │ │└DISTRIBUTE HASH ON person_id 901343 901343 │ │ │ TABLE SCAN aka_name 36244344 2841468 │ │ DISTRIBUTE HASH ON person_id 36244344 2841468 │ │ 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 <= 3233413)) AND ((id >= 862) AND (id <= 3233413))) AND TRUE) WHEN 3 THEN ((((id >= 161) AND (id <= 3233335)) AND ((id >= 161) AND (id <= 3233335))) AND TRUE) WHEN 5 THEN ((((id >= 15) AND (id <= 3233447)) AND ((id >= 15) AND (id <= 3233447))) AND TRUE) WHEN 7 THEN ((((id >= 333) AND (id <= 3233169)) AND ((id >= 333) AND (id <= 3233169))) AND TRUE) WHEN 9 THEN ((((id >= 836) A... 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 <= 2525740)) AND ((id >= 57) AND (id <= 2525740))) AND TRUE) WHEN 3 THEN ((((id >= 51) AND (id <= 2525737)) AND ((id >= 51) AND (id <= 2525737))) AND TRUE) WHEN 5 THEN ((((id >= 56) AND (id <= 2525744)) AND ((id >= 56) AND (id <= 2525744))) AND TRUE) WHEN 7 THEN ((((id >= 54) AND (id <= 2525743)) AND ((id >= 54) AND (id <= 2525743))) AND TRUE) WHEN 9 THEN ((((id >= 50) AND (id <= ...
	Estimate Actual Operator 1 1 AGGREGATE MIN(a1.name), MIN(t.title) 1 19 DISTRIBUTE GATHER 1 19 AGGREGATE MIN(a1.name), MIN(t.title) 209000 2487611 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 2343724 INNER JOIN HASH ON a1.person_id = ci.person_id 901000 2728943 │└DISTRIBUTE HASH ON ci.person_id 12100 2728943 │ INNER JOIN HASH ON ci.role_id = rt.id 12100 1 │ │└DISTRIBUTE GATHER 2530000 1 │ │ TABLE SCAN role_type WHERE rt.role = 'writer' 2610000 11557556 │ TABLE SCAN cast_info 3290000 901343 INNER JOIN HASH ON a1.person_id = n1.id 3290000 901343 │└DISTRIBUTE HASH ON a1.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 2487611 INNER JOIN HASH ON ci.person_id = a1.person_id 901343 901343 │└TABLE SCAN aka_name AS a1 2577820 1970658 INNER JOIN HASH ON t.id = mc.movie_id 2577810 1970658 │└INNER JOIN HASH ON ci.role_id = rt.id 1 1 │ │└FILTER role as role = 'writer' 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 1970658 │ 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 835568 │ TABLE SEEK cast_info AS ci WHERE BLOOM(role_id as role_id) AND BLOOM(movie_id as movie_id) 2528310 362147 TABLE SCAN title AS t WHERE BLOOM(id as id)
	Estimate Actual Operator 1 ∞ PROJECT min AS writer_pseudo_name, min_40 AS movie_title 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 - 2487611 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 - 2487611 INNER JOIN HASH ON role_id = id_29 12 1 │└DISTRIBUTE GATHER 12 1 │ PROJECT id AS id_29 12 1 │ FILTER role = 'writer' 12 1 │ TABLE SCAN role_type - 2546630 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 - 2546630 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 - 2546630 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 - 2387398 INNER JOIN HASH ON person_id_1 = person_id 901343 901343 │└DISTRIBUTE GATHER 901343 901343 │ TABLE SCAN aka_name 36244344 2782378 PROJECT person_id AS person_id_1, movie_id, role_id 36244344 2782378 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 5 5 AGGREGATE PARTIAL MIN(name), PARTIAL MIN(title) 1894980 497522 INNER JOIN HASH ON id = person_id 1453780 901343 │└TABLE SCAN aka_name AS a1 3900520 1970658 INNER JOIN LOOP ON id = person_id 780104 394131 │└INNER JOIN HASH ON id = movie_id 14983525 13644715 │ │└INNER JOIN LOOP ON role_id = id 1 1 │ │ │└TABLE SCAN role_type AS rt WHERE rt.role = 'writer' 16345660 13644715 │ │ 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 275864 275864 TABLE SEEK name AS n1