Planner — IMDB — JOB-10B

SELECT MIN(chn.name) AS character,
       MIN(t.title) AS russian_mov_with_actor_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 = '[ru]'
  AND rt.role = 'actor'
  AND t.production_year > 2010
  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 1,381 1.4K Rank		Estimation Error Est Err 15 15 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,391 1.4K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 7,576,578 7.6M Rank		Estimation Error Est Err 7,576,577 7.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 26 26 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 0 0 Rank		Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 21,223,219 21M Rank		Estimation Error Est Err 73 73 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,142,500 6.1M Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 6,142,544 6.1M Rank
Estimation Error Est Err 2,299,437 2.3M Rank		Estimation Error Est Err 2,298,073 2.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 18,842 19K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 21,566 22K Rank	Estimation Error Est Err 21,561 22K Rank	Estimation Error Est Err 22,818 23K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 20,201 20K Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 10,175 10K Rank	Estimation Error Est Err 8,814 8.8K Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 10,164 10K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 401,822 402K Rank		Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 401,822 402K Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 401,838 402K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 5 0 INNER JOIN HASH ON id = company_type_id 4 4 │└TABLE SCAN company_type 2 0 INNER JOIN HASH ON id54 = person_role_id 2 0 │└INNER JOIN HASH ON id39 = movie_id32 14 0 │ │└INNER JOIN HASH ON id6 = company_id 917 1361 │ │ │└TABLE SCAN company_name WHERE country_code = ru 3687 0 │ │ INNER JOIN HASH ON movie_id = movie_id32 2260 13 │ │ │└INNER JOIN HASH ON id16 = role_id 1 1 │ │ │ │└TABLE SCAN role_type WHERE role = actor 24534 15 │ │ │ TABLE SCAN cast_info WHERE note LIKE '%(producer)%' 2076277 0 │ │ TABLE SCAN movie_companies 365420 0 │ TABLE SCAN title WHERE production_year >= 2011 3140339 0 TABLE SCAN char_name
	Estimate Actual Operator - 1 PROJECT a1 AS character, a2 AS russian_mov_with_actor_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_79.id = PROJECTION_46.company_id - 0 │└PROJECT company_id, name, title - 0 │ PROJECT name, company_id, title - 0 │ INNER JOIN HASH ON PROJECTION_76.id = PROJECTION_49.company_type_id - 0 │ │└PROJECT company_type_id, name, company_id, title - 0 │ │ PROJECT name, company_id, company_type_id, title - 0 │ │ INNER JOIN HASH ON tuple(PROJECTION_73.movie_id,PROJECTION_73.movie_id) = tuple(PROJECTION_52.movie_id,PROJECTION_52.id) - 0 │ │ │└PROJECT movie_id AS movie_id_right, id, name, title - 0 │ │ │ PROJECT name, movie_id, title, id - 0 │ │ │ INNER JOIN HASH ON PROJECTION_70.id = PROJECTION_55.person_role_id - 0 │ │ │ │└PROJECT person_role_id, movie_id, title, id AS id_right - 0 │ │ │ │ PROJECT person_role_id, movie_id, title, id - 0 │ │ │ │ INNER JOIN HASH ON PROJECTION_67.id = PROJECTION_58.movie_id - 13 │ │ │ │ │└PROJECT movie_id, person_role_id - 13 │ │ │ │ │ PROJECT person_role_id, movie_id - 13 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_64.role_id = PROJECTION_61.id - 1 │ │ │ │ │ │└PROJECT id - 1 │ │ │ │ │ │ PROJECT id - 1 │ │ │ │ │ │ TABLE SCAN role_type WHERE role = 'actor' - 1435439 │ │ │ │ │ PROJECT role_id, person_role_id, movie_id - 1435439 │ │ │ │ │ PROJECT person_role_id, movie_id, role_id - 1435439 │ │ │ │ │ TABLE SCAN cast_info WHERE note LIKE '%(producer)%' - 391666 │ │ │ │ PROJECT id, title - 391666 │ │ │ │ PROJECT id, title - 391666 │ │ │ │ TABLE SCAN title WHERE production_year > 2010 - 3140339 │ │ │ PROJECT id AS id_left, 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'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 903 0 PROJECT name, title 903 0 INNER JOIN HASH ON id = person_role_id 903 0 │└INNER JOIN HASH ON company_type_id = id 4 2 │ │└TABLE SCAN company_type WHERE id <= 2 903 0 │ INNER JOIN HASH ON role_id = id 1 1 │ │└FILTER id <= 11 1 1 │ │ TABLE SCAN role_type WHERE role = 'actor' 10844 0 │ INNER JOIN HASH ON movie_id = id 3716 1259 │ │└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 2275887 │ │ │ TABLE SCAN movie_companies 505662 1706 │ │ FILTER id BETWEEN 2 AND 2525745 505662 1706 │ │ TABLE SCAN title WHERE production_year > 2010 7248868 0 │ FILTER movie_id BETWEEN 2 AND 2525745 7248868 0 │ TABLE SCAN cast_info WHERE contains(note,'(producer)') 3140339 20480 TABLE SCAN char_name
	Estimate Actual Operator 1 1 PROJECT character, russian_mov_with_actor_producer 1 1 AGGREGATE MIN(name), MIN(title) 162535 10 DISTRIBUTE GATHER 162535 10 AGGREGATE MIN(name), MIN(title) 162535 0 PROJECT name, title 162535 0 INNER JOIN HASH ON id = movie_id AND id = movie_id 162535 391666 │└DISTRIBUTE HASH ON id, id 162535 391666 │ FILTER production_year > 2010 2528312 2528312 │ TABLE SCAN title WHERE production_year > 2010 408414 0 DISTRIBUTE HASH ON movie_id, movie_id 408414 0 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 0 INNER JOIN HASH ON id = company_type_id 4 4 │└TABLE SCAN company_type 1497518 0 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 30 PROJECT name, movie_id, role_id, movie_id, company_id, company_type_id 7487498 30 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 >= 13) AND (movie_id <= 2528194)) AND ((movie_id >= 13) AND (movie_id <= 2528194))) AND TRUE) WHEN 3 THEN ((((movie_id >= 11) AND (movie_id <= 2528183)) AND ((movie_id >= 11) AND (movie_id <= 2528183))) AND TRU... 7248869 13 DISTRIBUTE HASH ON movie_id 7248869 13 INNER JOIN HASH ON id = person_role_id 3140339 3140339 │└DISTRIBUTE HASH ON id 3140339 3140339 │ TABLE SCAN char_name 7248869 13 DISTRIBUTE HASH ON person_role_id 7248869 13 FILTER note LIKE '%(producer)%' 36244344 12710426 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 0 DISTRIBUTE GATHER 1 0 AGGREGATE MIN(chn.name), MIN(t.title) 209000 ∞ DISTRIBUTE GATHER 209000 ∞ INNER JOIN HASH ON ci.role_id = rt.id 209000 ∞ │└DISTRIBUTE GATHER 2610000 ∞ │ TABLE SCAN role_type WHERE rt.role = 'actor' 209000 ∞ INNER JOIN HASH ON mc.movie_id = ci.movie_id 209000 ∞ │└DISTRIBUTE GATHER 209000 ∞ │ INNER JOIN HASH ON mc.movie_id = t.id 209000 ∞ │ │└DISTRIBUTE GATHER 12700 ∞ │ │ INNER JOIN HASH ON mc.company_type_id = ct.id 12700 ∞ │ │ │└DISTRIBUTE GATHER 2530000 ∞ │ │ │ TABLE SCAN company_type 12100 ∞ │ │ INNER JOIN HASH ON mc.company_id = cn.id 12100 ∞ │ │ │└DISTRIBUTE GATHER 12 ∞ │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'ru') 36200000 ∞ │ │ TABLE SCAN movie_companies 4 ∞ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2010L) 235000 ∞ TABLE SCAN cast_info WHERE (ci.note IS NOT NULL) AND (ci.person_role_id IS NOT NULL) AND contains(ci.note,'(producer)')
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1037) AS Expr1014, MIN(partialagg1038) AS Expr1015 5 0 AGGREGATE MIN(name as name) AS partialagg1037, MIN(title as title) AS partialagg1038 938 0 INNER JOIN HASH ON mc.company_id = cn.id 1286 1361 │└TABLE SCAN company_name AS cn WHERE country_code as country_code = 'ru' 424 0 INNER JOIN LOOP ON Bmk1012 = Bmk1012 0 0 │└TABLE SEEK title AS t WHERE production_year as production_year > 2010 1312 ∞ PROJECT BmkToPage Bmk1012 AS Expr1613 1312 0 INNER JOIN LOOP ON mc.movie_id = t.id 1 0 │└TABLE SEEK title AS t 1312 0 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 0 │└TABLE SEEK char_name AS chn 1312 ∞ PROJECT BmkToPage Bmk1000 AS Expr1610 1312 0 INNER JOIN LOOP ON ci.person_role_id = chn.id 1 0 │└TABLE SEEK char_name AS chn 2917 0 INNER JOIN HASH ON ci.role_id = rt.id 1 1 │└FILTER role as role = 'actor' 12 12 │ INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 12 │ │└TABLE SEEK role_type AS rt 12 12 │ TABLE SEEK role_type AS rt 3209 0 INNER JOIN HASH ON ci.movie_id = mc.movie_id 26091 8790 │└TABLE SEEK movie_companies AS mc WHERE BLOOM(company_id as company_id) 147 0 TABLE SCAN cast_info AS ci WHERE note as note LIKE '%(producer)%' AND BLOOM(movie_id as movie_id) AND BLOOM(role_id as role_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS character, min_33 AS russian_mov_with_actor_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 - 0 INNER JOIN HASH ON movie_id = id_26 159036 391666 │└DISTRIBUTE HASH ON id_26 159036 391666 │ PROJECT id AS id_26, title 159036 391666 │ FILTER production_year > 2010 159036 391666 │ TABLE SCAN title - 0 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 - 0 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 - 0 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 - 0 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 - 0 INNER JOIN HASH ON id = person_role_id 32619910 0 │└DISTRIBUTE HASH ON person_role_id 32619910 0 │ FILTER note LIKE '%(producer)%' 32619910 0 │ TABLE SCAN cast_info 3140339 0 TABLE SCAN char_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 2 2 AGGREGATE PARTIAL MIN(name), PARTIAL MIN(title) 196 0 INNER JOIN HASH ON company_type_id = id 4 4 │└TABLE SCAN company_type AS ct 392 2 INNER JOIN LOOP ON id = person_role_id 196 0 │└INNER JOIN HASH ON role_id = id 1 1 │ │└TABLE SCAN role_type AS rt WHERE rt.role = 'actor' 4718 2614 │ INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 2772 1259 │ │└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 > 2010 2518 2618 │ TABLE SEEK cast_info AS ci WHERE ci.note LIKE '%(producer)%' 0 0 TABLE SEEK char_name AS chn