Planner — IMDB — JOB-2D

SELECT MIN(t.title) AS movie_title
FROM job.company_name AS cn,
     job.keyword AS k,
     job.movie_companies AS mc,
     job.movie_keyword AS mk,
     job.title AS t
WHERE cn.country_code ='[us]'
  AND k.keyword ='character-name-in-title'
  AND cn.id = mc.company_id
  AND mc.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND mc.movie_id = mk.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 9,670,808 9.7M Rank		Estimation Error Est Err 9,729,687 9.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 407,260 407K Rank	Estimation Error Est Err 68,316 68K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,661,372 9.7M Rank		Estimation Error Est Err 7,094,082 7.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,692,809 2.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,730,033 9.7M Rank		Estimation Error Est Err 9,793,734 9.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 317,068 317K Rank	Estimation Error Est Err 68,317 68K Rank	Estimation Error Est Err 168,525 169K Rank
Estimation Error Est Err 10,030,538 10M Rank		Estimation Error Est Err 29,492,703 29M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 13,468,061 13M Rank	Estimation Error Est Err 68,326 68K Rank	Estimation Error Est Err 8,493,380 8.5M Rank
Estimation Error Est Err 326,035 326K Rank		Estimation Error Est Err 368,610 369K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 407,258 407K Rank	Estimation Error Est Err 68,316 68K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 380,765 381K Rank	Estimation Error Est Err 380,765 381K Rank	Estimation Error Est Err 380,784 381K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 380,784 381K Rank	Estimation Error Est Err 68,316 68K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 526,915 527K Rank	Estimation Error Est Err 517,416 517K Rank	Estimation Error Est Err 585,732 586K Rank	Estimation Error Est Err 68,316 68K Rank	Estimation Error Est Err 665,968 666K Rank	Estimation Error Est Err 68,326 68K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,728,076 2.7M Rank		Estimation Error Est Err 273,223 273K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,813,431 2.8M Rank	Estimation Error Est Err 68,332 68K Rank	Estimation Error Est Err 2,718,656 2.7M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min 120 68316 INNER JOIN HASH ON movie_id14 = id31 104 68316 │└INNER JOIN HASH ON id21 = company_id 284 148552 │ │└INNER JOIN HASH ON movie_id14 = movie_id 32 41840 │ │ │└INNER JOIN HASH ON keyword_id = id 1 1 │ │ │ │└TABLE SCAN keyword WHERE keyword = character - name - in - title 4523930 4523930 │ │ │ TABLE SCAN movie_keyword 2609129 2609129 │ │ TABLE SCAN movie_companies 90648 9436 │ TABLE SCAN company_name WHERE country_code = us 2528312 2528312 TABLE SCAN title
	Estimate Actual Operator - 1 PROJECT a1 AS movie_title - 1 AGGREGATE MIN(title) AS a1 - 0 PROJECT title - 0 PROJECT title - 0 INNER JOIN HASH ON tuple(PROJECTION_4151.movie_id,PROJECTION_4151.id) = tuple(PROJECTION_4142.movie_id,PROJECTION_4142.movie_id) - 0 │└PROJECT movie_id AS movie_id_right - 0 │ PROJECT movie_id - 0 │ INNER JOIN HASH ON PROJECTION_4148.id = PROJECTION_4145.company_id - 2609129 │ │└PROJECT company_id, movie_id - 2609129 │ │ PROJECT company_id, movie_id - 2609129 │ │ TABLE SCAN movie_companies - 0 │ PROJECT id - 0 │ PROJECT id - 0 │ TABLE SCAN company_name WHERE country_code = 'us' - 41840 PROJECT movie_id AS movie_id_left, id, title - 41840 PROJECT movie_id, title, id - 41840 INNER JOIN HASH ON PROJECTION_4163.id = PROJECTION_4154.movie_id - 41840 │└PROJECT movie_id - 41840 │ PROJECT movie_id - 41840 │ INNER JOIN HASH ON PROJECTION_4160.keyword_id = PROJECTION_4157.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN keyword WHERE keyword = 'character-name-in-title' - 4523930 │ PROJECT keyword_id, movie_id - 4523930 │ PROJECT movie_id, keyword_id - 4523930 │ TABLE SCAN movie_keyword - 2528312 PROJECT id, title - 2528312 PROJECT title, id - 2528312 TABLE SCAN title
	Estimate Actual Operator - 1 AGGREGATE MIN(#0) 0 68316 PROJECT title 0 68316 INNER JOIN HASH ON id = movie_id 0 68316 │└INNER JOIN HASH ON id = company_id 72 148552 │ │└INNER JOIN HASH ON movie_id = movie_id 69 41838 │ │ │└INNER JOIN HASH ON keyword_id = id 2 1 │ │ │ │└TABLE SCAN keyword WHERE keyword = 'character-name-in-title' 4523930 41838 │ │ │ TABLE SCAN movie_keyword WHERE movie_id <= 2525745 2609129 173613 │ │ TABLE SCAN movie_companies 1644 84843 │ TABLE SCAN company_name WHERE country_code = 'us' 2528312 25740 TABLE SCAN title WHERE id >= 2 AND id <= 2525745
	Estimate Actual Operator 1 1 PROJECT movie_title 1 1 AGGREGATE MIN(title) 186916 10 DISTRIBUTE GATHER 186916 10 AGGREGATE MIN(title) 186916 68316 INNER JOIN HASH ON movie_id = id AND movie_id = id 186916 68316 │└DISTRIBUTE HASH ON movie_id, movie_id 186916 68316 │ INNER JOIN HASH ON id = keyword_id 26834 1 │ │└DISTRIBUTE GATHER 26834 1 │ │ FILTER keyword = 'character-name-in-title' 134170 134170 │ │ DISTRIBUTE ROUND ROBIN 134170 134170 │ │ TABLE SCAN keyword WHERE keyword = 'character-name-in-title' 934584 12177631 │ INNER JOIN HASH ON movie_id = movie_id 521832 1153798 │ │└DISTRIBUTE HASH ON movie_id 521832 1153798 │ │ INNER JOIN HASH ON id = company_id 47000 84843 │ │ │└DISTRIBUTE GATHER 47000 84843 │ │ │ FILTER country_code = 'us' 234997 234997 │ │ │ TABLE SCAN company_name WHERE country_code = 'us' 2609129 2609129 │ │ TABLE SCAN movie_companies WHERE ((company_id >= 1) AND (company_id <= 234997)) AND TRUE 4523930 4523930 │ DISTRIBUTE HASH ON movie_id 4523930 4523930 │ TABLE SCAN movie_keyword WHERE CASE MOD(HASH_REPARTITION movie_id,10) WHEN 0 THEN (((movie_id >= 67) AND (movie_id <= 2525688)) AND TRUE) WHEN 1 THEN (((movie_id >= 45) AND (movie_id <= 2525732)) AND TRUE) WHEN 2 THEN (((movie_id >= 24) AND (movie_id <= 2525701)) AND TRUE) WHEN 3 THEN (((movie_id >= 2) AND (movie_id <= 2525729)) AND TRUE) WHEN 4 THEN (((movie_id >= 55) AND (movie_id <= 2525738)) AND TRUE) WHEN 5 THEN (((movie_id >= 54) AND (movie_id <= 2525715)) AND TR... 2528312 2528312 DISTRIBUTE HASH ON id, id 2528312 2528312 TABLE SCAN title WHERE CASE MOD(HASH_REPARTITION(id,id),10) WHEN 1 THEN ((((id >= 2558) AND (id <= 2524485)) AND ((id >= 2558) AND (id <= 2524485))) AND TRUE) WHEN 3 THEN ((((id >= 3532) AND (id <= 2524891)) AND ((id >= 3532) AND (id <= 2524891))) AND TRUE) WHEN 5 THEN ((((id >= 6313) AND (id <= 2521831)) AND ((id >= 6313) AND (id <= 2521831))) AND TRUE) WHEN 7 THEN ((((id >= 4732) AND (id <= 2523559)) AND ((id >= 4732) AND (id <= 2523559))) AND TRUE) WHEN 9 THEN ((((id >=...
	Estimate Actual Operator 1 1 AGGREGATE MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(t.title) 199 68316 INNER JOIN HASH ON mc.company_id = cn.id 199 84843 │└DISTRIBUTE GATHER 4520000 84843 │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 192 148545 INNER JOIN HASH ON mk.movie_id = mc.movie_id 192 41840 │└DISTRIBUTE GATHER 75 41840 │ INNER JOIN HASH ON mk.movie_id = t.id 75 41840 │ │└DISTRIBUTE GATHER 71 41840 │ │ INNER JOIN HASH ON mk.keyword_id = k.id 71 1 │ │ │└DISTRIBUTE GATHER 235000 1 │ │ │ TABLE SCAN keyword WHERE k.keyword = 'character-name-in-title' 2610000 4519861 │ │ TABLE SCAN movie_keyword 2530000 2520276 │ TABLE SCAN title 134000 2605052 TABLE SCAN movie_companies
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1015) AS Expr1010 5 10 AGGREGATE MIN(title as title) AS partialagg1015 323 68316 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 68316 │└TABLE SEEK title AS t 323 68316 SORT Expr1019 323 68316 PROJECT BmkToPage Bmk1008 AS Expr1019 323 68316 INNER JOIN LOOP ON mk.movie_id = t.id 1 68316 │└TABLE SEEK title AS t 323 68316 INNER JOIN HASH ON cn.id = mc.company_id 899 148552 │└INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 148552 │ │└TABLE SEEK movie_companies AS mc 899 148552 │ PROJECT BmkToPage Bmk1004 AS Expr1023 899 148552 │ INNER JOIN LOOP ON mk.movie_id = mc.movie_id 9 148552 │ │└TABLE SEEK movie_companies AS mc 90 41840 │ INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 41840 │ │└TABLE SEEK movie_keyword AS mk 90 41840 │ INNER JOIN LOOP ON k.id = mk.keyword_id 90 41840 │ │└TABLE SEEK movie_keyword AS mk 1 1 │ TABLE SCAN keyword AS k WHERE keyword as keyword = 'character-name-in-title' 84576 9498 TABLE SCAN company_name AS cn WHERE (PROBE(Bitmap1025,id as id,N'IN ROW')) AND (country_code as country_code = 'us')
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_title 1 1 AGGREGATE MIN(min_20) AS min - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(title) AS min_20 - 68316 INNER JOIN HASH ON movie_id = id_13 2528312 2528312 │└DISTRIBUTE HASH ON id_13 2528312 2528312 │ PROJECT id AS id_13, title 2528312 2528312 │ TABLE SCAN title - 68316 INNER JOIN HASH ON keyword_id = id_0 134170 1 │└DISTRIBUTE GATHER 134170 1 │ PROJECT id AS id_0 134170 1 │ FILTER keyword = 'character-name-in-title' 134170 1 │ TABLE SCAN keyword - 68316 INNER JOIN HASH ON movie_id = movie_id_9 4523930 41840 │└DISTRIBUTE HASH ON movie_id_9 4523930 41840 │ PROJECT movie_id AS movie_id_9, keyword_id 4523930 41840 │ TABLE SCAN movie_keyword - 68275 INNER JOIN HASH ON id = company_id 2609129 148487 │└DISTRIBUTE HASH ON company_id 2609129 148487 │ TABLE SCAN movie_companies 211497 9436 FILTER country_code = 'us' 211497 9436 TABLE SCAN company_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(title) 63 68316 INNER JOIN LOOP ON id = company_id 173 148552 │└INNER JOIN LOOP ON movie_id = id 34 41840 │ │└INNER JOIN LOOP ON id = movie_id 34 41840 │ │ │└INNER JOIN LOOP ON keyword_id = id 1 1 │ │ │ │└TABLE SEEK keyword AS k 305 41840 │ │ │ TABLE SEEK movie_keyword AS mk 41840 41840 │ │ TABLE SEEK title AS t 209200 148532 │ TABLE SEEK movie_companies AS mc 148552 148552 TABLE SEEK company_name AS cn WHERE cn.country_code = 'us'