Planner — IMDB — JOB-3B

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 ('Bulgaria')
  AND t.production_year > 2010
  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,526,440 4.5M Rank		Estimation Error Est Err 4,524,170 4.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,127 4.1K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 19,751,346 20M Rank		Estimation Error Est Err 14,861,622 15M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 7,976,976 8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,837,314 4.8M Rank		Estimation Error Est Err 4,835,027 4.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,127 4.1K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 3,435 3.4K Rank
Estimation Error Est Err 14,721,944 15M Rank		Estimation Error Est Err 4,526,238 4.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 417,568 418K Rank	Estimation Error Est Err 15 15 Rank	Estimation Error Est Err 541,135 541K Rank
Estimation Error Est Err 15,153 15K Rank		Estimation Error Est Err 12,867 13K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,127 4.1K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 26,337 26K Rank	Estimation Error Est Err 26,307 26K Rank	Estimation Error Est Err 26,307 26K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 26,307 26K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 25,979 26K Rank	Estimation Error Est Err 25,949 26K Rank	Estimation Error Est Err 38,895 39K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 25,949 26K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 400,827 401K Rank		Estimation Error Est Err 61 61 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 400,822 401K Rank	Estimation Error Est Err 21 21 Rank	Estimation Error Est Err 400,838 401K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	`Estimate Actual Operator 1 1 AGGREGATE min 1 5 INNER JOIN HASH ON id30 = keyword_id 19 920 │└INNER JOIN HASH ON movie_id25 = movie_id 1 227 │ │└INNER JOIN HASH ON id9 = movie_id 5 2287 │ │ │└TABLE SCAN movie_info WHERE info = Bulgaria 365420 210 │ │ TABLE SCAN title WHERE production_year >= 2011 4523930 4523930 │ TABLE SCAN movie_keyword 131 13 TABLE SCAN keyword WHERE keyword LIKE '%sequel%'`
	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_4930.movie_id,PROJECTION_4930.movie_id) = tuple(PROJECTION_4921.movie_id,PROJECTION_4921.id) - 1726523 │└PROJECT movie_id AS movie_id_right, id, title - 1726523 │ PROJECT movie_id, title, id - 1726523 │ INNER JOIN HASH ON PROJECTION_4927.movie_id = PROJECTION_4924.id - 391666 │ │└PROJECT id, title - 391666 │ │ PROJECT id, title - 391666 │ │ TABLE SCAN title WHERE production_year > 2010 - 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_4936.id = PROJECTION_4933.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) 1 5 PROJECT title 1 5 INNER JOIN HASH ON id = keyword_id 2 920 │└INNER JOIN HASH ON movie_id = movie_id 3 227 │ │└INNER JOIN HASH ON id = movie_id 15 2287 │ │ │└FILTER (movie_id BETWEEN 2 AND 2525971) AND (movie_id BETWEEN 2 AND 2525971) 15 2287 │ │ │ TABLE SCAN movie_info WHERE info = 'Bulgaria' 505662 2855 │ │ FILTER id BETWEEN 2 AND 2525971 505662 2856 │ │ TABLE SCAN title WHERE production_year > 2010 4523930 10007 │ TABLE SCAN movie_keyword 26834 3 TABLE SCAN keyword WHERE contains(keyword,'sequel')
	Estimate Actual Operator 1 1 PROJECT movie_title 1 1 AGGREGATE MIN(title) 162535 10 DISTRIBUTE GATHER 162535 10 AGGREGATE MIN(title) 162535 5 INNER JOIN HASH ON id = movie_id AND id = movie_id 162535 391666 │└DISTRIBUTE HASH ON id, id 162535 391666 │ FILTER production_year > 2010 2528312 2528312 │ TABLE SCAN title WHERE (production_year > 2010) AND (title < '300: Rise of an Empire') 1062618 21 DISTRIBUTE HASH ON movie_id, movie_id 1062618 21 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(40815,40221,126175,122943,122578,59320,69167,61591,64972,72203,73181,398,89546,83664,87951,83665,21183,100855,102660,90485,78133,75006,76744,72594,77599,39892,45883,118026,128826,92506)) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 13) AND (movie_id <= 2528194)) AND ((movie_id >= 13) AND (movie_id <= 2528194))) AND TRUE) WHEN 3 THEN (((... 2967144 2287 DISTRIBUTE HASH ON movie_id 2967144 2287 FILTER info = 'Bulgaria' 14835720 7535532 TABLE SCAN movie_info WHERE (info = 'Bulgaria') 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 (movie_id <= 2524066)) AND TRUE) WHEN 5 THEN (((movie_id >= 77673)...
	Estimate Actual Operator 1 1 AGGREGATE MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(t.title) 57 5 INNER JOIN HASH ON mk.keyword_id = k.id 57 920 │└DISTRIBUTE GATHER 1470000 920 │ INNER JOIN HASH ON mi.movie_id = mk.movie_id 1470000 227 │ │└DISTRIBUTE GATHER 3300000 227 │ │ INNER JOIN HASH ON mi.movie_id = t.id 3300000 2287 │ │ │└DISTRIBUTE GATHER 14800000 2287 │ │ │ TABLE SCAN movie_info WHERE mi.info = 'Bulgaria' 2530000 372506 │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2010L) 4520000 4462491 │ TABLE SCAN movie_keyword 134000 30 TABLE SCAN keyword WHERE contains(k.keyword,'sequel')
	Estimate Actual Operator 1 1 AGGREGATE MIN(title as title) AS Expr1008 1 5 INNER JOIN LOOP ON Bmk1006 = Bmk1006 0 5 │└TABLE SEEK title AS t WHERE production_year as production_year > 2010 1 21 PROJECT BmkToPage Bmk1006 AS Expr1024 1 21 INNER JOIN LOOP ON mk.movie_id = t.id 1 21 │└TABLE SEEK title AS t 1 21 INNER JOIN LOOP ON mk.movie_id = mi.movie_id 1 21 │└TABLE SEEK movie_info AS mi WHERE info as info = 'Bulgaria' 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 - 5 INNER JOIN HASH ON movie_id_5 = id_9 159036 391666 │└DISTRIBUTE HASH ON id_9 159036 391666 │ PROJECT id AS id_9, title 159036 391666 │ FILTER production_year > 2010 159036 391666 │ TABLE SCAN title - 21 INNER JOIN HASH ON movie_id_5 = movie_id 13352148 2287 │└DISTRIBUTE HASH ON movie_id 13352148 2287 │ FILTER info = 'Bulgaria' 13352148 2287 │ TABLE SCAN movie_info - 19 INNER JOIN HASH ON id = keyword_id 4523930 6869 │└DISTRIBUTE HASH ON keyword_id 4523930 6869 │ PROJECT movie_id AS movie_id_5, keyword_id 4523930 6869 │ TABLE SCAN movie_keyword 120753 5 FILTER keyword LIKE '%sequel%' 120753 5 TABLE SCAN keyword
	`Estimate Actual Operator 1 1 AGGREGATE MIN(title) 1 5 INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 69 405 │└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 > 2010 405 405 TABLE SEEK movie_info AS mi WHERE mi.info = 'Bulgaria'`