Planner — IMDB — JOB-1D

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 >2000
  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,139 1.4M Rank		Estimation Error Est Err 1,380,239 1.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 120 120 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,098,578 4.1M Rank		Estimation Error Est Err 1,747,413 1.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,718,561 2.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,455,740 1.5M Rank		Estimation Error Est Err 1,530,013 1.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 74,301 74K Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 1,381,472 1.4M Rank
Estimation Error Est Err 3,931,315 3.9M Rank		Estimation Error Est Err 1,592,093 1.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 281,818 282K Rank	Estimation Error Est Err 14 14 Rank	Estimation Error Est Err 1,549,116 1.5M Rank
Estimation Error Est Err 430,741 431K Rank		Estimation Error Est Err 430,743 431K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 30 30 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,380,127 1.4M Rank	Estimation Error Est Err 91 91 Rank	Estimation Error Est Err 1,380,201 1.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 88 88 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 254 254 Rank	Estimation Error Est Err 244 244 Rank	Estimation Error Est Err 135 135 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 141 141 Rank	Estimation Error Est Err 5 5 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,708,959 2.7M Rank		Estimation Error Est Err 262,630 263K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,708,962 2.7M Rank	Estimation Error Est Err 20 20 Rank	Estimation Error Est Err 2,708,974 2.7M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 2730 4 INNER JOIN HASH ON id27 = movie_id20 4718 5 │└INNER JOIN HASH ON id = company_type_id 1 1 │ │└TABLE SCAN company_type WHERE kind = production companies 18870 100 │ INNER JOIN HASH ON movie_id20 = 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 1368263 100 │ TABLE SCAN movie_companies WHERE NOT (note23 LIKE '%(as Metro-Goldwyn-Mayer Pictures)%') 1402423 2 TABLE SCAN title WHERE production_year >= 2001
	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_1923.movie_id,PROJECTION_1923.id) = tuple(PROJECTION_1914.movie_id,PROJECTION_1914.movie_id) - 10 │└PROJECT movie_id AS movie_id_right - 10 │ PROJECT movie_id - 10 │ INNER JOIN HASH ON PROJECTION_1920.info_type_id = PROJECTION_1917.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 - 85570 PROJECT movie_id AS movie_id_left, id, note, title, production_year - 85570 PROJECT note, movie_id, title, production_year, id - 85570 INNER JOIN HASH ON PROJECTION_1929.movie_id = PROJECTION_1926.id - 1381453 │└PROJECT id, title, production_year - 1381453 │ PROJECT id, production_year, title - 1381453 │ TABLE SCAN title WHERE production_year > 2000 - 140904 PROJECT movie_id, note - 140904 PROJECT note, movie_id - 140904 INNER JOIN HASH ON PROJECTION_1935.id = PROJECTION_1932.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 4 PROJECT note, title, production_year 261 4 INNER JOIN HASH ON company_type_id = id 1 1 │└FILTER id <= 2 1 1 │ TABLE SCAN company_type WHERE kind = 'production companies' 1044 4 INNER JOIN HASH ON movie_id = movie_id 4972 6 │└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 357561 │ FILTER id BETWEEN 2 AND 2525745 505662 357561 │ TABLE SCAN title WHERE production_year > 2000 521825 73168 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) 260913 10 DISTRIBUTE GATHER 260913 10 AGGREGATE MIN(note), MIN(title), MIN(production_year) 260913 4 INNER JOIN HASH ON movie_id = id AND movie_id = id 260913 5 │└DISTRIBUTE HASH ON movie_id, movie_id 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) 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)... 343129 1381453 DISTRIBUTE HASH ON id, id 343129 1381453 FILTER production_year > 2000 2528312 2528312 TABLE SCAN title WHERE (production_year > 2000) AND CASE MOD(HASH_REPARTITION(id,id),10) WHEN 1 THEN ((((id >= 1834714) AND (id <= 2322570)) AND ((id >= 1834714) AND (id <= 2322570))) AND struct(id,id) IN( < expr > , < expr > , < expr > , < expr > )) WHEN 7 THEN ((((id >= 2101545) AND (id <= 2101545)) AND ((id >= 2101545) AND (id <= 2101545))) AND struct(id,id) IN( < expr > )) ELSE FALSE END
	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 4 INNER JOIN HASH ON mi_idx.movie_id = mc.movie_id 401000 6 │└DISTRIBUTE GATHER 290000 6 │ INNER JOIN HASH ON mi_idx.movie_id = t.id 290000 10 │ │└DISTRIBUTE GATHER 401000 10 │ │ INNER JOIN HASH ON mi_idx.info_type_id = it.id 401000 1 │ │ │└DISTRIBUTE GATHER 2610000 1 │ │ │ TABLE SCAN info_type WHERE it.info = 'bottom 10 rank' 4 10 │ │ TABLE SCAN movie_info_idx 290000 1381453 │ DISTRIBUTE HASH ON t.id 113 1381453 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2000L) 276000 74275 INNER JOIN HASH ON ct.id = mc.company_type_id 276000 1 │└DISTRIBUTE GATHER 2530000 1 │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 1380000 74275 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(partialagg1024) AS Expr1010, MIN(partialagg1025) AS Expr1011, MIN(partialagg1026) AS Expr1012 5 1 AGGREGATE MIN(note as note) AS partialagg1024, MIN(title as title) AS partialagg1025, MIN(production_year as production_year) AS partialagg1026 160701 4 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 80350 4 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 160701 4 INNER JOIN HASH ON mc.movie_id = t.id 74991 6 │└INNER JOIN HASH ON t.id = mi_idx.movie_id 138004 10 │ │└TABLE SEEK movie_info_idx AS mi_idx WHERE BLOOM(info_type_id as info_type_id) 13738 6 │ TABLE SCAN title AS t WHERE (production_year as production_year > 2000) AND (BLOOM(id as id)) 132347 4 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 - 4 INNER JOIN HASH ON movie_id = id_15 335742 1381453 │└DISTRIBUTE HASH ON id_15 335742 1381453 │ PROJECT id AS id_15, title, production_year 335742 1381453 │ FILTER production_year > 2000 335742 1381453 │ 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) 1816 1 INNER JOIN HASH ON company_type_id = id 1 1 │└TABLE SCAN company_type AS ct WHERE ct.kind = 'production companies' 21792 78 INNER JOIN LOOP ON movie_id = id 8307 6 │└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 > 2000 18 78 TABLE SEEK movie_companies AS mc WHERE mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'