Planner — IMDB — JOB-11B

SELECT MIN(cn.name) AS from_company,
       MIN(lt.link) AS movie_link_type,
       MIN(t.title) AS sequel_movie
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_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 '%follows%'
  AND mc.note IS NULL
  AND t.production_year = 1998
  AND t.title LIKE '%Money%'
  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 ml.movie_id = mk.movie_id
  AND ml.movie_id = mc.movie_id
  AND mk.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 4,553,936 4.6M Rank		Estimation Error Est Err 4,553,996 4.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 21,170 21K Rank	Estimation Error Est Err 14 14 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 10,060,557 10M 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 5,757,442 5.8M Rank		Estimation Error Est Err 5,727,521 5.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 51,108 51K Rank	Estimation Error Est Err 15 15 Rank	Estimation Error Est Err 40,549 41K Rank
Estimation Error Est Err 6,545,137 6.5M Rank		Estimation Error Est Err 7,314,288 7.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,791,679 1.8M Rank	Estimation Error Est Err 24 24 Rank	Estimation Error Est Err 439,155 439K Rank
Estimation Error Est Err 75 75 Rank		Estimation Error Est Err 122 122 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 186 186 Rank	Estimation Error Est Err 14 14 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 21,871 22K Rank	Estimation Error Est Err 21,870 22K Rank	Estimation Error Est Err 22,120 22K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 21,933 22K Rank	Estimation Error Est Err 14 14 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 32,120 32K Rank	Estimation Error Est Err 32,119 32K Rank	Estimation Error Est Err 42,392 42K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 32,119 32K Rank	Estimation Error Est Err 14 14 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 48,351 48K Rank		Estimation Error Est Err 50 50 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 48,372 48K Rank	Estimation Error Est Err 30 30 Rank	Estimation Error Est Err 48,365 48K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 1 14 INNER JOIN HASH ON company_type_id = id 1 1 │└TABLE SCAN company_type WHERE kind = production companies 1 14 INNER JOIN HASH ON company_id = id53 4 21 │└INNER JOIN HASH ON id23 = movie_id46 1 7 │ │└INNER JOIN HASH ON id6 = link_type_id 1 1 │ │ │└TABLE SCAN link_type WHERE link LIKE '%follows%' 1 19 │ │ INNER JOIN HASH ON movie_id39 = id23 1 1 │ │ │└INNER JOIN HASH ON id23 = 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 1711 1 │ │ │ TABLE SCAN title WHERE production_year = 1998 AND title LIKE '%Money%' 29997 29997 │ │ TABLE SCAN movie_link 1203251 3 │ 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 from_company, a2 AS movie_link_type, a3 AS sequel_movie - ∞ 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_190.__lhs_const = PROJECTION_187.__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_196.__lhs_const = PROJECTION_193.__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_202.__lhs_const = PROJECTION_199.__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_208.__lhs_const = PROJECTION_205.__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_214.__lhs_const = PROJECTION_211.__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 14 PROJECT name, link, title 0 14 INNER JOIN HASH ON id = company_type_id 0 14 │└INNER JOIN HASH ON id = company_id 0 21 │ │└INNER JOIN HASH ON movie_id = movie_id 0 7 │ │ │└INNER JOIN HASH ON id = link_type_id 0 19 │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 0 1 │ │ │ │ │└INNER JOIN HASH ON 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 17200 1 │ │ │ │ │ FILTER id BETWEEN 2 AND 186175 17200 1 │ │ │ │ │ TABLE SCAN title WHERE production_year = 1998 AND contains(title,'Money') 29997 19 │ │ │ │ TABLE SCAN movie_link 3 1 │ │ │ FILTER id <= 17 3 1 │ │ │ TABLE SCAN link_type WHERE contains(link,'follows') 521825 3 │ │ FILTER movie_id <= 186175 521825 3 │ │ 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'
	Estimate Actual Operator 1 1 PROJECT from_company, movie_link_type, sequel_movie 1 1 AGGREGATE MIN(name), MIN(link), MIN(title) 6313 10 DISTRIBUTE GATHER 6313 10 AGGREGATE MIN(name), MIN(link), MIN(title) 6313 14 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id 6313 102 │└DISTRIBUTE GATHER 6313 102 │ PROJECT name, movie_id, movie_id, movie_id, link 6313 102 │ INNER JOIN HASH ON id = link_type_id 4 1 │ │└DISTRIBUTE GATHER 4 1 │ │ FILTER link LIKE '%follows%' 18 18 │ │ DISTRIBUTE ROUND ROBIN 18 18 │ │ TABLE SCAN link_type WHERE link LIKE '%follows%' 26834 422 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 26834 2293 │ │└DISTRIBUTE GATHER 26834 2293 │ │ 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' 93458 1337515 │ │ 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) 4523930 4523930 │ │ TABLE SCAN movie_keyword WHERE (((movie_id >= 50) AND (movie_id <= 2525744)) 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 <= 2525558)) AND ((movie_id >= 31741) AND (movie_id <= 2525558))) AND TRUE) AND (((link_type_id >= 1) AND (link_type_id <= 1)) AND link_type_id IN 1) 505663 2 FILTER (production_year = 1998) AND title LIKE '%Money%' 2528312 245760 TABLE SCAN title WHERE ((production_year = 1998) AND title LIKE '%Money%') AND (((((id >= 31741) AND (id <= 138935)) AND ((id >= 31741) AND (id <= 138935))) AND ((id >= 31741) AND (id <= 138935))) AND struct(id,id,id) IN( < expr > , < expr > , < expr > , < expr > , < 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) 878 14 INNER JOIN HASH ON ml.link_type_id = lt.id 878 1 │└DISTRIBUTE GATHER 2610000 1 │ TABLE SCAN link_type WHERE contains(lt.link,'follows') 41400 38 INNER JOIN HASH ON mc.movie_id = ml.movie_id 41400 29997 │└DISTRIBUTE GATHER 2530000 29997 │ TABLE SCAN movie_link 40000 2 INNER JOIN HASH ON mc.company_id = cn.id 40000 3 │└DISTRIBUTE GATHER 40000 3 │ INNER JOIN HASH ON mc.company_type_id = ct.id 40000 1 │ │└DISTRIBUTE GATHER 235000 1 │ │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 316 3 │ INNER JOIN HASH ON mk.movie_id = mc.movie_id 316 1 │ │└DISTRIBUTE GATHER 316 1 │ │ INNER JOIN HASH ON mk.movie_id = t.id 316 10544 │ │ │└DISTRIBUTE GATHER 71 10544 │ │ │ INNER JOIN HASH ON mk.keyword_id = k.id 71 1 │ │ │ │└DISTRIBUTE GATHER 18 1 │ │ │ │ TABLE SCAN keyword WHERE k.keyword = 'sequel' 30000 4519836 │ │ │ TABLE SCAN movie_keyword 134000 33 │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year = 1998L) AND contains(t.title,'Money') 4520000 1160822 │ TABLE SCAN movie_companies WHERE mc.note IS NULL 4 46751 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'))
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1016, MIN(link as link) AS Expr1017, MIN(title as title) AS Expr1018 1 14 FILTER (name as name LIKE '%Film%' OR name as name LIKE '%Warner%') AND country_code as country_code <> 'pl' 2 21 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 21 │└TABLE SEEK company_name AS cn 2 21 INNER JOIN LOOP ON mc.company_id = cn.id 1 21 │└TABLE SEEK company_name AS cn 2 21 FILTER kind as kind = 'production companies' 4 21 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 21 │└TABLE SEEK company_type AS ct 4 21 INNER JOIN LOOP ON mc.company_type_id = ct.id 1 21 │└TABLE SEEK company_type AS ct 4 21 FILTER note as note IS NULL 9 28 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 28 │└TABLE SEEK movie_companies AS mc 9 28 PROJECT BmkToPage Bmk1008 AS Expr1049 9 28 INNER JOIN LOOP ON t.id = mc.movie_id 9 28 │└TABLE SEEK movie_companies AS mc 1 7 FILTER link as link LIKE '%follows%' 1 19 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 19 │└TABLE SEEK link_type AS lt 1 19 INNER JOIN LOOP ON ml.link_type_id = lt.id 1 19 │└TABLE SEEK link_type AS lt 1 19 INNER JOIN LOOP ON Bmk1012 = Bmk1012 1 19 │└TABLE SEEK movie_link AS ml 1 19 INNER JOIN LOOP ON t.id = ml.movie_id 1 19 │└TABLE SEEK movie_link AS ml 1 1 FILTER title as title LIKE '%Money%' 1 271 INNER JOIN LOOP ON Bmk1014 = Bmk1014 0 271 │└TABLE SEEK title AS t WHERE production_year as production_year = 1998 90 10544 PROJECT BmkToPage Bmk1014 AS Expr1047 90 10544 INNER JOIN LOOP ON mk.movie_id = t.id 1 10544 │└TABLE SEEK title AS t 90 10544 INNER JOIN LOOP ON Bmk1010 = Bmk1010 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'
	Estimate Actual Operator 1 ∞ PROJECT min AS from_company, min_33 AS movie_link_type, min_34 AS sequel_movie 1 1 AGGREGATE MIN(min_35) AS min, MIN(min_36) AS min_33, MIN(min_37) AS min_34 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name) AS min_35, MIN(link) AS min_36, MIN(title) AS min_37 - 14 INNER JOIN HASH ON movie_id = id_26 2528312 36 │└DISTRIBUTE HASH ON id_26 2528312 36 │ PROJECT id AS id_26, title 2528312 36 │ FILTER (production_year = 1998) AND (title LIKE '%Money%') 2528312 36 │ TABLE SCAN title - 14 INNER JOIN HASH ON link_type_id = id_8 18 1 │└DISTRIBUTE GATHER 18 1 │ PROJECT id AS id_8, link 18 1 │ FILTER link LIKE '%follows%' 18 1 │ TABLE SCAN link_type - 14 INNER JOIN HASH ON movie_id = movie_id_22 29997 7 │└DISTRIBUTE GATHER 29997 7 │ PROJECT movie_id AS movie_id_22, link_type_id 29997 7 │ TABLE SCAN movie_link - 2 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 - 2 INNER JOIN HASH ON movie_id = movie_id_17 4523930 1 │└DISTRIBUTE HASH ON movie_id_17 4523930 1 │ PROJECT movie_id AS movie_id_17, keyword_id 4523930 1 │ TABLE SCAN movie_keyword - 2 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 - 2 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 2 FILTER note IS NULL 1271989 2 TABLE SCAN movie_companies
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(link), MIN(title) 1 14 INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 1 102 │└INNER JOIN LOOP ON id = company_type_id 1 103 │ │└INNER JOIN LOOP ON id = company_id 1 288 │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = movie_id AND (movie_id = movie_id) 1 64 │ │ │ │└INNER JOIN LOOP ON keyword = 'sequel' AND id = link_type_id AND (id = link_type_id) 1 1 │ │ │ │ │└TABLE SCAN link_type AS lt WHERE lt.link LIKE '%follows%' 10 251 │ │ │ │ INNER JOIN LOOP ON movie_id = movie_id 34 10544 │ │ │ │ │└INNER JOIN LOOP ON keyword_id = id 1 1 │ │ │ │ │ │└TABLE SEEK keyword AS k 305 10544 │ │ │ │ │ TABLE SEEK movie_keyword AS mk 52720 10544 │ │ │ │ TABLE SEEK movie_link AS ml 128 288 │ │ │ TABLE SEEK movie_companies AS mc WHERE mc.note IS NULL 288 288 │ │ TABLE SEEK company_name AS cn WHERE (cn.country_code <> 'pl') AND ((cn.name LIKE '%Film%') OR (cn.name LIKE '%Warner%')) 103 103 │ TABLE SEEK company_type AS ct WHERE ct.kind = 'production companies' 102 102 TABLE SEEK title AS t WHERE (t.title LIKE '%Money%') AND (t.production_year = 1998)