Planner — IMDB — JOB-6F

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 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 44,947,616 45M Rank		Estimation Error Est Err 44,949,930 45M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,642,050 1.6M Rank	Estimation Error Est Err 785,477 785K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 46,451,388 46M Rank		Estimation Error Est Err 234,859,594 235M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 237,413,144 237M Rank	Estimation Error Est Err 114,182,642 114M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 46,285,122 46M Rank		Estimation Error Est Err 42,910,723 43M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,642,050 1.6M Rank	Estimation Error Est Err 1,570,956 1.6M Rank	Estimation Error Est Err 835,217 835K Rank
Estimation Error Est Err 47,598,247 48M Rank		Estimation Error Est Err 434,659,564 435M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 222,878,726 223M Rank	Estimation Error Est Err 785,487 785K Rank	Estimation Error Est Err 49,580,016 50M Rank
Estimation Error Est Err 3,647,703 3.6M Rank		Estimation Error Est Err 4,051,295 4.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,100,421 3.1M Rank	Estimation Error Est Err 785,477 785K 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 785,477 785K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,262,725 3.3M Rank	Estimation Error Est Err 3,262,717 3.3M Rank	Estimation Error Est Err 3,284,100 3.3M Rank	Estimation Error Est Err 35,548 36K Rank	Estimation Error Est Err 3,262,717 3.3M Rank	Estimation Error Est Err 785,485 785K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,797,264 6.8M Rank		Estimation Error Est Err 6,257,220 6.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 8,677,554 8.7M Rank	Estimation Error Est Err 785,493 785K Rank	Estimation Error Est Err 5,584,516 5.6M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 4915 785477 INNER JOIN HASH ON id38 = person_id 4441 785477 │└INNER JOIN HASH ON movie_id30 = movie_id 141 14165 │ │└INNER JOIN HASH ON id13 = movie_id 244 35548 │ │ │└INNER JOIN HASH ON id = 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 4167491 4167491 TABLE SCAN name
	Estimate Actual Operator - ∞ PROJECT a1 AS movie_keyword, a2 AS actor_name, a3 AS hero_movie - ∞ AGGREGATE MIN(keyword) AS a1, MIN(name) AS a2, MIN(title) AS a3 - 114M PROJECT keyword, name, title - 114M PROJECT keyword, name, title - 114M INNER JOIN HASH ON PROJECTION_5307.id = PROJECTION_5286.person_id - 114M │└PROJECT person_id, keyword, title - 114M │ PROJECT person_id, keyword, title - 114M │ INNER JOIN HASH ON tuple(PROJECTION_5304.movie_id,PROJECTION_5304.movie_id) = tuple(PROJECTION_5289.movie_id,PROJECTION_5289.id) - 1970380 │ │└PROJECT movie_id AS movie_id_right, id, keyword, title - 1970380 │ │ PROJECT movie_id, keyword, title, id - 1970380 │ │ INNER JOIN HASH ON PROJECTION_5301.id = PROJECTION_5292.movie_id - 4523930 │ │ │└PROJECT movie_id, keyword - 4523930 │ │ │ PROJECT movie_id, keyword - 4523930 │ │ │ INNER JOIN HASH ON PROJECTION_5298.keyword_id = PROJECTION_5295.id - 134170 │ │ │ │└PROJECT id, keyword - 134170 │ │ │ │ PROJECT id, keyword - 134170 │ │ │ │ TABLE SCAN keyword WHERE TRUE - 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 - 36244344 │ PROJECT movie_id AS movie_id_left, person_id - 36244344 │ PROJECT movie_id, person_id - 36244344 │ TABLE SCAN cast_info - 4167491 PROJECT id, name - 4167491 PROJECT name, id - 4167491 TABLE SCAN name
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 3210675 785477 PROJECT keyword, name, title 3210675 785477 INNER JOIN HASH ON id = person_id 2768855 785477 │└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 1164422 │ │ 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 1181749 │ │ TABLE SCAN movie_keyword 36244344 664305 │ TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525971 4167491 286018 TABLE SCAN "name" WHERE id <= 4061926
	Estimate Actual Operator 1 1 PROJECT movie_keyword, actor_name, hero_movie 1 1 AGGREGATE MIN(keyword), MIN(name), MIN(title) 1763540 10 DISTRIBUTE GATHER 1763540 10 AGGREGATE MIN(keyword), MIN(name), MIN(title) 1763540 785477 PROJECT keyword, name, title 1763540 785477 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 12982462 1564305 DISTRIBUTE HASH ON movie_id, movie_id 12982462 1564305 PROJECT movie_id, movie_id, keyword, name 12982462 1564305 INNER JOIN HASH ON id = person_id 4167491 4167491 │└DISTRIBUTE HASH ON id 4167491 4167491 │ TABLE SCAN name 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 9 0 SEQUENCE 1 1 ├─AGGREGATE MIN(k.keyword), MIN(n.name), MIN(t.title) 1 2 │ DISTRIBUTE GATHER 1 2 │ AGGREGATE MIN(k.keyword), MIN(n.name), MIN(t.title) 5500 785477 │ INNER JOIN HASH ON ci.person_id = n.id 5500 785477 │ │└DISTRIBUTE GATHER 5500 785477 │ │ INNER JOIN HASH ON mk.movie_id = ci.movie_id 5500 14165 │ │ │└DISTRIBUTE GATHER 334 14165 │ │ │ INNER JOIN HASH ON mk.movie_id = t.id 334 35548 │ │ │ │└DISTRIBUTE GATHER 317 35548 │ │ │ │ INNER JOIN HASH ON mk.keyword_id = k.id 317 8 │ │ │ │ │└DISTRIBUTE GATHER - 8 │ │ │ │ │ TABLE SCAN keyword WHERE k.keyword IN('superhero','sequel','second-part','marvel-comics','based-on-comic','tv-special','fight','violence') 4170000 4519843 │ │ │ │ TABLE SCAN movie_keyword 36200000 1377014 │ │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2000L) 2530000 36228389 │ │ TABLE SCAN cast_info 346000 285454 │ FILTER 4520000 4159860 │ TABLE SCAN name 9 0 └─FILTER 5500 1 DISTRIBUTE HASH 5500 1 AGGREGATE bloom_filter_agg(bloom_expr(ci.person_id,ci.person_id),785477L,8388608L) 5500 785477 SELECT 134000 8 DISTRIBUTE HASH 134000 8 DISTRIBUTE HASH 134000 8 TABLE SCAN keyword WHERE k.keyword IN('superhero','sequel','second-part','marvel-comics','based-on-comic','tv-special','fight','violence')
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1020) AS Expr1010, MIN(partialagg1021) AS Expr1011, MIN(partialagg1022) AS Expr1012 5 8 AGGREGATE MIN(keyword as keyword) AS partialagg1020, MIN(name as name) AS partialagg1021, MIN(title as title) AS partialagg1022 11916 785477 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 785477 │└TABLE SEEK name AS n 11916 785477 PROJECT BmkToPage Bmk1006 AS Expr1038 11916 785477 INNER JOIN LOOP ON ci.person_id = n.id 1 785477 │└TABLE SEEK name AS n 11916 785477 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 785477 │└TABLE SEEK cast_info AS ci 11916 785477 PROJECT BmkToPage Bmk1000 AS Expr1035 11916 785477 INNER JOIN LOOP ON t.id = ci.movie_id 30 785477 │└TABLE SEEK cast_info AS ci 394 14165 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 14165 │└TABLE SEEK title AS t WHERE production_year as production_year > 2000 725 35548 INNER JOIN LOOP ON mk.movie_id = t.id 1 35548 │└TABLE SEEK title AS t 725 35548 SORT movie_id 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'
	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 - 785477 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 - 1564305 INNER JOIN HASH ON person_id = id_9 4167491 4167491 │└DISTRIBUTE HASH ON id_9 4167491 4167491 │ PROJECT id AS id_9, name 4167491 4167491 │ TABLE SCAN name - 1564305 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 - 1564305 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 1212764 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(keyword), MIN(name), MIN(title) 5768 785477 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