Planner — IMDB — JOB-21B

SELECT MIN(cn.name) AS company_name,
       MIN(lt.link) AS link_type,
       MIN(t.title) AS german_follow_up
FROM job.company_name AS cn,
     job.company_type AS ct,
     job.keyword AS k,
     job.link_type AS lt,
     job.movie_companies AS mc,
     job.movie_info AS mi,
     job.movie_keyword AS mk,
     job.movie_link AS ml,
     job.title AS t
WHERE cn.country_code !='[pl]'
  AND (cn.name LIKE '%Film%'
       OR cn.name LIKE '%Warner%')
  AND ct.kind ='production companies'
  AND k.keyword ='sequel'
  AND lt.link LIKE '%follow%'
  AND mc.note IS NULL
  AND mi.info IN ('Germany',
                  'German')
  AND t.production_year BETWEEN 2000 AND 2010
  AND lt.id = ml.link_type_id
  AND ml.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND t.id = mc.movie_id
  AND mc.company_type_id = ct.id
  AND mc.company_id = cn.id
  AND mi.movie_id = t.id
  AND ml.movie_id = mk.movie_id
  AND ml.movie_id = mc.movie_id
  AND mk.movie_id = mc.movie_id
  AND ml.movie_id = mi.movie_id
  AND mk.movie_id = mi.movie_id
  AND mc.movie_id = mi.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 4,553,951 4.6M Rank		Estimation Error Est Err 4,554,524 4.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 21,908 22K Rank	Estimation Error Est Err 38 38 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 24,896,277 25M 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 6,944,663 6.9M Rank		Estimation Error Est Err 7,030,209 7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 335,388 335K Rank	Estimation Error Est Err 39 39 Rank	Estimation Error Est Err 208,565 209K Rank
