Planner — IMDB — JOB-15D

SELECT MIN(aka_t.title) AS aka_title,
       MIN(t.title) AS internet_movie_title
FROM job.aka_title AS aka_t,
     job.company_name AS cn,
     job.company_type AS ct,
     job.info_type AS it1,
     job.keyword AS k,
     job.movie_companies AS mc,
     job.movie_info AS mi,
     job.movie_keyword AS mk,
     job.title AS t
WHERE cn.country_code = '[us]'
  AND it1.info = 'release dates'
  AND mi.note LIKE '%internet%'
  AND t.production_year > 1990
  AND t.id = aka_t.movie_id
  AND t.id = mi.movie_id
  AND t.id = mk.movie_id
  AND t.id = mc.movie_id
  AND mk.movie_id = mi.movie_id
  AND mk.movie_id = mc.movie_id
  AND mk.movie_id = aka_t.movie_id
  AND mi.movie_id = mc.movie_id
  AND mi.movie_id = aka_t.movie_id
  AND mc.movie_id = aka_t.movie_id
  AND k.id = mk.keyword_id
  AND it1.id = mi.info_type_id
  AND cn.id = mc.company_id
  AND ct.id = mc.company_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,698,094 4.7M Rank		Estimation Error Est Err 4,724,879 4.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 136,661 137K Rank	Estimation Error Est Err 11,347 11K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,388,455 9.4M Rank		Estimation Error Est Err 9,390,580 9.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 27,748 28K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,460,890 9.5M Rank		Estimation Error Est Err 13,566,006 14M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,022,661 5M Rank	Estimation Error Est Err 11,348 11K Rank	Estimation Error Est Err 2,350,858 2.4M Rank
