Planner — IMDB — JOB-6D

SELECT MIN(k.keyword) AS movie_keyword,
       MIN(n.name) AS actor_name,
       MIN(t.title) AS hero_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 IN ('superhero',
                    'sequel',
                    'second-part',
                    'marvel-comics',
                    'based-on-comic',
                    'tv-special',
                    'fight',
                    'violence')
  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,780,127 41M Rank		Estimation Error Est Err 41,567,916 42M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 856,661 857K Rank	Estimation Error Est Err 88 88 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 42,283,899 42M Rank		Estimation Error Est Err 42,283,897 42M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 11,376 11K Rank	Estimation Error Est Err 5,202 5.2K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 22,412,386 22M Rank		Estimation Error Est Err 21,036,420 21M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,387,038 1.4M Rank	Estimation Error Est Err 89 89 Rank	Estimation Error Est Err 1,381,770 1.4M Rank
Estimation Error Est Err 47,598,247 48M Rank		Estimation Error Est Err 433,095,371 433M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 218,711,347 219M Rank	Estimation Error Est Err 98 98 Rank	Estimation Error Est Err 43,848,334 44M Rank
Estimation Error Est Err 2,693,772 2.7M Rank		Estimation Error Est Err 2,473,251 2.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,543,720 1.5M Rank	Estimation Error Est Err 88 88 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,642,030 1.6M Rank	Estimation Error Est Err 1,642,030 1.6M Rank	Estimation Error Est Err 1,642,050 1.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,642,050 1.6M Rank	Estimation Error Est Err 88 88 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,636,097 1.6M Rank	Estimation Error Est Err 1,636,087 1.6M Rank	Estimation Error Est Err 1,636,597 1.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,200,392 3.2M Rank	Estimation Error Est Err 88 88 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,417,071 1.4M Rank		Estimation Error Est Err 448 448 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,417,225 1.4M Rank	Estimation Error Est Err 104 104 Rank	Estimation Error Est Err 1,417,027 1.4M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 2 88 INNER JOIN HASH ON id = person_id 2821 2 │└TABLE SCAN name WHERE name LIKE '%Downey%Robert%' 2112 785477 INNER JOIN HASH ON id25 = movie_id42 141 14165 │└INNER JOIN HASH ON id25 = movie_id 244 35548 │ │└INNER JOIN HASH ON id13 = keyword_id 8 8 │ │ │└TABLE SCAN keyword WHERE keyword BETWEEN based - on - comic AND violence AND keyword IN(based - on - comic,fight,marvel - comics,second - part,sequel,superhero,tv - special,violence) 4523930 4523930 │ │ TABLE SCAN movie_keyword 1402423 11843 │ TABLE SCAN title WHERE production_year >= 2001 36244344 36244344 TABLE SCAN cast_info
	Estimate Actual Operator - 1 PROJECT a1 AS movie_keyword, a2 AS actor_name, a3 AS hero_movie - 1 AGGREGATE MIN(keyword) AS a1, MIN(name) AS a2, MIN(title) AS a3 - 5202 PROJECT keyword, name, title - 5202 PROJECT keyword, name, title - 5202 INNER JOIN HASH ON PROJECTION_5249.id = PROJECTION_5228.keyword_id - 5202 │└PROJECT keyword_id, name, title - 5202 │ PROJECT keyword_id, name, title - 5202 │ INNER JOIN HASH ON tuple(PROJECTION_5246.movie_id,PROJECTION_5246.movie_id) = tuple(PROJECTION_5231.movie_id,PROJECTION_5231.id) - 306 │ │└PROJECT movie_id AS movie_id_right, id, name, title - 306 │ │ PROJECT movie_id, name, title, id - 306 │ │ INNER JOIN HASH ON PROJECTION_5243.id = PROJECTION_5234.movie_id - 486 │ │ │└PROJECT movie_id, name - 486 │ │ │ PROJECT movie_id, name - 486 │ │ │ INNER JOIN HASH ON PROJECTION_5240.person_id = PROJECTION_5237.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 - 1381453 │ │ PROJECT id, title - 1381453 │ │ PROJECT id, title - 1381453 │ │ TABLE SCAN title WHERE production_year > 2000 - 4523930 │ PROJECT movie_id AS movie_id_left, keyword_id - 4523930 │ PROJECT movie_id, keyword_id - 4523930 │ TABLE SCAN movie_keyword - 134170 PROJECT id, keyword - 134170 PROJECT id, keyword - 134170 TABLE SCAN keyword WHERE TRUE
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 642135 88 PROJECT keyword, name, title 642135 88 INNER JOIN HASH ON person_id = id 833498 2 │└FILTER id <= 4061926 833498 2 │ TABLE SCAN "name" WHERE name LIKE '%Downey%Robert%' 2768855 88 INNER JOIN HASH ON movie_id = id 189748 14165 │└INNER JOIN HASH ON keyword_id = id 26834 8 │ │└FILTER IN ... 134170 134170 │ │ INNER JOIN HASH ON keyword = #0 0 8 │ │ │└SCAN MATERIALISED 134170 134170 │ │ TABLE SCAN keyword WHERE keyword IN('superhero','sequel','second-part','marvel-comics','based-on-comic','tv-special','fight','violence') 920995 1160807 │ INNER JOIN HASH ON movie_id = id 505662 1381132 │ │└FILTER id BETWEEN 2 AND 2525971 505662 1381453 │ │ TABLE SCAN title WHERE production_year > 2000 4523930 1178098 │ TABLE SCAN movie_keyword 36244344 41 TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525971
	Estimate Actual Operator 1 1 PROJECT movie_keyword, actor_name, hero_movie 1 1 AGGREGATE MIN(keyword), MIN(name), MIN(title) 361874 10 DISTRIBUTE GATHER 361874 10 AGGREGATE MIN(keyword), MIN(name), MIN(title) 361874 88 PROJECT keyword, name, title 361874 88 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 112 DISTRIBUTE HASH ON movie_id, movie_id 2663974 112 PROJECT movie_id, movie_id, keyword, name 2663974 112 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 1564305 DISTRIBUTE HASH ON person_id 12982462 1564305 PROJECT person_id, movie_id, movie_id, keyword 12982462 1564305 INNER JOIN HASH ON id = keyword_id 26834 8 │└DISTRIBUTE GATHER 26834 8 │ FILTER keyword IN('superhero','sequel','second-part','marvel-comics','based-on-comic','tv-special','fight','violence') 134170 134170 │ DISTRIBUTE ROUND ROBIN 134170 134170 │ TABLE SCAN keyword WHERE keyword IN('superhero','sequel','second-part','marvel-comics','based-on-comic','tv-special','fight','violence') 64912311 215M PROJECT person_id, movie_id, movie_id, keyword_id 64912311 215M INNER JOIN HASH ON movie_id = movie_id 4523930 4523930 │└DISTRIBUTE HASH ON movie_id 4523930 4523930 │ TABLE SCAN movie_keyword WHERE (((keyword_id >= 393) AND (keyword_id <= 8201)) AND keyword_id IN(8201,393,2905,1732,875,1038,398,1578)) 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 <= 25... 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 <= 2525771)) AND TRUE) WHEN 1 THEN (((movie_id >= 64) AND (movie_id <= 2525732)) AND TRUE) WHEN 2 THEN (((movie_id >= 24) AND (movie_id <= 2525642)) 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 <= 2525715)) AND TRUE) WH...
	Estimate Actual Operator 1 1 AGGREGATE MIN(k.keyword), MIN(n.name), MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(k.keyword), MIN(n.name), MIN(t.title) 398 88 INNER JOIN HASH ON mk.keyword_id = k.id 398 8 │└DISTRIBUTE GATHER 36200000 8 │ TABLE SCAN keyword WHERE k.keyword IN('superhero','sequel','second-part','marvel-comics','based-on-comic','tv-special','fight','violence') 5690000 5190 INNER JOIN HASH ON ci.movie_id = mk.movie_id 5690000 306 │└DISTRIBUTE GATHER 5690000 306 │ INNER JOIN HASH ON ci.movie_id = t.id 5690000 1381453 │ │└DISTRIBUTE GATHER 2530000 1381453 │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2000L) 25700000 307 │ INNER JOIN HASH ON ci.person_id = n.id 25700000 2 │ │└DISTRIBUTE HASH ON n.id 134000 2 │ │ TABLE SCAN name WHERE n.name LIKE '%Downey%Robert%' 4520000 16732272 │ TABLE SCAN cast_info 4170000 4298651 TABLE SCAN movie_keyword
	Estimate Actual Operator 1 1 AGGREGATE MIN(keyword as keyword) AS Expr1010, MIN(name as name) AS Expr1011, MIN(title as title) AS Expr1012 27 88 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 88 │└TABLE SEEK title AS t WHERE production_year as production_year > 2000 51 112 PROJECT BmkToPage Bmk1008 AS Expr1041 51 112 INNER JOIN LOOP ON mk.movie_id = t.id 1 112 │└TABLE SEEK title AS t 51 112 INNER JOIN HASH ON Bmk1000 = Bmk1000 21930 1564305 │└INNER JOIN LOOP ON mk.movie_id = ci.movie_id 30 1564305 │ │└TABLE SEEK cast_info AS ci 725 35548 │ INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 35548 │ │└TABLE SEEK movie_keyword AS mk 725 35548 │ INNER JOIN LOOP ON k.id = mk.keyword_id 90 35548 │ │└TABLE SEEK movie_keyword AS mk 8 8 │ TABLE SCAN keyword AS k WHERE keyword as keyword = 'based-on-comic' OR keyword as keyword = 'fight' OR keyword as keyword = 'marvel-comics' OR keyword as keyword = 'second-part' OR keyword as keyword = 'sequel' OR keyword as keyword = 'superhero' OR keyword as keyword = 'tv-special' OR keyword as keyword = 'violence' 84661 486 INNER JOIN LOOP ON n.id = ci.person_id 579 486 │└TABLE SEEK cast_info AS ci 145 2 TABLE SCAN name AS n WHERE name as name LIKE '%Downey%Robert%'
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_keyword, min_21 AS actor_name, min_22 AS hero_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 - 88 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 - 112 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 - 112 INNER JOIN HASH ON keyword_id = id_0 134170 8 │└DISTRIBUTE GATHER 134170 8 │ PROJECT id AS id_0, keyword 134170 8 │ FILTER keyword IN('based-on-comic','fight','marvel-comics','second-part','sequel','superhero','tv-special','violence') 134170 8 │ TABLE SCAN keyword - 112 INNER JOIN HASH ON movie_id = movie_id_5 4523930 35548 │└DISTRIBUTE HASH ON movie_id_5 4523930 35548 │ PROJECT movie_id AS movie_id_5, keyword_id 4523930 35548 │ TABLE SCAN movie_keyword 36244344 60 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(keyword), MIN(name), MIN(title) 1 88 INNER JOIN LOOP ON id = person_id 5768 785477 │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 147 14165 │ │└INNER JOIN LOOP ON id = movie_id 270 35548 │ │ │└INNER JOIN LOOP ON keyword_id = id 8 8 │ │ │ │└TABLE SEEK keyword AS k 2440 35548 │ │ │ TABLE SEEK movie_keyword AS mk 35548 35548 │ │ TABLE SEEK title AS t WHERE t.production_year > 2000 552435 785449 │ TABLE SEEK cast_info AS ci 785477 785477 TABLE SEEK name AS n WHERE n.name LIKE '%Downey%Robert%'