Planner — IMDB — JOB-4B

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 > '9.0'
  AND t.production_year > 2010
  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,524,338 4.5M Rank		Estimation Error Est Err 4,525,377 4.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,710 1.7K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,923,949 4.9M Rank		Estimation Error Est Err 9,234 9.2K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,915,698 4.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,666,708 4.7M Rank		Estimation Error Est Err 4,287,892 4.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 404,939 405K Rank	Estimation Error Est Err 7 7 Rank	Estimation Error Est Err 392,103 392K Rank
Estimation Error Est Err 6,966,178 7M Rank		Estimation Error Est Err 4,665,056 4.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,388 6.4K Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 4,658,580 4.7M Rank
Estimation Error Est Err 26,413 26K Rank		Estimation Error Est Err 26,415 26K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,750 2.8K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 25,956 26K Rank	Estimation Error Est Err 25,926 26K Rank	Estimation Error Est Err 25,944 26K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 26,229 26K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 12,539 13K Rank	Estimation Error Est Err 12,396 12K Rank	Estimation Error Est Err 18,443 18K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 12,426 12K Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 404,891 405K Rank		Estimation Error Est Err 82 82 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 404,890 405K Rank	Estimation Error Est Err 22 22 Rank	Estimation Error Est Err 404,906 405K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 1 6 INNER JOIN HASH ON id = keyword_id 131 30 │└TABLE SCAN keyword WHERE keyword LIKE '%sequel%' 13 1070 INNER JOIN HASH ON id20 = movie_id36 7 72 │└INNER JOIN HASH ON id20 = movie_id 48 305 │ │└INNER JOIN HASH ON id7 = info_type_id 1 1 │ │ │└TABLE SCAN info_type WHERE info = rating 5390 305 │ │ TABLE SCAN movie_info_idx WHERE info > 9.0 365420 72 │ TABLE SCAN title WHERE production_year >= 2011 4523930 4523930 TABLE SCAN movie_keyword
	Estimate Actual Operator - 1 PROJECT a1 AS rating, a2 AS movie_title - 1 AGGREGATE MIN(info) AS a1, MIN(title) AS a2 - 0 PROJECT info, title - 0 PROJECT info, title - 0 INNER JOIN HASH ON PROJECTION_4999.keyword_id = PROJECTION_4996.id - 30 │└PROJECT id - 30 │ PROJECT id - 30 │ TABLE SCAN keyword WHERE keyword LIKE '%sequel%' - 0 PROJECT keyword_id, info, title - 0 PROJECT info, keyword_id, title - 0 INNER JOIN HASH ON tuple(PROJECTION_5005.movie_id,PROJECTION_5005.id) = tuple(PROJECTION_5002.movie_id,PROJECTION_5002.movie_id) - 4523930 │└PROJECT movie_id AS movie_id_right, keyword_id - 4523930 │ PROJECT movie_id, keyword_id - 4523930 │ TABLE SCAN movie_keyword - 72 PROJECT movie_id AS movie_id_left, id, info, title - 72 PROJECT info, movie_id, title, id - 72 INNER JOIN HASH ON PROJECTION_5011.info_type_id = PROJECTION_5008.id - 1 │└PROJECT id AS id_right - 1 │ PROJECT id - 1 │ TABLE SCAN info_type WHERE info = 'rating' - 840 PROJECT info_type_id, info, movie_id, title, id AS id_left - 840 PROJECT info, info_type_id, movie_id, title, id - 840 INNER JOIN HASH ON PROJECTION_5017.movie_id = PROJECTION_5014.id - 391666 │└PROJECT id, title - 391666 │ PROJECT id, title - 391666 │ TABLE SCAN title WHERE production_year > 2010 - 8322 PROJECT movie_id, info, info_type_id - 8322 PROJECT info_type_id, info, movie_id - 8322 TABLE SCAN movie_info_idx WHERE info > '9.0'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 373 6 PROJECT info, title 373 6 INNER JOIN HASH ON id = keyword_id 1811 1070 │└INNER JOIN HASH ON movie_id = movie_id 994 72 │ │└INNER JOIN HASH ON id = movie_id 4885 305 │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ TABLE SCAN info_type WHERE info = 'rating' 276007 305 │ │ │ TABLE SCAN movie_info_idx WHERE info > '9.0' 505662 3608 │ │ FILTER id BETWEEN 2 AND 2525793 505662 3609 │ │ TABLE SCAN title WHERE production_year > 2010 4523930 22496 │ TABLE SCAN movie_keyword WHERE movie_id <= 2525793 26834 2 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 6 INNER JOIN HASH ON movie_id = id AND movie_id = id 98863 21 │└DISTRIBUTE GATHER 98863 21 │ 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 6032 │ INNER JOIN HASH ON movie_id = movie_id 276007 305 │ │└DISTRIBUTE HASH ON movie_id 276007 305 │ │ 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 8322 │ │ FILTER info > '9.0' 1380035 1380035 │ │ TABLE SCAN movie_info_idx WHERE (info > '9.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 >= 180634) AND (movie_id <= 2480703)) AND movie_id IN(1218702,1448000,2279139,2373344,2480703,1888226,1682937,785572,983555,992207,1019178,1043521,1729971,180634,180638,180655,180658,509074,509077,615754,615759,615866,676346,2106177,2149846,2196990,2211742,2218634)) WHEN 1 THEN (((movie_id >= 120759) AND (movie_id <= 2294632)) AND movie_id IN(1149021,1149027,1303584,1449448,22... 162535 126772 FILTER production_year > 2010 2528312 927930 TABLE SCAN title WHERE (production_year > 2010) AND ((((id >= 1699377) AND (id <= 2464061)) AND ((id >= 1699377) AND (id <= 2464061))) AND struct(id,id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > ))
	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) 293000 6 INNER JOIN HASH ON mi_idx.info_type_id = it.id 293000 1 │└DISTRIBUTE GATHER 4520000 1 │ TABLE SCAN info_type WHERE it.info = 'rating' 1470000 19 INNER JOIN HASH ON mk.movie_id = mi_idx.movie_id 1470000 405 │└DISTRIBUTE GATHER 1470000 405 │ INNER JOIN HASH ON mk.movie_id = t.id 1470000 391666 │ │└DISTRIBUTE GATHER 2530000 391666 │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2010L) 3300000 12862 │ INNER JOIN HASH ON mk.keyword_id = k.id 3300000 30 │ │└DISTRIBUTE GATHER 113 30 │ │ TABLE SCAN keyword WHERE contains(k.keyword,'sequel') 1380000 4269979 │ TABLE SCAN movie_keyword 134000 5032 TABLE SCAN movie_info_idx WHERE mi_idx.info > '9.0'
	Estimate Actual Operator 1 1 AGGREGATE MIN(info as info) AS Expr1010, MIN(title as title) AS Expr1011 5 6 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 6 │└TABLE SEEK title AS t WHERE production_year as production_year > 2010 8 21 PROJECT BmkToPage Bmk1008 AS Expr1037 8 21 INNER JOIN LOOP ON mk.movie_id = t.id 1 21 │└TABLE SEEK title AS t 8 21 INNER JOIN HASH ON mk.keyword_id = k.id 47 30 │└TABLE SCAN keyword AS k WHERE keyword as keyword LIKE '%sequel%' 9413 6032 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 6032 │└TABLE SEEK movie_keyword AS mk 9413 6032 INNER JOIN LOOP ON mi_idx.movie_id = mk.movie_id 7 6032 │└TABLE SEEK movie_keyword AS mk 1277 305 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 6387 305 TABLE SEEK movie_info_idx AS mi_idx WHERE (PROBE(Bitmap1033,info_type_id as info_type_id,N'IN ROW')) AND (info as info > '9.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 - 6 INNER JOIN HASH ON movie_id = id_14 159036 391666 │└DISTRIBUTE HASH ON id_14 159036 391666 │ PROJECT id AS id_14, title 159036 391666 │ FILTER production_year > 2010 159036 391666 │ TABLE SCAN title - 21 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 - 21 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 - 19 INNER JOIN HASH ON id = info_type_id 1380035 243 │└DISTRIBUTE HASH ON info_type_id 1380035 243 │ PROJECT movie_id, info_type_id, info AS info_5 1380035 243 │ FILTER info > '9.0' 1380035 243 │ 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) 1 6 INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 1 21 │└INNER JOIN LOOP ON id = info_type_id 6 306 │ │└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 12951 12951 │ │ TABLE SEEK movie_info_idx AS mi_idx WHERE mi_idx.info > '9.0' 3 3 │ TABLE SEEK info_type AS it WHERE it.info = 'rating' 21 21 TABLE SEEK title AS t WHERE t.production_year > 2010