Planner — IMDB — JOB-12B

SELECT MIN(mi.info) AS budget,
       MIN(t.title) AS unsuccsessful_movie
FROM job.company_name AS cn,
     job.company_type AS ct,
     job.info_type AS it1,
     job.info_type AS it2,
     job.movie_companies AS mc,
     job.movie_info AS mi,
     job.movie_info_idx AS mi_idx,
     job.title AS t
WHERE cn.country_code ='[us]'
  AND ct.kind IS NOT NULL
  AND (ct.kind ='production companies'
       OR ct.kind = 'distributors')
  AND it1.info ='budget'
  AND it2.info ='bottom 10 rank'
  AND t.production_year >2000
  AND (t.title LIKE 'Birdemic%'
       OR t.title LIKE '%Movie%')
  AND t.id = mi.movie_id
  AND t.id = mi_idx.movie_id
  AND mi.info_type_id = it1.id
  AND mi_idx.info_type_id = it2.id
  AND t.id = mc.movie_id
  AND ct.id = mc.company_type_id
  AND cn.id = mc.company_id
  AND mc.movie_id = mi.movie_id
  AND mc.movie_id = mi_idx.movie_id
  AND mi.movie_id = mi_idx.movie_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 18,827,430 19M Rank		Estimation Error Est Err 18,827,793 19M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,969 3K Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 18,827,424 19M Rank		Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,670,335 3.7M Rank		Estimation Error Est Err 4,817,316 4.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,539,444 3.5M Rank	Estimation Error Est Err 14 14 Rank	Estimation Error Est Err 1,238,669 1.2M Rank
