Planner — IMDB — JOB-32B

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 ='character-name-in-title'
  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 9,610,570 9.6M Rank		Estimation Error Est Err 9,614,939 9.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 96,844 97K Rank	Estimation Error Est Err 4,388 4.4K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,610,570 9.6M Rank		Estimation Error Est Err 125,520 126K 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 9,434,330 9.4M Rank		Estimation Error Est Err 6,990,891 7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 122,453 122K Rank	Estimation Error Est Err 34,387 34K Rank	Estimation Error Est Err 136,241 136K Rank
Estimation Error Est Err 7,462,187 7.5M Rank		Estimation Error Est Err 7,306,778 7.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 122,453 122K Rank	Estimation Error Est Err 4,398 4.4K Rank	Estimation Error Est Err 426,169 426K Rank
Estimation Error Est Err 19,994 20K Rank		Estimation Error Est Err 28,206 28K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 19,785 20K Rank	Estimation Error Est Err 4,388 4.4K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 96,845 97K Rank	Estimation Error Est Err 96,845 97K Rank	Estimation Error Est Err 96,844 97K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 96,844 97K Rank	Estimation Error Est Err 4,388 4.4K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 114,397 114K Rank	Estimation Error Est Err 114,396 114K Rank	Estimation Error Est Err 114,396 114K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 151,848 152K Rank	Estimation Error Est Err 4,388 4.4K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,111,711 5.1M Rank		Estimation Error Est Err 18,092 18K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,116,080 5.1M Rank	Estimation Error Est Err 4,404 4.4K 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 4388 INNER JOIN HASH ON id40 = movie_id 9 4388 │└INNER JOIN HASH ON linked_movie_id = id25 2 4388 │ │└INNER JOIN HASH ON id20 = link_type_id 2 4388 │ │ │└INNER JOIN HASH ON movie_id = movie_id14 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 29997 29997 │ │ │ TABLE SCAN movie_link 18 18 │ │ TABLE SCAN link_type 2528312 2528312 │ TABLE SCAN title 2528312 2528312 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_4617.linked_movie_id = PROJECTION_4614.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_4623.link_type_id = PROJECTION_4620.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_4629.id = PROJECTION_4626.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 - 41840 PROJECT id, title - 41840 PROJECT title, id - 41840 INNER JOIN HASH ON tuple(PROJECTION_4635.movie_id,PROJECTION_4635.movie_id) = tuple(PROJECTION_4632.id,PROJECTION_4632.id) - 2528312 │└PROJECT id, title - 2528312 │ PROJECT title, id - 2528312 │ TABLE SCAN title - 41840 PROJECT movie_id - 41840 PROJECT movie_id - 41840 INNER JOIN HASH ON PROJECTION_4641.id = PROJECTION_4638.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 = 'character-name-in-title'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 0 4388 PROJECT link, title, title 0 4388 INNER JOIN HASH ON id = movie_id 0 4388 │└INNER JOIN HASH ON id = linked_movie_id 0 4388 │ │└INNER JOIN HASH ON id = link_type_id 0 4388 │ │ │└INNER JOIN HASH ON movie_id = movie_id 69 2233 │ │ │ │└INNER JOIN HASH ON keyword_id = id 2 1 │ │ │ │ │└TABLE SCAN keyword WHERE keyword = 'character-name-in-title' 4523930 2233 │ │ │ │ TABLE SCAN movie_keyword WHERE movie_id <= 186175 29997 4630 │ │ │ TABLE SCAN movie_link 18 17 │ │ TABLE SCAN link_type WHERE id <= 17 2528312 12567 │ TABLE SCAN title WHERE id >= 284 AND id <= 2524994 2528312 546 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 4388 PROJECT link, title, title 29997 4388 INNER JOIN HASH ON linked_movie_id = id 29997 4388 │└DISTRIBUTE GATHER 29997 4388 │ PROJECT title, linked_movie_id, link 29997 4388 │ INNER JOIN HASH ON id = link_type_id 18 18 │ │└TABLE SCAN link_type 29997 4388 │ PROJECT title, linked_movie_id, link_type_id 29997 4388 │ 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 2540 │ INNER JOIN HASH ON movie_id = id 904786 41840 │ │└DISTRIBUTE HASH ON movie_id 904786 41840 │ │ 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' 4523930 4523930 │ │ TABLE SCAN movie_keyword WHERE ((keyword_id >= 117) AND (keyword_id <= 117)) AND keyword_id IN 117 2528312 245760 │ DISTRIBUTE HASH ON id 2528312 245760 │ TABLE SCAN title WHERE CASE MOD(HASH_REPARTITION id,10) WHEN 0 THEN (((id >= 826) AND (id <= 2525771)) AND TRUE) WHEN 1 THEN (((id >= 11076) AND (id <= 2524986)) AND TRUE) WHEN 2 THEN (((id >= 979) AND (id <= 2524891)) AND TRUE) WHEN 3 THEN (((id >= 318) AND (id <= 2525672)) AND TRUE) WHEN 4 THEN (((id >= 980) AND (id <= 2523559)) AND TRUE) WHEN 5 THEN (((id >= 6313) AND (id <= 2524079)) AND TRUE) WHEN 6 THEN (((id >= 983) AND (id <= 2525667)) AND TRUE) WHEN 7 THEN (((... 2528312 2528312 TABLE SCAN title WHERE ((id >= 6606) AND (id <= 2523213)) AND TRUE
	Estimate Actual Operator 2 0 SEQUENCE 1 1 ├─AGGREGATE MIN(lt.link), MIN(t1.title), MIN(t2.title) 1 2 │ DISTRIBUTE GATHER 1 2 │ AGGREGATE MIN(lt.link), MIN(t1.title), MIN(t2.title) 352 4388 │ INNER JOIN HASH ON ml.linked_movie_id = t2.id 352 4388 │ │└DISTRIBUTE GATHER 334 4388 │ │ INNER JOIN HASH ON ml.link_type_id = lt.id 334 18 │ │ │└DISTRIBUTE GATHER 2530000 18 │ │ │ TABLE SCAN link_type 334 4388 │ │ INNER JOIN HASH ON t1.id = ml.movie_id 334 29997 │ │ │└DISTRIBUTE GATHER 30000 29997 │ │ │ TABLE SCAN movie_link 75 37169 │ │ INNER JOIN HASH ON mk.movie_id = t1.id 75 41840 │ │ │└DISTRIBUTE GATHER 71 41840 │ │ │ INNER JOIN HASH ON k.id = mk.keyword_id 71 1 │ │ │ │└DISTRIBUTE GATHER - 1 │ │ │ │ TABLE SCAN keyword WHERE k.keyword = 'character-name-in-title' 2530000 4519861 │ │ │ TABLE SCAN movie_keyword 18 2334315 │ │ TABLE SCAN title 134000 95158 │ FILTER 134000 2520141 │ TABLE SCAN title 2 0 └─FILTER 334 1 DISTRIBUTE HASH 334 1 AGGREGATE bloom_filter_agg(bloom_expr(ml.linked_movie_id,ml.linked_movie_id),29997L,131072L) 4520000 29997 DISTRIBUTE HASH 4520000 29997 DISTRIBUTE HASH 4520000 29997 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 4388 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 4388 │└TABLE SEEK title AS t2 29 4388 PROJECT BmkToPage Bmk1010 AS Expr1027 29 4388 INNER JOIN LOOP ON ml.linked_movie_id = t2.id 1 4388 │└TABLE SEEK title AS t2 7 4388 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 4388 │└TABLE SEEK title AS t1 7 4388 PROJECT BmkToPage Bmk1008 AS Expr1026 7 4388 INNER JOIN LOOP ON ml.movie_id = t1.id 1 4388 │└TABLE SEEK title AS t1 7 4388 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 4388 │└TABLE SEEK link_type AS lt 7 4388 INNER JOIN LOOP ON ml.link_type_id = lt.id 1 4388 │└TABLE SEEK link_type AS lt 7 4388 INNER JOIN HASH ON ml.movie_id = mk.movie_id 90 41840 │└INNER JOIN LOOP ON Bmk1004 = Bmk1004 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' 2 4388 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 - 4388 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 - 4388 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 - 4388 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 - 4388 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 - 540 INNER JOIN HASH ON id = keyword_id 4523930 25071 │└DISTRIBUTE HASH ON keyword_id 4523930 25071 │ TABLE SCAN movie_keyword 120753 1 FILTER keyword = 'character-name-in-title' 120753 1 TABLE SCAN keyword
	Estimate Actual Operator 1 1 AGGREGATE MIN(link), MIN(title), MIN(title) 10 4388 INNER JOIN LOOP ON id = linked_movie_id 10 4388 │└INNER JOIN LOOP ON id = movie_id 10 4388 │ │└INNER JOIN LOOP ON id = link_type_id 10 4388 │ │ │└INNER JOIN LOOP ON movie_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 209200 41840 │ │ │ TABLE SEEK movie_link AS ml 4388 4388 │ │ TABLE SEEK link_type AS lt 4388 4388 │ TABLE SEEK title AS t1 4388 4388 TABLE SEEK title AS t2