Planner — IMDB — JOB-10A

SELECT MIN(chn.name) AS uncredited_voiced_character,
       MIN(t.title) AS russian_movie
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 '%(voice)%'
  AND ci.note LIKE '%(uncredited)%'
  AND cn.country_code = '[ru]'
  AND rt.role = 'actor'
  AND t.production_year > 2005
  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,160,674 3.2M Rank		Estimation Error Est Err 3,159,799 3.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 18,318 18K Rank	Estimation Error Est Err 104 104 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,794,681 6.8M Rank		Estimation Error Est Err 6,880,803 6.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 310,129 310K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,119,148 6.1M Rank		Estimation Error Est Err 6,126,707 6.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 31,117 31K Rank	Estimation Error Est Err 106 106 Rank	Estimation Error Est Err 14,801 15K Rank
Estimation Error Est Err 21,223,219 21M Rank		Estimation Error Est Err 198,981 199K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,764,279 6.8M Rank	Estimation Error Est Err 114 114 Rank	Estimation Error Est Err 6,815,454 6.8M Rank
Estimation Error Est Err 2,300,110 2.3M Rank		Estimation Error Est Err 2,298,970 2.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 22,455 22K Rank	Estimation Error Est Err 104 104 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 23,599 24K Rank	Estimation Error Est Err 23,598 24K Rank	Estimation Error Est Err 22,488 22K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 22,383 22K Rank	Estimation Error Est Err 106 106 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 87,285 87K Rank	Estimation Error Est Err 899 899 Rank	Estimation Error Est Err 210,336 210K Rank	Estimation Error Est Err 530 530 Rank	Estimation Error Est Err 116,623 117K Rank	Estimation Error Est Err 104 104 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,023,429 1M Rank		Estimation Error Est Err 1,514 1.5K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,023,799 1M Rank	Estimation Error Est Err 120 120 Rank	Estimation Error Est Err 1,023,290 1M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 62 104 INNER JOIN HASH ON id = company_type_id 4 4 │└TABLE SCAN company_type 49 104 INNER JOIN HASH ON id54 = person_role_id 51 104 │└INNER JOIN HASH ON id39 = movie_id 119 265 │ │└INNER JOIN HASH ON id6 = role_id 1 1 │ │ │└TABLE SCAN role_type WHERE role = actor 151 337 │ │ INNER JOIN HASH ON movie_id31 = movie_id 7830 8790 │ │ │└INNER JOIN HASH ON id11 = company_id 917 1361 │ │ │ │└TABLE SCAN company_name WHERE country_code = ru 2076277 8790 │ │ │ TABLE SCAN movie_companies 24534 10125 │ │ TABLE SCAN cast_info WHERE note33 LIKE '%(voice)%' AND note33 LIKE '%(uncredited)%' 1049348 54 │ TABLE SCAN title WHERE production_year >= 2006 3140339 3140339 TABLE SCAN char_name
	Estimate Actual Operator - 1 PROJECT a1 AS uncredited_voiced_character, a2 AS russian_movie - 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_38.id,PROJECTION_38.id) = tuple(PROJECTION_5.movie_id,PROJECTION_5.movie_id) - 0 │└PROJECT movie_id, movie_id, name - 0 │ PROJECT name, movie_id, movie_id - 0 │ INNER JOIN HASH ON PROJECTION_35.id = PROJECTION_8.company_id - 86127 │ │└PROJECT company_id, name, movie_id, movie_id - 86127 │ │ PROJECT name, movie_id, movie_id, company_id - 86127 │ │ INNER JOIN HASH ON PROJECTION_32.id = PROJECTION_11.company_type_id - 86127 │ │ │└PROJECT company_type_id, name, movie_id, movie_id, company_id - 86127 │ │ │ PROJECT name, movie_id, movie_id, company_id, company_type_id - 86127 │ │ │ INNER JOIN HASH ON PROJECTION_29.movie_id = PROJECTION_14.movie_id - 25523 │ │ │ │└PROJECT movie_id AS movie_id_right, name - 25523 │ │ │ │ PROJECT name, movie_id - 25523 │ │ │ │ INNER JOIN HASH ON PROJECTION_26.id = PROJECTION_17.person_role_id - 25874 │ │ │ │ │└PROJECT person_role_id, movie_id - 25874 │ │ │ │ │ PROJECT person_role_id, movie_id - 25874 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_23.role_id = PROJECTION_20.id - 1 │ │ │ │ │ │└PROJECT id - 1 │ │ │ │ │ │ PROJECT id - 1 │ │ │ │ │ │ TABLE SCAN role_type WHERE role = 'actor' - 32288 │ │ │ │ │ PROJECT role_id, person_role_id, movie_id - 32288 │ │ │ │ │ PROJECT person_role_id, movie_id, role_id - 32288 │ │ │ │ │ TABLE SCAN cast_info WHERE note LIKE '%(voice)%' AND note LIKE '%(uncredited)%' - 3140339 │ │ │ │ PROJECT id, name - 3140339 │ │ │ │ PROJECT name, id - 3140339 │ │ │ │ TABLE SCAN char_name - 2609129 │ │ │ PROJECT movie_id AS movie_id_left, company_id, company_type_id - 2609129 │ │ │ PROJECT movie_id, company_id, company_type_id - 2609129 │ │ │ TABLE SCAN movie_companies - 4 │ │ PROJECT id - 4 │ │ PROJECT id - 4 │ │ TABLE SCAN company_type - 0 │ PROJECT id - 0 │ PROJECT id - 0 │ TABLE SCAN company_name WHERE country_code = 'ru' - 1012920 PROJECT id, title - 1012920 PROJECT id, title - 1012920 TABLE SCAN title WHERE production_year > 2005
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 903 104 PROJECT name, title 903 104 INNER JOIN HASH ON id = person_role_id 903 112 │└INNER JOIN HASH ON company_type_id = id 4 2 │ │└TABLE SCAN company_type WHERE id <= 2 903 112 │ INNER JOIN HASH ON role_id = id 1 1 │ │└FILTER id <= 11 1 1 │ │ TABLE SCAN role_type WHERE role = 'actor' 10844 112 │ INNER JOIN HASH ON movie_id = id 3716 4539 │ │└INNER JOIN HASH ON id = movie_id 18253 8790 │ │ │└INNER JOIN HASH ON company_id = id 1644 1361 │ │ │ │└TABLE SCAN company_name WHERE country_code = 'ru' 2609129 2291340 │ │ │ TABLE SCAN movie_companies 505662 5279 │ │ FILTER id BETWEEN 2 AND 2525745 505662 5279 │ │ TABLE SCAN title WHERE production_year > 2005 7248868 136 │ FILTER movie_id BETWEEN 2 AND 2525745 7248868 136 │ TABLE SCAN cast_info WHERE contains(note,'(voice)') AND contains(note,'(uncredited)') 3140339 1991 TABLE SCAN char_name
	Estimate Actual Operator 1 1 PROJECT uncredited_voiced_character, russian_movie 1 1 AGGREGATE MIN(name), MIN(title) 252832 10 DISTRIBUTE GATHER 252832 10 AGGREGATE MIN(name), MIN(title) 252832 104 PROJECT name, title 252832 104 INNER JOIN HASH ON id = movie_id AND id = movie_id 252832 1012920 │└DISTRIBUTE HASH ON id, id 252832 1012920 │ FILTER production_year > 2005 2528312 2528312 │ TABLE SCAN title WHERE production_year > 2005 408414 265 DISTRIBUTE HASH ON movie_id, movie_id 408414 265 INNER JOIN HASH ON id = role_id 3 1 │└DISTRIBUTE GATHER 3 1 │ FILTER role = 'actor' 12 12 │ DISTRIBUTE ROUND ROBIN 12 12 │ TABLE SCAN role_type WHERE role = 'actor' 1497518 265 INNER JOIN HASH ON id = company_type_id 4 4 │└TABLE SCAN company_type 1497518 265 INNER JOIN HASH ON id = company_id 47000 1361 │└DISTRIBUTE GATHER 47000 1361 │ FILTER country_code = 'ru' 234997 234997 │ TABLE SCAN company_name WHERE country_code = 'ru' 7487498 86151 PROJECT name, movie_id, role_id, movie_id, company_id, company_type_id 7487498 86151 INNER JOIN HASH ON movie_id = movie_id 2609129 2609129 │└DISTRIBUTE HASH ON movie_id 2609129 2609129 │ TABLE SCAN movie_companies WHERE ((((company_id >= 220) AND (company_id <= 234623)) 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 >= 3) AND (movie_id <= 2528185)) AND ((movie_id >= 3) AND (movie_id <= 2528185))) AND TRUE) W... 7248869 25533 DISTRIBUTE HASH ON movie_id 7248869 25533 INNER JOIN HASH ON id = person_role_id 3140339 3140339 │└DISTRIBUTE HASH ON id 3140339 3140339 │ TABLE SCAN char_name 7248869 25884 DISTRIBUTE HASH ON person_role_id 7248869 25884 FILTER note LIKE '%(voice)%' AND note LIKE '%(uncredited)%' 36244344 12710426 TABLE SCAN cast_info WHERE (((note LIKE '%(voice)%' AND note LIKE '%(uncredited)%') 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_...
	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) 209000 104 INNER JOIN HASH ON ci.person_role_id = chn.id 209000 104 │└DISTRIBUTE GATHER 12700 104 │ INNER JOIN HASH ON ci.role_id = rt.id 12700 1 │ │└DISTRIBUTE GATHER 235000 1 │ │ TABLE SCAN role_type WHERE rt.role = 'actor' 12100 135 │ INNER JOIN HASH ON mc.movie_id = ci.movie_id 12100 4539 │ │└DISTRIBUTE GATHER 12100 4539 │ │ INNER JOIN HASH ON mc.movie_id = t.id 12100 8790 │ │ │└DISTRIBUTE GATHER 2430000 8790 │ │ │ INNER JOIN HASH ON mc.company_type_id = ct.id 2430000 4 │ │ │ │└DISTRIBUTE GATHER 36200000 4 │ │ │ │ TABLE SCAN company_type 2430000 8790 │ │ │ INNER JOIN HASH ON mc.company_id = cn.id 2430000 1361 │ │ │ │└DISTRIBUTE GATHER 3140000 1361 │ │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'ru') 12 2605035 │ │ │ TABLE SCAN movie_companies 2530000 1009626 │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2005L) 4 31005 │ TABLE SCAN cast_info WHERE (ci.note IS NOT NULL) AND (ci.person_role_id IS NOT NULL) AND contains(ci.note,'(voice)') AND contains(ci.note,'(uncredited)') 2610000 2472116 TABLE SCAN char_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1014, MIN(title as title) AS Expr1015 63 104 INNER JOIN LOOP ON Bmk1012 = Bmk1012 0 104 │└TABLE SEEK title AS t WHERE production_year as production_year > 2005 75 265 SORT Expr1039 75 265 PROJECT BmkToPage Bmk1012 AS Expr1039 75 265 INNER JOIN LOOP ON mc.movie_id = t.id 1 265 │└TABLE SEEK title AS t 75 265 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 265 │└TABLE SEEK char_name AS chn 75 265 SORT Expr1037 75 265 PROJECT BmkToPage Bmk1000 AS Expr1037 75 265 INNER JOIN LOOP ON ci.person_role_id = chn.id 1 265 │└TABLE SEEK char_name AS chn 166 275 INNER JOIN HASH ON mc.company_id = cn.id 1286 1361 │└TABLE SCAN company_name AS cn WHERE country_code as country_code = 'ru' 7544 88489 INNER JOIN HASH ON mc.movie_id = ci.movie_id 3468 25874 │└INNER JOIN HASH ON ci.role_id = rt.id 1 1 │ │└FILTER role as role = 'actor' 12 12 │ │ TABLE SCAN role_type AS rt 38151 32288 │ TABLE SCAN cast_info AS ci WHERE (PROBE(Bitmap1042,role_id as role_id,N'IN ROW')) AND (note as note LIKE '%(voice)%' AND note as note LIKE '%(uncredited)%') 2609130 52725 TABLE SCAN movie_companies AS mc WHERE PROBE(Bitmap1043,movie_id as movie_id,N'IN ROW')
	Estimate Actual Operator 1 ∞ PROJECT min AS uncredited_voiced_character, min_33 AS russian_movie 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 - 104 INNER JOIN HASH ON movie_id = id_26 247389 1012920 │└DISTRIBUTE HASH ON id_26 247389 1012920 │ PROJECT id AS id_26, title 247389 1012920 │ FILTER production_year > 2005 247389 1012920 │ TABLE SCAN title - 265 INNER JOIN HASH ON role_id = id_22 12 1 │└DISTRIBUTE GATHER 12 1 │ PROJECT id AS id_22 12 1 │ FILTER role = 'actor' 12 1 │ TABLE SCAN role_type - 265 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 - 265 INNER JOIN HASH ON company_id = id_4 234997 1361 │└DISTRIBUTE GATHER 234997 1361 │ PROJECT id AS id_4 234997 1361 │ FILTER country_code = 'ru' 234997 1361 │ TABLE SCAN company_name - 265 INNER JOIN HASH ON movie_id = movie_id_17 2609129 8790 │└DISTRIBUTE HASH ON movie_id_17 2609129 8790 │ PROJECT movie_id AS movie_id_17, company_id, company_type_id 2609129 8790 │ TABLE SCAN movie_companies - 189 INNER JOIN HASH ON id = person_role_id 32619910 198 │└DISTRIBUTE HASH ON person_role_id 32619910 198 │ FILTER (note LIKE '%(voice)%') AND (note LIKE '%(uncredited)%') 32619910 198 │ TABLE SCAN cast_info 3140339 155 TABLE SCAN char_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 2 2 AGGREGATE PARTIAL MIN(name), PARTIAL MIN(title) 18 104 INNER JOIN LOOP ON id = company_type_id 18 104 │└INNER JOIN LOOP ON id = person_role_id 9 56 │ │└INNER JOIN HASH ON role_id = id 1 1 │ │ │└TABLE SCAN role_type AS rt WHERE rt.role = 'actor' 204 151 │ │ INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 6996 4539 │ │ │└INNER JOIN LOOP ON id = movie_id 17698 8790 │ │ │ │└INNER JOIN LOOP ON company_id = id 1594 1361 │ │ │ │ │└TABLE SEEK company_name AS cn 181013 8792 │ │ │ │ TABLE SEEK movie_companies AS mc 8790 8790 │ │ │ TABLE SEEK title AS t WHERE t.production_year > 2005 4539 4539 │ │ TABLE SEEK cast_info AS ci WHERE (ci.note LIKE '%(voice)%') AND (ci.note LIKE '%(uncredited)%') 112 112 │ TABLE SEEK char_name AS chn 4 4 TABLE SEEK company_type AS ct