Planner — IMDB — JOB-12A

SELECT MIN(cn.name) AS movie_company,
       MIN(mi_idx.info) AS rating,
       MIN(t.title) AS drama_horror_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 = 'production companies'
  AND it1.info = 'genres'
  AND it2.info = 'rating'
  AND mi.info IN ('Drama',
                  'Horror')
  AND mi_idx.info > '8.0'
  AND t.production_year BETWEEN 2005 AND 2008
  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 2,837,443 2.8M Rank		Estimation Error Est Err 2,839,771 2.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 39,492 39K Rank	Estimation Error Est Err 397 397 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 17,943,341 18M Rank		Estimation Error Est Err 14,888,349 15M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,154,506 6.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,183,579 2.2M Rank		Estimation Error Est Err 2,731,002 2.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,125,160 1.1M Rank	Estimation Error Est Err 398 398 Rank	Estimation Error Est Err 476,236 476K Rank
Estimation Error Est Err 7,243,840 7.2M Rank		Estimation Error Est Err 2,826,639 2.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,882,462 1.9M Rank	Estimation Error Est Err 407 407 Rank	Estimation Error Est Err 1,013,971 1M Rank
Estimation Error Est Err 139,413 139K Rank		Estimation Error Est Err 147,424 147K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 78,045 78K Rank	Estimation Error Est Err 397 397 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 38,388 38K Rank	Estimation Error Est Err 38,387 38K Rank	Estimation Error Est Err 40,878 41K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 39,652 40K Rank	Estimation Error Est Err 397 397 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 29,977 30K Rank	Estimation Error Est Err 29,864 30K Rank	Estimation Error Est Err 46,986 47K Rank	Estimation Error Est Err 3,359 3.4K Rank	Estimation Error Est Err 31,694 32K Rank	Estimation Error Est Err 397 397 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 467,091 467K Rank		Estimation Error Est Err 9,083 9.1K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 469,976 470K Rank	Estimation Error Est Err 413 413 Rank	Estimation Error Est Err 466,109 466K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 1892 397 INNER JOIN HASH ON info_type_id41 = id 1 1 │└TABLE SCAN info_type WHERE info = genres 1892 397 INNER JOIN HASH ON id55 = company_id 11602 708 │└INNER JOIN HASH ON id6 = company_type_id 1 1 │ │└TABLE SCAN company_type WHERE kind = production companies 24684 1934 │ INNER JOIN HASH ON movie_id48 = movie_id40 4815 557 │ │└INNER JOIN HASH ON movie_id40 = movie_id 5012 4047 │ │ │└INNER JOIN HASH ON id24 = movie_id 24234 15849 │ │ │ │└INNER JOIN HASH ON info_type_id = id11 1 1 │ │ │ │ │└TABLE SCAN info_type WHERE info = rating 53907 15849 │ │ │ │ TABLE SCAN movie_info_idx WHERE info > 8.0 501218 172465 │ │ │ TABLE SCAN title WHERE production_year BETWEEN 2005 AND 2008 347712 557 │ │ TABLE SCAN movie_info WHERE info42 IN(Drama,Horror) 2609129 2609129 │ TABLE SCAN movie_companies 90648 39440 TABLE SCAN company_name WHERE country_code = us
	Estimate Actual Operator - 1 PROJECT a1 AS movie_company, a2 AS rating, a3 AS drama_horror_movie - 1 AGGREGATE MIN(name) AS a1, MIN(info) AS a2, MIN(title) AS a3 - 0 PROJECT name, info, title - 0 PROJECT name, info, title - 0 INNER JOIN HASH ON tuple(PROJECTION_331.movie_id,PROJECTION_331.movie_id,PROJECTION_331.id) = tuple(PROJECTION_322.movie_id,PROJECTION_322.movie_id,PROJECTION_322.movie_id) - 1533909 │└PROJECT movie_id AS movie_id_right - 1533909 │ PROJECT movie_id - 1533909 │ INNER JOIN HASH ON PROJECTION_328.info_type_id = PROJECTION_325.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'genres' - 14835720 │ PROJECT info_type_id, movie_id - 14835720 │ PROJECT movie_id, info_type_id - 14835720 │ TABLE SCAN movie_info WHERE TRUE - 0 PROJECT movie_id AS movie_id_left, movie_id AS movie_id_left_2, id, name, info, title - 0 PROJECT name, movie_id, info, movie_id, title, id - 0 INNER JOIN HASH ON tuple(PROJECTION_343.movie_id,PROJECTION_343.id) = tuple(PROJECTION_334.movie_id,PROJECTION_334.movie_id) - 15849 │└PROJECT movie_id AS movie_id_right, info - 15849 │ PROJECT info, movie_id - 15849 │ INNER JOIN HASH ON PROJECTION_340.info_type_id = PROJECTION_337.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'rating' - 52629 │ PROJECT info_type_id, info, movie_id - 52629 │ PROJECT movie_id, info, info_type_id - 52629 │ TABLE SCAN movie_info_idx WHERE info > '8.0' - 0 PROJECT movie_id AS movie_id_left, id, name, title - 0 PROJECT name, movie_id, title, id - 0 INNER JOIN HASH ON PROJECTION_349.movie_id = PROJECTION_346.id - 445860 │└PROJECT id, title - 445860 │ PROJECT id, title - 445860 │ TABLE SCAN title WHERE (production_year >= 2005) AND (production_year <= 2008) - 0 PROJECT movie_id, name - 0 PROJECT name, movie_id - 0 INNER JOIN HASH ON PROJECTION_355.company_type_id = PROJECTION_352.id - 1 │└PROJECT id - 1 │ PROJECT id - 1 │ TABLE SCAN company_type WHERE kind = 'production companies' - 0 PROJECT company_type_id, name, movie_id - 0 PROJECT name, company_type_id, movie_id - 0 INNER JOIN HASH ON PROJECTION_361.id = PROJECTION_358.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, name - 0 PROJECT id, name - 0 TABLE SCAN company_name WHERE country_code = 'us'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 0 397 PROJECT name, info, title 0 397 INNER JOIN HASH ON info_type_id = id 2 1 │└FILTER id <= 110 2 1 │ TABLE SCAN info_type WHERE info = 'genres' 2 397 INNER JOIN HASH ON movie_id = movie_id 1 5674 │└INNER JOIN HASH ON company_type_id = id 1 1 │ │└FILTER id <= 2 1 1 │ │ TABLE SCAN company_type WHERE kind = 'production companies' 7 9068 │ INNER JOIN HASH ON id = company_id 1044 17301 │ │└INNER JOIN HASH ON movie_id = movie_id 994 4047 │ │ │└INNER JOIN HASH ON id = movie_id 4885 15849 │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ │ TABLE SCAN info_type WHERE info = 'rating' 276007 15849 │ │ │ │ FILTER movie_id <= 2525745 276007 15849 │ │ │ │ TABLE SCAN movie_info_idx WHERE info > '8.0' 505662 6213 │ │ │ FILTER id BETWEEN 2 AND 2525745 505662 6213 │ │ │ TABLE SCAN title WHERE production_year >= 2005 AND production_year <= 2008 2609129 30658 │ │ TABLE SCAN movie_companies 1644 84842 │ TABLE SCAN company_name WHERE country_code = 'us' 2967144 358 FILTER movie_id BETWEEN 2 AND 2525745 2967144 358 FILTER (info = 'Drama') OR (info = 'Horror') 14835720 1848 TABLE SCAN movie_info WHERE info IN('Drama','Horror')
	Estimate Actual Operator 1 1 PROJECT movie_company, rating, drama_horror_movie 1 1 AGGREGATE MIN(name), MIN(info), MIN(title) 64071 10 DISTRIBUTE GATHER 64071 10 AGGREGATE MIN(name), MIN(info), MIN(title) 64071 397 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id 64071 1311 │└DISTRIBUTE GATHER 64071 1311 │ INNER JOIN HASH ON id = info_type_id 23 1 │ │└DISTRIBUTE GATHER 23 1 │ │ FILTER info = 'rating' 113 113 │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ TABLE SCAN info_type WHERE info = 'rating' 64071 6511 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 64071 102526 │ │└DISTRIBUTE GATHER 64071 102526 │ │ INNER JOIN HASH ON id = info_type_id 23 1 │ │ │└DISTRIBUTE GATHER 23 1 │ │ │ FILTER info = 'genres' 113 113 │ │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ │ TABLE SCAN info_type WHERE info = 'genres' 306430 102526 │ │ INNER JOIN HASH ON movie_id = movie_id 260916 552328 │ │ │└DISTRIBUTE HASH ON movie_id 260916 552328 │ │ │ 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' 521832 570132 │ │ │ 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 1376261 │ │ │ TABLE SCAN movie_companies WHERE (((company_id >= 1) AND (company_id <= 234997)) AND TRUE) AND (((company_type_id >= 2) AND (company_type_id <= 2)) AND company_type_id IN 2) 2967144 272720 │ │ DISTRIBUTE HASH ON movie_id 2967144 272720 │ │ FILTER (info = 'Drama') OR (info = 'Horror') 14835720 1724005 │ │ TABLE SCAN movie_info WHERE (((info = 'Drama') OR (info = 'Horror')) AND CASE MOD(HASH_REPARTITION movie_id,10) WHEN 0 THEN (((movie_id >= 67) AND (movie_id <= 2525688)) AND TRUE) WHEN 1 THEN (((movie_id >= 45) AND (movie_id <= 2525732)) AND TRUE) WHEN 2 THEN (((movie_id >= 24) AND (movie_id <= 2525701)) AND TRUE) WHEN 3 THEN (((movie_id >= 2) AND (movie_id <= 2525729)) AND TRUE) WHEN 4 THEN (((movie_id >= 55) AND (movie_id <= 2525738)) AND TRUE) WHEN 5 THEN (((mov... 276007 52629 │ FILTER info > '8.0' 1380035 1380035 │ TABLE SCAN movie_info_idx WHERE ((info > '8.0') AND ((((movie_id >= 196) AND (movie_id <= 2525743)) AND ((movie_id >= 196) AND (movie_id <= 2525743))) AND TRUE)) AND (((info_type_id >= 101) AND (info_type_id <= 101)) AND info_type_id IN 101) 72238 445860 FILTER (production_year >= 2005) AND (production_year <= 2008) 2528312 2528312 TABLE SCAN title WHERE ((production_year >= 2005) AND (production_year <= 2008)) AND (((((id >= 8805) AND (id <= 2521635)) AND ((id >= 8805) AND (id <= 2521635))) AND ((id >= 8805) AND (id <= 2521635))) AND TRUE)
	Estimate Actual Operator 1 1 AGGREGATE MIN(cn.name), MIN(mi_idx.info), MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(cn.name), MIN(mi_idx.info), MIN(t.title) 108 397 INNER JOIN HASH ON mi_idx.info_type_id = it2.id 108 1 │└DISTRIBUTE GATHER 14800000 1 │ TABLE SCAN info_type WHERE it2.info = 'rating' 33 1421 INNER JOIN HASH ON mc.movie_id = mi_idx.movie_id 33 16143 │└DISTRIBUTE GATHER 31 16143 │ INNER JOIN HASH ON mc.movie_id = t.id 31 102526 │ │└DISTRIBUTE GATHER 31 102526 │ │ INNER JOIN HASH ON mc.company_id = cn.id 31 84843 │ │ │└DISTRIBUTE GATHER 4 84843 │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 12 315424 │ │ INNER JOIN HASH ON mc.company_type_id = ct.id 12 1 │ │ │└DISTRIBUTE GATHER 1380000 1 │ │ │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 170000 315424 │ │ INNER JOIN HASH ON mi.movie_id = mc.movie_id 170000 272720 │ │ │└DISTRIBUTE GATHER 276000 272720 │ │ │ INNER JOIN HASH ON mi.info_type_id = it1.id 276000 1 │ │ │ │└DISTRIBUTE GATHER 2530000 1 │ │ │ │ TABLE SCAN info_type WHERE it1.info = 'genres' 113 272720 │ │ │ TABLE SCAN movie_info WHERE mi.info IN('Drama','Horror') 235000 1331227 │ │ TABLE SCAN movie_companies 2610000 444540 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year >= 2005L) AND (t.production_year <= 2008L) 113 50246 TABLE SCAN movie_info_idx WHERE mi_idx.info > '8.0'
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1016, MIN(info as info) AS Expr1017, MIN(title as title) AS Expr1018 20 397 FILTER country_code as country_code = 'us' 58 708 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 708 │└TABLE SEEK company_name AS cn 58 708 SORT Expr1072 58 708 PROJECT BmkToPage Bmk1000 AS Expr1072 58 708 INNER JOIN LOOP ON mc.company_id = cn.id 1 708 │└TABLE SEEK company_name AS cn 58 708 SORT company_id 58 708 FILTER kind as kind = 'production companies' 116 1934 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 1934 │└TABLE SEEK company_type AS ct 116 1934 INNER JOIN LOOP ON mc.company_type_id = ct.id 1 1934 │└TABLE SEEK company_type AS ct 116 1934 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 1934 │└TABLE SEEK movie_companies AS mc 116 1934 PROJECT BmkToPage Bmk1008 AS Expr1081 116 1934 INNER JOIN LOOP ON t.id = mc.movie_id 9 1934 │└TABLE SEEK movie_companies AS mc 11 557 INNER JOIN LOOP ON Bmk1014 = Bmk1014 0 557 │└TABLE SEEK title AS t WHERE production_year as production_year >= 2005 AND production_year as production_year <= 2008 68 1943 PROJECT BmkToPage Bmk1014 AS Expr1079 68 1943 INNER JOIN LOOP ON mi.movie_id = t.id 1 1943 │└TABLE SEEK title AS t 68 1943 SORT movie_id 68 1943 INNER JOIN HASH ON mi.info_type_id = it1.id 1 1 │└FILTER info as info = 'genres' 113 113 │ TABLE SCAN info_type AS it1 4774 2137 INNER JOIN LOOP ON mi_idx.movie_id = mi.movie_id 1 2137 │└TABLE SEEK movie_info AS mi WHERE info as info = 'Drama' OR info as info = 'Horror' 10726 15849 INNER JOIN HASH ON mi_idx.info_type_id = it2.id 1 1 │└FILTER info as info = 'rating' 113 113 │ INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 113 │ │└TABLE SEEK info_type AS it2 113 113 │ TABLE SEEK info_type AS it2 53631 15849 TABLE SEEK movie_info_idx AS mi_idx WHERE (PROBE(Bitmap1075,info_type_id as info_type_id,N'IN ROW')) AND (info as info > '8.0')
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_company, min_38 AS rating, min_39 AS drama_horror_movie 1 1 AGGREGATE MIN(min_40) AS min, MIN(min_41) AS min_38, MIN(min_42) AS min_39 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name) AS min_40, MIN(info_27) AS min_41, MIN(title) AS min_42 - 397 INNER JOIN HASH ON movie_id = id_32 53012 445860 │└DISTRIBUTE HASH ON id_32 53012 445860 │ PROJECT id AS id_32, title 53012 445860 │ FILTER production_year BETWEEN 2005 AND 2008 53012 445860 │ TABLE SCAN title - 1311 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 = 'rating' 113 1 │ TABLE SCAN info_type - 1311 INNER JOIN HASH ON movie_id = movie_id_25 1380035 15849 │└DISTRIBUTE HASH ON movie_id_25 1380035 15849 │ PROJECT movie_id AS movie_id_25, info_type_id AS info_type_id_26, info AS info_27 1380035 15849 │ FILTER info > '8.0' 1380035 15849 │ TABLE SCAN movie_info_idx - 1311 INNER JOIN HASH ON info_type_id = id_4 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_4 113 1 │ FILTER info = 'genres' 113 1 │ TABLE SCAN info_type - 1311 INNER JOIN HASH ON movie_id = movie_id_18 13352148 1943 │└DISTRIBUTE HASH ON movie_id_18 13352148 1943 │ PROJECT movie_id AS movie_id_18, info_type_id 13352148 1943 │ FILTER info IN('Drama','Horror') 13352148 1943 │ TABLE SCAN movie_info - 1264 INNER JOIN HASH ON company_type_id = id_0 4 1 │└DISTRIBUTE GATHER 4 1 │ PROJECT id AS id_0 4 1 │ FILTER kind = 'production companies' 4 1 │ TABLE SCAN company_type - 1264 INNER JOIN HASH ON id = company_id 2609129 2438 │└DISTRIBUTE HASH ON company_id 2609129 2438 │ TABLE SCAN movie_companies 211497 998 FILTER country_code = 'us' 211497 998 TABLE SCAN company_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(info), MIN(title) 1 397 INNER JOIN LOOP ON id = company_id 1 708 │└INNER JOIN LOOP ON info = 'genres' AND id = company_type_id AND (id = company_type_id) 1 1 │ │└TABLE SCAN company_type AS ct WHERE ct.kind = 'production companies' 2 1934 │ INNER JOIN LOOP ON movie_id = id 1 557 │ │└INNER JOIN LOOP ON info = 'rating' AND id = info_type_id AND (id = info_type_id) 1 1 │ │ │└TABLE SEEK info_type AS it1 49 557 │ │ INNER JOIN LOOP ON movie_id = id 79 4047 │ │ │└INNER JOIN LOOP ON id = movie_id 459 15849 │ │ │ │└INNER JOIN LOOP ON info_type_id = id 1 1 │ │ │ │ │└TABLE SEEK info_type AS it2 12961 15849 │ │ │ │ TABLE SEEK movie_info_idx AS mi_idx WHERE mi_idx.info > '8.0' 15849 15849 │ │ │ TABLE SEEK title AS t WHERE (t.production_year >= 2005) AND (t.production_year <= 2008) 4047 4047 │ │ TABLE SEEK movie_info AS mi WHERE mi.info IN('Drama','Horror') 2785 1932 │ TABLE SEEK movie_companies AS mc 708 708 TABLE SEEK company_name AS cn WHERE cn.country_code = 'us'