Planner — IMDB — JOB-10C

SELECT MIN(chn.name) AS character,
       MIN(t.title) AS movie_with_american_producer
FROM job.char_name AS chn,
     job.cast_info AS ci,
     job.company_name AS cn,
     job.company_type AS ct,
     job.movie_companies AS mc,
     job.role_type AS rt,
     job.title AS t
WHERE ci.note LIKE '%(producer)%'
  AND cn.country_code = '[us]'
  AND t.production_year > 1990
  AND t.id = mc.movie_id
  AND t.id = ci.movie_id
  AND ci.movie_id = mc.movie_id
  AND chn.id = ci.person_role_id
  AND rt.id = ci.role_id
  AND cn.id = mc.company_id
  AND ct.id = mc.company_type_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 3,180,094 3.2M Rank		Estimation Error Est Err 3,180,083 3.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 93 93 Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 8,933,955 8.9M Rank		Estimation Error Est Err 8,933,951 8.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,218,258 5.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,834,567 6.8M Rank		Estimation Error Est Err 7,903,516 7.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,057,666 4.1M Rank	Estimation Error Est Err 12 12 Rank	Estimation Error Est Err 1,834,919 1.8M Rank
Estimation Error Est Err 44,757,137 45M Rank		Estimation Error Est Err 1,435,543 1.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 7,583,371 7.6M Rank	Estimation Error Est Err 20 20 Rank	Estimation Error Est Err 9,018,821 9M Rank
Estimation Error Est Err 4,786,956 4.8M Rank		Estimation Error Est Err 6,518,037 6.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,250,158 5.3M Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,224,117 5.2M Rank	Estimation Error Est Err 865,940 866K Rank	Estimation Error Est Err 5,259,535 5.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,144,989 2.1M Rank	Estimation Error Est Err 7 7 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,869,878 2.9M Rank	Estimation Error Est Err 1,153,798 1.2M Rank	Estimation Error Est Err 1,496,232 1.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,085,481 4.1M Rank	Estimation Error Est Err 30 30 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,423,131 4.4M Rank		Estimation Error Est Err 86 86 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,423,140 4.4M Rank	Estimation Error Est Err 26 26 Rank	Estimation Error Est Err 4,423,144 4.4M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 14145 10 INNER JOIN HASH ON id = role_id 12 12 │└TABLE SCAN role_type 11122 10 INNER JOIN HASH ON id6 = company_type_id 4 4 │└TABLE SCAN company_type 8851 10 INNER JOIN HASH ON id54 = person_role_id 9263 10 │└INNER JOIN HASH ON id44 = company_id 25238 23 │ │└INNER JOIN HASH ON movie_id = movie_id37 15472 12 │ │ │└INNER JOIN HASH ON id21 = movie_id 21328 15 │ │ │ │└TABLE SCAN cast_info WHERE note LIKE '%(producer)%' 1757966 12 │ │ │ TABLE SCAN title WHERE production_year >= 1991 2076277 23 │ │ TABLE SCAN movie_companies 90648 39689 │ TABLE SCAN company_name WHERE country_code = us 3140339 3140339 TABLE SCAN char_name
	Estimate Actual Operator - 1 PROJECT a1 AS character, a2 AS movie_with_american_producer - 1 AGGREGATE MIN(name) AS a1, MIN(title) AS a2 - 0 PROJECT name, title - 0 PROJECT name, title - 0 INNER JOIN HASH ON PROJECTION_120.id = PROJECTION_87.person_role_id - 0 │└PROJECT person_role_id, title - 0 │ PROJECT person_role_id, title - 0 │ INNER JOIN HASH ON tuple(PROJECTION_117.id,PROJECTION_117.id) = tuple(PROJECTION_90.movie_id,PROJECTION_90.movie_id) - 0 │ │└PROJECT movie_id, movie_id, person_role_id - 0 │ │ PROJECT person_role_id, movie_id, movie_id - 0 │ │ INNER JOIN HASH ON PROJECTION_114.id = PROJECTION_93.role_id - 0 │ │ │└PROJECT role_id, person_role_id, movie_id, movie_id - 0 │ │ │ PROJECT person_role_id, movie_id, role_id, movie_id - 0 │ │ │ INNER JOIN HASH ON PROJECTION_111.movie_id = PROJECTION_96.movie_id - 0 │ │ │ │└PROJECT movie_id AS movie_id_right - 0 │ │ │ │ PROJECT movie_id - 0 │ │ │ │ INNER JOIN HASH ON PROJECTION_108.id = PROJECTION_99.company_id - 2609129 │ │ │ │ │└PROJECT company_id, movie_id - 2609129 │ │ │ │ │ PROJECT movie_id, company_id - 2609129 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_105.company_type_id = PROJECTION_102.id - 4 │ │ │ │ │ │└PROJECT id - 4 │ │ │ │ │ │ PROJECT id - 4 │ │ │ │ │ │ TABLE SCAN company_type - 2609129 │ │ │ │ │ PROJECT company_type_id, movie_id, company_id - 2609129 │ │ │ │ │ PROJECT movie_id, company_id, company_type_id - 2609129 │ │ │ │ │ TABLE SCAN movie_companies - 0 │ │ │ │ PROJECT id - 0 │ │ │ │ PROJECT id - 0 │ │ │ │ TABLE SCAN company_name WHERE country_code = 'us' - 1435439 │ │ │ PROJECT movie_id AS movie_id_left, person_role_id, role_id - 1435439 │ │ │ PROJECT person_role_id, movie_id, role_id - 1435439 │ │ │ TABLE SCAN cast_info WHERE note LIKE '%(producer)%' - 12 │ │ PROJECT id - 12 │ │ PROJECT id - 12 │ │ TABLE SCAN role_type - 1749032 │ PROJECT id, title - 1749032 │ PROJECT id, title - 1749032 │ TABLE SCAN title WHERE production_year > 1990 - 3140339 PROJECT id, name - 3140339 PROJECT name, id - 3140339 TABLE SCAN char_name
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 10844 10 PROJECT name, title 10844 10 INNER JOIN HASH ON id = person_role_id 10844 782104 │└INNER JOIN HASH ON role_id = id 12 11 │ │└TABLE SCAN role_type WHERE id <= 11 10844 782104 │ INNER JOIN HASH ON movie_id = id 3716 596250 │ │└INNER JOIN HASH ON company_type_id = id 4 2 │ │ │└TABLE SCAN company_type WHERE id <= 2 3716 596250 │ │ INNER JOIN HASH ON 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 505662 1748440 │ │ FILTER id BETWEEN 2 AND 2525745 505662 1749032 │ │ TABLE SCAN title WHERE production_year > 1990 7248868 343938 │ FILTER movie_id BETWEEN 2 AND 2525745 7248868 343938 │ TABLE SCAN cast_info WHERE contains(note,'(producer)') 3140339 1 TABLE SCAN char_name
	Estimate Actual Operator 1 1 PROJECT character, movie_with_american_producer 1 1 AGGREGATE MIN(name), MIN(title) 523722 10 DISTRIBUTE GATHER 523722 10 AGGREGATE MIN(name), MIN(title) 523722 10 PROJECT name, title 523722 10 INNER JOIN HASH ON id = movie_id AND id = movie_id 523722 1749032 │└DISTRIBUTE HASH ON id, id 523722 1749032 │ FILTER production_year > 1990 2528312 2528312 │ TABLE SCAN title WHERE production_year > 1990 1497518 14 DISTRIBUTE HASH ON movie_id, movie_id 1497518 14 INNER JOIN HASH ON id = role_id 12 12 │└TABLE SCAN role_type 1497518 14 INNER JOIN HASH ON id = company_type_id 4 4 │└TABLE SCAN company_type 1497518 14 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' 7487498 31 PROJECT name, movie_id, role_id, movie_id, company_id, company_type_id 7487498 31 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 (((company_type_id >= 1) AND (company_type_id <= 4)) AND company_type_id IN(1,2,3,4))) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 4) AND (movie_id <= 2528297)) AND ((movie_id >= 4) AND (movie_id <= 2528297))) AND TRUE) WHEN 3 THEN ((((movie_id >= 1) AND (movie_id <= 2528257)) AND ((movie_id >= 1) AND (movie_id <= 2528257))) AND TRUE) WHE... 7248869 15 DISTRIBUTE HASH ON movie_id 7248869 15 INNER JOIN HASH ON id = person_role_id 3140339 3140339 │└DISTRIBUTE HASH ON id 3140339 3140339 │ TABLE SCAN char_name 7248869 1435439 DISTRIBUTE HASH ON person_role_id 7248869 1435439 FILTER note LIKE '%(producer)%' 36244344 36244344 TABLE SCAN cast_info WHERE ((note LIKE '%(producer)%' AND CASE MOD(HASH_REPARTITION person_role_id,10) WHEN 0 THEN (((person_role_id >= 5) AND (person_role_id <= 3140327)) AND TRUE) WHEN 1 THEN (((person_role_id >= 6) AND (person_role_id <= 3140324)) AND TRUE) WHEN 2 THEN (((person_role_id >= 10) AND (person_role_id <= 3140333)) AND TRUE) WHEN 3 THEN (((person_role_id >= 1) AND (person_role_id <= 3140339)) AND TRUE) WHEN 4 THEN (((person_role_id >= 12) AND (person_role_id ...
	Estimate Actual Operator 1 1 AGGREGATE MIN(chn.name), MIN(t.title) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE MIN(chn.name), MIN(t.title) 32200 10 INNER JOIN HASH ON ci.person_role_id = chn.id 32200 10 │└DISTRIBUTE GATHER 6940000 10 │ INNER JOIN HASH ON ci.role_id = rt.id 6940000 12 │ │└DISTRIBUTE GATHER 12 12 │ │ TABLE SCAN role_type 6940000 10 │ INNER JOIN HASH ON mc.movie_id = ci.movie_id 6940000 596250 │ │└DISTRIBUTE GATHER 6940000 596250 │ │ INNER JOIN HASH ON mc.movie_id = t.id 6940000 1153798 │ │ │└DISTRIBUTE GATHER 6940000 1153798 │ │ │ INNER JOIN HASH ON mc.company_type_id = ct.id 6940000 4 │ │ │ │└DISTRIBUTE GATHER 2610000 4 │ │ │ │ TABLE SCAN company_type 6940000 1153798 │ │ │ INNER JOIN HASH ON mc.company_id = cn.id 6940000 84843 │ │ │ │└DISTRIBUTE GATHER 235000 84843 │ │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 4 2606542 │ │ │ TABLE SCAN movie_companies 2530000 1744764 │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 1990L) 3140000 14 │ TABLE SCAN cast_info WHERE (ci.note IS NOT NULL) AND (ci.person_role_id IS NOT NULL) AND contains(ci.note,'(producer)') 36200000 2398388 TABLE SCAN char_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1027) AS Expr1014, MIN(partialagg1028) AS Expr1015 5 4 AGGREGATE MIN(name as name) AS partialagg1027, MIN(title as title) AS partialagg1028 358493 26 INNER JOIN HASH ON t.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) 45822 12 INNER JOIN HASH ON id as id = person_role_id as person_role_id 101857 342396 │└INNER JOIN HASH ON t.id = ci.movie_id 1475210 1435489 │ │└TABLE SCAN cast_info AS ci WHERE note as note LIKE '%(producer)%' 174569 195744 │ TABLE SCAN title AS t WHERE (production_year as production_year > 1990) AND (BLOOM(id as id) AND BLOOM(id as id)) 31403 4 TABLE SCAN char_name AS chn WHERE BLOOM(id as id)
	Estimate Actual Operator 1 ∞ PROJECT min AS character, min_33 AS movie_with_american_producer 1 1 AGGREGATE MIN(min_34) AS min, MIN(min_35) AS min_33 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name) AS min_34, MIN(title) AS min_35 - 10 INNER JOIN HASH ON movie_id = id_26 512448 1749032 │└DISTRIBUTE HASH ON id_26 512448 1749032 │ PROJECT id AS id_26, title 512448 1749032 │ FILTER production_year > 1990 512448 1749032 │ TABLE SCAN title - 14 INNER JOIN HASH ON role_id = id_22 12 12 │└DISTRIBUTE GATHER 12 12 │ PROJECT id AS id_22 12 12 │ TABLE SCAN role_type - 14 INNER JOIN HASH ON company_type_id = id_12 4 4 │└DISTRIBUTE GATHER 4 4 │ PROJECT id AS id_12 4 4 │ TABLE SCAN company_type - 14 INNER JOIN HASH ON company_id = id_4 234997 84843 │└DISTRIBUTE GATHER 234997 84843 │ PROJECT id AS id_4 234997 84843 │ FILTER country_code = 'us' 234997 84843 │ TABLE SCAN company_name - 14 INNER JOIN HASH ON movie_id = movie_id_17 2609129 1153798 │└DISTRIBUTE HASH ON movie_id_17 2609129 1153798 │ PROJECT movie_id AS movie_id_17, company_id, company_type_id 2609129 1153798 │ TABLE SCAN movie_companies - 15 INNER JOIN HASH ON id = person_role_id 32619910 1435439 │└DISTRIBUTE HASH ON person_role_id 32619910 1435439 │ FILTER note LIKE '%(producer)%' 32619910 1435439 │ TABLE SCAN cast_info 3140339 3 TABLE SCAN char_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 5 5 AGGREGATE PARTIAL MIN(name), PARTIAL MIN(title) 279297 2 INNER JOIN HASH ON role_id = id 12 12 │└TABLE SCAN role_type AS rt 1396485 10 INNER JOIN LOOP ON id = person_role_id 1396485 782104 │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 164100 119250 │ │└INNER JOIN HASH ON company_type_id = id 4 4 │ │ │└TABLE SCAN company_type AS ct 164100 119250 │ │ 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 2177530 1749032 │ │ TABLE SCAN title AS t WHERE t.production_year > 1990 1192500 781087 │ TABLE SEEK cast_info AS ci WHERE ci.note LIKE '%(producer)%' 10 10 TABLE SEEK char_name AS chn