Planner — IMDB — JOB-3C

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',
                  'USA',
                  'American')
  AND t.production_year > 1990
  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,535,376 4.5M Rank		Estimation Error Est Err 4,539,054 4.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 33,776 34K Rank	Estimation Error Est Err 7,250 7.3K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 21,108,712 21M Rank		Estimation Error Est Err 23,941,356 24M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 13,629,566 14M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,970,010 7M Rank		Estimation Error Est Err 6,271,180 6.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 732,576 733K Rank	Estimation Error Est Err 7,252 7.3K Rank	Estimation Error Est Err 719,657 720K Rank
Estimation Error Est Err 16,229,359 16M Rank		Estimation Error Est Err 5,242,767 5.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,774,934 1.8M Rank	Estimation Error Est Err 7,260 7.3K Rank	Estimation Error Est Err 2,615,030 2.6M Rank
Estimation Error Est Err 4,673,909 4.7M Rank		Estimation Error Est Err 5,619,432 5.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,934,468 2.9M Rank	Estimation Error Est Err 7,250 7.3K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 33,806 34K Rank	Estimation Error Est Err 33,776 34K Rank	Estimation Error Est Err 33,776 34K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 33,776 34K Rank	Estimation Error Est Err 7,250 7.3K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 54,007 54K Rank	Estimation Error Est Err 53,977 54K Rank	Estimation Error Est Err 59,678 60K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 53,977 54K Rank	Estimation Error Est Err 7,260 7.3K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,979,666 7M Rank		Estimation Error Est Err 38,065 38K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,979,636 7M Rank	Estimation Error Est Err 7,266 7.3K Rank	Estimation Error Est Err 6,979,652 7M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min 2106 7250 INNER JOIN HASH ON id13 = movie_id29 3073 7874 │└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 1757966 6056 │ TABLE SCAN title WHERE production_year >= 1991 608496 5360 TABLE SCAN movie_info WHERE info BETWEEN American AND USA AND info IN(American,Denish,Denmark,German,Germany,Norway,Norwegian,Sweden,Swedish,USA)
	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_4953.movie_id,PROJECTION_4953.id) = tuple(PROJECTION_4944.movie_id,PROJECTION_4944.movie_id) - 12951 │└PROJECT movie_id AS movie_id_right - 12951 │ PROJECT movie_id - 12951 │ INNER JOIN HASH ON PROJECTION_4950.id = PROJECTION_4947.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%' - 9092685 PROJECT movie_id AS movie_id_left, id, title - 9092685 PROJECT movie_id, title, id - 9092685 INNER JOIN HASH ON PROJECTION_4959.movie_id = PROJECTION_4956.id - 1749032 │└PROJECT id, title - 1749032 │ PROJECT id, title - 1749032 │ TABLE SCAN title WHERE production_year > 1990 - 14835720 PROJECT movie_id - 14835720 PROJECT movie_id - 14835720 TABLE SCAN movie_info WHERE TRUE
	Estimate Actual Operator - 1 AGGREGATE MIN(#0) 226672 7250 PROJECT title 226672 7250 INNER JOIN HASH ON movie_id = movie_id 189748 7874 │└INNER JOIN HASH ON keyword_id = id 26834 30 │ │└TABLE SCAN keyword WHERE contains(keyword,'sequel') 920995 2687345 │ INNER JOIN HASH ON movie_id = id 505662 1748483 │ │└FILTER id BETWEEN 2 AND 2525971 505662 1749032 │ │ TABLE SCAN title WHERE production_year > 1990 4523930 2693462 │ TABLE SCAN movie_keyword 2967144 7057 FILTER movie_id BETWEEN 2 AND 2525971 2967144 7057 FILTER IN ... 14835720 231375 INNER JOIN HASH ON info = #0 0 10 │└SCAN MATERIALISED 14835720 231375 TABLE SCAN movie_info WHERE info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American')
	Estimate Actual Operator 1 1 PROJECT movie_title 1 1 AGGREGATE MIN(title) 523722 10 DISTRIBUTE GATHER 523722 10 AGGREGATE MIN(title) 523722 7250 INNER JOIN HASH ON id = movie_id AND id = movie_id 523722 1749032 │└DISTRIBUTE HASH ON id, id 523722 1749032 │ FILTER production_year > 1990 2528312 2528312 │ TABLE SCAN title WHERE (production_year > 1990) AND (title < '& Teller 2') 1062618 12163 DISTRIBUTE HASH ON movie_id, movie_id 1062618 12163 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(398,89546,83664,87951,83665,39892,45883,118026,128826,92506,21183,100855,102660,78133,75006,76744,72594,77599,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 706674 DISTRIBUTE HASH ON movie_id 2967144 706674 FILTER info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American') 14835720 9042947 TABLE SCAN movie_info WHERE info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American') 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...
	Estimate Actual Operator 1 1 AGGREGATE MIN(t.title) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE MIN(t.title) 511 7250 INNER JOIN HASH ON mk.movie_id = mi.movie_id 511 706674 │└DISTRIBUTE GATHER 134000 706674 │ TABLE SCAN movie_info WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American') 3300000 7874 INNER JOIN HASH ON mk.movie_id = t.id 3300000 12951 │└DISTRIBUTE GATHER 3300000 12951 │ INNER JOIN HASH ON k.id = mk.keyword_id 3300000 30 │ │└DISTRIBUTE GATHER 14800000 30 │ │ TABLE SCAN keyword WHERE contains(k.keyword,'sequel') 2530000 4519836 │ TABLE SCAN movie_keyword 4520000 1743470 TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 1990L)
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1015) AS Expr1008 5 10 AGGREGATE MIN(title as title) AS partialagg1015 7937 7250 INNER JOIN LOOP ON t.id = mi.movie_id 2 7250 │└TABLE SEEK movie_info AS mi WHERE info as info = 'American' OR 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' OR info as info = 'USA' 2975 7874 INNER JOIN LOOP ON Bmk1006 = Bmk1006 0 7874 │└TABLE SEEK title AS t WHERE production_year as production_year > 1990 4309 12951 PROJECT BmkToPage Bmk1006 AS Expr1022 4309 12951 INNER JOIN LOOP ON mk.movie_id = t.id 1 12951 │└TABLE SEEK title AS t 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 - 7250 INNER JOIN HASH ON movie_id_5 = id_9 512448 1749032 │└DISTRIBUTE HASH ON id_9 512448 1749032 │ PROJECT id AS id_9, title 512448 1749032 │ FILTER production_year > 1990 512448 1749032 │ TABLE SCAN title - 12163 INNER JOIN HASH ON movie_id_5 = movie_id 13352148 706674 │└DISTRIBUTE HASH ON movie_id 13352148 706674 │ FILTER info IN('American','Denish','Denmark','German','Germany','Norway','Norwegian','Sweden','Swedish','USA') 13352148 706674 │ TABLE SCAN movie_info - 12951 INNER JOIN HASH ON id = keyword_id 4523930 4523930 │└DISTRIBUTE HASH ON keyword_id 4523930 4523930 │ PROJECT movie_id AS movie_id_5, keyword_id 4523930 4523930 │ TABLE SCAN movie_keyword 120753 30 FILTER keyword LIKE '%sequel%' 120753 30 TABLE SCAN keyword
	Estimate Actual Operator 1 1 AGGREGATE MIN(title) 559 7250 INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 302 7874 │└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 > 1990 15748 7874 TABLE SEEK movie_info AS mi WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American')