Planner — IMDB — JOB-1B

SELECT MIN(mc.note) AS production_note,
       MIN(t.title) AS movie_title,
       MIN(t.production_year) AS movie_year
FROM job.company_type AS ct,
     job.info_type AS it,
     job.movie_companies AS mc,
     job.movie_info_idx AS mi_idx,
     job.title AS t
WHERE ct.kind = 'production companies'
  AND it.info = 'bottom 10 rank'
  AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
  AND t.production_year BETWEEN 2005 AND 2010
  AND ct.id = mc.company_type_id
  AND t.id = mc.movie_id
  AND t.id = mi_idx.movie_id
  AND mc.movie_id = mi_idx.movie_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 1,380,107 1.4M Rank		Estimation Error Est Err 1,380,171 1.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 89 89 Rank	Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,433,384 3.4M Rank		Estimation Error Est Err 1,708,352 1.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,053,367 2.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 857,092 857K Rank		Estimation Error Est Err 328,161 328K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 857,100 857K Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 716,272 716K Rank
Estimation Error Est Err 3,931,315 3.9M Rank		Estimation Error Est Err 210,645 211K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 998,072 998K Rank	Estimation Error Est Err 13 13 Rank	Estimation Error Est Err 883,917 884K Rank
Estimation Error Est Err 265,735 266K Rank		Estimation Error Est Err 265,736 266K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 27 27 Rank	Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,380,115 1.4M Rank	Estimation Error Est Err 79 79 Rank	Estimation Error Est Err 1,380,177 1.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 74 74 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 251 251 Rank	Estimation Error Est Err 244 244 Rank	Estimation Error Est Err 137 137 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 144 144 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,043,765 2M Rank		Estimation Error Est Err 262,630 263K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,043,768 2M Rank	Estimation Error Est Err 19 19 Rank	Estimation Error Est Err 2,043,780 2M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 1509 3 INNER JOIN HASH ON id = company_type_id 1 1 │└TABLE SCAN company_type WHERE kind = production companies 6037 66 INNER JOIN HASH ON movie_id35 = movie_id 3907 4 │└INNER JOIN HASH ON id19 = movie_id 12213 10 │ │└INNER JOIN HASH ON id6 = info_type_id 1 1 │ │ │└TABLE SCAN info_type WHERE info = bottom 10 rank 1380035 1380035 │ │ TABLE SCAN movie_info_idx 775283 4 │ TABLE SCAN title WHERE production_year BETWEEN 2005 AND 2010 1368263 66 TABLE SCAN movie_companies WHERE NOT (note38 LIKE '%(as Metro-Goldwyn-Mayer Pictures)%')
	Estimate Actual Operator - 1 PROJECT a1 AS production_note, a2 AS movie_title, a3 AS movie_year - 1 AGGREGATE MIN(note) AS a1, MIN(title) AS a2, MIN(production_year) AS a3 - 0 PROJECT note, title, production_year - 0 PROJECT note, title, production_year - 0 INNER JOIN HASH ON tuple(PROJECTION_1865.movie_id,PROJECTION_1865.id) = tuple(PROJECTION_1856.movie_id,PROJECTION_1856.movie_id) - 10 │└PROJECT movie_id AS movie_id_right - 10 │ PROJECT movie_id - 10 │ INNER JOIN HASH ON PROJECTION_1862.info_type_id = PROJECTION_1859.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'bottom 10 rank' - 1380035 │ PROJECT info_type_id, movie_id - 1380035 │ PROJECT movie_id, info_type_id - 1380035 │ TABLE SCAN movie_info_idx - 46509 PROJECT movie_id AS movie_id_left, id, note, title, production_year - 46509 PROJECT note, movie_id, title, production_year, id - 46509 INNER JOIN HASH ON PROJECTION_1871.movie_id = PROJECTION_1868.id - 716259 │└PROJECT id, title, production_year - 716259 │ PROJECT id, production_year, title - 716259 │ TABLE SCAN title WHERE (production_year >= 2005) AND (production_year <= 2010) - 140904 PROJECT movie_id, note - 140904 PROJECT note, movie_id - 140904 INNER JOIN HASH ON PROJECTION_1877.id = PROJECTION_1874.company_type_id - 1337088 │└PROJECT company_type_id, note, movie_id - 1337088 │ PROJECT company_type_id, note, movie_id - 1337088 │ TABLE SCAN movie_companies WHERE notLike(note,'%(as Metro-Goldwyn-Mayer Pictures)%') - 1 PROJECT id - 1 PROJECT id - 1 TABLE SCAN company_type WHERE kind = 'production companies'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 261 3 PROJECT note, title, production_year 261 3 INNER JOIN HASH ON company_type_id = id 1 1 │└FILTER id <= 2 1 1 │ TABLE SCAN company_type WHERE kind = 'production companies' 1044 3 INNER JOIN HASH ON movie_id = movie_id 4972 4 │└INNER JOIN HASH ON id = movie_id 24425 10 │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │└FILTER id >= 99 2 1 │ │ │ TABLE SCAN info_type WHERE info = 'bottom 10 rank' 1380035 10 │ │ TABLE SCAN movie_info_idx WHERE movie_id <= 2525745 505662 182508 │ FILTER id BETWEEN 2 AND 2525745 505662 182508 │ TABLE SCAN title WHERE production_year >= 2005 AND production_year <= 2010 521825 83215 TABLE SCAN movie_companies WHERE NOT contains(note,'(as Metro-Goldwyn-Mayer Pictures)')
	Estimate Actual Operator 1 1 PROJECT production_note, movie_title, movie_year 1 1 AGGREGATE MIN(note), MIN(title), MIN(production_year) 108357 10 DISTRIBUTE GATHER 108357 10 AGGREGATE MIN(note), MIN(title), MIN(production_year) 108357 3 PROJECT note, title, production_year 108357 3 INNER JOIN HASH ON id = movie_id AND id = movie_id 108357 716259 │└DISTRIBUTE GATHER 108357 716259 │ FILTER (production_year >= 2005) AND (production_year <= 2010) 2528312 2528312 │ TABLE SCAN title WHERE (production_year >= 2005) AND (production_year <= 2010) 260913 5 INNER JOIN HASH ON id = info_type_id 23 1 │└DISTRIBUTE GATHER 23 1 │ FILTER info = 'bottom 10 rank' 113 113 │ DISTRIBUTE ROUND ROBIN 113 113 │ TABLE SCAN info_type WHERE info = 'bottom 10 rank' 260913 5097 INNER JOIN HASH ON movie_id = movie_id 260913 140904 │└DISTRIBUTE HASH ON movie_id 260913 140904 │ INNER JOIN HASH ON id = company_type_id 1 1 │ │└DISTRIBUTE GATHER 1 1 │ │ FILTER kind = 'production companies' 4 4 │ │ DISTRIBUTE ROUND ROBIN 4 4 │ │ TABLE SCAN company_type WHERE kind = 'production companies' 521826 178918 │ FILTER note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' 2609129 1376261 │ TABLE SCAN movie_companies WHERE (note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND (((company_type_id >= 2) AND (company_type_id <= 2)) AND company_type_id IN 2)) AND ((((movie_id >= 2) AND (movie_id <= 2528297)) AND ((movie_id >= 2) AND (movie_id <= 2528297))) AND TRUE) 1380035 26625 DISTRIBUTE HASH ON movie_id 1380035 26625 TABLE SCAN movie_info_idx WHERE CASE MOD(HASH_REPARTITION movie_id,10) WHEN 0 THEN (((movie_id >= 286) AND (movie_id <= 2525688)) AND TRUE) WHEN 1 THEN (((movie_id >= 350) AND (movie_id <= 2525697)) AND TRUE) WHEN 2 THEN (((movie_id >= 921) AND (movie_id <= 2525701)) AND TRUE) WHEN 3 THEN (((movie_id >= 320) AND (movie_id <= 2525700)) AND TRUE) WHEN 4 THEN (((movie_id >= 925) AND (movie_id <= 2525716)) AND TRUE) WHEN 5 THEN (((movie_id >= 290) AND (movie_id <= 2525689)) AN...
	Estimate Actual Operator 1 1 AGGREGATE MIN(mc.note), MIN(t.title), MIN(t.production_year) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(mc.note), MIN(t.title), MIN(t.production_year) 401000 3 INNER JOIN HASH ON mc.movie_id = mi_idx.movie_id 401000 10 │└DISTRIBUTE GATHER 276000 10 │ INNER JOIN HASH ON mi_idx.info_type_id = it.id 276000 1 │ │└DISTRIBUTE GATHER 4 1 │ │ TABLE SCAN info_type WHERE it.info = 'bottom 10 rank' 113 10 │ TABLE SCAN movie_info_idx 276000 46509 INNER JOIN HASH ON mc.movie_id = t.id 276000 716259 │└DISTRIBUTE GATHER 1380000 716259 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year >= 2005L) AND (t.production_year <= 2010L) 401000 140821 INNER JOIN HASH ON ct.id = mc.company_type_id 401000 1 │└DISTRIBUTE GATHER 2610000 1 │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 2530000 140821 TABLE SCAN movie_companies WHERE (mc.note IS NOT NULL) AND ( NOT contains(mc.note,'(as Metro-Goldwyn-Mayer Pictures)'))
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1028) AS Expr1010, MIN(partialagg1029) AS Expr1011, MIN(partialagg1030) AS Expr1012 5 1 AGGREGATE MIN(note as note) AS partialagg1028, MIN(title as title) AS partialagg1029, MIN(production_year as production_year) AS partialagg1030 83497 3 INNER JOIN HASH ON mc.company_type_id = ct.id 1 1 │└FILTER kind as kind = 'production companies' 4 4 │ INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 4 │ │└TABLE SEEK company_type AS ct 4 4 │ TABLE SEEK company_type AS ct 166996 3 INNER JOIN HASH ON mc.movie_id = t.id 77928 4 │└INNER JOIN HASH ON t.id = mi_idx.movie_id 276007 10 │ │└INNER JOIN HASH ON mi_idx.info_type_id = it.id 1 1 │ │ │└FILTER info as info = 'bottom 10 rank' 113 113 │ │ │ INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 113 │ │ │ │└TABLE SEEK info_type AS it 113 113 │ │ │ TABLE SEEK info_type AS it 276007 10 │ │ TABLE SEEK movie_info_idx AS mi_idx 71385 4 │ TABLE SCAN title AS t WHERE (production_year as production_year >= 2005 AND production_year as production_year <= 2010) AND (BLOOM(id as id)) 132347 3 TABLE SCAN movie_companies AS mc WHERE NOT note as note LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND BLOOM(movie_id as movie_id) AND BLOOM(company_type_id as company_type_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS production_note, min_19 AS movie_title, min_20 AS movie_year 1 1 AGGREGATE MIN(min_21) AS min, MIN(min_22) AS min_19, MIN(min_23) AS min_20 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(note) AS min_21, MIN(title) AS min_22, MIN(production_year) AS min_23 - 3 INNER JOIN HASH ON movie_id = id_15 88353 716259 │└DISTRIBUTE HASH ON id_15 88353 716259 │ PROJECT id AS id_15, title, production_year 88353 716259 │ FILTER production_year BETWEEN 2005 AND 2010 88353 716259 │ TABLE SCAN title - 5 INNER JOIN HASH ON info_type_id = id_0 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_0 113 1 │ FILTER info = 'bottom 10 rank' 113 1 │ TABLE SCAN info_type - 5 INNER JOIN HASH ON movie_id = movie_id_9 1380035 10 │└DISTRIBUTE HASH ON movie_id_9 1380035 10 │ PROJECT movie_id AS movie_id_9, info_type_id 1380035 10 │ TABLE SCAN movie_info_idx - 131310 INNER JOIN HASH ON id = company_type_id 2348216 1327494 │└DISTRIBUTE HASH ON company_type_id 2348216 1327494 │ FILTER NOT (note LIKE '%(as Metro-Goldwyn-Mayer Pictures)%') 2348216 1327494 │ TABLE SCAN movie_companies 4 1 FILTER kind = 'production companies' 4 1 TABLE SCAN company_type
	Estimate Actual Operator 1 1 AGGREGATE MIN(note), MIN(title), MIN(production_year) 3 3 AGGREGATE PARTIAL MIN(note), PARTIAL MIN(title), PARTIAL MIN(production_year) 923 1 INNER JOIN HASH ON company_type_id = id 1 1 │└TABLE SCAN company_type AS ct WHERE ct.kind = 'production companies' 11076 66 INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 4221 3 │└INNER JOIN LOOP ON id = movie_id 5089 3 │ │└INNER JOIN HASH ON info_type_id = id 3 3 │ │ │└TABLE SEEK info_type AS it 1725045 1380035 │ │ TABLE SCAN movie_info_idx AS mi_idx 10 10 │ TABLE SEEK title AS t WHERE (t.production_year >= 2005) AND (t.production_year <= 2010) 12 66 TABLE SEEK movie_companies AS mc WHERE mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'