Planner — IMDB — JOB-3A

SELECT MIN(t.title) AS movie_title
FROM job.keyword AS k,
     job.movie_info AS mi,
     job.movie_keyword AS mk,
     job.title AS t
WHERE k.keyword LIKE '%sequel%'
  AND mi.info IN ('Sweden',
                  'Norway',
                  'Germany',
                  'Denmark',
                  'Swedish',
                  'Denish',
                  'Norwegian',
                  'German')
  AND t.production_year > 2005
  AND t.id = mi.movie_id
  AND t.id = mk.movie_id
  AND mk.movie_id = mi.movie_id
  AND k.id = mk.keyword_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 4,525,635 4.5M Rank		Estimation Error Est Err 4,526,371 4.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 28,137 28K Rank	Estimation Error Est Err 206 206 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 20,372,600 20M Rank		Estimation Error Est Err 14,861,622 15M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 14,368,648 14M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,318,912 5.3M Rank		Estimation Error Est Err 5,325,691 5.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 314,809 315K Rank	Estimation Error Est Err 207 207 Rank	Estimation Error Est Err 202,096 202K Rank
Estimation Error Est Err 16,229,359 16M Rank		Estimation Error Est Err 4,679,593 4.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,038,822 1M Rank	Estimation Error Est Err 216 216 Rank	Estimation Error Est Err 1,315,744 1.3M Rank
Estimation Error Est Err 2,353,939 2.4M Rank		Estimation Error Est Err 2,567,740 2.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,329,367 1.3M Rank	Estimation Error Est Err 206 206 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 28,167 28K Rank	Estimation Error Est Err 28,137 28K Rank	Estimation Error Est Err 28,137 28K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 28,137 28K Rank	Estimation Error Est Err 206 206 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 29,670 30K Rank	Estimation Error Est Err 29,640 30K Rank	Estimation Error Est Err 42,385 42K Rank	Estimation Error Est Err 3,532 3.5K Rank	Estimation Error Est Err 29,640 30K Rank	Estimation Error Est Err 215 215 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,581,872 1.6M Rank		Estimation Error Est Err 4,660 4.7K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,581,842 1.6M Rank	Estimation Error Est Err 222 222 Rank	Estimation Error Est Err 1,581,858 1.6M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min 796 206 INNER JOIN HASH ON id13 = movie_id29 1834 2235 │└INNER JOIN HASH ON id13 = movie_id 4236 12951 │ │└INNER JOIN HASH ON id = keyword_id 131 30 │ │ │└TABLE SCAN keyword WHERE keyword LIKE '%sequel%' 4523930 4523930 │ │ TABLE SCAN movie_keyword 1049348 1523 │ TABLE SCAN title WHERE production_year >= 2006 101416 152 TABLE SCAN movie_info WHERE info BETWEEN Denish AND Swedish AND info IN(Denish,Denmark,German,Germany,Norway,Norwegian,Sweden,Swedish)
	Estimate Actual Operator - 1 PROJECT a1 AS movie_title - 1 AGGREGATE MIN(title) AS a1 - 0 PROJECT title - 0 PROJECT title - 0 INNER JOIN HASH ON tuple(PROJECTION_4907.movie_id,PROJECTION_4907.movie_id) = tuple(PROJECTION_4898.movie_id,PROJECTION_4898.id) - 4922359 │└PROJECT movie_id AS movie_id_right, id, title - 4922359 │ PROJECT movie_id, title, id - 4922359 │ INNER JOIN HASH ON PROJECTION_4904.movie_id = PROJECTION_4901.id - 1012920 │ │└PROJECT id, title - 1012920 │ │ PROJECT id, title - 1012920 │ │ TABLE SCAN title WHERE production_year > 2005 - 14835720 │ PROJECT movie_id - 14835720 │ PROJECT movie_id - 14835720 │ TABLE SCAN movie_info WHERE TRUE - 12951 PROJECT movie_id AS movie_id_left - 12951 PROJECT movie_id - 12951 INNER JOIN HASH ON PROJECTION_4913.id = PROJECTION_4910.keyword_id - 4523930 │└PROJECT keyword_id, movie_id - 4523930 │ PROJECT keyword_id, movie_id - 4523930 │ TABLE SCAN movie_keyword - 30 PROJECT id - 30 PROJECT id - 30 TABLE SCAN keyword WHERE keyword LIKE '%sequel%'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0) 226672 206 PROJECT title 226672 206 INNER JOIN HASH ON movie_id = movie_id 189748 2235 │└INNER JOIN HASH ON keyword_id = id 26834 30 │ │└TABLE SCAN keyword WHERE contains(keyword,'sequel') 920995 1226226 │ INNER JOIN HASH ON movie_id = id 505662 1012683 │ │└FILTER id BETWEEN 2 AND 2525971 505662 1012920 │ │ TABLE SCAN title WHERE production_year > 2005 4523930 1242310 │ TABLE SCAN movie_keyword 2967144 533 FILTER movie_id BETWEEN 2 AND 2525971 2967144 533 FILTER IN ... 14835720 98671 INNER JOIN HASH ON info = #0 0 8 │└SCAN MATERIALISED 14835720 98671 TABLE SCAN movie_info WHERE info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German')
	Estimate Actual Operator 1 1 PROJECT movie_title 1 1 AGGREGATE MIN(title) 252832 10 DISTRIBUTE GATHER 252832 10 AGGREGATE MIN(title) 252832 206 INNER JOIN HASH ON id = movie_id AND id = movie_id 252832 1012920 │└DISTRIBUTE HASH ON id, id 252832 1012920 │ FILTER production_year > 2005 2528312 2528312 │ TABLE SCAN title WHERE (production_year > 2005) AND (title < '2 Days in New York') 1062618 1766 DISTRIBUTE HASH ON movie_id, movie_id 1062618 1766 INNER JOIN HASH ON movie_id = movie_id 904786 12951 │└DISTRIBUTE HASH ON movie_id 904786 12951 │ INNER JOIN HASH ON id = keyword_id 26834 30 │ │└DISTRIBUTE GATHER 26834 30 │ │ FILTER keyword LIKE '%sequel%' 134170 134170 │ │ DISTRIBUTE ROUND ROBIN 134170 134170 │ │ TABLE SCAN keyword WHERE keyword LIKE '%sequel%' 4523930 4523930 │ TABLE SCAN movie_keyword WHERE (((keyword_id >= 398) AND (keyword_id <= 128826)) AND keyword_id IN(78133,75006,76744,72594,77599,21183,100855,102660,92506,39892,45883,118026,128826,398,89546,83664,87951,83665,69167,61591,64972,72203,73181,59320,40815,40221,126175,122943,122578,90485)) 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 ((((m... 2967144 153897 DISTRIBUTE HASH ON movie_id 2967144 153897 FILTER info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German') 14835720 9042947 TABLE SCAN movie_info WHERE info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German') AND CASE MOD(HASH_REPARTITION movie_id,10) WHEN 0 THEN (((movie_id >= 963) AND (movie_id <= 2524070)) AND TRUE) WHEN 1 THEN (((movie_id >= 15613) AND (movie_id <= 2525732)) AND TRUE) WHEN 2 THEN (((movie_id >= 34044) AND (movie_id <= 2524779)) AND TRUE) WHEN 3 THEN (((movie_id >= 19450) AND (movie_id <= 2524082)) AND TRUE) WHEN 4 THEN (((movie_id >= 31741) AND...
	Estimate Actual Operator 1 1 AGGREGATE MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(t.title) 425 206 INNER JOIN HASH ON mk.keyword_id = k.id 425 30 │└DISTRIBUTE GATHER 134000 30 │ TABLE SCAN keyword WHERE contains(k.keyword,'sequel') 2280000 160706 INNER JOIN HASH ON mi.movie_id = mk.movie_id 2280000 48168 │└DISTRIBUTE GATHER 3300000 48168 │ INNER JOIN HASH ON mi.movie_id = t.id 3300000 153897 │ │└DISTRIBUTE GATHER 14800000 153897 │ │ TABLE SCAN movie_info WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German') 2530000 1009693 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2005L) 4520000 4155292 TABLE SCAN movie_keyword
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1016) AS Expr1008 5 9 AGGREGATE MIN(title as title) AS partialagg1016 961 206 INNER JOIN LOOP ON Bmk1006 = Bmk1006 0 206 │└TABLE SEEK title AS t WHERE production_year as production_year > 2005 2414 1766 SORT Expr1017 2414 1766 PROJECT BmkToPage Bmk1006 AS Expr1017 2414 1766 INNER JOIN LOOP ON mi.movie_id = t.id 1 1766 │└TABLE SEEK title AS t 2414 1766 SORT movie_id 2414 1766 INNER JOIN LOOP ON mk.movie_id = mi.movie_id 1 1766 │└TABLE SEEK movie_info AS mi WHERE info as info = 'Denish' OR info as info = 'Denmark' OR info as info = 'German' OR info as info = 'Germany' OR info as info = 'Norway' OR info as info = 'Norwegian' OR info as info = 'Sweden' OR info as info = 'Swedish' 4309 12951 INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 12951 │└TABLE SEEK movie_keyword AS mk 4309 12951 INNER JOIN LOOP ON k.id = mk.keyword_id 90 12951 │└TABLE SEEK movie_keyword AS mk 47 30 TABLE SCAN keyword AS k WHERE keyword as keyword LIKE '%sequel%'
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_title 1 1 AGGREGATE MIN(min_14) AS min - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(title) AS min_14 - 206 INNER JOIN HASH ON movie_id_5 = id_9 247389 1012920 │└DISTRIBUTE HASH ON id_9 247389 1012920 │ PROJECT id AS id_9, title 247389 1012920 │ FILTER production_year > 2005 247389 1012920 │ TABLE SCAN title - 1766 INNER JOIN HASH ON movie_id_5 = movie_id 13352148 153897 │└DISTRIBUTE HASH ON movie_id 13352148 153897 │ FILTER info IN('Denish','Denmark','German','Germany','Norway','Norwegian','Sweden','Swedish') 13352148 153897 │ TABLE SCAN movie_info - 1128 INNER JOIN HASH ON id = keyword_id 4523930 415025 │└DISTRIBUTE HASH ON keyword_id 4523930 415025 │ PROJECT movie_id AS movie_id_5, keyword_id 4523930 415025 │ TABLE SCAN movie_keyword 120753 30 FILTER keyword LIKE '%sequel%' 120753 30 TABLE SCAN keyword
	Estimate Actual Operator 1 1 AGGREGATE MIN(title) 63 206 INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 173 2235 │└INNER JOIN LOOP ON id = movie_id 438 12951 │ │└INNER JOIN LOOP ON keyword_id = id 13 30 │ │ │└TABLE SCAN keyword AS k WHERE k.keyword LIKE '%sequel%' 9150 12951 │ │ TABLE SEEK movie_keyword AS mk 12951 12951 │ TABLE SEEK title AS t WHERE t.production_year > 2005 2235 2235 TABLE SEEK movie_info AS mi WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German')