Planner — IMDB — JOB-1C

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)%')
  AND t.production_year >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,386,835 1.4M Rank		Estimation Error Est Err 1,386,838 1.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 526 526 Rank	Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,789,519 1.8M Rank		Estimation Error Est Err 1,789,524 1.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 36,127 36K Rank	Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 401,501 402K Rank		Estimation Error Est Err 411,082 411K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 10,095 10K Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 391,931 392K Rank
Estimation Error Est Err 3,931,315 3.9M Rank		Estimation Error Est Err 45,397 45K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 427,311 427K Rank	Estimation Error Est Err 13 13 Rank	Estimation Error Est Err 436,254 436K Rank
Estimation Error Est Err 132,715 133K Rank		Estimation Error Est Err 132,562 133K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 515 515 Rank	Estimation Error Est Err 3 3 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,380,301 1.4M Rank	Estimation Error Est Err 266 266 Rank	Estimation Error Est Err 1,380,300 1.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 181 181 Rank	Estimation Error Est Err 6 6 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 18,236 18K Rank	Estimation Error Est Err 276 276 Rank	Estimation Error Est Err 569 569 Rank	Estimation Error Est Err 46 46 Rank	Estimation Error Est Err 18,142 18K Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 409,295 409K Rank		Estimation Error Est Err 34,790 35K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 409,316 409K Rank	Estimation Error Est Err 19 19 Rank	Estimation Error Est Err 409,310 409K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 75 3 INNER JOIN HASH ON id = company_type_id 1 1 │└TABLE SCAN company_type WHERE kind = production companies 75 3 INNER JOIN HASH ON id27 = movie_id20 246 23 │└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 17835 6797 │ TABLE SCAN movie_companies WHERE note23 LIKE '%(co-production)%' AND NOT (note23 LIKE '%(as Metro-Goldwyn-Mayer Pictures)%') 365420 1 TABLE SCAN title WHERE production_year >= 2011
	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 - 3 PROJECT note, title, production_year - 3 PROJECT note, title, production_year - 3 INNER JOIN HASH ON tuple(PROJECTION_1894.movie_id,PROJECTION_1894.id) = tuple(PROJECTION_1885.movie_id,PROJECTION_1885.movie_id) - 17811 │└PROJECT movie_id AS movie_id_right, note - 17811 │ PROJECT note, movie_id - 17811 │ INNER JOIN HASH ON PROJECTION_1891.id = PROJECTION_1888.company_type_id - 17816 │ │└PROJECT company_type_id, note, movie_id - 17816 │ │ PROJECT company_type_id, note, movie_id - 17816 │ │ TABLE SCAN movie_companies WHERE notLike(note,'%(as Metro-Goldwyn-Mayer Pictures)%') AND note LIKE '%(co-production)%' - 1 │ PROJECT id - 1 │ PROJECT id - 1 │ TABLE SCAN company_type WHERE kind = 'production companies' - 12 PROJECT movie_id AS movie_id_left, id, title, production_year - 12 PROJECT movie_id, title, production_year, id - 12 INNER JOIN HASH ON PROJECTION_1906.id = PROJECTION_1897.movie_id - 250 │└PROJECT movie_id - 250 │ PROJECT movie_id - 250 │ INNER JOIN HASH ON PROJECTION_1903.info_type_id = PROJECTION_1900.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 - 391666 PROJECT id, title, production_year - 391666 PROJECT id, production_year, title - 391666 TABLE SCAN title WHERE production_year > 2010
	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 12 │└INNER JOIN HASH ON 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 505662 121004 │ FILTER id BETWEEN 2 AND 2525745 505662 121158 │ TABLE SCAN title WHERE production_year > 2010 521825 11305 TABLE SCAN movie_companies WHERE ( NOT contains(note,'(as Metro-Goldwyn-Mayer Pictures)')) AND contains(note,'(co-production)')
	Estimate Actual Operator 1 1 PROJECT production_note, movie_title, movie_year 1 1 AGGREGATE MIN(note), MIN(title), MIN(production_year) 162535 10 DISTRIBUTE GATHER 162535 10 AGGREGATE MIN(note), MIN(title), MIN(production_year) 162535 3 PROJECT note, title, production_year 162535 3 INNER JOIN HASH ON id = movie_id AND id = movie_id 162535 391666 │└DISTRIBUTE HASH ON id, id 162535 391666 │ FILTER production_year > 2010 2528312 2528312 │ TABLE SCAN title WHERE production_year > 2010 260913 23 DISTRIBUTE HASH ON movie_id, movie_id 260913 23 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 938 INNER JOIN HASH ON movie_id = movie_id 260913 17811 │└DISTRIBUTE HASH ON movie_id 260913 17811 │ 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 17811 │ FILTER note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND note LIKE '%(co-production)%' 2609129 1376261 │ TABLE SCAN movie_companies WHERE ((note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND note LIKE '%(co-production)%') AND (((company_type_id >= 2) AND (company_type_id <= 2)) AND company_type_id IN 2)) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 13) AND (movie_id <= 2528194)) AND ((movie_id >= 13) AND (movie_id <= 2528194))) AND TRUE) WHEN 3 THEN ((((movie_id >= 11) AND (movie_id <= 2528183)) AND ((movie_id >= 11) AND (movie_id... 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 <= 2525357)) AND TRUE) WHEN 2 THEN (((movie_id >= 11659) AND (movie_id <= 2524853)) AND TRUE) WHEN 3 THEN (((movie_id >= 28990) AND (movie_id <= 2524668)) AND TRUE) WHEN 4 THEN (((movie_id >= 34954) AND (movie_id <= 2525616)) AND TRUE) WHEN 5 THEN (((movie_id >= 41521) AND (movie_id <= 2...
	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) 281000 3 INNER JOIN HASH ON mi_idx.movie_id = mc.movie_id 281000 12 │└DISTRIBUTE GATHER 276000 12 │ INNER JOIN HASH ON mi_idx.movie_id = t.id 276000 250 │ │└DISTRIBUTE GATHER 281000 250 │ │ INNER JOIN HASH ON mi_idx.info_type_id = it.id 281000 1 │ │ │└DISTRIBUTE GATHER 2610000 1 │ │ │ TABLE SCAN info_type WHERE it.info = 'top 250 rank' 4 250 │ │ TABLE SCAN movie_info_idx 276000 391666 │ DISTRIBUTE HASH ON t.id 113 391666 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2010L) 276000 9583 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 9583 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)')
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1029) AS Expr1010, MIN(partialagg1030) AS Expr1011, MIN(partialagg1031) AS Expr1012 5 1 AGGREGATE MIN(note as note) AS partialagg1029, MIN(title as title) AS partialagg1030, MIN(production_year as production_year) AS partialagg1031 615 3 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 3 │└TABLE SEEK title AS t WHERE production_year as production_year > 2010 4025 23 SORT Expr1032 4025 23 PROJECT BmkToPage Bmk1008 AS Expr1032 4025 23 INNER JOIN LOOP ON mc.movie_id = t.id 1 23 │└TABLE SEEK title AS t 4025 23 SORT movie_id 4025 23 INNER JOIN HASH ON mc.company_type_id = ct.id 1 1 │└FILTER kind as kind = 'production companies' 4 4 │ TABLE SCAN company_type AS ct 8050 23 INNER JOIN HASH ON mi_idx.movie_id = mc.movie_id 17961 17843 │└TABLE SCAN movie_companies AS mc WHERE NOT note as note LIKE '%(as Metro-Goldwyn-Mayer Pictures)%' AND note as note LIKE '%(co-production)%' 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 TABLE SCAN info_type AS it
	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 159036 391666 │└DISTRIBUTE HASH ON id_15 159036 391666 │ PROJECT id AS id_15, title, production_year 159036 391666 │ FILTER production_year > 2010 159036 391666 │ TABLE SCAN title - 23 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 - 23 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 - 17372 INNER JOIN HASH ON id = company_type_id 2348216 17377 │└DISTRIBUTE HASH ON company_type_id 2348216 17377 │ FILTER NOT (note LIKE '%(as Metro-Goldwyn-Mayer Pictures)%') AND (note LIKE '%(co-production)%') 2348216 17377 │ 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) 15 3 INNER JOIN LOOP ON id = company_type_id 66 3 │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 2391 12 │ │└INNER JOIN LOOP ON 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 title AS t WHERE t.production_year > 2010 12 12 │ TABLE SEEK movie_companies AS mc WHERE (mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%') AND (mc.note LIKE '%(co-production)%') 1 1 TABLE SEEK company_type AS ct WHERE ct.kind = 'production companies'