Estimation Error Est Err 16,079,604 16M Rank		Estimation Error Est Err 6,736,945 6.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 995,088 995K Rank	Estimation Error Est Err 48 48 Rank	Estimation Error Est Err 485,291 485K Rank
Estimation Error Est Err 444 444 Rank		Estimation Error Est Err 1,118 1.1K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,565 1.6K Rank	Estimation Error Est Err 38 38 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 45,353 45K Rank	Estimation Error Est Err 45,351 45K Rank	Estimation Error Est Err 45,852 46K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 45,549 46K Rank	Estimation Error Est Err 38 38 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 23,122 23K Rank	Estimation Error Est Err 23,121 23K Rank	Estimation Error Est Err 23,302 23K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 33,415 33K Rank	Estimation Error Est Err 38 38 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,093,479 1.1M Rank		Estimation Error Est Err 1,020 1K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,093,813 1.1M Rank	Estimation Error Est Err 54 54 Rank	Estimation Error Est Err 1,093,477 1.1M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 1 38 INNER JOIN HASH ON company_type_id = id 1 1 │└TABLE SCAN company_type WHERE kind = production companies 1 38 INNER JOIN HASH ON company_id = id61 4 133 │└INNER JOIN HASH ON movie_id24 = movie_id54 1 19 │ │└INNER JOIN HASH ON movie_id24 = id38 1 118 │ │ │└INNER JOIN HASH ON movie_id24 = movie_id31 1 199 │ │ │ │└INNER JOIN HASH ON id6 = link_type_id 2 2 │ │ │ │ │└TABLE SCAN link_type WHERE link LIKE '%follow%' 2 251 │ │ │ │ INNER JOIN HASH ON movie_id24 = movie_id 32 10544 │ │ │ │ │└INNER JOIN HASH ON keyword_id = id11 1 1 │ │ │ │ │ │└TABLE SCAN keyword WHERE keyword = sequel 4523930 4523930 │ │ │ │ │ TABLE SCAN movie_keyword 29997 29997 │ │ │ │ TABLE SCAN movie_link 57952 10 │ │ │ TABLE SCAN movie_info WHERE info IN(German,Germany) 1081445 1 │ │ TABLE SCAN title WHERE production_year BETWEEN 2000 AND 2010 1203251 7 │ TABLE SCAN movie_companies WHERE note IS NULL 51405 2 TABLE SCAN company_name WHERE name LIKE '%Film%' OR name LIKE '%Warner%' AND country_code <> pl
	Estimate Actual Operator - ∞ PROJECT a1 AS company_name, a2 AS link_type, a3 AS german_follow_up - ∞ AGGREGATE MIN(name) AS a1, MIN(link) AS a2, MIN(title) AS a3 - ∞ PROJECT name, link, title - ∞ FILTER 0 - ∞ PROJECT name, link, title - ∞ INNER JOIN HASH ON TRUE - 0 │└PROJECT title - 0 │ FILTER 0 - 2528312 │ PROJECT title - 2528312 │ TABLE SCAN title - ∞ PROJECT name, link - ∞ FILTER 0 - ∞ PROJECT name, link - ∞ INNER JOIN HASH ON PROJECTION_2188.__lhs_const = PROJECTION_2185.__rhs_const - ∞ │└PROJECT __rhs_const, id - 29997 │ PROJECT id - 29997 │ TABLE SCAN movie_link - ∞ PROJECT __lhs_const, name, link - ∞ PROJECT name, link - ∞ INNER JOIN HASH ON PROJECTION_2194.__lhs_const = PROJECTION_2191.__rhs_const - ∞ │└PROJECT __rhs_const, movie_id - 4523930 │ PROJECT movie_id - 4523930 │ TABLE SCAN movie_keyword - ∞ PROJECT __lhs_const, name, link - ∞ PROJECT name, link - ∞ INNER JOIN HASH ON PROJECTION_2200.__lhs_const = PROJECTION_2197.__rhs_const - ∞ │└PROJECT __rhs_const, info_type_id - 14835720 │ PROJECT info_type_id - 14835720 │ TABLE SCAN movie_info - ∞ PROJECT __lhs_const, name, link - ∞ PROJECT name, link - ∞ INNER JOIN HASH ON PROJECTION_2206.__lhs_const = PROJECTION_2203.__rhs_const - ∞ │└PROJECT __rhs_const, company_type_id - 2609129 │ PROJECT company_type_id - 2609129 │ TABLE SCAN movie_companies - ∞ PROJECT __lhs_const, name, link - ∞ PROJECT name, link - ∞ INNER JOIN HASH ON PROJECTION_2212.__lhs_const = PROJECTION_2209.__rhs_const - ∞ │└PROJECT __rhs_const, link - 18 │ PROJECT link - 18 │ TABLE SCAN link_type - ∞ PROJECT __lhs_const, name - ∞ PROJECT name - ∞ INNER JOIN HASH ON PROJECTION_2218.__lhs_const = PROJECTION_2215.__rhs_const - ∞ │└PROJECT __rhs_const, id - 134170 │ PROJECT id - 134170 │ TABLE SCAN keyword - ∞ PROJECT __lhs_const, name - 939988 PROJECT name - 939988 INNER JOIN HASH ON TRUE - 4 │└PROJECT id - 4 │ PROJECT id - 4 │ TABLE SCAN company_type - 234997 PROJECT name - 234997 PROJECT name - 234997 TABLE SCAN company_name
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 0 38 PROJECT name, link, title 0 38 INNER JOIN HASH ON movie_id = movie_id 0 92 │└INNER JOIN HASH ON id = company_type_id 0 92 │ │└INNER JOIN HASH ON id = company_id 0 317 │ │ │└INNER JOIN HASH ON movie_id = movie_id 0 48 │ │ │ │└INNER JOIN HASH ON id = movie_id 0 199 │ │ │ │ │└INNER JOIN HASH ON id = link_type_id 0 251 │ │ │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 69 46 │ │ │ │ │ │ │└INNER JOIN HASH ON keyword_id = id 2 1 │ │ │ │ │ │ │ │└TABLE SCAN keyword WHERE keyword = 'sequel' 4523930 46 │ │ │ │ │ │ │ TABLE SCAN movie_keyword WHERE movie_id <= 186175 29997 286 │ │ │ │ │ │ TABLE SCAN movie_link 3 2 │ │ │ │ │ FILTER id <= 17 3 2 │ │ │ │ │ TABLE SCAN link_type WHERE contains(link,'follow') 505662 8 │ │ │ │ FILTER id BETWEEN 2 AND 186175 505662 9 │ │ │ │ TABLE SCAN title WHERE production_year >= 2000 AND production_year <= 2010 521825 23 │ │ │ FILTER movie_id <= 186175 521825 23 │ │ │ TABLE SCAN movie_companies WHERE note IS NULL 46999 3 │ │ TABLE SCAN company_name WHERE country_code != 'pl' AND (contains(name,'Film') OR contains(name,'Warner')) 1 1 │ FILTER id <= 2 1 1 │ TABLE SCAN company_type WHERE kind = 'production companies' 2967144 1 FILTER movie_id BETWEEN 2 AND 186175 2967144 1 FILTER (info = 'Germany') OR (info = 'German') 14835720 73 TABLE SCAN movie_info WHERE info IN('Germany','German')
	Estimate Actual Operator 1 1 PROJECT company_name, link_type, german_follow_up 1 1 AGGREGATE MIN(name), MIN(link), MIN(title) 6313 10 DISTRIBUTE GATHER 6313 10 AGGREGATE MIN(name), MIN(link), MIN(title) 6313 38 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id AND movie_id = id 6313 308 │└DISTRIBUTE GATHER 6313 308 │ PROJECT name, movie_id, movie_id, movie_id, movie_id, link 6313 308 │ INNER JOIN HASH ON id = link_type_id 4 2 │ │└DISTRIBUTE GATHER 4 2 │ │ FILTER link LIKE '%follow%' 18 18 │ │ DISTRIBUTE ROUND ROBIN 18 18 │ │ TABLE SCAN link_type WHERE link LIKE '%follow%' 26834 308 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id 26834 488 │ │└DISTRIBUTE GATHER 26834 488 │ │ INNER JOIN HASH ON id = keyword_id 26834 1 │ │ │└DISTRIBUTE GATHER 26834 1 │ │ │ FILTER keyword = 'sequel' 134170 134170 │ │ │ DISTRIBUTE ROUND ROBIN 134170 134170 │ │ │ TABLE SCAN keyword WHERE keyword = 'sequel' 109759 319866 │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 61285 47734 │ │ │└DISTRIBUTE GATHER 61285 47734 │ │ │ INNER JOIN HASH ON movie_id = movie_id 52183 224256 │ │ │ │└DISTRIBUTE GATHER 52183 224256 │ │ │ │ INNER JOIN HASH ON id = company_type_id 1 1 │ │ │ │ │└DISTRIBUTE GATHER 1 1 │ │ │ │ │ FILTER kind = 'production companies' 4 4 │ │ │ │ │ DISTRIBUTE ROUND ROBIN 4 4 │ │ │ │ │ TABLE SCAN company_type WHERE kind = 'production companies' 104366 225118 │ │ │ │ INNER JOIN HASH ON id = company_id 47000 48302 │ │ │ │ │└DISTRIBUTE GATHER 47000 48302 │ │ │ │ │ FILTER (country_code <> 'pl') AND (name LIKE '%Film%' OR name LIKE '%Warner%') 234997 234997 │ │ │ │ │ TABLE SCAN company_name WHERE (country_code <> 'pl') AND (name LIKE '%Film%' OR name LIKE '%Warner%') 521826 1197292 │ │ │ │ FILTER note IS NULL 2609129 1376261 │ │ │ │ TABLE SCAN movie_companies WHERE (note IS NULL AND (((company_id >= 9) AND (company_id <= 234994)) AND TRUE)) AND (((company_type_id >= 2) AND (company_type_id <= 2)) AND company_type_id IN 2) 2967144 116559 │ │ │ FILTER (info = 'Germany') OR (info = 'German') 14835720 9042947 │ │ │ TABLE SCAN movie_info WHERE ((info = 'Germany') OR (info = 'German')) AND (((movie_id >= 50) AND (movie_id <= 2525744)) AND TRUE) 4523930 4523930 │ │ TABLE SCAN movie_keyword WHERE ((((movie_id >= 870) AND (movie_id <= 2525727)) AND ((movie_id >= 870) AND (movie_id <= 2525727))) AND TRUE) AND (((keyword_id >= 398) AND (keyword_id <= 398)) AND keyword_id IN 398) 29997 29997 │ DISTRIBUTE ROUND ROBIN 29997 29997 │ TABLE SCAN movie_link WHERE (((((movie_id >= 31741) AND (movie_id <= 2524777)) AND ((movie_id >= 31741) AND (movie_id <= 2524777))) AND ((movie_id >= 31741) AND (movie_id <= 2524777))) AND TRUE) AND (((link_type_id >= 1) AND (link_type_id <= 2)) AND link_type_id IN(1,2)) 198654 323875 FILTER (production_year >= 2000) AND (production_year <= 2010) 2528312 737280 TABLE SCAN title WHERE ((production_year >= 2000) AND (production_year <= 2010)) AND ((((((id >= 31741) AND (id <= 138935)) AND ((id >= 31741) AND (id <= 138935))) AND ((id >= 31741) AND (id <= 138935))) AND ((id >= 31741) AND (id <= 138935))) AND struct(id,id,id,id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr ...
	Estimate Actual Operator 1 1 AGGREGATE MIN(cn.name), MIN(lt.link), MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(cn.name), MIN(lt.link), MIN(t.title) 886 38 INNER JOIN HASH ON ml.link_type_id = lt.id 886 2 │└DISTRIBUTE GATHER 4520000 2 │ TABLE SCAN link_type WHERE contains(lt.link,'follow') 886 38 INNER JOIN HASH ON mc.movie_id = ml.movie_id 886 29997 │└DISTRIBUTE GATHER 134000 29997 │ TABLE SCAN movie_link 886 144 INNER JOIN HASH ON mc.movie_id = mk.movie_id 886 3158 │└DISTRIBUTE GATHER 199 3158 │ INNER JOIN HASH ON mk.movie_id = t.id 199 10544 │ │└DISTRIBUTE GATHER 75 10544 │ │ INNER JOIN HASH ON mk.keyword_id = k.id 75 1 │ │ │└DISTRIBUTE GATHER 18 1 │ │ │ TABLE SCAN keyword WHERE k.keyword = 'sequel' 4 4519836 │ │ TABLE SCAN movie_keyword 2530000 1039493 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year >= 2000L) AND (t.production_year <= 2010L) 192 47734 INNER JOIN HASH ON mc.company_id = cn.id 192 48302 │└DISTRIBUTE GATHER 2610000 48302 │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND ( NOT (cn.country_code = 'pl')) AND (contains(cn.name,'Film') OR contains(cn.name,'Warner')) 192 116246 INNER JOIN HASH ON mc.company_type_id = ct.id 192 1 │└DISTRIBUTE GATHER 235000 1 │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 71 116246 INNER JOIN HASH ON mc.movie_id = mi.movie_id 71 116559 │└DISTRIBUTE GATHER 14800000 116559 │ TABLE SCAN movie_info WHERE mi.info IN('Germany','German') 30000 1190472 TABLE SCAN movie_companies WHERE mc.note IS NULL
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1018, MIN(link as link) AS Expr1019, MIN(title as title) AS Expr1020 1 38 FILTER (name as name LIKE '%Film%' OR name as name LIKE '%Warner%') AND country_code as country_code <> 'pl' 2 133 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 133 │└TABLE SEEK company_name AS cn 2 133 INNER JOIN LOOP ON mc.company_id = cn.id 1 133 │└TABLE SEEK company_name AS cn 2 133 FILTER kind as kind = 'production companies' 4 133 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 133 │└TABLE SEEK company_type AS ct 4 133 INNER JOIN LOOP ON mc.company_type_id = ct.id 1 133 │└TABLE SEEK company_type AS ct 4 133 FILTER note as note IS NULL 9 247 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 247 │└TABLE SEEK movie_companies AS mc 9 247 PROJECT BmkToPage Bmk1008 AS Expr1072 9 247 INNER JOIN LOOP ON t.id = mc.movie_id 9 247 │└TABLE SEEK movie_companies AS mc 1 19 INNER JOIN LOOP ON Bmk1016 = Bmk1016 1 19 │└TABLE SEEK title AS t WHERE production_year as production_year >= 2000 AND production_year as production_year <= 2010 1 118 PROJECT BmkToPage Bmk1016 AS Expr1071 1 118 INNER JOIN LOOP ON ml.movie_id = t.id 1 118 │└TABLE SEEK title AS t 1 118 INNER JOIN LOOP ON ml.movie_id = mi.movie_id 1 118 │└TABLE SEEK movie_info AS mi WHERE info as info = 'German' OR info as info = 'Germany' 1 199 FILTER link as link LIKE '%follow%' 7 251 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 251 │└TABLE SEEK link_type AS lt 7 251 INNER JOIN LOOP ON ml.link_type_id = lt.id 1 251 │└TABLE SEEK link_type AS lt 7 251 INNER JOIN HASH ON ml.movie_id = mk.movie_id 90 10544 │└INNER JOIN LOOP ON Bmk1012 = Bmk1012 1 10544 │ │└TABLE SEEK movie_keyword AS mk 90 10544 │ INNER JOIN LOOP ON k.id = mk.keyword_id 90 10544 │ │└TABLE SEEK movie_keyword AS mk 1 1 │ TABLE SCAN keyword AS k WHERE keyword as keyword = 'sequel' 2 250 TABLE SEEK movie_link AS ml WHERE BLOOM(movie_id as movie_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS company_name, min_39 AS link_type, min_40 AS german_follow_up 1 1 AGGREGATE MIN(min_41) AS min, MIN(min_42) AS min_39, MIN(min_43) AS min_40 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name) AS min_41, MIN(link) AS min_42, MIN(title) AS min_43 - 38 INNER JOIN HASH ON movie_id = id_32 176706 1042800 │└DISTRIBUTE HASH ON id_32 176706 1042800 │ PROJECT id AS id_32, title 176706 1042800 │ FILTER production_year BETWEEN 2000 AND 2010 176706 1042800 │ TABLE SCAN title - 308 INNER JOIN HASH ON link_type_id = id_8 18 2 │└DISTRIBUTE GATHER 18 2 │ PROJECT id AS id_8, link 18 2 │ FILTER link LIKE '%follow%' 18 2 │ TABLE SCAN link_type - 308 INNER JOIN HASH ON movie_id = movie_id_28 29997 2315 │└DISTRIBUTE GATHER 29997 2315 │ PROJECT movie_id AS movie_id_28, link_type_id 29997 2315 │ TABLE SCAN movie_link - 20 INNER JOIN HASH ON keyword_id = id_4 134170 1 │└DISTRIBUTE GATHER 134170 1 │ PROJECT id AS id_4 134170 1 │ FILTER keyword = 'sequel' 134170 1 │ TABLE SCAN keyword - 20 INNER JOIN HASH ON movie_id = movie_id_23 4523930 30 │└DISTRIBUTE HASH ON movie_id_23 4523930 30 │ PROJECT movie_id AS movie_id_23, keyword_id 4523930 30 │ TABLE SCAN movie_keyword - 20 INNER JOIN HASH ON movie_id = movie_id_17 13352148 10 │└DISTRIBUTE HASH ON movie_id_17 13352148 10 │ PROJECT movie_id AS movie_id_17 13352148 10 │ FILTER info IN('German','Germany') 13352148 10 │ TABLE SCAN movie_info - 18 INNER JOIN HASH ON company_type_id = id_0 4 1 │└DISTRIBUTE GATHER 4 1 │ PROJECT id AS id_0 4 1 │ FILTER kind = 'production companies' 4 1 │ TABLE SCAN company_type - 18 INNER JOIN HASH ON company_id = id 94983 48302 │└DISTRIBUTE GATHER 94983 48302 │ FILTER (country_code <> 'pl') AND ((name LIKE '%Film%') OR (name LIKE '%Warner%')) 94983 48302 │ TABLE SCAN company_name 1271989 18 FILTER note IS NULL 1271989 18 TABLE SCAN movie_companies
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(link), MIN(title) 1 38 INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 1 308 │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = movie_id AND (movie_id = movie_id) 1 402 │ │└INNER JOIN LOOP ON id = company_type_id 1 422 │ │ │└INNER JOIN LOOP ON id = company_id 1 1021 │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = movie_id AND (movie_id = movie_id) 1 199 │ │ │ │ │└INNER JOIN LOOP ON keyword = 'sequel' AND id = link_type_id AND (id = link_type_id) 1 2 │ │ │ │ │ │└TABLE SCAN link_type AS lt WHERE lt.link LIKE '%follow%' 20 502 │ │ │ │ │ INNER JOIN LOOP ON movie_id = movie_id 68 21088 │ │ │ │ │ │└INNER JOIN LOOP ON keyword_id = id 2 2 │ │ │ │ │ │ │└TABLE SEEK keyword AS k 610 21088 │ │ │ │ │ │ TABLE SEEK movie_keyword AS mk 105440 21088 │ │ │ │ │ TABLE SEEK movie_link AS ml 398 1020 │ │ │ │ TABLE SEEK movie_companies AS mc WHERE mc.note IS NULL 1021 1021 │ │ │ TABLE SEEK company_name AS cn WHERE (cn.country_code <> 'pl') AND ((cn.name LIKE '%Film%') OR (cn.name LIKE '%Warner%')) 422 422 │ │ TABLE SEEK company_type AS ct WHERE ct.kind = 'production companies' 402 402 │ TABLE SEEK movie_info AS mi WHERE mi.info IN('Germany','German') 308 308 TABLE SEEK title AS t WHERE (t.production_year >= 2000) AND (t.production_year <= 2010)