Planner — IMDB — JOB-32A

SELECT MIN(lt.link) AS link_type,
       MIN(t1.title) AS first_movie,
       MIN(t2.title) AS second_movie
FROM job.keyword AS k,
     job.link_type AS lt,
     job.movie_keyword AS mk,
     job.movie_link AS ml,
     job.title AS t1,
     job.title AS t2
WHERE k.keyword ='10,000-mile-club'
  AND mk.keyword_id = k.id
  AND t1.id = mk.movie_id
  AND ml.movie_id = t1.id
  AND ml.linked_movie_id = t2.id
  AND lt.id = ml.link_type_id
  AND mk.movie_id = t1.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,928 4.6M Rank		Estimation Error Est Err 4,553,927 4.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,610,570 9.6M Rank		Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 9,610,569 9.6M Rank	Estimation Error Est Err 0 0 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 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,032,301 3M Rank		Estimation Error Est Err 2,868,116 2.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 30,017 30K Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 134,182 134K Rank
Estimation Error Est Err 26,357 26K Rank		Estimation Error Est Err 26,356 26K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,111,710 5.1M Rank		Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,111,710 5.1M Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 5,111,726 5.1M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 55 0 INNER JOIN HASH ON id40 = movie_id 9 0 │└INNER JOIN HASH ON linked_movie_id = id25 2 0 │ │└INNER JOIN HASH ON id20 = link_type_id 2 0 │ │ │└INNER JOIN HASH ON movie_id = movie_id14 32 1 │ │ │ │└INNER JOIN HASH ON keyword_id = id 1 1 │ │ │ │ │└TABLE SCAN keyword WHERE keyword = 10,000 - mile - club 4523930 4523930 │ │ │ │ TABLE SCAN movie_keyword 29997 29997 │ │ │ TABLE SCAN movie_link 18 0 │ │ TABLE SCAN link_type 2528312 0 │ TABLE SCAN title 2528312 0 TABLE SCAN title
	Estimate Actual Operator - 1 PROJECT a1 AS link_type, a2 AS first_movie, a3 AS second_movie - 1 AGGREGATE MIN(link) AS a1, MIN(title_left) AS a2, MIN(title_right) AS a3 - 0 PROJECT link, title, title - 0 PROJECT title, link, title - 0 INNER JOIN HASH ON PROJECTION_4582.linked_movie_id = PROJECTION_4579.id - 2528312 │└PROJECT id, title AS title_right - 2528312 │ PROJECT title, id - 2528312 │ TABLE SCAN title - 0 PROJECT linked_movie_id, title AS title_left, link - 0 PROJECT title, linked_movie_id, link - 0 INNER JOIN HASH ON PROJECTION_4588.link_type_id = PROJECTION_4585.id - 18 │└PROJECT id, link - 18 │ PROJECT link, id - 18 │ TABLE SCAN link_type - 0 PROJECT link_type_id, title, linked_movie_id - 0 PROJECT title, link_type_id, linked_movie_id - 0 INNER JOIN HASH ON PROJECTION_4594.id = PROJECTION_4591.movie_id - 29997 │└PROJECT movie_id, link_type_id, linked_movie_id - 29997 │ PROJECT movie_id, link_type_id, linked_movie_id - 29997 │ TABLE SCAN movie_link - 1 PROJECT id, title - 1 PROJECT title, id - 1 INNER JOIN HASH ON tuple(PROJECTION_4600.movie_id,PROJECTION_4600.movie_id) = tuple(PROJECTION_4597.id,PROJECTION_4597.id) - 2528312 │└PROJECT id, title - 2528312 │ PROJECT title, id - 2528312 │ TABLE SCAN title - 1 PROJECT movie_id - 1 PROJECT movie_id - 1 INNER JOIN HASH ON PROJECTION_4606.id = PROJECTION_4603.keyword_id - 4523930 │└PROJECT keyword_id, movie_id - 4523930 │ PROJECT keyword_id, movie_id - 4523930 │ TABLE SCAN movie_keyword - 1 PROJECT id - 1 PROJECT id - 1 TABLE SCAN keyword WHERE keyword = '10,000-mile-club'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 0 0 PROJECT link, title, title 0 0 INNER JOIN HASH ON id = movie_id 0 0 │└INNER JOIN HASH ON id = linked_movie_id 0 0 │ │└INNER JOIN HASH ON id = link_type_id 0 0 │ │ │└INNER JOIN HASH ON movie_id = movie_id 69 0 │ │ │ │└INNER JOIN HASH ON keyword_id = id 2 1 │ │ │ │ │└TABLE SCAN keyword WHERE keyword = '10,000-mile-club' 4523930 0 │ │ │ │ TABLE SCAN movie_keyword WHERE movie_id <= 186175 29997 2048 │ │ │ TABLE SCAN movie_link 18 17 │ │ TABLE SCAN link_type WHERE id <= 17 2528312 20476 │ TABLE SCAN title WHERE id >= 284 AND id <= 2524994 2528312 3815 TABLE SCAN title WHERE id >= 2 AND id <= 186175
	Estimate Actual Operator 1 1 PROJECT link_type, first_movie, second_movie 1 1 AGGREGATE MIN(link), MIN(title), MIN(title) 29997 10 DISTRIBUTE GATHER 29997 10 AGGREGATE MIN(link), MIN(title), MIN(title) 29997 0 PROJECT link, title, title 29997 0 INNER JOIN HASH ON linked_movie_id = id 29997 0 │└DISTRIBUTE GATHER 29997 0 │ PROJECT title, linked_movie_id, link 29997 0 │ INNER JOIN HASH ON id = link_type_id 18 18 │ │└TABLE SCAN link_type 29997 0 │ PROJECT title, linked_movie_id, link_type_id 29997 0 │ INNER JOIN HASH ON movie_id = id 29997 29997 │ │└TABLE SCAN movie_link WHERE ((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) 904786 0 │ INNER JOIN HASH ON movie_id = id 904786 1 │ │└DISTRIBUTE HASH ON movie_id 904786 1 │ │ INNER JOIN HASH ON id = keyword_id 26834 1 │ │ │└DISTRIBUTE GATHER 26834 1 │ │ │ FILTER keyword = '10,000-mile-club' 134170 134170 │ │ │ DISTRIBUTE ROUND ROBIN 134170 134170 │ │ │ TABLE SCAN keyword WHERE keyword = '10,000-mile-club' 4523930 339804 │ │ TABLE SCAN movie_keyword WHERE ((keyword_id >= 131852) AND (keyword_id <= 131852)) AND keyword_id IN 131852 2528312 0 │ DISTRIBUTE HASH ON id 2528312 0 │ TABLE SCAN title WHERE (((id >= 2469620) AND (id <= 2469620)) AND id IN 2469620) AND (((id >= 2) AND (id <= 186175)) AND TRUE) 2528312 2528312 TABLE SCAN title WHERE TRUE
	Estimate Actual Operator 1 1 AGGREGATE MIN(lt.link), MIN(t1.title), MIN(t2.title) 1 0 DISTRIBUTE GATHER 1 0 AGGREGATE MIN(lt.link), MIN(t1.title), MIN(t2.title) 352 ∞ DISTRIBUTE GATHER 352 ∞ INNER JOIN HASH ON ml.link_type_id = lt.id 352 ∞ │└DISTRIBUTE GATHER 134000 ∞ │ TABLE SCAN link_type 334 ∞ INNER JOIN HASH ON t1.id = ml.movie_id 334 ∞ │└DISTRIBUTE GATHER 334 ∞ │ INNER JOIN HASH ON mk.movie_id = t1.id 334 ∞ │ │└DISTRIBUTE GATHER 75 ∞ │ │ INNER JOIN HASH ON k.id = mk.keyword_id 75 ∞ │ │ │└DISTRIBUTE GATHER 18 ∞ │ │ │ TABLE SCAN keyword WHERE k.keyword = '10,000-mile-club' 30000 ∞ │ │ TABLE SCAN movie_keyword 2530000 ∞ │ TABLE SCAN title 4520000 ∞ TABLE SCAN movie_link
	Estimate Actual Operator 1 1 AGGREGATE MIN(link as link) AS Expr1012, MIN(title as title) AS Expr1013, MIN(title as title) AS Expr1014 29 0 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 0 │└TABLE SEEK title AS t2 29 ∞ PROJECT BmkToPage Bmk1010 AS Expr1027 29 0 INNER JOIN LOOP ON ml.linked_movie_id = t2.id 1 0 │└TABLE SEEK title AS t2 7 0 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 0 │└TABLE SEEK title AS t1 7 ∞ PROJECT BmkToPage Bmk1008 AS Expr1026 7 0 INNER JOIN LOOP ON ml.movie_id = t1.id 1 0 │└TABLE SEEK title AS t1 7 0 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 0 │└TABLE SEEK link_type AS lt 7 0 INNER JOIN LOOP ON ml.link_type_id = lt.id 1 0 │└TABLE SEEK link_type AS lt 7 0 INNER JOIN HASH ON ml.movie_id = mk.movie_id 90 1 │└INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 1 │ │└TABLE SEEK movie_keyword AS mk 90 1 │ INNER JOIN LOOP ON k.id = mk.keyword_id 90 1 │ │└TABLE SEEK movie_keyword AS mk 1 1 │ TABLE SCAN keyword AS k WHERE keyword as keyword = '10,000-mile-club' 2 0 TABLE SEEK movie_link AS ml WHERE BLOOM(movie_id as movie_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS link_type, min_33 AS first_movie, min_34 AS second_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(link) AS min_35, MIN(title) AS min_36, MIN(title_19) AS min_37 - 0 INNER JOIN HASH ON linked_movie_id = id_18 2528312 2528312 │└DISTRIBUTE HASH ON id_18 2528312 2528312 │ PROJECT id AS id_18, title AS title_19 2528312 2528312 │ TABLE SCAN title - 0 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 - 0 INNER JOIN HASH ON link_type_id = id_0 18 18 │└DISTRIBUTE GATHER 18 18 │ PROJECT id AS id_0, link 18 18 │ TABLE SCAN link_type - 0 INNER JOIN HASH ON movie_id = movie_id_9 29997 29997 │└DISTRIBUTE GATHER 29997 29997 │ PROJECT movie_id AS movie_id_9, linked_movie_id, link_type_id 29997 29997 │ TABLE SCAN movie_link - 0 INNER JOIN HASH ON id = keyword_id 4523930 25071 │└DISTRIBUTE HASH ON keyword_id 4523930 25071 │ TABLE SCAN movie_keyword 120753 0 FILTER keyword = '10,000-mile-club' 120753 0 TABLE SCAN keyword
	Estimate Actual Operator 1 1 AGGREGATE MIN(link), MIN(title), MIN(title) 10 1 INNER JOIN LOOP ON id = linked_movie_id 10 1 │└INNER JOIN LOOP ON id = movie_id 10 1 │ │└INNER JOIN LOOP ON id = link_type_id 10 1 │ │ │└INNER JOIN LOOP ON movie_id = movie_id 34 1 │ │ │ │└INNER JOIN LOOP ON keyword_id = id 1 1 │ │ │ │ │└TABLE SEEK keyword AS k 305 1 │ │ │ │ TABLE SEEK movie_keyword AS mk 5 1 │ │ │ TABLE SEEK movie_link AS ml 0 0 │ │ TABLE SEEK link_type AS lt 0 0 │ TABLE SEEK title AS t1 0 0 TABLE SEEK title AS t2