Planner — IMDB — JOB-6C

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 > 2014
  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,278 41M Rank		Estimation Error Est Err 40,768,278 41M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 94 94 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 40,768,715 41M Rank		Estimation Error Est Err 40,769,198 41M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 516 516 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 12,619,496 13M Rank		Estimation Error Est Err 4,623,706 4.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 469 469 Rank	Estimation Error Est Err 444 444 Rank	Estimation Error Est Err 462 462 Rank
Estimation Error Est Err 45,013,038 45M Rank		Estimation Error Est Err 292,180,210 292M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 146,091,167 146M Rank	Estimation Error Est Err 12 12 Rank	Estimation Error Est Err 39,426,911 39M Rank
Estimation Error Est Err 51 51 Rank		Estimation Error Est Err 51 51 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 94 94 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 95 95 Rank	Estimation Error Est Err 95 95 Rank	Estimation Error Est Err 94 94 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 94 94 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 177 177 Rank	Estimation Error Est Err 176 176 Rank	Estimation Error Est Err 188 188 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 176 176 Rank	Estimation Error Est Err 2 2 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 445 445 Rank		Estimation Error Est Err 8 8 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 444 444 Rank	Estimation Error Est Err 18 18 Rank	Estimation Error Est Err 459 459 Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 1 2 INNER JOIN HASH ON id38 = person_id 15 33 │└INNER JOIN HASH ON id13 = movie_id30 1 2 │ │└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 1711 2 │ │ TABLE SCAN title WHERE production_year >= 2015 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 - 2 PROJECT keyword, name, title - 2 PROJECT keyword, name, title - 2 INNER JOIN HASH ON tuple(PROJECTION_5214.movie_id,PROJECTION_5214.movie_id) = tuple(PROJECTION_5199.movie_id,PROJECTION_5199.id) - 2 │└PROJECT movie_id AS movie_id_right, id, keyword, title - 2 │ PROJECT movie_id, keyword, title, id - 2 │ INNER JOIN HASH ON PROJECTION_5211.id = PROJECTION_5202.movie_id - 14 │ │└PROJECT movie_id, keyword - 14 │ │ PROJECT movie_id, keyword - 14 │ │ INNER JOIN HASH ON PROJECTION_5208.keyword_id = PROJECTION_5205.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 - 438 │ PROJECT id, title - 438 │ PROJECT id, title - 438 │ TABLE SCAN title WHERE production_year > 2014 - 486 PROJECT movie_id AS movie_id_left, name - 486 PROJECT movie_id, name - 486 INNER JOIN HASH ON PROJECTION_5220.person_id = PROJECTION_5217.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 2 PROJECT keyword, name, title 47 2 INNER JOIN HASH ON id = person_id 206 33 │└INNER JOIN HASH ON movie_id = id 14 2 │ │└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 2 │ │ FILTER id BETWEEN 2 AND 2525971 505662 2 │ │ TABLE SCAN title WHERE production_year > 2014 36244344 33 │ 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) 95230 10 DISTRIBUTE GATHER 95230 10 AGGREGATE MIN(keyword), MIN(name), MIN(title) 95230 2 PROJECT keyword, name, title 95230 2 INNER JOIN HASH ON id = movie_id AND id = movie_id 90297 438 │└DISTRIBUTE GATHER 90297 438 │ FILTER production_year > 2014 2528312 1420329 │ TABLE SCAN title WHERE production_year > 2014 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 ((((movie_id >= 203875) AND (movie_id <= 2520697)) AND ((movie_id >= 203875) AND (movie_id <= 2520697))) AND TRUE) 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 36200000 0 SEQUENCE 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 2 │ 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' 4170000 4499355 │ │ TABLE SCAN movie_keyword 1500000 3 │ INNER JOIN HASH ON ci.movie_id = t.id 1500000 438 │ │└DISTRIBUTE GATHER 2530000 438 │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2014L) 71 3 │ 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%' 2920000 124345 │ FILTER - 8119699 │ TABLE SCAN cast_info 36200000 0 └─FILTER 25700000 1 DISTRIBUTE HASH 25700000 1 AGGREGATE bloom_filter_agg(bloom_expr(t.id,t.id),438L,4096L) 25700000 438 SELECT 36200000 1 DISTRIBUTE HASH 36200000 1 DISTRIBUTE HASH 36200000 1 TABLE SCAN keyword WHERE k.keyword = 'marvel-cinematic-universe'
	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 2 FILTER name as name LIKE '%Downey%Robert%' 30 33 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 33 │└TABLE SEEK name AS n 30 33 PROJECT BmkToPage Bmk1006 AS Expr1029 30 33 INNER JOIN LOOP ON ci.person_id = n.id 1 33 │└TABLE SEEK name AS n 30 33 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 33 │└TABLE SEEK cast_info AS ci 30 33 PROJECT BmkToPage Bmk1000 AS Expr1026 30 33 INNER JOIN LOOP ON t.id = ci.movie_id 30 33 │└TABLE SEEK cast_info AS ci 1 2 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 2 │└TABLE SEEK title AS t WHERE production_year as production_year > 2014 90 14 PROJECT BmkToPage Bmk1008 AS Expr1024 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 - 2 INNER JOIN HASH ON movie_id = id_13 88353 438 │└DISTRIBUTE HASH ON id_13 88353 438 │ PROJECT id AS id_13, title 88353 438 │ FILTER production_year > 2014 88353 438 │ TABLE SCAN title - 2 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 - 2 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 - 2 INNER JOIN HASH ON movie_id = movie_id_5 4523930 2 │└DISTRIBUTE HASH ON movie_id_5 4523930 2 │ PROJECT movie_id AS movie_id_5, keyword_id 4523930 2 │ TABLE SCAN movie_keyword 36244344 2 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(keyword), MIN(name), MIN(title) 1 2 INNER JOIN LOOP ON id = person_id 1 33 │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 2 │ │└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 > 2014 78 33 │ TABLE SEEK cast_info AS ci 33 33 TABLE SEEK name AS n WHERE n.name LIKE '%Downey%Robert%'