Estimation Error Est Err 6,142,886 6.1M Rank		Estimation Error Est Err 9,989,410 10M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,690,243 5.7M Rank	Estimation Error Est Err 20 20 Rank	Estimation Error Est Err 1,412,005 1.4M Rank
Estimation Error Est Err 2,696,518 2.7M Rank		Estimation Error Est Err 2,613,830 2.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,311,880 2.3M Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,380,348 1.4M Rank	Estimation Error Est Err 313 313 Rank	Estimation Error Est Err 1,380,563 1.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 306 306 Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,138 4.1K Rank	Estimation Error Est Err 1,424 1.4K Rank	Estimation Error Est Err 3,946 3.9K Rank	Estimation Error Est Err 106 106 Rank	Estimation Error Est Err 1,789 1.8K Rank	Estimation Error Est Err 39 39 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,583 2.6K Rank		Estimation Error Est Err 70 70 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,619 2.6K Rank	Estimation Error Est Err 26 26 Rank	Estimation Error Est Err 2,593 2.6K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 33 10 INNER JOIN HASH ON id55 = company_id 89 33 │└INNER JOIN HASH ON id = company_type_id 2 2 │ │└TABLE SCAN company_type WHERE kind IN(production companies,distributors) 101 33 │ INNER JOIN HASH ON movie_id48 = movie_id40 9 2 │ │└INNER JOIN HASH ON info_type_id41 = id6 1 1 │ │ │└TABLE SCAN info_type WHERE info = budget 1060 221 │ │ INNER JOIN HASH ON movie_id40 = movie_id 12 2 │ │ │└INNER JOIN HASH ON info_type_id = id11 1 1 │ │ │ │└TABLE SCAN info_type WHERE info = bottom 10 rank 1406 2645 │ │ │ INNER JOIN HASH ON id16 = movie_id 2469 2536 │ │ │ │└TABLE SCAN title WHERE production_year >= 2001 AND title LIKE '%Movie%' 1380035 1380035 │ │ │ TABLE SCAN movie_info_idx 14835720 14835720 │ │ TABLE SCAN movie_info 2609129 2609129 │ TABLE SCAN movie_companies 90648 6 TABLE SCAN company_name WHERE country_code = us
	Estimate Actual Operator - ∞ PROJECT a1 AS budget, a2 AS unsuccsessful_movie - ∞ AGGREGATE MIN(info) AS a1, MIN(title) AS a2 - ∞ PROJECT info, title - ∞ PROJECT info, title - ∞ INNER JOIN HASH ON tuple(PROJECTION_384.movie_id,PROJECTION_384.movie_id,PROJECTION_384.movie_id,PROJECTION_384.movie_id) = tuple(PROJECTION_369.movie_id,PROJECTION_369.movie_id,PROJECTION_369.id,PROJECTION_369.id) - ∞ │└PROJECT movie_id AS movie_id_right, id, info, title - ∞ │ PROJECT info, movie_id, title, id - ∞ │ INNER JOIN HASH ON PROJECTION_381.id = PROJECTION_372.movie_id - 121863 │ │└PROJECT movie_id, info - 121863 │ │ PROJECT info, movie_id - 121863 │ │ INNER JOIN HASH ON PROJECTION_378.info_type_id = PROJECTION_375.id - 1 │ │ │└PROJECT id - 1 │ │ │ FILTER (1 AND info = 'budget'_String) AS a43 - 1 │ │ │ TABLE SCAN info_type WHERE info = 'budget' - 14835720 │ │ PROJECT info_type_id, info, movie_id - 14835720 │ │ PROJECT info, movie_id, info_type_id - 14835720 │ │ TABLE SCAN movie_info - ∞ │ PROJECT id, title - ∞ │ FILTER (1 AND production_year > 2000_UInt16 AND OR (startsWith(title,'Birdemic'_String), LIKE (title,'%Movie%'_String))) AS a28 - 2536 │ TABLE SCAN title WHERE (production_year > 2000) AND (startsWith(title,'Birdemic') OR title LIKE '%Movie%') - ∞ PROJECT movie_id AS movie_id_left, movie_id AS movie_id_left_2 - ∞ PROJECT movie_id, movie_id - ∞ INNER JOIN HASH ON PROJECTION_396.movie_id = PROJECTION_387.movie_id - 10 │└PROJECT movie_id AS movie_id_right - 10 │ PROJECT movie_id - 10 │ INNER JOIN HASH ON PROJECTION_393.info_type_id = PROJECTION_390.id - 1 │ │└PROJECT id - 1 │ │ FILTER (1 AND info = 'bottom 10 rank'_String) AS a23 - 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 - ∞ PROJECT movie_id AS movie_id_left - ∞ PROJECT movie_id - ∞ INNER JOIN HASH ON PROJECTION_402.company_type_id = PROJECTION_399.id - ∞ │└PROJECT id - ∞ │ FILTER (1 AND OR ((kind = 'production companies'_String),(kind = 'distributors'_String))) AS a14 - 2 │ TABLE SCAN company_type WHERE (kind = 'production companies') OR (kind = 'distributors') - 0 PROJECT company_type_id, movie_id - 0 PROJECT company_type_id, movie_id - 0 INNER JOIN HASH ON PROJECTION_408.id = PROJECTION_405.company_id - 2609129 │└PROJECT company_id, company_type_id, movie_id - 2609129 │ PROJECT company_id, company_type_id, movie_id - 2609129 │ TABLE SCAN movie_companies - 0 PROJECT id - 0 FILTER (1 AND country_code = 'us'_String) AS a10 - 0 TABLE SCAN company_name WHERE country_code = 'us'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 0 10 PROJECT info, title 0 10 INNER JOIN HASH ON info_type_id = id 2 1 │└FILTER id <= 110 2 1 │ TABLE SCAN info_type WHERE info = 'budget' 54 10 INNER JOIN HASH ON movie_id = movie_id 9 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' 516 10 │ INNER JOIN HASH ON movie_id = movie_id 929 2127 │ │└INNER JOIN HASH ON company_type_id = id 1 2 │ │ │└FILTER id <= 2 1 2 │ │ │ FILTER (kind = 'production companies') OR (kind = 'distributors') 1 4 │ │ │ TABLE SCAN company_type WHERE kind = 'production companies' OR kind = 'distributors' AND (kind IS NOT NULL) 3716 2127 │ │ INNER JOIN HASH ON id = movie_id 18253 1153798 │ │ │└INNER JOIN HASH ON company_id = id 1644 84843 │ │ │ │└TABLE SCAN company_name WHERE country_code = 'us' 2609129 2609129 │ │ │ TABLE SCAN movie_companies 505662 2534 │ │ FILTER id BETWEEN 2 AND 2525745 505662 2536 │ │ TABLE SCAN title WHERE production_year > 2000 AND (prefix(title,'Birdemic') OR contains(title,'Movie')) 1380035 2 │ TABLE SCAN movie_info_idx WHERE movie_id <= 2525745 14835720 2 TABLE SCAN movie_info WHERE movie_id >= 2 AND movie_id <= 2525745
	Estimate Actual Operator 1 1 PROJECT budget, unsuccsessful_movie 1 1 AGGREGATE MIN(info), MIN(title) 320359 10 DISTRIBUTE GATHER 320359 10 AGGREGATE MIN(info), MIN(title) 320359 10 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id 320359 42 │└DISTRIBUTE HASH ON movie_id, movie_id, movie_id 320359 42 │ 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' 320359 7864 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 320359 143917 │ │└DISTRIBUTE HASH ON movie_id, movie_id 320359 143917 │ │ INNER JOIN HASH ON id = info_type_id 23 1 │ │ │└DISTRIBUTE GATHER 23 1 │ │ │ FILTER info = 'budget' 113 113 │ │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ │ TABLE SCAN info_type WHERE info = 'budget' 1532152 3094729 │ │ INNER JOIN HASH ON movie_id = movie_id 260916 1153798 │ │ │└DISTRIBUTE GATHER 260916 1153798 │ │ │ INNER JOIN HASH ON id = company_type_id 1 2 │ │ │ │└DISTRIBUTE GATHER 1 2 │ │ │ │ FILTER kind IS NOT NULL AND ((kind = 'production companies') OR (kind = 'distributors')) 4 4 │ │ │ │ DISTRIBUTE ROUND ROBIN 4 4 │ │ │ │ TABLE SCAN company_type WHERE kind IS NOT NULL AND ((kind = 'production companies') OR (kind = 'distributors')) 521832 1153798 │ │ │ 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' 2609129 2609129 │ │ │ TABLE SCAN movie_companies WHERE (((company_id >= 1) AND (company_id <= 234997)) AND TRUE) AND (((company_type_id >= 1) AND (company_type_id <= 2)) AND company_type_id IN(1,2)) 14835720 743593 │ │ TABLE SCAN movie_info WHERE (((movie_id >= 2) AND (movie_id <= 2525744)) AND TRUE) AND (((info_type_id >= 105) AND (info_type_id <= 105)) AND info_type_id IN 105) 1380035 26625 │ DISTRIBUTE HASH ON movie_id, movie_id 1380035 26625 │ TABLE SCAN movie_info_idx WHERE CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 228) AND (movie_id <= 2524713)) AND ((movie_id >= 228) AND (movie_id <= 2524713))) AND TRUE) WHEN 3 THEN ((((movie_id >= 11) AND (movie_id <= 2524891)) AND ((movie_id >= 11) AND (movie_id <= 2524891))) AND TRUE) WHEN 5 THEN ((((movie_id >= 678) AND (movie_id <= 2523627)) AND ((movie_id >= 678) AND (movie_id <= 2523627))) AND TRUE) WHEN 7 THEN ((((movie_id >= 24)... 505663 2536 DISTRIBUTE HASH ON id, id, id 505663 2536 FILTER (production_year > 2000) AND (title LIKE 'Birdemic%' OR title LIKE '%Movie%') 2528312 2528312 TABLE SCAN title WHERE ((production_year > 2000) AND (title LIKE 'Birdemic%' OR title LIKE '%Movie%')) AND CASE MOD(HASH_REPARTITION(id,id,id),10) WHEN 2 THEN (((((id >= 1730276) AND (id <= 2101545)) AND ((id >= 1730276) AND (id <= 2101545))) AND ((id >= 1730276) AND (id <= 2101545))) AND struct(id,id,id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , <...
	Estimate Actual Operator 1 1 AGGREGATE MIN(mi.info), MIN(t.title) 1 4 DISTRIBUTE GATHER 1 4 AGGREGATE MIN(mi.info), MIN(t.title) 119000 10 INNER JOIN HASH ON mc.movie_id = mi.movie_id 119000 10 │└DISTRIBUTE GATHER 237000 10 │ INNER JOIN HASH ON mc.movie_id = mi_idx.movie_id 237000 1153798 │ │└DISTRIBUTE GATHER 12100 1153798 │ │ INNER JOIN HASH ON mc.company_type_id = ct.id 12100 2 │ │ │└DISTRIBUTE GATHER 1380000 2 │ │ │ TABLE SCAN company_type WHERE ct.kind IN('production companies','distributors') 842000 1153798 │ │ INNER JOIN HASH ON cn.id = mc.company_id 842000 84843 │ │ │└DISTRIBUTE GATHER 113 84843 │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 113 2606542 │ │ TABLE SCAN movie_companies 37600 2 │ INNER JOIN HASH ON mi_idx.movie_id = t.id 37600 10 │ │└DISTRIBUTE GATHER 842000 10 │ │ INNER JOIN HASH ON mi_idx.info_type_id = it2.id 842000 1 │ │ │└DISTRIBUTE GATHER 14800000 1 │ │ │ TABLE SCAN info_type WHERE it2.info = 'bottom 10 rank' 235000 10 │ │ TABLE SCAN movie_info_idx 2530000 1020 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2000L) AND (startswith(t.title,'Birdemic') OR contains(t.title,'Movie')) 37600 78020 INNER JOIN HASH ON mi.info_type_id = it1.id 37600 1 │└DISTRIBUTE GATHER 4 1 │ TABLE SCAN info_type WHERE it1.info = 'budget' 2610000 977916 TABLE SCAN movie_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1071) AS Expr1016, MIN(partialagg1072) AS Expr1017 48 6 FILTER country_code as country_code = 'us' 133 22 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 22 │└TABLE SEEK company_name AS cn 133 22 INNER JOIN LOOP ON mc.company_id = cn.id 1 22 │└TABLE SEEK company_name AS cn 134 22 AGGREGATE MIN(info as info) AS partialagg1071, MIN(title as title) AS partialagg1072 GROUP BY SORT company_id 2518 33 SORT company_id 2518 33 INNER JOIN MERGE ON id as id = company_type_id as company_type_id 2 2 │└SORT id 2 2 │ FILTER kind as kind = 'distributors' OR kind as kind = 'production companies' 4 4 │ TABLE SCAN company_type AS ct 2518 33 SORT company_type_id 2518 33 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 33 │└TABLE SEEK movie_companies AS mc 2518 33 SORT Expr1076 2518 33 PROJECT BmkToPage Bmk1008 AS Expr1076 2518 33 INNER JOIN LOOP ON t.id = mc.movie_id 4 33 │└TABLE SEEK movie_companies AS mc 103 2 INNER JOIN MERGE ON info_type_id as info_type_id = id as id 1 1 │└FILTER info as info = 'bottom 10 rank' 113 113 │ INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 113 │ │└TABLE SEEK info_type AS it2 113 113 │ TABLE SEEK info_type AS it2 1270 5 SORT info_type_id 1270 635 INNER JOIN LOOP ON t.id = mi_idx.movie_id 2 635 │└TABLE SEEK movie_info_idx AS mi_idx 568 443 INNER JOIN HASH ON mi.info_type_id = it1.id 1 1 │└FILTER info as info = 'budget' 113 113 │ TABLE SCAN info_type AS it1 568 453 INNER JOIN LOOP ON t.id = mi.movie_id 56 453 │└TABLE SEEK movie_info AS mi WHERE BLOOM(info_type_id as info_type_id,N'IN ROW') 669 2597 TABLE SCAN title AS t WHERE (production_year as production_year > 2000) AND (title as title LIKE 'Birdemic%' OR title as title LIKE '%Movie%')
	Estimate Actual Operator 1 ∞ PROJECT min AS budget, min_38 AS unsuccsessful_movie 1 1 AGGREGATE MIN(min_39) AS min, MIN(min_40) AS min_38 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(info_19) AS min_39, MIN(title) AS min_40 - 10 INNER JOIN HASH ON movie_id = id_32 302168 2536 │└DISTRIBUTE HASH ON id_32 302168 2536 │ PROJECT id AS id_32, title 302168 2536 │ FILTER (production_year > 2000) AND ((title LIKE 'Birdemic%') OR (title LIKE '%Movie%')) 302168 2536 │ TABLE SCAN title - 10 INNER JOIN HASH ON info_type_id_26 = id_8 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_8 113 1 │ FILTER info = 'bottom 10 rank' 113 1 │ TABLE SCAN info_type - 10 INNER JOIN HASH ON movie_id = movie_id_25 1380035 2 │└DISTRIBUTE HASH ON movie_id_25 1380035 2 │ PROJECT movie_id AS movie_id_25, info_type_id AS info_type_id_26 1380035 2 │ TABLE SCAN movie_info_idx - 10 INNER JOIN HASH ON info_type_id = id_4 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_4 113 1 │ FILTER info = 'budget' 113 1 │ TABLE SCAN info_type - 10 INNER JOIN HASH ON movie_id = movie_id_18 14835720 2 │└DISTRIBUTE HASH ON movie_id_18 14835720 2 │ PROJECT movie_id AS movie_id_18, info_type_id, info AS info_19 14835720 2 │ TABLE SCAN movie_info - 10 INNER JOIN HASH ON company_type_id = id_0 4 2 │└DISTRIBUTE GATHER 4 2 │ PROJECT id AS id_0 4 2 │ FILTER kind IN('distributors','production companies') 4 2 │ TABLE SCAN company_type - 10 INNER JOIN HASH ON id = company_id 2609129 33 │└DISTRIBUTE HASH ON company_id 2609129 33 │ TABLE SCAN movie_companies 211497 6 FILTER country_code = 'us' 211497 6 TABLE SCAN company_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(info), MIN(title) 1 10 INNER JOIN LOOP ON id = company_type_id 3 9 │└INNER JOIN LOOP ON id = company_id 3 33 │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 0 │ │ │└INNER JOIN HASH ON info_type_id = id 3 3 │ │ │ │└TABLE SEEK info_type AS it1 48 221 │ │ │ INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 3 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 it2 1725045 1380035 │ │ │ │ │ TABLE SCAN movie_info_idx AS mi_idx 10 10 │ │ │ │ TABLE SEEK title AS t WHERE (t.production_year > 2000) AND ((t.title LIKE 'Birdemic%') OR (t.title LIKE '%Movie%')) 80 221 │ │ │ TABLE SEEK movie_info AS mi 10 33 │ │ TABLE SEEK movie_companies AS mc 33 33 │ TABLE SEEK company_name AS cn WHERE cn.country_code = 'us' 10 10 TABLE SEEK company_type AS ct WHERE (ct.kind = 'production companies') OR (ct.kind = 'distributors')