Estimation Error Est Err 13,604,673 14M Rank		Estimation Error Est Err 11,602,102 12M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,865,725 3.9M Rank	Estimation Error Est Err 11,357 11K Rank	Estimation Error Est Err 3,841,601 3.8M Rank
Estimation Error Est Err 4,808,959 4.8M Rank		Estimation Error Est Err 5,010,573 5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,178,173 3.2M Rank	Estimation Error Est Err 11,347 11K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 126,791 127K Rank	Estimation Error Est Err 126,787 127K Rank	Estimation Error Est Err 136,677 137K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 136,661 137K Rank	Estimation Error Est Err 11,347 11K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 121,432 121K Rank	Estimation Error Est Err 121,319 121K Rank	Estimation Error Est Err 131,419 131K Rank	Estimation Error Est Err 6,986 7K Rank	Estimation Error Est Err 121,319 121K Rank	Estimation Error Est Err 40,428 40K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,503,780 6.5M Rank		Estimation Error Est Err 38,881 39K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,505,162 6.5M Rank	Estimation Error Est Err 11,363 11K Rank	Estimation Error Est Err 6,503,232 6.5M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 5865 11347 INNER JOIN HASH ON movie_id35 = movie_id65 2158 25564 │└INNER JOIN HASH ON id58 = keyword_id 2250 25564 │ │└INNER JOIN HASH ON movie_id53 = movie_id35 116 9857 │ │ │└INNER JOIN HASH ON id = company_type_id 4 4 │ │ │ │└TABLE SCAN company_type 23 9857 │ │ │ INNER JOIN HASH ON id42 = company_id 62 14377 │ │ │ │└INNER JOIN HASH ON id19 = movie_id35 72 10662 │ │ │ │ │└INNER JOIN HASH ON id19 = movie_id 89 10679 │ │ │ │ │ │└INNER JOIN HASH ON id6 = info_type_id 1 1 │ │ │ │ │ │ │└TABLE SCAN info_type WHERE info = release dates 10042 10679 │ │ │ │ │ │ TABLE SCAN movie_info WHERE note LIKE '%internet%' 1757966 10564 │ │ │ │ │ TABLE SCAN title WHERE production_year >= 1991 2076277 14112 │ │ │ │ TABLE SCAN movie_companies 90648 4423 │ │ │ TABLE SCAN company_name WHERE country_code = us 4523930 4523930 │ │ TABLE SCAN movie_keyword 134170 134170 │ TABLE SCAN keyword 361379 211 TABLE SCAN aka_title
	Estimate Actual Operator - 1 PROJECT a1 AS aka_title, a2 AS internet_movie_title - 1 AGGREGATE MIN(title_right) AS a1, MIN(title_left) AS a2 - 0 PROJECT title, title - 0 PROJECT title, title - 0 INNER JOIN HASH ON PROJECTION_1020.id = PROJECTION_975.keyword_id - 0 │└PROJECT keyword_id, title, title - 0 │ PROJECT title, keyword_id, title - 0 │ INNER JOIN HASH ON tuple(PROJECTION_1017.movie_id,PROJECTION_1017.movie_id,PROJECTION_1017.movie_id,PROJECTION_1017.movie_id) = tuple(PROJECTION_978.movie_id,PROJECTION_978.movie_id,PROJECTION_978.movie_id,PROJECTION_978.id) - 0 │ │└PROJECT movie_id_right, movie_id_right_2, movie_id AS movie_id_right_3, id, title, title - 0 │ │ PROJECT title, movie_id, movie_id, movie_id, title, id - 0 │ │ INNER JOIN HASH ON tuple(PROJECTION_1014.id,PROJECTION_1014.id,PROJECTION_1014.id) = tuple(PROJECTION_981.movie_id,PROJECTION_981.movie_id,PROJECTION_981.movie_id) - 0 │ │ │└PROJECT movie_id, movie_id, movie_id, title AS title_right - 0 │ │ │ PROJECT title, movie_id, movie_id, movie_id - 0 │ │ │ INNER JOIN HASH ON PROJECTION_1011.id = PROJECTION_984.company_id - 2130 │ │ │ │└PROJECT company_id, title, movie_id, movie_id, movie_id - 2130 │ │ │ │ PROJECT title, movie_id, movie_id, company_id, movie_id - 2130 │ │ │ │ INNER JOIN HASH ON PROJECTION_1008.id = PROJECTION_987.company_type_id - 2130 │ │ │ │ │└PROJECT company_type_id, title, movie_id, movie_id, company_id, movie_id - 2130 │ │ │ │ │ PROJECT title, movie_id, movie_id, company_id, company_type_id, movie_id - 2130 │ │ │ │ │ INNER JOIN HASH ON tuple(PROJECTION_1005.movie_id,PROJECTION_1005.movie_id) = tuple(PROJECTION_990.movie_id,PROJECTION_990.movie_id) - 591 │ │ │ │ │ │└PROJECT movie_id_right, movie_id AS movie_id_right_2, title - 591 │ │ │ │ │ │ PROJECT title, movie_id, movie_id - 591 │ │ │ │ │ │ INNER JOIN HASH ON PROJECTION_1002.movie_id = PROJECTION_993.movie_id - 10679 │ │ │ │ │ │ │└PROJECT movie_id AS movie_id_right - 10679 │ │ │ │ │ │ │ PROJECT movie_id - 10679 │ │ │ │ │ │ │ INNER JOIN HASH ON PROJECTION_999.info_type_id = PROJECTION_996.id - 1 │ │ │ │ │ │ │ │└PROJECT id - 1 │ │ │ │ │ │ │ │ PROJECT id - 1 │ │ │ │ │ │ │ │ TABLE SCAN info_type WHERE info = 'release dates' - 10810 │ │ │ │ │ │ │ PROJECT info_type_id, movie_id - 10810 │ │ │ │ │ │ │ PROJECT movie_id, info_type_id - 10810 │ │ │ │ │ │ │ TABLE SCAN movie_info WHERE note LIKE '%internet%' - 361379 │ │ │ │ │ │ PROJECT movie_id AS movie_id_left, title - 361379 │ │ │ │ │ │ PROJECT title, movie_id - 361379 │ │ │ │ │ │ TABLE SCAN aka_title - 2609129 │ │ │ │ │ PROJECT movie_id AS movie_id_left, company_id, company_type_id - 2609129 │ │ │ │ │ PROJECT movie_id, company_id, company_type_id - 2609129 │ │ │ │ │ TABLE SCAN movie_companies - 4 │ │ │ │ PROJECT id - 4 │ │ │ │ PROJECT id - 4 │ │ │ │ TABLE SCAN company_type - 0 │ │ │ PROJECT id - 0 │ │ │ PROJECT id - 0 │ │ │ TABLE SCAN company_name WHERE country_code = 'us' - 1749032 │ │ PROJECT id, title AS title_left - 1749032 │ │ PROJECT id, title - 1749032 │ │ TABLE SCAN title WHERE production_year > 1990 - 4523930 │ PROJECT movie_id AS movie_id_left, keyword_id - 4523930 │ PROJECT movie_id, keyword_id - 4523930 │ TABLE SCAN movie_keyword - 134170 PROJECT id - 134170 PROJECT id - 134170 TABLE SCAN keyword
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 21 11347 PROJECT title, title 21 11347 INNER JOIN HASH ON id = keyword_id 20 11347 │└INNER JOIN HASH ON movie_id = movie_id 11 875 │ │└INNER JOIN HASH ON company_type_id = id 4 2 │ │ │└TABLE SCAN company_type WHERE id <= 2 11 875 │ │ INNER JOIN HASH ON info_type_id = id 2 1 │ │ │└FILTER id <= 110 2 1 │ │ │ TABLE SCAN info_type WHERE info = 'release dates' 645 875 │ │ INNER JOIN HASH ON movie_id = movie_id 540 218489 │ │ │└INNER JOIN HASH ON id = movie_id 2655 627946 │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 18253 1153647 │ │ │ │ │└INNER JOIN HASH ON company_id = id 1644 84843 │ │ │ │ │ │└TABLE SCAN company_name WHERE country_code = 'us' 2609129 2608947 │ │ │ │ │ TABLE SCAN movie_companies WHERE movie_id >= 50 AND movie_id <= 2525672 361379 361379 │ │ │ │ TABLE SCAN aka_title 505662 1748361 │ │ │ FILTER id BETWEEN 50 AND 2525672 505662 1748997 │ │ │ TABLE SCAN title WHERE production_year > 1990 2967144 251 │ │ FILTER movie_id BETWEEN 50 AND 2525672 2967144 251 │ │ TABLE SCAN movie_info WHERE contains(note,'internet') 4523930 2812 │ TABLE SCAN movie_keyword WHERE movie_id >= 50 AND movie_id <= 2525672 134170 1727 TABLE SCAN keyword
	Estimate Actual Operator 1 1 PROJECT aka_title, internet_movie_title 1 1 AGGREGATE MIN(title), MIN(title) 15112 10 DISTRIBUTE GATHER 15112 10 AGGREGATE MIN(title), MIN(title) 15112 11347 INNER JOIN HASH ON movie_id = CAST(t.id AS Int64) AND movie_id = id AND movie_id = id AND movie_id = id 15112 11557 │└DISTRIBUTE GATHER 15112 11557 │ INNER JOIN HASH ON keyword_id = id 15112 11557 │ │└DISTRIBUTE GATHER 15112 11557 │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = CAST(mk.movie_id AS Int64) 15112 896 │ │ │└DISTRIBUTE GATHER 15112 896 │ │ │ INNER JOIN HASH ON id = info_type_id 23 1 │ │ │ │└DISTRIBUTE GATHER 23 1 │ │ │ │ FILTER info = 'release dates' 113 113 │ │ │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ │ │ TABLE SCAN info_type WHERE info = 'release dates' 72276 896 │ │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = CAST(mi.movie_id AS Int64) 72276 627946 │ │ │ │└DISTRIBUTE GATHER 72276 627946 │ │ │ │ INNER JOIN HASH ON id = company_type_id 4 4 │ │ │ │ │└TABLE SCAN company_type 72276 627946 │ │ │ │ INNER JOIN HASH ON id = company_id 47000 84843 │ │ │ │ │└DISTRIBUTE GATHER 47000 84843 │ │ │ │ │ FILTER country_code = 'us' 234997 234997 │ │ │ │ │ TABLE SCAN company_name WHERE country_code = 'us' 361379 1946320 │ │ │ │ INNER JOIN HASH ON movie_id = CAST(mc.movie_id AS Int64) 361379 361379 │ │ │ │ │└DISTRIBUTE HASH ON movie_id 361379 361379 │ │ │ │ │ TABLE SCAN aka_title 2609129 2609129 │ │ │ │ DISTRIBUTE HASH ON CAST(mc.movie_id AS Int64) 2609129 2609129 │ │ │ │ TABLE SCAN movie_companies WHERE (CASE MOD(HASH_REPARTITION(CAST(movie_id AS INT64)),10) WHEN 0 THEN (((CAST(movie_id AS INT64) >= 826) AND (CAST(movie_id AS INT64) <= 2525588)) AND TRUE) WHEN 1 THEN (((CAST(movie_id AS INT64) >= 596) AND (CAST(movie_id AS INT64) <= 2525544)) AND TRUE) WHEN 2 THEN (((CAST(movie_id AS INT64) >= 902) AND (CAST(movie_id AS INT64) <= 2525574)) AND TRUE) WHEN 3 THEN (((CAST(movie_id AS INT64) >= 2255) AND (CAST(movie_id AS INT64... 2967144 10679 │ │ │ PROJECT movie_id, info_type_id, CAST(movie_id AS INT64) 2967144 10679 │ │ │ FILTER note LIKE '%internet%' 14835720 3212639 │ │ │ TABLE SCAN movie_info WHERE (note LIKE '%internet%' AND ((((movie_id >= 50) AND (movie_id <= 2525593)) AND ((CAST(movie_id AS INT64) >= 50) AND (CAST(movie_id AS INT64) <= 2525593))) AND TRUE)) AND (((info_type_id >= 16) AND (info_type_id <= 16)) AND info_type_id IN 16) 4523930 4523930 │ │ TABLE SCAN movie_keyword WHERE ((((movie_id >= 9133) AND (movie_id <= 2523270)) AND ((movie_id >= 9133) AND (movie_id <= 2523270))) AND ((CAST(movie_id AS INT64) >= 9133) AND (CAST(movie_id AS INT64) <= 2523270))) AND TRUE 134170 134170 │ DISTRIBUTE ROUND ROBIN 134170 134170 │ TABLE SCAN keyword WHERE ((id >= 1) AND (id <= 133880)) AND TRUE 523722 1749032 PROJECT id, title, CAST(id AS INT64) 523722 1749032 FILTER production_year > 1990 2528312 2528312 TABLE SCAN title WHERE (production_year > 1990) AND ((((((CAST(id AS INT64) >= 9133) AND (CAST(id AS INT64) <= 2523270)) AND ((id >= 9133) AND (id <= 2523270))) AND ((id >= 9133) AND (id <= 2523270))) AND ((id >= 9133) AND (id <= 2523270))) AND TRUE)
	Estimate Actual Operator 1 1 AGGREGATE MIN(aka_t.title), MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(aka_t.title), MIN(t.title) 249000 11347 INNER JOIN HASH ON aka_t.movie_id = mk.movie_id 249000 875 │└DISTRIBUTE GATHER 119000 875 │ INNER JOIN HASH ON mc.movie_id = aka_t.movie_id 119000 9874 │ │└DISTRIBUTE GATHER 12100 9874 │ │ INNER JOIN HASH ON mc.company_id = cn.id 12100 84843 │ │ │└DISTRIBUTE GATHER 235000 84843 │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 514000 14400 │ │ INNER JOIN HASH ON mc.company_type_id = ct.id 514000 4 │ │ │└DISTRIBUTE GATHER 2530000 4 │ │ │ TABLE SCAN company_type 514000 14400 │ │ INNER JOIN HASH ON mi.movie_id = mc.movie_id 514000 10679 │ │ │└DISTRIBUTE GATHER 201000 10679 │ │ │ INNER JOIN HASH ON mi.info_type_id = it1.id 201000 1 │ │ │ │└DISTRIBUTE GATHER 361000 1 │ │ │ │ TABLE SCAN info_type WHERE it1.info = 'release dates' 134000 10679 │ │ │ TABLE SCAN movie_info WHERE (mi.note IS NOT NULL) AND contains(mi.note,'internet') 4520000 2605043 │ │ TABLE SCAN movie_companies 12700 140974 │ INNER JOIN HASH ON aka_t.movie_id = t.id 12700 361379 │ │└DISTRIBUTE HASH ON aka_t.movie_id 2610000 361379 │ │ TABLE SCAN aka_title WHERE aka_t.movie_id IS NOT NULL 12700 1749032 │ DISTRIBUTE HASH ON t.id 4 1749032 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 1990L) 114000 4515739 INNER JOIN HASH ON mk.keyword_id = k.id 114000 134170 │└DISTRIBUTE GATHER 113 134170 │ TABLE SCAN keyword 14800000 4515739 TABLE SCAN movie_keyword
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1038) AS Expr1018, MIN(partialagg1039) AS Expr1019 1 261 FILTER country_code as country_code = 'us' 1 682 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 682 │└TABLE SEEK company_name AS cn 1 682 INNER JOIN LOOP ON mc.company_id = cn.id 1 682 │└TABLE SEEK company_name AS cn 1 682 AGGREGATE MIN(title as title) AS partialagg1038, MIN(title as title) AS partialagg1039 GROUP BY HASH company_id 20359 40167 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 40167 │└TABLE SEEK movie_companies AS mc 20359 40167 PROJECT BmkToPage Bmk1010 AS Expr1053 20359 40167 INNER JOIN LOOP ON t.id = mc.movie_id 9 40167 │└TABLE SEEK movie_companies AS mc 2054 6398 SORT movie_id 2054 6398 INNER JOIN LOOP ON t.id = mk.movie_id 18 6398 │└TABLE SEEK movie_keyword AS mk 113 588 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 588 │└TABLE SEEK aka_title AS aka_t 113 588 SORT Expr1043 113 588 PROJECT BmkToPage Bmk1000 AS Expr1043 113 588 INNER JOIN LOOP ON t.id = aka_t.movie_id 1 588 │└TABLE SEEK aka_title AS aka_t 101 10671 INNER JOIN LOOP ON Bmk1016 = Bmk1016 0 10671 │└TABLE SEEK title AS t WHERE production_year as production_year > 1990 147 10688 PROJECT BmkToPage Bmk1016 AS Expr1047 147 10688 INNER JOIN LOOP ON mi.movie_id = t.id 1 10688 │└TABLE SEEK title AS t 147 10688 INNER JOIN HASH ON mi.info_type_id = it1.id 1 1 │└FILTER info as info = 'release dates' 113 113 │ TABLE SCAN info_type AS it1 10327 10688 TABLE SEEK movie_info AS mi WHERE (PROBE(Bitmap1045,info_type_id as info_type_id,N'IN ROW')) AND (note as note LIKE '%internet%')
	Estimate Actual Operator 1 ∞ PROJECT min AS aka_title, min_50 AS internet_movie_title 1 1 AGGREGATE MIN(min_51) AS min, MIN(min_52) AS min_50 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(title) AS min_51, MIN(title_37) AS min_52 - 11347 INNER JOIN HASH ON movie_id_19 = id_36 512448 1749032 │└DISTRIBUTE HASH ON id_36 512448 1749032 │ PROJECT id AS id_36, title AS title_37 512448 1749032 │ FILTER production_year > 1990 512448 1749032 │ TABLE SCAN title - 11557 INNER JOIN HASH ON keyword_id = id_13 134170 134170 │└DISTRIBUTE GATHER 134170 134170 │ PROJECT id AS id_13 134170 134170 │ TABLE SCAN keyword - 11557 INNER JOIN HASH ON movie_id_19 = movie_id_32 4523930 4523930 │└DISTRIBUTE HASH ON movie_id_32 4523930 4523930 │ PROJECT movie_id AS movie_id_32, keyword_id 4523930 4523930 │ TABLE SCAN movie_keyword - 896 INNER JOIN HASH ON info_type_id = id_9 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_9 113 1 │ FILTER info = 'release dates' 113 1 │ TABLE SCAN info_type - 896 INNER JOIN HASH ON movie_id_19 = movie_id_25 13352148 10679 │└DISTRIBUTE HASH ON movie_id_25 13352148 10679 │ PROJECT movie_id AS movie_id_25, info_type_id 13352148 10679 │ FILTER note LIKE '%internet%' 13352148 10679 │ TABLE SCAN movie_info - 806 INNER JOIN HASH ON company_type_id = id_5 4 4 │└DISTRIBUTE GATHER 4 4 │ PROJECT id AS id_5 4 4 │ TABLE SCAN company_type - 806 INNER JOIN HASH ON company_id = id_0 234997 84843 │└DISTRIBUTE GATHER 234997 84843 │ PROJECT id AS id_0 234997 84843 │ FILTER country_code = 'us' 234997 84843 │ TABLE SCAN company_name - 806 INNER JOIN HASH ON movie_id_19 = movie_id 361379 557 │└DISTRIBUTE GATHER 361379 557 │ TABLE SCAN aka_title 2609129 564 PROJECT movie_id AS movie_id_19, company_id, company_type_id 2609129 564 TABLE SCAN movie_companies
	Estimate Actual Operator 1 1 AGGREGATE MIN(title), MIN(title) 3232 11347 INNER JOIN LOOP ON movie_id = id 1229 25564 │└INNER JOIN LOOP ON id = keyword_id 1229 25564 │ │└INNER JOIN LOOP ON movie_id = id 102 9857 │ │ │└INNER JOIN HASH ON company_type_id = id 4 4 │ │ │ │└TABLE SCAN company_type AS ct 102 9857 │ │ │ INNER JOIN LOOP ON id = company_id 280 14377 │ │ │ │└INNER JOIN LOOP ON movie_id = id 55 10662 │ │ │ │ │└INNER JOIN LOOP ON id = movie_id 80 10679 │ │ │ │ │ │└INNER JOIN LOOP ON info_type_id = id 1 1 │ │ │ │ │ │ │└TABLE SEEK info_type AS it1 129 10679 │ │ │ │ │ │ TABLE SEEK movie_info AS mi WHERE mi.note LIKE '%internet%' 10679 10679 │ │ │ │ │ TABLE SEEK title AS t WHERE t.production_year > 1990 53310 14393 │ │ │ │ TABLE SEEK movie_companies AS mc 14377 14377 │ │ │ TABLE SEEK company_name AS cn WHERE cn.country_code = 'us' 443565 25529 │ │ TABLE SEEK movie_keyword AS mk 25564 25564 │ TABLE SEEK keyword AS k 76692 25564 TABLE SEEK aka_title AS aka_t