Planner — IMDB — JOB-4A

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 > '5.0'
  AND t.production_year > 2005
  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,526,048 4.5M Rank		Estimation Error Est Err 4,527,645 4.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 28,877 29K Rank	Estimation Error Est Err 740 740 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,054,563 6.1M Rank		Estimation Error Est Err 812,001 812K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,054,562 6.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,993,663 6M Rank		Estimation Error Est Err 5,994,775 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 28,877 29K Rank	Estimation Error Est Err 741 741 Rank	Estimation Error Est Err 1,028,138 1M Rank
Estimation Error Est Err 8,566,560 8.6M Rank		Estimation Error Est Err 8,936,189 8.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,246,287 3.2M Rank	Estimation Error Est Err 750 750 Rank	Estimation Error Est Err 5,017,064 5M Rank
Estimation Error Est Err 1,170,159 1.2M Rank		Estimation Error Est Err 1,170,868 1.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,101,282 2.1M Rank	Estimation Error Est Err 740 740 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 31,753 32K Rank	Estimation Error Est Err 31,723 32K Rank	Estimation Error Est Err 40,185 40K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 37,542 38K Rank	Estimation Error Est Err 740 740 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 372,757 373K Rank	Estimation Error Est Err 372,727 373K Rank	Estimation Error Est Err 366,223 366K Rank	Estimation Error Est Err 12,951 13K Rank	Estimation Error Est Err 21,104 21K Rank	Estimation Error Est Err 750 750 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,033,174 1M Rank		Estimation Error Est Err 22,542 23K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,038,963 1M Rank	Estimation Error Est Err 756 756 Rank	Estimation Error Est Err 1,033,189 1M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 1047 740 INNER JOIN HASH ON id = info_type_id 1 1 │└TABLE SCAN info_type WHERE info = rating 407 740 INNER JOIN HASH ON id18 = movie_id34 1834 2235 │└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 1049348 1523 │ TABLE SCAN title WHERE production_year >= 2006 537728 564 TABLE SCAN movie_info_idx WHERE info > 5.0
	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_4970.keyword_id = PROJECTION_4967.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_4976.movie_id,PROJECTION_4976.id) = tuple(PROJECTION_4973.movie_id,PROJECTION_4973.movie_id) - 4523930 │└PROJECT movie_id AS movie_id_right, keyword_id - 4523930 │ PROJECT movie_id, keyword_id - 4523930 │ TABLE SCAN movie_keyword - 106021 PROJECT movie_id AS movie_id_left, id, info, title - 106021 PROJECT info, movie_id, title, id - 106021 INNER JOIN HASH ON PROJECTION_4982.movie_id = PROJECTION_4979.id - 1012920 │└PROJECT id, title - 1012920 │ PROJECT id, title - 1012920 │ TABLE SCAN title WHERE production_year > 2005 - 352990 PROJECT movie_id, info - 352990 PROJECT info, movie_id - 352990 INNER JOIN HASH ON PROJECTION_4988.id = PROJECTION_4985.info_type_id - 517682 │└PROJECT info_type_id, info, movie_id - 517682 │ PROJECT info_type_id, info, movie_id - 517682 │ TABLE SCAN movie_info_idx WHERE info > '5.0' - 1 PROJECT id - 1 PROJECT id - 1 TABLE SCAN info_type WHERE info = 'rating'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 373 740 PROJECT info, title 373 740 INNER JOIN HASH ON id = keyword_id 1811 697651 │└INNER JOIN HASH ON movie_id = movie_id 994 106021 │ │└INNER JOIN HASH ON id = movie_id 4885 352990 │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ TABLE SCAN info_type WHERE info = 'rating' 276007 352990 │ │ │ TABLE SCAN movie_info_idx WHERE info > '5.0' 505662 107758 │ │ FILTER id BETWEEN 2 AND 2525793 505662 107758 │ │ TABLE SCAN title WHERE production_year > 2005 4523930 709380 │ 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 740 INNER JOIN HASH ON movie_id = id AND movie_id = id 98863 5820 │└DISTRIBUTE GATHER 98863 5820 │ 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 2881657 │ INNER JOIN HASH ON movie_id = movie_id 276007 352990 │ │└DISTRIBUTE HASH ON movie_id 276007 352990 │ │ 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 517682 │ │ FILTER info > '5.0' 1380035 1380035 │ │ TABLE SCAN movie_info_idx WHERE (info > '5.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 <= 2525664)) AND TRUE) WHEN 1 THEN (((movie_id >= 63) AND (movie_id <= 2525670)) AND TRUE) WHEN 2 THEN (((movie_id >= 51) AND (movie_id <= 2525791)) AND TRUE) WHEN 3 THEN (((movie_id >= 11) 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 T... 252832 1012920 FILTER production_year > 2005 2528312 2528312 TABLE SCAN title WHERE (production_year > 2005) 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) 456000 740 INNER JOIN HASH ON mi_idx.info_type_id = it.id 456000 1 │└DISTRIBUTE GATHER 4520000 1 │ TABLE SCAN info_type WHERE it.info = 'rating' 2280000 1143 INNER JOIN HASH ON mk.movie_id = mi_idx.movie_id 2280000 2235 │└DISTRIBUTE GATHER 2280000 2235 │ INNER JOIN HASH ON mk.movie_id = t.id 2280000 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 2280000 1012920 │ DISTRIBUTE HASH ON t.id 2530000 1012920 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2005L) 134000 460876 TABLE SCAN movie_info_idx WHERE mi_idx.info > '5.0'
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1018) AS Expr1010, MIN(partialagg1019) AS Expr1011 5 10 AGGREGATE MIN(info as info) AS partialagg1018, MIN(title as title) AS partialagg1019 287 740 INNER JOIN HASH ON mk.keyword_id = k.id 47 30 │└TABLE SCAN keyword AS k WHERE keyword as keyword LIKE '%sequel%' 301 740 INNER JOIN HASH ON mi_idx.info_type_id = it.id 1 1 │└FILTER info as info = 'rating' 113 113 │ INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 113 │ │└TABLE SEEK info_type AS it 113 113 │ TABLE SEEK info_type AS it 150 740 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 740 │└TABLE SEEK title AS t WHERE production_year as production_year > 2005 378 5820 PROJECT BmkToPage Bmk1008 AS Expr1135 378 5820 INNER JOIN LOOP ON mk.movie_id = t.id 1 5820 │└TABLE SEEK title AS t 378 5820 INNER JOIN MERGE ON movie_id as movie_id = movie_id as movie_id 4523 12951 │└SORT movie_id 4523 12951 │ TABLE SEEK movie_keyword AS mk WHERE BLOOM(keyword_id as keyword_id) 51373 352990 TABLE SEEK movie_info_idx AS mi_idx WHERE info as info > '5.0' AND BLOOM(info_type_id as info_type_id)
	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 - 740 INNER JOIN HASH ON movie_id = id_14 247389 1012920 │└DISTRIBUTE HASH ON id_14 247389 1012920 │ PROJECT id AS id_14, title 247389 1012920 │ FILTER production_year > 2005 247389 1012920 │ TABLE SCAN title - 5820 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 - 5820 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 - 5082 INNER JOIN HASH ON id = info_type_id 1380035 7272 │└DISTRIBUTE HASH ON info_type_id 1380035 7272 │ PROJECT movie_id, info_type_id, info AS info_5 1380035 7272 │ FILTER info > '5.0' 1380035 7272 │ 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) 2 740 INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 4 5820 │└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 481 8463 │ 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 > '5.0' 5820 5820 TABLE SEEK title AS t WHERE t.production_year > 2005