Planner — IMDB — JOB-4C

SELECT MIN(mi_idx.info) AS rating,
       MIN(t.title) AS movie_title
FROM job.info_type AS it,
     job.keyword AS k,
     job.movie_info_idx AS mi_idx,
     job.movie_keyword AS mk,
     job.title AS t
WHERE it.info ='rating'
  AND k.keyword LIKE '%sequel%'
  AND mi_idx.info > '2.0'
  AND t.production_year > 1990
  AND t.id = mi_idx.movie_id
  AND t.id = mk.movie_id
  AND mk.movie_id = mi_idx.movie_id
  AND k.id = mk.keyword_id
  AND it.id = mi_idx.info_type_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,533,784 4.5M Rank		Estimation Error Est Err 4,541,204 4.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 38,476 38K Rank	Estimation Error Est Err 4,700 4.7K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 7,079,358 7.1M Rank		Estimation Error Est Err 7,087,419 7.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 7,328,905 7.3M Rank	Estimation Error Est Err 4,700 4.7K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 7,067,276 7.1M Rank		Estimation Error Est Err 7,075,845 7.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 39,202 39K Rank	Estimation Error Est Err 4,701 4.7K Rank	Estimation Error Est Err 20,857 21K Rank
Estimation Error Est Err 8,566,560 8.6M Rank		Estimation Error Est Err 10,515,249 11M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,901,075 3.9M Rank	Estimation Error Est Err 4,710 4.7K Rank	Estimation Error Est Err 5,115,315 5.1M Rank
Estimation Error Est Err 2,780,627 2.8M Rank		Estimation Error Est Err 2,785,293 2.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,923,320 4.9M Rank	Estimation Error Est Err 4,700 4.7K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 35,967 36K Rank	Estimation Error Est Err 35,937 36K Rank	Estimation Error Est Err 50,957 51K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 44,092 44K Rank	Estimation Error Est Err 4,700 4.7K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 51,116 51K Rank	Estimation Error Est Err 50,973 51K Rank	Estimation Error Est Err 71,284 71K Rank	Estimation Error Est Err 8,092 8.1K Rank	Estimation Error Est Err 61,385 61K Rank	Estimation Error Est Err 4,701 4.7K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,774,273 1.8M Rank		Estimation Error Est Err 31,113 31K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,782,334 1.8M Rank	Estimation Error Est Err 4,716 4.7K Rank	Estimation Error Est Err 1,774,288 1.8M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 2110 4700 INNER JOIN HASH ON id = info_type_id 1 1 │└TABLE SCAN info_type WHERE info = rating 1020 4700 INNER JOIN HASH ON id18 = movie_id34 3073 7874 │└INNER JOIN HASH ON id18 = movie_id 4236 12951 │ │└INNER JOIN HASH ON id6 = 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 804571 3767 TABLE SCAN movie_info_idx WHERE info > 2.0
	Estimate Actual Operator - 1 PROJECT a1 AS rating, a2 AS movie_title - 1 AGGREGATE MIN(info) AS a1, MIN(title) AS a2 - 4700 PROJECT info, title - 4700 PROJECT info, title - 4700 INNER JOIN HASH ON tuple(PROJECTION_5046.id,PROJECTION_5046.id) = tuple(PROJECTION_5025.movie_id,PROJECTION_5025.movie_id) - 8092 │└PROJECT movie_id, movie_id, info - 8092 │ PROJECT info, movie_id, movie_id - 8092 │ INNER JOIN HASH ON PROJECTION_5043.id = PROJECTION_5028.keyword_id - 3435922 │ │└PROJECT keyword_id, info, movie_id, movie_id - 3435922 │ │ PROJECT info, movie_id, movie_id, keyword_id - 3435922 │ │ INNER JOIN HASH ON PROJECTION_5040.movie_id = PROJECTION_5031.movie_id - 448969 │ │ │└PROJECT movie_id AS movie_id_right, info - 448969 │ │ │ PROJECT info, movie_id - 448969 │ │ │ INNER JOIN HASH ON PROJECTION_5037.info_type_id = PROJECTION_5034.id - 1 │ │ │ │└PROJECT id - 1 │ │ │ │ PROJECT id - 1 │ │ │ │ TABLE SCAN info_type WHERE info = 'rating' - 806365 │ │ │ PROJECT info_type_id, info, movie_id - 806365 │ │ │ PROJECT info_type_id, info, movie_id - 806365 │ │ │ TABLE SCAN movie_info_idx WHERE info > '2.0' - 4523930 │ │ PROJECT movie_id AS movie_id_left, keyword_id - 4523930 │ │ PROJECT movie_id, keyword_id - 4523930 │ │ TABLE SCAN movie_keyword - 30 │ PROJECT id - 30 │ PROJECT id - 30 │ TABLE SCAN keyword WHERE keyword LIKE '%sequel%' - 1749032 PROJECT id, title - 1749032 PROJECT id, title - 1749032 TABLE SCAN title WHERE production_year > 1990
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 373 4700 PROJECT info, title 373 4700 INNER JOIN HASH ON id = keyword_id 1811 2012691 │└INNER JOIN HASH ON movie_id = movie_id 994 294217 │ │└INNER JOIN HASH ON id = movie_id 4885 448969 │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ TABLE SCAN info_type WHERE info = 'rating' 276007 448969 │ │ │ TABLE SCAN movie_info_idx WHERE info > '2.0' 505662 299419 │ │ FILTER id BETWEEN 2 AND 2525793 505662 299422 │ │ TABLE SCAN title WHERE production_year > 1990 4523930 2032205 │ TABLE SCAN movie_keyword WHERE movie_id <= 2525793 26834 30 TABLE SCAN keyword WHERE contains(keyword,'sequel')
	Estimate Actual Operator 1 1 PROJECT rating, movie_title 1 1 AGGREGATE MIN(info), MIN(title) 98863 10 DISTRIBUTE GATHER 98863 10 AGGREGATE MIN(info), MIN(title) 98863 4700 INNER JOIN HASH ON movie_id = id AND movie_id = id 98863 8092 │└DISTRIBUTE GATHER 98863 8092 │ 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%' 494319 3435922 │ INNER JOIN HASH ON movie_id = movie_id 276007 448969 │ │└DISTRIBUTE HASH ON movie_id 276007 448969 │ │ INNER JOIN HASH ON id = info_type_id 23 1 │ │ │└DISTRIBUTE GATHER 23 1 │ │ │ FILTER info = 'rating' 113 113 │ │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ │ TABLE SCAN info_type WHERE info = 'rating' 276007 806365 │ │ FILTER info > '2.0' 1380035 1380035 │ │ TABLE SCAN movie_info_idx WHERE (info > '2.0') AND (((info_type_id >= 101) AND (info_type_id <= 101)) AND info_type_id IN 101) 4523930 4523930 │ DISTRIBUTE HASH ON movie_id 4523930 4523930 │ TABLE SCAN movie_keyword WHERE CASE MOD(HASH_REPARTITION movie_id,10) WHEN 0 THEN (((movie_id >= 67) AND (movie_id <= 2525666)) AND TRUE) WHEN 1 THEN (((movie_id >= 63) AND (movie_id <= 2525670)) AND TRUE) WHEN 2 THEN (((movie_id >= 24) AND (movie_id <= 2525791)) AND TRUE) WHEN 3 THEN (((movie_id >= 2) AND (movie_id <= 2525793)) AND TRUE) WHEN 4 THEN (((movie_id >= 55) AND (movie_id <= 2525632)) AND TRUE) WHEN 5 THEN (((movie_id >= 54) AND (movie_id <= 2525669)) AND TR... 523722 1749032 FILTER production_year > 1990 2528312 2528312 TABLE SCAN title WHERE (production_year > 1990) AND ((((id >= 963) AND (id <= 2525558)) AND ((id >= 963) AND (id <= 2525558))) AND TRUE)
	Estimate Actual Operator 1 1 AGGREGATE MIN(mi_idx.info), MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(mi_idx.info), MIN(t.title) 660000 4700 INNER JOIN HASH ON mi_idx.info_type_id = it.id 660000 1 │└DISTRIBUTE GATHER 4520000 1 │ TABLE SCAN info_type WHERE it.info = 'rating' 3300000 8600 INNER JOIN HASH ON mk.movie_id = mi_idx.movie_id 3300000 7874 │└DISTRIBUTE GATHER 3300000 7874 │ INNER JOIN HASH ON mk.movie_id = t.id 3300000 12951 │ │└DISTRIBUTE GATHER 3300000 12951 │ │ INNER JOIN HASH ON mk.keyword_id = k.id 3300000 30 │ │ │└DISTRIBUTE GATHER 113 30 │ │ │ TABLE SCAN keyword WHERE contains(k.keyword,'sequel') 1380000 4519836 │ │ TABLE SCAN movie_keyword 2530000 1743470 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 1990L) 134000 803939 TABLE SCAN movie_info_idx WHERE mi_idx.info > '2.0'
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1018) AS Expr1010, MIN(partialagg1019) AS Expr1011 5 1 AGGREGATE MIN(info as info) AS partialagg1018, MIN(title as title) AS partialagg1019 779 4700 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 4700 │└TABLE SEEK title AS t WHERE production_year as production_year > 1990 1128 8092 SORT Expr1020 1128 8092 PROJECT BmkToPage Bmk1008 AS Expr1020 1128 8092 INNER JOIN LOOP ON mk.movie_id = t.id 1 8092 │└TABLE SEEK title AS t 1128 8092 INNER JOIN HASH ON mi_idx.info_type_id = it.id 1 1 │└FILTER info as info = 'rating' 113 113 │ TABLE SCAN info_type AS it 5644 14599 INNER JOIN HASH ON mi_idx.movie_id = mk.movie_id 4309 12951 │└INNER JOIN LOOP ON Bmk1006 = Bmk1006 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%' 804005 12279 TABLE SEEK movie_info_idx AS mi_idx WHERE (PROBE(Bitmap1026,movie_id as movie_id,N'IN ROW')) AND (info as info > '2.0')
	Estimate Actual Operator 1 ∞ PROJECT min AS rating, min_19 AS movie_title 1 1 AGGREGATE MIN(min_20) AS min, MIN(min_21) AS min_19 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(info_5) AS min_20, MIN(title) AS min_21 - 4700 INNER JOIN HASH ON movie_id = id_14 512448 1749032 │└DISTRIBUTE HASH ON id_14 512448 1749032 │ PROJECT id AS id_14, title 512448 1749032 │ FILTER production_year > 1990 512448 1749032 │ TABLE SCAN title - 8092 INNER JOIN HASH ON keyword_id = id_0 134170 30 │└DISTRIBUTE GATHER 134170 30 │ PROJECT id AS id_0 134170 30 │ FILTER keyword LIKE '%sequel%' 134170 30 │ TABLE SCAN keyword - 8092 INNER JOIN HASH ON movie_id = movie_id_10 4523930 12951 │└DISTRIBUTE HASH ON movie_id_10 4523930 12951 │ PROJECT movie_id AS movie_id_10, keyword_id 4523930 12951 │ TABLE SCAN movie_keyword - 6837 INNER JOIN HASH ON id = info_type_id 1380035 12259 │└DISTRIBUTE HASH ON info_type_id 1380035 12259 │ PROJECT movie_id, info_type_id, info AS info_5 1380035 12259 │ FILTER info > '2.0' 1380035 12259 │ TABLE SCAN movie_info_idx 102 1 FILTER info = 'rating' 102 1 TABLE SCAN info_type
	Estimate Actual Operator 1 1 AGGREGATE MIN(info), MIN(title) 5 4700 INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 7 8092 │└INNER JOIN LOOP ON keyword_id = id AND id = info_type_id AND (id = info_type_id) 1 1 │ │└TABLE SEEK info_type AS it 799 14957 │ INNER JOIN LOOP ON movie_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 25902 14893 │ TABLE SEEK movie_info_idx AS mi_idx WHERE mi_idx.info > '2.0' 8092 8092 TABLE SEEK title AS t WHERE t.production_year > 1990