Planner — IMDB — JOB-1A

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 = 'top 250 rank'
  AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
  AND (mc.note LIKE '%(co-production)%'
       OR mc.note LIKE '%(presents)%')
  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 3,919,478 3.9M Rank		Estimation Error Est Err 3,919,623 3.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 784 784 Rank	Estimation Error Est Err 142 142 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,937,238 3.9M Rank		Estimation Error Est Err 3,937,254 3.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 58,046 58K Rank	Estimation Error Est Err 142 142 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,549,120 2.5M Rank		Estimation Error Est Err 2,577,775 2.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 57,814 58K Rank	Estimation Error Est Err 144 144 Rank	Estimation Error Est Err 28,911 29K Rank
Estimation Error Est Err 3,931,315 3.9M Rank		Estimation Error Est Err 2,585,199 2.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 57,598 58K Rank	Estimation Error Est Err 152 152 Rank	Estimation Error Est Err 2,583,865 2.6M Rank
Estimation Error Est Err 26,853 27K Rank		Estimation Error Est Err 26,993 27K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 784 784 Rank	Estimation Error Est Err 142 142 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,380,431 1.4M Rank	Estimation Error Est Err 395 395 Rank	Estimation Error Est Err 1,380,574 1.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 419 419 Rank	Estimation Error Est Err 144 144 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 29,386 29K Rank	Estimation Error Est Err 702 702 Rank	Estimation Error Est Err 29,456 29K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 58,094 58K Rank	Estimation Error Est Err 152 152 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,556,775 2.6M Rank		Estimation Error Est Err 56,242 56K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,556,915 2.6M Rank	Estimation Error Est Err 158 158 Rank	Estimation Error Est Err 2,556,790 2.6M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 330 142 INNER JOIN HASH ON id27 = movie_id20 316 142 │└INNER JOIN HASH ON id = company_type_id 1 1 │ │└TABLE SCAN company_type WHERE kind = production companies 316 147 │ INNER JOIN HASH ON movie_id20 = movie_id 12213 250 │ │└INNER JOIN HASH ON id6 = info_type_id 1 1 │ │ │└TABLE SCAN info_type WHERE info = top 250 rank 1380035 1380035 │ │ TABLE SCAN movie_info_idx 22931 11129 │ TABLE SCAN movie_companies WHERE note23 LIKE '%(co-production)%' OR note23 LIKE '%(presents)%' AND NOT (note23 LIKE '%(as Metro-Goldwyn-Mayer Pictures)%') 2528312 2528312 TABLE SCAN title
	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 - 142 PROJECT note, title, production_year - 142 PROJECT note, title, production_year - 142 INNER JOIN HASH ON tuple(PROJECTION_1836.movie_id,PROJECTION_1836.id) = tuple(PROJECTION_1827.movie_id,PROJECTION_1827.movie_id) - 28657 │└PROJECT movie_id AS movie_id_right, note - 28657 │ PROJECT note, movie_id - 28657 │ INNER JOIN HASH ON PROJECTION_1833.id = PROJECTION_1830.company_type_id - 28889 │ │└PROJECT company_type_id, note, movie_id - 28889 │ │ PROJECT company_type_id, note, movie_id - 28889 │ │ TABLE SCAN movie_companies WHERE notLike(note,'%(as Metro-Goldwyn-Mayer Pictures)%') AND (note LIKE '%(co-production)%' OR note LIKE '%(presents)%') - 1 │ PROJECT id - 1 │ PROJECT id - 1 │ TABLE SCAN company_type WHERE kind = 'production companies' - 250 PROJECT movie_id AS movie_id_left, id, title, production_year - 250 PROJECT movie_id, title, production_year, id - 250 INNER JOIN HASH ON PROJECTION_1848.id = PROJECTION_1839.movie_id - 250 │└PROJECT movie_id - 250 │ PROJECT movie_id - 250 │ INNER JOIN HASH ON PROJECTION_1845.info_type_id = PROJECTION_1842.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'top 250 rank' - 1380035 │ PROJECT info_type_id, movie_id - 1380035 │ PROJECT movie_id, info_type_id - 1380035 │ TABLE SCAN movie_info_idx - 2528312 PROJECT id, title, production_year - 2528312 PROJECT title, production_year, id - 2528312 TABLE SCAN title
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 1305 142 PROJECT note, title, production_year 1305 142 INNER JOIN HASH ON id = movie_id 1282 142 │└INNER JOIN HASH ON company_type_id = id 1 1 │ │└FILTER id <= 2 1 1 │ │ TABLE SCAN company_type WHERE kind = 'production companies' 5131 142 │ INNER JOIN HASH ON movie_id = movie_id 24425 250 │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │└FILTER id >= 99 2 1 │ │ │ TABLE SCAN info_type WHERE info = 'top 250 rank' 1380035 250 │ │ TABLE SCAN movie_info_idx WHERE movie_id <= 2525745 521825 24653 │ TABLE SCAN movie_companies WHERE ( NOT contains(note,'(as Metro-Goldwyn-Mayer Pictures)')) AND (contains(note,'(co-production)') OR contains(note,'(presents)')) 2528312 1948 TABLE SCAN title WHERE id >= 2 AND id <= 2525745
	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 142 INNER JOIN HASH ON movie_id = id AND movie_id = id 260913 142 │└DISTRIBUTE HASH ON movie_id, movie_id 260913 142 │ INNER JOIN HASH ON id = info_type_id 23 1 │ │└DISTRIBUTE GATHER 23 1 │ │ FILTER info = 'top 250 rank' 113 113 │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ TABLE SCAN info_type WHERE info = 'top 250 rank' 260913 1597 │ INNER JOIN HASH ON movie_id = movie_id 260913 28657 │ │└DISTRIBUTE HASH ON movie_id 260913 28657 │ │ 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 28665 │ │ FILTER note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND (note LIKE '%(co-production)%' OR note LIKE '%(presents)%') 2609129 1376261 │ │ TABLE SCAN movie_companies WHERE (note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND (note LIKE '%(co-production)%' OR note LIKE '%(presents)%')) 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 >= 62498) AND (movie_id <= 2524578)) AND TRUE) WHEN 1 THEN (((movie_id >= 1186) AND (movie_id <= 2525625)) AND TRUE) WHEN 2 THEN (((movie_id >= 9011) AND (movie_id <= 2525455)) AND TRUE) WHEN 3 THEN (((movie_id >= 8335) AND (movie_id <= 2524668)) AND TRUE) WHEN 4 THEN (((movie_id >= 34954) AND (movie_id <= 2525626)) AND TRUE) WHEN 5 THEN (((movie_id >= 11422) AND (movie_id <=... 2528312 2528312 DISTRIBUTE HASH ON id, id 2528312 2528312 TABLE SCAN title WHERE CASE MOD(HASH_REPARTITION(id,id),10) WHEN 1 THEN ((((id >= 1717896) AND (id <= 2471561)) AND ((id >= 1717896) AND (id <= 2471561))) 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 > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < e...
	Estimate Actual Operator 1 1 AGGREGATE MIN(mc.note), MIN(t.title), MIN(t.production_year) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE MIN(mc.note), MIN(t.title), MIN(t.production_year) 365000 142 INNER JOIN HASH ON mc.movie_id = mi_idx.movie_id 365000 250 │└DISTRIBUTE GATHER 276000 250 │ INNER JOIN HASH ON mi_idx.info_type_id = it.id 276000 1 │ │└DISTRIBUTE GATHER 4 1 │ │ TABLE SCAN info_type WHERE it.info = 'top 250 rank' 113 250 │ TABLE SCAN movie_info_idx 290000 28657 INNER JOIN HASH ON mc.movie_id = t.id 290000 28657 │└DISTRIBUTE GATHER 365000 28657 │ INNER JOIN HASH ON ct.id = mc.company_type_id 365000 1 │ │└DISTRIBUTE GATHER 2610000 1 │ │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 2530000 28657 │ TABLE SCAN movie_companies WHERE (mc.note IS NOT NULL) AND ( NOT contains(mc.note,'(as Metro-Goldwyn-Mayer Pictures)')) AND (contains(mc.note,'(co-production)') OR contains(mc.note,'(presents)')) 1380000 2520211 TABLE SCAN title
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1027) AS Expr1010, MIN(partialagg1028) AS Expr1011, MIN(partialagg1029) AS Expr1012 5 10 AGGREGATE MIN(note as note) AS partialagg1027, MIN(title as title) AS partialagg1028, MIN(production_year as production_year) AS partialagg1029 6125 142 INNER JOIN HASH ON t.id = mi_idx.movie_id 276007 250 │└INNER JOIN LOOP ON it.id = mi_idx.info_type_id 276007 250 │ │└TABLE SEEK movie_info_idx AS mi_idx 1 1 │ FILTER info as info = 'top 250 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 13707 148 INNER JOIN HASH ON t.id = mc.movie_id 13707 28684 │└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 27414 28684 │ TABLE SCAN movie_companies AS mc WHERE NOT note as note LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND (note as note LIKE '%(co-production)%' OR note as note LIKE '%(presents)%') AND BLOOM(company_type_id as company_type_id) 1496 109 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 109 │└TABLE SEEK title AS t 1496 109 PROJECT BmkToPage Bmk1008 AS Expr1162 2528310 109 TABLE SEEK title AS t WHERE BLOOM(id as id,N'IN ROW')
	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 - 142 INNER JOIN HASH ON movie_id = id_15 2528312 2528312 │└DISTRIBUTE HASH ON id_15 2528312 2528312 │ PROJECT id AS id_15, title, production_year 2528312 2528312 │ TABLE SCAN title - 142 INNER JOIN HASH ON info_type_id = id_0 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_0 113 1 │ FILTER info = 'top 250 rank' 113 1 │ TABLE SCAN info_type - 142 INNER JOIN HASH ON movie_id = movie_id_9 1380035 250 │└DISTRIBUTE HASH ON movie_id_9 1380035 250 │ PROJECT movie_id AS movie_id_9, info_type_id 1380035 250 │ TABLE SCAN movie_info_idx - 27979 INNER JOIN HASH ON id = company_type_id 2348216 28211 │└DISTRIBUTE HASH ON company_type_id 2348216 28211 │ FILTER NOT (note LIKE '%(as Metro-Goldwyn-Mayer Pictures)%') AND ((note LIKE '%(co-production)%') OR (note LIKE '%(presents)%')) 2348216 28211 │ 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) 132 141 INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 44 47 │└INNER JOIN HASH ON company_type_id = id 1 1 │ │└TABLE SCAN company_type AS ct WHERE ct.kind = 'production companies' 528 147 │ INNER JOIN LOOP ON movie_id = movie_id 5089 83 │ │└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 250 250 │ TABLE SEEK movie_companies AS mc WHERE (mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%') AND ((mc.note LIKE '%(co-production)%') OR (mc.note LIKE '%(presents)%')) 142 142 TABLE SEEK title AS t