Planner — IMDB — JOB-6E

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 > 2000
  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 42,149,730 42M Rank		Estimation Error Est Err 42,150,213 42M 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 22,613,083 23M Rank		Estimation Error Est Err 21,232,240 21M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,381,788 1.4M Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 1,381,474 1.4M 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 147,472,182 147M Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 40,807,932 41M Rank
Estimation Error Est Err 1,683 1.7K Rank		Estimation Error Est Err 1,686 1.7K 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 1,381,476 1.4M Rank		Estimation Error Est Err 24 24 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,381,479 1.4M Rank	Estimation Error Est Err 22 22 Rank	Estimation Error Est Err 1,381,486 1.4M 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 280 1224 │└INNER JOIN HASH ON id13 = movie_id30 19 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 1402423 11 │ │ TABLE SCAN title WHERE production_year >= 2001 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_5272.movie_id,PROJECTION_5272.movie_id) = tuple(PROJECTION_5257.movie_id,PROJECTION_5257.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_5269.id = PROJECTION_5260.movie_id - 14 │ │└PROJECT movie_id, keyword - 14 │ │ PROJECT movie_id, keyword - 14 │ │ INNER JOIN HASH ON PROJECTION_5266.keyword_id = PROJECTION_5263.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 - 1381453 │ PROJECT id, title - 1381453 │ PROJECT id, title - 1381453 │ TABLE SCAN title WHERE production_year > 2000 - 486 PROJECT movie_id AS movie_id_left, name - 486 PROJECT movie_id, name - 486 INNER JOIN HASH ON PROJECTION_5278.person_id = PROJECTION_5275.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 442 │ │ FILTER id BETWEEN 2 AND 2525971 505662 443 │ │ TABLE SCAN title WHERE production_year > 2000 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) 361874 10 DISTRIBUTE GATHER 361874 10 AGGREGATE MIN(keyword), MIN(name), MIN(title) 361874 6 PROJECT keyword, name, title 361874 6 INNER JOIN HASH ON id = movie_id AND id = movie_id 343129 1381453 │└DISTRIBUTE HASH ON id, id 343129 1381453 │ FILTER production_year > 2000 2528312 2528312 │ TABLE SCAN title WHERE production_year > 2000 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 >= 4) AND (movie_id <= 2528297)) AND ((movie_id >= 4) AND (movie_id <= 2528297))) AND TRUE) WHEN 3 THEN ((((movie_id >= 3) AND (movie_id <= 2528185)) AND ((movie_id >= 3) AND (movie_id <= 2528185))) AND TRUE) WHEN 5 THEN ((((movie_id >= 7) AND (movie_id <= 2528256)) AND ((movie_id >= 7) AND... 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 4 DISTRIBUTE GATHER 1 4 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 4520000 1 │ │ TABLE SCAN keyword WHERE k.keyword = 'marvel-cinematic-universe' 36200000 4499355 │ TABLE SCAN movie_keyword 5690000 306 INNER JOIN HASH ON ci.movie_id = t.id 5690000 1381453 │└DISTRIBUTE GATHER 4170000 1381453 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2000L) 71 307 INNER JOIN HASH ON ci.person_id = n.id 71 2 │└DISTRIBUTE HASH ON n.id 134000 2 │ TABLE SCAN name WHERE n.name LIKE '%Downey%Robert%' 2530000 16732272 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 3 6 FILTER name as name LIKE '%Downey%Robert%' 1489 1224 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 1224 │└TABLE SEEK name AS n 1489 1224 PROJECT BmkToPage Bmk1006 AS Expr1040 1489 1224 INNER JOIN LOOP ON ci.person_id = n.id 1 1224 │└TABLE SEEK name AS n 1489 1224 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 1224 │└TABLE SEEK cast_info AS ci 1489 1224 PROJECT BmkToPage Bmk1000 AS Expr1037 1489 1224 INNER JOIN LOOP ON t.id = ci.movie_id 30 1224 │└TABLE SEEK cast_info AS ci 49 11 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 11 │└TABLE SEEK title AS t WHERE production_year as production_year > 2000 90 14 PROJECT BmkToPage Bmk1008 AS Expr1034 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 335742 1381453 │└DISTRIBUTE HASH ON id_13 335742 1381453 │ PROJECT id AS id_13, title 335742 1381453 │ FILTER production_year > 2000 335742 1381453 │ 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 721 1224 │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 18 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 > 2000 429 1223 │ TABLE SEEK cast_info AS ci 1224 1224 TABLE SEEK name AS n WHERE n.name LIKE '%Downey%Robert%'