Planner — IMDB — JOB-6A

SELECT MIN(k.keyword) AS movie_keyword,
       MIN(n.name) AS actor_name,
       MIN(t.title) AS marvel_movie
FROM job.cast_info AS ci,
     job.keyword AS k,
     job.movie_keyword AS mk,
     job.name AS n,
     job.title AS t
WHERE k.keyword = 'marvel-cinematic-universe'
  AND n.name LIKE '%Downey%Robert%'
  AND t.production_year > 2010
  AND k.id = mk.keyword_id
  AND t.id = mk.movie_id
  AND t.id = ci.movie_id
  AND ci.movie_id = mk.movie_id
  AND n.id = ci.person_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 40,768,287 41M Rank		Estimation Error Est Err 40,768,291 41M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,476 2.5K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 41,159,943 41M Rank		Estimation Error Est Err 41,160,426 41M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 525 525 Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 41,135,368 41M Rank		Estimation Error Est Err 40,744,246 41M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 392,180 392K Rank	Estimation Error Est Err 28 28 Rank	Estimation Error Est Err 36,636,049 37M Rank
Estimation Error Est Err 46,121,021 46M Rank		Estimation Error Est Err 292,180,210 292M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 146,482,395 146M Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 39,818,145 40M Rank
Estimation Error Est Err 1,385 1.4K Rank		Estimation Error Est Err 1,389 1.4K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,476 2.5K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,476 2.5K Rank	Estimation Error Est Err 2,476 2.5K Rank	Estimation Error Est Err 2,476 2.5K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,476 2.5K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,950 5K Rank	Estimation Error Est Err 4,949 4.9K Rank	Estimation Error Est Err 4,952 5K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,949 4.9K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 391,689 392K Rank		Estimation Error Est Err 24 24 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 391,692 392K Rank	Estimation Error Est Err 22 22 Rank	Estimation Error Est Err 391,699 392K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 1 6 INNER JOIN HASH ON id38 = person_id 73 1224 │└INNER JOIN HASH ON id13 = movie_id30 5 11 │ │└INNER JOIN HASH ON id13 = movie_id 32 14 │ │ │└INNER JOIN HASH ON id = keyword_id 1 1 │ │ │ │└TABLE SCAN keyword WHERE keyword = marvel - cinematic - universe 4523930 4523930 │ │ │ TABLE SCAN movie_keyword 365420 11 │ │ TABLE SCAN title WHERE production_year >= 2011 36244344 36244344 │ TABLE SCAN cast_info 2821 1 TABLE SCAN name WHERE name LIKE '%Downey%Robert%'
	Estimate Actual Operator - 1 PROJECT a1 AS movie_keyword, a2 AS actor_name, a3 AS marvel_movie - 1 AGGREGATE MIN(keyword) AS a1, MIN(name) AS a2, MIN(title) AS a3 - 6 PROJECT keyword, name, title - 6 PROJECT keyword, name, title - 6 INNER JOIN HASH ON tuple(PROJECTION_5156.movie_id,PROJECTION_5156.movie_id) = tuple(PROJECTION_5141.movie_id,PROJECTION_5141.id) - 11 │└PROJECT movie_id AS movie_id_right, id, keyword, title - 11 │ PROJECT movie_id, keyword, title, id - 11 │ INNER JOIN HASH ON PROJECTION_5153.id = PROJECTION_5144.movie_id - 14 │ │└PROJECT movie_id, keyword - 14 │ │ PROJECT movie_id, keyword - 14 │ │ INNER JOIN HASH ON PROJECTION_5150.keyword_id = PROJECTION_5147.id - 1 │ │ │└PROJECT id, keyword - 1 │ │ │ PROJECT id, keyword - 1 │ │ │ TABLE SCAN keyword WHERE keyword = 'marvel-cinematic-universe' - 4523930 │ │ PROJECT keyword_id, movie_id - 4523930 │ │ PROJECT movie_id, keyword_id - 4523930 │ │ TABLE SCAN movie_keyword - 391666 │ PROJECT id, title - 391666 │ PROJECT id, title - 391666 │ TABLE SCAN title WHERE production_year > 2010 - 486 PROJECT movie_id AS movie_id_left, name - 486 PROJECT movie_id, name - 486 INNER JOIN HASH ON PROJECTION_5162.person_id = PROJECTION_5159.id - 2 │└PROJECT id, name - 2 │ PROJECT id, name - 2 │ TABLE SCAN name WHERE name LIKE '%Downey%Robert%' - 36244344 PROJECT person_id, movie_id - 36244344 PROJECT movie_id, person_id - 36244344 TABLE SCAN cast_info
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 47 6 PROJECT keyword, name, title 47 6 INNER JOIN HASH ON id = person_id 206 1224 │└INNER JOIN HASH ON movie_id = id 14 11 │ │└INNER JOIN HASH ON id = movie_id 69 14 │ │ │└INNER JOIN HASH ON keyword_id = id 2 1 │ │ │ │└TABLE SCAN keyword WHERE keyword = 'marvel-cinematic-universe' 4523930 14 │ │ │ TABLE SCAN movie_keyword 505662 145 │ │ FILTER id BETWEEN 2 AND 2525971 505662 145 │ │ TABLE SCAN title WHERE production_year > 2010 36244344 1224 │ TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525971 833498 1 FILTER id <= 4061926 833498 1 TABLE SCAN "name" WHERE name LIKE '%Downey%Robert%'
	Estimate Actual Operator 1 1 PROJECT movie_keyword, actor_name, marvel_movie 1 1 AGGREGATE MIN(keyword), MIN(name), MIN(title) 171414 10 DISTRIBUTE GATHER 171414 10 AGGREGATE MIN(keyword), MIN(name), MIN(title) 171414 6 PROJECT keyword, name, title 171414 6 INNER JOIN HASH ON id = movie_id AND id = movie_id 162535 391666 │└DISTRIBUTE HASH ON id, id 162535 391666 │ FILTER production_year > 2010 2528312 2528312 │ TABLE SCAN title WHERE production_year > 2010 2663974 6 DISTRIBUTE HASH ON movie_id, movie_id 2663974 6 PROJECT movie_id, movie_id, keyword, name 2663974 6 INNER JOIN HASH ON id = person_id 833499 2 │└DISTRIBUTE HASH ON id 833499 2 │ FILTER name LIKE '%Downey%Robert%' 4167491 4167491 │ TABLE SCAN name WHERE name LIKE '%Downey%Robert%' 12982462 1242 DISTRIBUTE HASH ON person_id 12982462 1242 PROJECT person_id, movie_id, movie_id, keyword 12982462 1242 INNER JOIN HASH ON id = keyword_id 26834 1 │└DISTRIBUTE GATHER 26834 1 │ FILTER keyword = 'marvel-cinematic-universe' 134170 134170 │ DISTRIBUTE ROUND ROBIN 134170 134170 │ TABLE SCAN keyword WHERE keyword = 'marvel-cinematic-universe' 64912311 146M PROJECT person_id, movie_id, movie_id, keyword_id 64912311 146M INNER JOIN HASH ON movie_id = movie_id 4523930 3046704 │└DISTRIBUTE HASH ON movie_id 4523930 3046704 │ TABLE SCAN movie_keyword WHERE (((keyword_id >= 8200) AND (keyword_id <= 8200)) AND keyword_id IN 8200) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 13) AND (movie_id <= 2528194)) AND ((movie_id >= 13) AND (movie_id <= 2528194))) AND TRUE) WHEN 3 THEN ((((movie_id >= 11) AND (movie_id <= 2528183)) AND ((movie_id >= 11) AND (movie_id <= 2528183))) AND TRUE) WHEN 5 THEN ((((movie_id >= 12) AND (movie_id <= 2528256)) AND ((movie_id >= 1... 36244344 36244344 DISTRIBUTE HASH ON movie_id 36244344 36244344 TABLE SCAN cast_info WHERE CASE MOD(HASH_REPARTITION movie_id,10) WHEN 0 THEN (((movie_id >= 86) AND (movie_id <= 2525735)) AND TRUE) WHEN 1 THEN (((movie_id >= 64) AND (movie_id <= 2525706)) AND TRUE) WHEN 2 THEN (((movie_id >= 24) AND (movie_id <= 2525574)) AND TRUE) WHEN 3 THEN (((movie_id >= 2) AND (movie_id <= 2525707)) AND TRUE) WHEN 4 THEN (((movie_id >= 228) AND (movie_id <= 2525736)) AND TRUE) WHEN 5 THEN (((movie_id >= 302) AND (movie_id <= 2525713)) AND TRUE) WH...
	Estimate Actual Operator 1 1 AGGREGATE MIN(k.keyword), MIN(n.name), MIN(t.title) 1 22 DISTRIBUTE GATHER 1 22 AGGREGATE MIN(k.keyword), MIN(n.name), MIN(t.title) 1230 6 INNER JOIN HASH ON ci.movie_id = mk.movie_id 1230 14 │└DISTRIBUTE GATHER 25700000 14 │ INNER JOIN HASH ON mk.keyword_id = k.id 25700000 1 │ │└DISTRIBUTE GATHER 2530000 1 │ │ TABLE SCAN keyword WHERE k.keyword = 'marvel-cinematic-universe' 36200000 4499355 │ TABLE SCAN movie_keyword 2700000 61 INNER JOIN HASH ON ci.movie_id = t.id 2700000 391666 │└DISTRIBUTE GATHER 4170000 391666 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2010L) 71 486 INNER JOIN HASH ON ci.person_id = n.id 71 2 │└DISTRIBUTE HASH ON n.id 4520000 2 │ TABLE SCAN name WHERE n.name LIKE '%Downey%Robert%' 71 36244344 DISTRIBUTE HASH ON ci.person_id 134000 36244344 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(keyword as keyword) AS Expr1010, MIN(name as name) AS Expr1011, MIN(title as title) AS Expr1012 1 6 FILTER name as name LIKE '%Downey%Robert%' 420 1224 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 1224 │└TABLE SEEK name AS n 420 1224 PROJECT BmkToPage Bmk1006 AS Expr1040 420 1224 INNER JOIN LOOP ON ci.person_id = n.id 1 1224 │└TABLE SEEK name AS n 420 1224 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 1224 │└TABLE SEEK cast_info AS ci 420 1224 PROJECT BmkToPage Bmk1000 AS Expr1037 420 1224 INNER JOIN LOOP ON t.id = ci.movie_id 30 1224 │└TABLE SEEK cast_info AS ci 13 11 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 11 │└TABLE SEEK title AS t WHERE production_year as production_year > 2010 90 14 PROJECT BmkToPage Bmk1008 AS Expr1035 90 14 INNER JOIN LOOP ON mk.movie_id = t.id 1 14 │└TABLE SEEK title AS t 90 14 INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 14 │└TABLE SEEK movie_keyword AS mk 90 14 INNER JOIN LOOP ON k.id = mk.keyword_id 90 14 │└TABLE SEEK movie_keyword AS mk 1 1 TABLE SCAN keyword AS k WHERE keyword as keyword = 'marvel-cinematic-universe'
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_keyword, min_21 AS actor_name, min_22 AS marvel_movie 1 1 AGGREGATE MIN(min_23) AS min, MIN(min_24) AS min_21, MIN(min_25) AS min_22 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(keyword) AS min_23, MIN(name) AS min_24, MIN(title) AS min_25 - 6 INNER JOIN HASH ON movie_id = id_13 159036 391666 │└DISTRIBUTE HASH ON id_13 159036 391666 │ PROJECT id AS id_13, title 159036 391666 │ FILTER production_year > 2010 159036 391666 │ TABLE SCAN title - 6 INNER JOIN HASH ON person_id = id_9 4167491 2 │└DISTRIBUTE HASH ON id_9 4167491 2 │ PROJECT id AS id_9, name 4167491 2 │ FILTER name LIKE '%Downey%Robert%' 4167491 2 │ TABLE SCAN name - 6 INNER JOIN HASH ON keyword_id = id_0 134170 1 │└DISTRIBUTE GATHER 134170 1 │ PROJECT id AS id_0, keyword 134170 1 │ FILTER keyword = 'marvel-cinematic-universe' 134170 1 │ TABLE SCAN keyword - 6 INNER JOIN HASH ON movie_id = movie_id_5 4523930 14 │└DISTRIBUTE HASH ON movie_id_5 4523930 14 │ PROJECT movie_id AS movie_id_5, keyword_id 4523930 14 │ TABLE SCAN movie_keyword 36244344 6 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(keyword), MIN(name), MIN(title) 1 6 INNER JOIN LOOP ON id = person_id 208 1224 │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 5 11 │ │└INNER JOIN LOOP ON id = movie_id 34 14 │ │ │└INNER JOIN LOOP ON keyword_id = id 1 1 │ │ │ │└TABLE SEEK keyword AS k 305 14 │ │ │ TABLE SEEK movie_keyword AS mk 14 14 │ │ TABLE SEEK title AS t WHERE t.production_year > 2010 429 1223 │ TABLE SEEK cast_info AS ci 1224 1224 TABLE SEEK name AS n WHERE n.name LIKE '%Downey%Robert%'