Planner — IMDB — JOB-11C

SELECT MIN(cn.name) AS from_company,
       MIN(mc.note) AS production_note,
       MIN(t.title) AS movie_based_on_book
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 '20th Century Fox%'
       OR cn.name LIKE 'Twentieth Century Fox%')
  AND ct.kind != 'production companies'
  AND ct.kind IS NOT NULL
  AND k.keyword IN ('sequel',
                    'revenge',
                    'based-on-novel')
  AND mc.note IS NOT NULL
  AND t.production_year > 1950
  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,554,086 4.6M Rank		Estimation Error Est Err 4,588,657 4.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 129,536 130K Rank	Estimation Error Est Err 6,946 6.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,584,607 9.6M 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 8,163,509 8.2M Rank		Estimation Error Est Err 8,421,976 8.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 410,571 411K Rank	Estimation Error Est Err 6,947 6.9K Rank	Estimation Error Est Err 113,577 114K Rank
Estimation Error Est Err 8,314,016 8.3M Rank		Estimation Error Est Err 8,416,880 8.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 879,533 880K Rank	Estimation Error Est Err 6,956 7K Rank	Estimation Error Est Err 189,140 189K Rank
Estimation Error Est Err 286,721 287K Rank		Estimation Error Est Err 1,094,330 1.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,816,758 1.8M Rank	Estimation Error Est Err 6,946 6.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 235,346 235K Rank	Estimation Error Est Err 235,345 235K Rank	Estimation Error Est Err 409,579 410K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 245,277 245K Rank	Estimation Error Est Err 6,946 6.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 174,367 174K Rank	Estimation Error Est Err 174,361 174K Rank	Estimation Error Est Err 193,078 193K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 235,266 235K Rank	Estimation Error Est Err 6,946 6.9K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,317,611 2.3M Rank		Estimation Error Est Err 21,006 21K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,324,575 2.3M Rank	Estimation Error Est Err 6,962 7K Rank	Estimation Error Est Err 2,317,585 2.3M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 1 6946 INNER JOIN HASH ON id58 = link_type_id 1 6946 │└INNER JOIN HASH ON id43 = movie_id36 1 6946 │ │└INNER JOIN HASH ON company_id = id 151 89 │ │ │└TABLE SCAN company_name WHERE country_code <> pl 23 6946 │ │ INNER JOIN HASH ON company_type_id = id11 3 3 │ │ │└TABLE SCAN company_type WHERE kind != production companies 31 6946 │ │ INNER JOIN HASH ON movie_id = movie_id36 7 1460 │ │ │└INNER JOIN HASH ON movie_id29 = movie_id 97 47403 │ │ │ │└INNER JOIN HASH ON keyword_id = id16 3 3 │ │ │ │ │└TABLE SCAN keyword WHERE keyword IN(based - on - novel,revenge,sequel) 4523930 4523930 │ │ │ │ TABLE SCAN movie_keyword 29997 29997 │ │ │ TABLE SCAN movie_link 1368263 42 │ │ TABLE SCAN movie_companies 2286344 4 │ TABLE SCAN title WHERE production_year >= 1951 18 18 TABLE SCAN link_type
	Estimate Actual Operator - ∞ PROJECT a1 AS from_company, a2 AS production_note, a3 AS movie_based_on_book - ∞ AGGREGATE MIN(name) AS a1, MIN(note) AS a2, MIN(title) AS a3 - ∞ PROJECT name, note, title - ∞ PROJECT name, note, title - ∞ INNER JOIN HASH ON PROJECTION_231.keyword_id = PROJECTION_228.id - ∞ │└PROJECT id - ∞ │ FILTER (1 AND in(keyword,__set_String_3121726789508643859_9359213533155979499)) AS a38 - 134170 │ TABLE SCAN keyword WHERE TRUE - ∞ PROJECT keyword_id, name, note, title - ∞ PROJECT name, note, keyword_id, title - ∞ INNER JOIN HASH ON tuple(PROJECTION_237.movie_id,PROJECTION_237.movie_id,PROJECTION_237.id) = tuple(PROJECTION_234.movie_id,PROJECTION_234.movie_id,PROJECTION_234.movie_id) - 4523930 │└PROJECT movie_id AS movie_id_right, keyword_id - 4523930 │ PROJECT movie_id, keyword_id - 4523930 │ TABLE SCAN movie_keyword - ∞ PROJECT movie_id_left, movie_id AS movie_id_left_2, id, name, note, title - ∞ PROJECT name, note, movie_id, movie_id, title, id - ∞ INNER JOIN HASH ON tuple(PROJECTION_243.movie_id,PROJECTION_243.movie_id) = tuple(PROJECTION_240.id,PROJECTION_240.id) - 2287271 │└PROJECT id, title - 2287271 │ FILTER (1 AND production_year > 1950_UInt16) AS a34 - 2287271 │ TABLE SCAN title WHERE production_year > 1950 - ∞ PROJECT movie_id, movie_id, name, note - ∞ PROJECT name, note, movie_id, movie_id - ∞ INNER JOIN HASH ON PROJECTION_249.link_type_id = PROJECTION_246.id - 18 │└PROJECT id - 18 │ PROJECT id - 18 │ TABLE SCAN link_type - ∞ PROJECT link_type_id, name, note, movie_id, movie_id - ∞ PROJECT name, note, movie_id, movie_id, link_type_id - ∞ INNER JOIN HASH ON PROJECTION_255.movie_id = PROJECTION_252.movie_id - 29997 │└PROJECT movie_id AS movie_id_right, link_type_id - 29997 │ PROJECT movie_id, link_type_id - 29997 │ TABLE SCAN movie_link - ∞ PROJECT movie_id AS movie_id_left, name, note - ∞ PROJECT name, note, movie_id - ∞ INNER JOIN HASH ON PROJECTION_261.company_type_id = PROJECTION_258.id - 3 │└PROJECT id - 3 │ FILTER (kind <> 'production companies'_String AND 1) AS a30 - 3 │ TABLE SCAN company_type WHERE kind <> 'production companies' - ∞ PROJECT company_type_id, name, note, movie_id - ∞ PROJECT name, note, company_type_id, movie_id - ∞ INNER JOIN HASH ON PROJECTION_267.id = PROJECTION_264.company_id - 2609129 │└PROJECT company_id, note, company_type_id, movie_id - 2609129 │ PROJECT note, company_id, company_type_id, movie_id - 2609129 │ TABLE SCAN movie_companies - ∞ PROJECT id, name - ∞ FILTER (country_code <> 'pl'_String AND OR (startsWith(name,'20th Century Fox'_String),startsWith(name,'Twentieth Century Fox'_String)) AND 1) AS a16 - 89 TABLE SCAN company_name WHERE (country_code <> 'pl') AND (startsWith(name,'20th Century Fox') OR startsWith(name,'Twentieth Century Fox'))
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 24 6946 PROJECT name, note, title 24 6946 INNER JOIN HASH ON link_type_id = id 18 17 │└TABLE SCAN link_type WHERE id <= 17 24 6946 INNER JOIN HASH ON id = keyword_id 116 780776 │└INNER JOIN HASH ON movie_id = movie_id 64 8393 │ │└INNER JOIN HASH ON company_type_id = id 1 1 │ │ │└FILTER id <= 2 1 3 │ │ │ TABLE SCAN company_type WHERE kind != 'production companies' AND (kind IS NOT NULL) 256 8402 │ │ INNER JOIN HASH ON id = company_id 641 104935 │ │ │└INNER JOIN HASH ON movie_id = id 6106 29726 │ │ │ │└INNER JOIN HASH ON id = movie_id 29997 29997 │ │ │ │ │└TABLE SCAN movie_link 505662 177923 │ │ │ │ FILTER id BETWEEN 2 AND 186175 505662 226724 │ │ │ │ TABLE SCAN title WHERE production_year > 1950 521825 7709 │ │ │ FILTER movie_id <= 186175 521825 7719 │ │ │ TABLE SCAN movie_companies WHERE note IS NOT NULL 46999 89 │ │ TABLE SCAN company_name WHERE country_code != 'pl' AND (prefix(name,'20th Century Fox') OR prefix(name,'Twentieth Century Fox')) 4523930 1616 │ TABLE SCAN movie_keyword WHERE movie_id <= 186175 26834 3 FILTER (keyword = 'sequel') OR (keyword = 'revenge') OR (keyword = 'based-on-novel') 134170 20556 TABLE SCAN keyword WHERE keyword IN('sequel','revenge','based-on-novel')
	Estimate Actual Operator 1 1 PROJECT from_company, production_note, movie_based_on_book 1 1 AGGREGATE MIN(name), MIN(note), MIN(title) 26834 10 DISTRIBUTE GATHER 26834 10 AGGREGATE MIN(name), MIN(note), MIN(title) 26834 6946 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id 26834 6946 │└DISTRIBUTE GATHER 26834 6946 │ INNER JOIN HASH ON id = link_type_id 18 18 │ │└TABLE SCAN link_type 26834 6946 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 26834 4726 │ │└DISTRIBUTE GATHER 26834 4726 │ │ INNER JOIN HASH ON id = keyword_id 26834 3 │ │ │└DISTRIBUTE GATHER 26834 3 │ │ │ FILTER ((keyword = 'sequel') OR (keyword = 'revenge')) OR (keyword = 'based-on-novel') 134170 134170 │ │ │ DISTRIBUTE ROUND ROBIN 134170 134170 │ │ │ TABLE SCAN keyword WHERE ((keyword = 'sequel') OR (keyword = 'revenge')) OR (keyword = 'based-on-novel') 93458 827544 │ │ INNER JOIN HASH ON movie_id = movie_id 52183 13192 │ │ │└DISTRIBUTE GATHER 52183 13192 │ │ │ INNER JOIN HASH ON id = company_type_id 1 3 │ │ │ │└DISTRIBUTE GATHER 1 3 │ │ │ │ FILTER (kind <> 'production companies') AND kind IS NOT NULL 4 4 │ │ │ │ DISTRIBUTE ROUND ROBIN 4 4 │ │ │ │ TABLE SCAN company_type WHERE (kind <> 'production companies') AND kind IS NOT NULL 104366 13233 │ │ │ INNER JOIN HASH ON id = company_id 47000 89 │ │ │ │└DISTRIBUTE GATHER 47000 89 │ │ │ │ FILTER (country_code <> 'pl') AND (name LIKE '20th Century Fox%' OR name LIKE 'Twentieth Century Fox%') 234997 234997 │ │ │ │ TABLE SCAN company_name WHERE (country_code <> 'pl') AND (name LIKE '20th Century Fox%' OR name LIKE 'Twentieth Century Fox%') 521826 1202647 │ │ │ FILTER note IS NOT NULL 2609129 1356171 │ │ │ TABLE SCAN movie_companies WHERE (note IS NOT NULL AND (((company_id >= 423) AND (company_id <= 70247)) AND company_id IN(63280,58583,11410,11408,52207,61361,18836,70246,9617,63279,12093,11964,3043,6726,18150,2982,460,1920,8002,424,12781,12065,34463,47849,9329,63935,45919,63281,9247,29349,1635,12062,42632,3815,52786,452,2346,12094,16574,44066,451,29571,13774,50022,49954,20442,63958,58879,18739,70247,44103,23687,14960,9133,19496,11412,461,31647,17627,23046,25729... 4523930 4523930 │ │ TABLE SCAN movie_keyword WHERE (((movie_id >= 8343) AND (movie_id <= 2525637)) AND TRUE) AND (((keyword_id >= 359) AND (keyword_id <= 398)) AND keyword_id IN(373,359,398)) 29997 29997 │ DISTRIBUTE ROUND ROBIN 29997 29997 │ TABLE SCAN movie_link WHERE ((((movie_id >= 8805) AND (movie_id <= 2517829)) AND ((movie_id >= 8805) AND (movie_id <= 2517829))) AND TRUE) AND (((link_type_id >= 1) AND (link_type_id <= 18)) AND link_type_id IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)) 1246097 1812583 FILTER production_year > 1950 2528312 2034729 TABLE SCAN title WHERE (production_year > 1950) AND (((((id >= 8805) AND (id <= 73570)) AND ((id >= 8805) AND (id <= 73570))) AND ((id >= 8805) AND (id <= 73570))) 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 > , < expr > , < expr > , < expr > , < ...
	Estimate Actual Operator 1 1 AGGREGATE MIN(cn.name), MIN(mc.note), MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(cn.name), MIN(mc.note), MIN(t.title) 1320 6946 INNER JOIN HASH ON ml.link_type_id = lt.id 1320 18 │└DISTRIBUTE GATHER 4520000 18 │ TABLE SCAN link_type 1320 6946 INNER JOIN HASH ON mc.movie_id = ml.movie_id 1320 29997 │└DISTRIBUTE GATHER 134000 29997 │ TABLE SCAN movie_link 297 4137 INNER JOIN HASH ON mc.company_id = cn.id 297 89 │└DISTRIBUTE GATHER 2530000 89 │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND ( NOT (cn.country_code = 'pl')) AND (startswith(cn.name,'20th Century Fox') OR startswith(cn.name,'Twentieth Century Fox')) 287 130792 INNER JOIN HASH ON mc.company_type_id = ct.id 287 3 │└DISTRIBUTE GATHER 4 3 │ TABLE SCAN company_type WHERE NOT (ct.kind = 'production companies') 287 143893 INNER JOIN HASH ON mk.movie_id = mc.movie_id 287 36063 │└DISTRIBUTE GATHER 112 36063 │ INNER JOIN HASH ON mk.movie_id = t.id 112 47403 │ │└DISTRIBUTE GATHER 106 47403 │ │ INNER JOIN HASH ON mk.keyword_id = k.id 106 3 │ │ │└DISTRIBUTE GATHER 18 3 │ │ │ TABLE SCAN keyword WHERE k.keyword IN('sequel','revenge','based-on-novel') 30000 4519842 │ │ TABLE SCAN movie_keyword 235000 2280440 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 1950L) 2610000 1333117 TABLE SCAN movie_companies WHERE mc.note IS NOT NULL
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1016, MIN(note as note) AS Expr1017, MIN(title as title) AS Expr1018 1 6946 INNER JOIN LOOP ON Bmk1014 = Bmk1014 1 6946 │└TABLE SEEK title AS t WHERE production_year as production_year > 1950 1 6946 PROJECT BmkToPage Bmk1014 AS Expr1041 1 6946 INNER JOIN LOOP ON ml.movie_id = t.id 1 6946 │└TABLE SEEK title AS t 1 6946 FILTER (name as name LIKE '20th Century Fox%' OR name as name LIKE 'Twentieth Century Fox%') AND country_code as country_code <> 'pl' 83 14916 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 14916 │└TABLE SEEK company_name AS cn 83 14916 INNER JOIN LOOP ON mc.company_id = cn.id 1 14916 │└TABLE SEEK company_name AS cn 83 14916 INNER JOIN HASH ON mc.company_type_id = ct.id 3 3 │└TABLE SCAN company_type AS ct WHERE kind as kind <> 'production companies' 110 14916 FILTER note as note IS NOT NULL 217 15331 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 15331 │└TABLE SEEK movie_companies AS mc WHERE BLOOM(company_type_id as company_type_id) 217 19086 PROJECT BmkToPage Bmk1008 AS Expr1037 217 19086 INNER JOIN LOOP ON ml.movie_id = mc.movie_id 9 19086 │└TABLE SEEK movie_companies AS mc 22 1460 INNER JOIN HASH ON ml.movie_id = mk.movie_id 272 47403 │└INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 47403 │ │└TABLE SEEK movie_keyword AS mk 272 47403 │ INNER JOIN LOOP ON k.id = mk.keyword_id 90 47403 │ │└TABLE SEEK movie_keyword AS mk 3 3 │ TABLE SCAN keyword AS k WHERE keyword as keyword = 'based-on-novel' OR keyword as keyword = 'revenge' OR keyword as keyword = 'sequel' 29 1414 TABLE SEEK movie_link AS ml WHERE BLOOM(movie_id as movie_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS from_company, min_33 AS production_note, min_34 AS movie_based_on_book 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(note) AS min_36, MIN(title) AS min_37 - 6946 INNER JOIN HASH ON movie_id = id_26 1219274 2287271 │└DISTRIBUTE HASH ON id_26 1219274 2287271 │ PROJECT id AS id_26, title 1219274 2287271 │ FILTER production_year > 1950 1219274 2287271 │ TABLE SCAN title - 6946 INNER JOIN HASH ON link_type_id = id_8 18 18 │└DISTRIBUTE GATHER 18 18 │ PROJECT id AS id_8 18 18 │ TABLE SCAN link_type - 6946 INNER JOIN HASH ON movie_id = movie_id_22 29997 29997 │└DISTRIBUTE GATHER 29997 29997 │ PROJECT movie_id AS movie_id_22, link_type_id 29997 29997 │ TABLE SCAN movie_link - 42 INNER JOIN HASH ON keyword_id = id_4 134170 3 │└DISTRIBUTE GATHER 134170 3 │ PROJECT id AS id_4 134170 3 │ FILTER keyword IN('based-on-novel','revenge','sequel') 134170 3 │ TABLE SCAN keyword - 42 INNER JOIN HASH ON movie_id = movie_id_17 4523930 188 │└DISTRIBUTE HASH ON movie_id_17 4523930 188 │ PROJECT movie_id AS movie_id_17, keyword_id 4523930 188 │ TABLE SCAN movie_keyword - 42 INNER JOIN HASH ON company_type_id = id_0 2 3 │└DISTRIBUTE GATHER 2 3 │ PROJECT id AS id_0 2 3 │ FILTER kind <> 'production companies' 2 3 │ TABLE SCAN company_type - 42 INNER JOIN HASH ON company_id = id 94983 89 │└DISTRIBUTE GATHER 94983 89 │ FILTER (country_code <> 'pl') AND (((name >= '20th Century Fox') AND (name < '20th Century Foy')) OR ((name >= 'Twentieth Century Fox') AND (name < 'Twentieth Century Foy'))) AND ((name LIKE '20th Century Fox%') OR (name LIKE 'Twentieth Century Fox%')) 94983 89 │ TABLE SCAN company_name 1337140 42 FILTER NOT (note IS NULL) 1337140 42 TABLE SCAN movie_companies
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(note), MIN(title) 1 6946 INNER JOIN LOOP ON id = link_type_id 1 6946 │└INNER JOIN LOOP ON movie_id = id 1 4137 │ │└INNER JOIN LOOP ON id = movie_id 1 4726 │ │ │└INNER JOIN LOOP ON id = company_type_id AND (id = company_type_id) 1 4921 │ │ │ │└INNER JOIN HASH ON company_id = id 42 89 │ │ │ │ │└TABLE SEEK company_name AS cn WHERE (cn.country_code <> 'pl') AND ((cn.name LIKE '20th Century Fox%') OR (cn.name LIKE 'Twentieth Century Fox%')) 265 169414 │ │ │ │ INNER JOIN LOOP ON movie_id = movie_id 101 47403 │ │ │ │ │└INNER JOIN LOOP ON keyword_id = id 3 3 │ │ │ │ │ │└TABLE SEEK keyword AS k 915 47403 │ │ │ │ │ TABLE SEEK movie_keyword AS mk 142209 169228 │ │ │ │ TABLE SEEK movie_companies AS mc WHERE mc.note IS NOT NULL 3 1 │ │ │ TABLE SCAN company_type AS ct WHERE ct.kind <> 'production companies' 4726 4726 │ │ TABLE SEEK title AS t WHERE t.production_year > 1950 20685 6950 │ TABLE SEEK movie_link AS ml 6946 6946 TABLE SEEK link_type AS lt