Planner — IMDB — JOB-11D

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 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,866 4.6M Rank		Estimation Error Est Err 4,602,766 4.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 143,068 143K Rank	Estimation Error Est Err 14,899 15K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,795,591 9.8M 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,373,489 8.4M Rank		Estimation Error Est Err 8,551,495 8.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 624,494 624K Rank	Estimation Error Est Err 14,900 15K Rank	Estimation Error Est Err 323,519 324K Rank
Estimation Error Est Err 6,525,047 6.5M Rank		Estimation Error Est Err 53,303,750 53M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 28,008,886 28M Rank	Estimation Error Est Err 14,909 15K Rank	Estimation Error Est Err 1,715,259 1.7M Rank
Estimation Error Est Err 414,558 415K Rank		Estimation Error Est Err 3,727,538 3.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,751,594 6.8M Rank	Estimation Error Est Err 14,899 15K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 126,710 127K Rank	Estimation Error Est Err 126,692 127K Rank	Estimation Error Est Err 143,068 143K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 143,662 144K Rank	Estimation Error Est Err 14,899 15K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 190,273 190K Rank	Estimation Error Est Err 190,267 190K Rank	Estimation Error Est Err 208,984 209K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 251,172 251K Rank	Estimation Error Est Err 14,899 15K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,528,023 2.5M Rank		Estimation Error Est Err 46,633 47K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,543,555 2.5M Rank	Estimation Error Est Err 14,915 15K Rank	Estimation Error Est Err 2,527,527 2.5M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 28 14899 INNER JOIN HASH ON company_id = id53 32 14916 │└INNER JOIN HASH ON company_type_id = id 3 3 │ │└TABLE SCAN company_type WHERE kind != production companies 21 15510 │ INNER JOIN HASH ON movie_id19 = movie_id46 4 1460 │ │└INNER JOIN HASH ON id40 = link_type_id 4 1460 │ │ │└INNER JOIN HASH ON id25 = movie_id19 7 1460 │ │ │ │└INNER JOIN HASH ON movie_id19 = movie_id 97 47403 │ │ │ │ │└INNER JOIN HASH ON keyword_id = id6 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 2286344 181 │ │ │ TABLE SCAN title WHERE production_year >= 1951 18 18 │ │ TABLE SCAN link_type 1368263 517 │ TABLE SCAN movie_companies 210441 217 TABLE SCAN company_name WHERE country_code <> pl
	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_278.keyword_id = PROJECTION_275.id - ∞ │└PROJECT id - ∞ │ FILTER (1 AND in(keyword,__set_String_3121726789508643859_9359213533155979499)) AS a28 - 134170 │ TABLE SCAN keyword WHERE TRUE - 0 PROJECT keyword_id, name, note, title - 0 PROJECT name, note, keyword_id, title - 0 INNER JOIN HASH ON tuple(PROJECTION_284.movie_id,PROJECTION_284.movie_id,PROJECTION_284.id) = tuple(PROJECTION_281.movie_id,PROJECTION_281.movie_id,PROJECTION_281.movie_id) - 4523930 │└PROJECT movie_id AS movie_id_right, keyword_id - 4523930 │ PROJECT movie_id, keyword_id - 4523930 │ TABLE SCAN movie_keyword - 102510 PROJECT movie_id_left, movie_id AS movie_id_left_2, id, name, note, title - 102510 PROJECT name, note, movie_id, movie_id, title, id - 102510 INNER JOIN HASH ON tuple(PROJECTION_290.movie_id,PROJECTION_290.movie_id) = tuple(PROJECTION_287.id,PROJECTION_287.id) - 2287271 │└PROJECT id, title - 2287271 │ FILTER (1 AND production_year > 1950_UInt16) AS a24 - 2287271 │ TABLE SCAN title WHERE production_year > 1950 - 102654 PROJECT movie_id, movie_id, name, note - 102654 PROJECT name, note, movie_id, movie_id - 102654 INNER JOIN HASH ON PROJECTION_296.link_type_id = PROJECTION_293.id - 18 │└PROJECT id - 18 │ PROJECT id - 18 │ TABLE SCAN link_type - 102654 PROJECT link_type_id, name, note, movie_id, movie_id - 102654 PROJECT name, note, movie_id, movie_id, link_type_id - 102654 INNER JOIN HASH ON PROJECTION_302.movie_id = PROJECTION_299.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 - 1248210 PROJECT movie_id AS movie_id_left, name, note - 1248210 PROJECT name, note, movie_id - 1248210 INNER JOIN HASH ON PROJECTION_308.company_type_id = PROJECTION_305.id - 3 │└PROJECT id - 3 │ FILTER (kind <> 'production companies'_String AND 1) AS a20 - 3 │ TABLE SCAN company_type WHERE kind <> 'production companies' - 2497734 PROJECT company_type_id, name, note, movie_id - 2497734 PROJECT name, note, company_type_id, movie_id - 2497734 INNER JOIN HASH ON PROJECTION_314.id = PROJECTION_311.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 - 211073 PROJECT id, name - 211073 FILTER (country_code <> 'pl'_String AND 1) AS a16 - 211073 TABLE SCAN company_name WHERE country_code <> 'pl'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 24 14899 PROJECT name, note, title 24 14899 INNER JOIN HASH ON link_type_id = id 18 17 │└TABLE SCAN link_type WHERE id <= 17 24 14899 INNER JOIN HASH ON id = keyword_id 116 3200479 │└INNER JOIN HASH ON movie_id = movie_id 64 95870 │ │└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 103282 │ │ 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 111121 │ │ TABLE SCAN company_name WHERE country_code != 'pl' 4523930 17617 │ TABLE SCAN movie_keyword WHERE movie_id <= 186175 26834 3 FILTER (keyword = 'sequel') OR (keyword = 'revenge') OR (keyword = 'based-on-novel') 134170 21360 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 14899 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id 26834 14899 │└DISTRIBUTE GATHER 26834 14899 │ INNER JOIN HASH ON id = link_type_id 18 18 │ │└TABLE SCAN link_type 26834 14899 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 26834 151940 │ │└DISTRIBUTE GATHER 26834 151940 │ │ 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 25320459 │ │ INNER JOIN HASH ON movie_id = movie_id 52183 1174202 │ │ │└DISTRIBUTE GATHER 52183 1174202 │ │ │ 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 1180547 │ │ │ INNER JOIN HASH ON id = company_id 47000 210031 │ │ │ │└DISTRIBUTE GATHER 47000 210031 │ │ │ │ FILTER country_code <> 'pl' 234997 234997 │ │ │ │ TABLE SCAN company_name WHERE country_code <> 'pl' 521826 1202647 │ │ │ FILTER note IS NOT NULL 2609129 1356171 │ │ │ TABLE SCAN movie_companies WHERE (note IS NOT NULL AND (((company_id >= 1) AND (company_id <= 234997)) AND TRUE)) AND (((company_type_id >= 1) AND (company_type_id <= 4)) AND company_type_id IN(1,3,4)) 4523930 4523930 │ │ TABLE SCAN movie_keyword WHERE (((movie_id >= 2) AND (movie_id <= 2525671)) 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 >= 907) AND (movie_id <= 2525539)) AND ((movie_id >= 907) AND (movie_id <= 2525539))) 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 234742 FILTER production_year > 1950 2528312 245760 TABLE SCAN title WHERE (production_year > 1950) AND (((((id >= 907) AND (id <= 164032)) AND ((id >= 907) AND (id <= 164032))) AND ((id >= 907) AND (id <= 164032))) AND TRUE)
	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 14899 INNER JOIN HASH ON ml.link_type_id = lt.id 1320 18 │└DISTRIBUTE GATHER 4520000 18 │ TABLE SCAN link_type 1320 14899 INNER JOIN HASH ON mc.movie_id = ml.movie_id 1320 29997 │└DISTRIBUTE GATHER 134000 29997 │ TABLE SCAN movie_link 297 128398 INNER JOIN HASH ON mc.company_id = cn.id 297 210031 │└DISTRIBUTE GATHER 2530000 210031 │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND ( NOT (cn.country_code = 'pl')) 287 130830 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 143931 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 1333155 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 71 14899 INNER JOIN LOOP ON Bmk1014 = Bmk1014 0 14899 │└TABLE SEEK title AS t WHERE production_year as production_year > 1950 74 14899 PROJECT BmkToPage Bmk1014 AS Expr1039 74 14899 INNER JOIN LOOP ON ml.movie_id = t.id 1 14899 │└TABLE SEEK title AS t 74 14899 FILTER 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 Expr1034 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 - 14899 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 - 14899 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 - 14899 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 - 456 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 - 456 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 - 445 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 - 512 INNER JOIN HASH ON company_id = id 105537 210031 │└DISTRIBUTE GATHER 105537 210031 │ FILTER country_code <> 'pl' 105537 210031 │ TABLE SCAN company_name 1337140 512 FILTER NOT (note IS NULL) 1337140 512 TABLE SCAN movie_companies
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(note), MIN(title) 48 14899 INNER JOIN LOOP ON id = company_id 54 14916 │└INNER JOIN LOOP ON id = company_type_id 72 15510 │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = movie_id AND (movie_id = movie_id) 27 1460 │ │ │└INNER JOIN HASH ON link_type_id = id 18 18 │ │ │ │└TABLE SCAN link_type AS lt 27 1460 │ │ │ INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 30 1460 │ │ │ │└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 237015 47403 │ │ │ │ TABLE SEEK movie_link AS ml 1460 1460 │ │ │ TABLE SEEK title AS t WHERE t.production_year > 1950 4380 15505 │ │ TABLE SEEK movie_companies AS mc WHERE mc.note IS NOT NULL 2 2 │ TABLE SEEK company_type AS ct WHERE ct.kind <> 'production companies' 14916 14916 TABLE SEEK company_name AS cn WHERE cn.country_code <> 'pl'