Planner — IMDB — JOB-12C

SELECT MIN(cn.name) AS movie_company,
       MIN(mi_idx.info) AS rating,
       MIN(t.title) AS mainstream_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',
                  'Western',
                  'Family')
  AND mi_idx.info > '7.0'
  AND t.production_year BETWEEN 2000 AND 2010
  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 3,380,145 3.4M Rank		Estimation Error Est Err 3,429,859 3.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 347,092 347K Rank	Estimation Error Est Err 4,711 4.7K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 18,680,491 19M Rank		Estimation Error Est Err 15,028,559 15M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,962,892 7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,973,132 3M Rank		Estimation Error Est Err 3,640,027 3.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,378,783 1.4M Rank	Estimation Error Est Err 4,712 4.7K Rank	Estimation Error Est Err 585,055 585K Rank
Estimation Error Est Err 7,243,840 7.2M Rank		Estimation Error Est Err 3,662,793 3.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,963,084 2M Rank	Estimation Error Est Err 4,721 4.7K Rank	Estimation Error Est Err 1,106,817 1.1M Rank
Estimation Error Est Err 456,999 457K Rank		Estimation Error Est Err 522,093 522K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 670,831 671K Rank	Estimation Error Est Err 4,711 4.7K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 428,316 428K Rank	Estimation Error Est Err 234,421 234K Rank	Estimation Error Est Err 451,815 452K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 253,335 253K Rank	Estimation Error Est Err 4,710 4.7K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 265,530 266K Rank	Estimation Error Est Err 265,527 266K Rank	Estimation Error Est Err 304,418 304K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 161,341 161K Rank	Estimation Error Est Err 4,711 4.7K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,227,328 1.2M Rank		Estimation Error Est Err 87,480 87K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,259,958 1.3M Rank	Estimation Error Est Err 4,727 4.7K Rank	Estimation Error Est Err 1,222,613 1.2M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 15248 4711 INNER JOIN HASH ON info_type_id26 = id 1 1 │└TABLE SCAN info_type WHERE info = genres 15248 4711 INNER JOIN HASH ON id55 = company_id 97779 15537 │└INNER JOIN HASH ON id6 = company_type_id 1 1 │ │└TABLE SCAN company_type WHERE kind = production companies 208033 45006 │ INNER JOIN HASH ON movie_id48 = movie_id25 40577 7964 │ │└INNER JOIN HASH ON id32 = movie_id25 46860 23157 │ │ │└INNER JOIN HASH ON movie_id25 = movie_id 122141 121572 │ │ │ │└INNER JOIN HASH ON info_type_id = id11 1 1 │ │ │ │ │└TABLE SCAN info_type WHERE info = rating 184633 121572 │ │ │ │ TABLE SCAN movie_info_idx WHERE info > 7.0 376688 136053 │ │ │ TABLE SCAN movie_info WHERE info27 IN(Drama,Family,Horror,Western) 1081445 491692 │ │ TABLE SCAN title WHERE production_year BETWEEN 2000 AND 2010 2609129 2609129 │ TABLE SCAN movie_companies 90648 21696 TABLE SCAN company_name WHERE country_code = us
	Estimate Actual Operator - 1 PROJECT a1 AS movie_company, a2 AS rating, a3 AS mainstream_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_425.movie_id,PROJECTION_425.movie_id,PROJECTION_425.id) = tuple(PROJECTION_416.movie_id,PROJECTION_416.movie_id,PROJECTION_416.movie_id) - 1533909 │└PROJECT movie_id AS movie_id_right - 1533909 │ PROJECT movie_id - 1533909 │ INNER JOIN HASH ON PROJECTION_422.info_type_id = PROJECTION_419.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_437.movie_id,PROJECTION_437.id) = tuple(PROJECTION_428.movie_id,PROJECTION_428.movie_id) - 121572 │└PROJECT movie_id AS movie_id_right, info - 121572 │ PROJECT info, movie_id - 121572 │ INNER JOIN HASH ON PROJECTION_434.info_type_id = PROJECTION_431.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'rating' - 192839 │ PROJECT info_type_id, info, movie_id - 192839 │ PROJECT movie_id, info, info_type_id - 192839 │ TABLE SCAN movie_info_idx WHERE info > '7.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_443.movie_id = PROJECTION_440.id - 1042800 │└PROJECT id, title - 1042800 │ PROJECT id, title - 1042800 │ TABLE SCAN title WHERE (production_year >= 2000) AND (production_year <= 2010) - 0 PROJECT movie_id, name - 0 PROJECT name, movie_id - 0 INNER JOIN HASH ON PROJECTION_449.company_type_id = PROJECTION_446.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_455.id = PROJECTION_452.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 4711 PROJECT name, info, title 0 4711 INNER JOIN HASH ON info_type_id = id 2 1 │└FILTER id <= 110 2 1 │ TABLE SCAN info_type WHERE info = 'genres' 2 4711 INNER JOIN HASH ON movie_id = movie_id 1 42332 │└INNER JOIN HASH ON company_type_id = id 1 1 │ │└FILTER id <= 2 1 1 │ │ TABLE SCAN company_type WHERE kind = 'production companies' 7 73295 │ INNER JOIN HASH ON id = company_id 1044 169157 │ │└INNER JOIN HASH ON movie_id = movie_id 994 52303 │ │ │└INNER JOIN HASH ON id = movie_id 4885 121571 │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ │ TABLE SCAN info_type WHERE info = 'rating' 276007 121571 │ │ │ │ FILTER movie_id <= 2525745 276007 121572 │ │ │ │ TABLE SCAN movie_info_idx WHERE info > '7.0' 505662 56627 │ │ │ FILTER id BETWEEN 2 AND 2525745 505662 56628 │ │ │ TABLE SCAN title WHERE production_year >= 2000 AND production_year <= 2010 2609129 176335 │ │ TABLE SCAN movie_companies 1644 84843 │ TABLE SCAN company_name WHERE country_code = 'us' 2967144 3348 FILTER movie_id BETWEEN 2 AND 2525745 2967144 3348 FILTER (info = 'Drama') OR (info = 'Horror') OR (info = 'Western') OR (info = 'Family') 14835720 17618 TABLE SCAN movie_info WHERE info IN('Drama','Horror','Western','Family')
	Estimate Actual Operator 1 1 PROJECT movie_company, rating, mainstream_movie 1 1 AGGREGATE MIN(name), MIN(info), MIN(title) 64071 10 DISTRIBUTE GATHER 64071 10 AGGREGATE MIN(name), MIN(info), MIN(title) 64071 4711 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id 64071 12752 │└DISTRIBUTE GATHER 64071 12752 │ 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 24110 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 64071 131396 │ │└DISTRIBUTE GATHER 64071 131396 │ │ 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 131396 │ │ 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 325255 │ │ DISTRIBUTE HASH ON movie_id 2967144 325255 │ │ FILTER info IN('Drama','Horror','Western','Family') 14835720 1724005 │ │ TABLE SCAN movie_info WHERE (info IN('Drama','Horror','Western','Family') 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 (... 276007 192839 │ FILTER info > '7.0' 1380035 1380035 │ TABLE SCAN movie_info_idx WHERE ((info > '7.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) 198654 1042800 FILTER (production_year >= 2000) AND (production_year <= 2010) 2528312 2528312 TABLE SCAN title WHERE ((production_year >= 2000) AND (production_year <= 2010)) AND (((((id >= 681) AND (id <= 2525733)) AND ((id >= 681) AND (id <= 2525733))) AND ((id >= 681) AND (id <= 2525733))) 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) 190 4711 INNER JOIN HASH ON mi_idx.info_type_id = it2.id 190 1 │└DISTRIBUTE GATHER 14800000 1 │ TABLE SCAN info_type WHERE it2.info = 'rating' 61 9273 INNER JOIN HASH ON mc.movie_id = mi_idx.movie_id 61 43557 │└DISTRIBUTE GATHER 59 43557 │ INNER JOIN HASH ON mc.movie_id = t.id 59 131396 │ │└DISTRIBUTE GATHER 59 131396 │ │ INNER JOIN HASH ON mc.company_id = cn.id 59 84843 │ │ │└DISTRIBUTE GATHER 4 84843 │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 23 371234 │ │ INNER JOIN HASH ON mc.company_type_id = ct.id 23 1 │ │ │└DISTRIBUTE GATHER 1380000 1 │ │ │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 276000 371234 │ │ INNER JOIN HASH ON mi.movie_id = mc.movie_id 276000 325255 │ │ │└DISTRIBUTE GATHER 276000 325255 │ │ │ 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 325255 │ │ │ TABLE SCAN movie_info WHERE mi.info IN('Drama','Horror','Western','Family') 235000 1331250 │ │ TABLE SCAN movie_companies 2610000 1039644 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year >= 2000L) AND (t.production_year <= 2010L) 113 192137 TABLE SCAN movie_info_idx WHERE mi_idx.info > '7.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 209 4711 INNER JOIN HASH ON mc.company_type_id = ct.id 1 1 │└TABLE SCAN company_type AS ct WHERE kind as kind = 'production companies' 419 4711 INNER JOIN HASH ON mi_idx.info_type_id = it2.id 1 1 │└TABLE SCAN info_type AS it2 WHERE info as info = 'rating' 209 4711 FILTER country_code as country_code = 'us' 582 15537 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 15537 │└TABLE SEEK company_name AS cn 582 15537 INNER JOIN LOOP ON mc.company_id = cn.id 1 15537 │└TABLE SEEK company_name AS cn 582 15537 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 15537 │└TABLE SEEK movie_companies AS mc WHERE BLOOM(company_type_id as company_type_id) 582 45006 PROJECT BmkToPage Bmk1008 AS Expr1270 582 45006 INNER JOIN LOOP ON t.id = mc.movie_id 9 45006 │└TABLE SEEK movie_companies AS mc 58 7964 INNER JOIN HASH ON mi.info_type_id = it1.id 1 1 │└TABLE SCAN info_type AS it1 WHERE info as info = 'genres' 41 7964 INNER JOIN LOOP ON Bmk1014 = Bmk1014 0 7964 │└TABLE SEEK title AS t WHERE production_year as production_year >= 2000 AND production_year as production_year <= 2010 100 22187 PROJECT BmkToPage Bmk1014 AS Expr1267 100 22187 INNER JOIN LOOP ON mi_idx.movie_id = t.id 1 22187 │└TABLE SEEK title AS t 100 22187 INNER JOIN LOOP ON mi_idx.movie_id = mi.movie_id 1 22187 │└TABLE SEEK movie_info AS mi WHERE (info as info = 'Drama' OR info as info = 'Family' OR info as info = 'Horror' OR info as info = 'Western') AND BLOOM(info_type_id as info_type_id) 18960 121572 TABLE SEEK movie_info_idx AS mi_idx WHERE info as info > '7.0' AND BLOOM(info_type_id as info_type_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_company, min_38 AS rating, min_39 AS mainstream_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 - 4711 INNER JOIN HASH ON movie_id = id_32 176706 1042800 │└DISTRIBUTE HASH ON id_32 176706 1042800 │ PROJECT id AS id_32, title 176706 1042800 │ FILTER production_year BETWEEN 2000 AND 2010 176706 1042800 │ TABLE SCAN title - 12752 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 - 12752 INNER JOIN HASH ON movie_id = movie_id_25 1380035 121572 │└DISTRIBUTE HASH ON movie_id_25 1380035 121572 │ PROJECT movie_id AS movie_id_25, info_type_id AS info_type_id_26, info AS info_27 1380035 121572 │ FILTER info > '7.0' 1380035 121572 │ TABLE SCAN movie_info_idx - 12752 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 - 12752 INNER JOIN HASH ON movie_id = movie_id_18 13352148 22187 │└DISTRIBUTE HASH ON movie_id_18 13352148 22187 │ PROJECT movie_id AS movie_id_18, info_type_id 13352148 22187 │ FILTER info IN('Drama','Family','Horror','Western') 13352148 22187 │ TABLE SCAN movie_info - 11860 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 - 11860 INNER JOIN HASH ON id = company_id 2609129 36035 │└DISTRIBUTE HASH ON company_id 2609129 36035 │ TABLE SCAN movie_companies 211497 4731 FILTER country_code = 'us' 211497 4731 TABLE SCAN company_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(info), MIN(title) 3 4710 INNER JOIN LOOP ON id = company_id 6 15537 │└INNER JOIN LOOP ON id = company_type_id 30 45006 │ │└INNER JOIN LOOP ON movie_id = id 2 2654 │ │ │└INNER JOIN HASH ON info_type_id = id 3 3 │ │ │ │└TABLE SEEK info_type AS it1 645 7964 │ │ │ INNER JOIN LOOP ON movie_id = id 870 52304 │ │ │ │└INNER JOIN LOOP ON id = movie_id 710 40524 │ │ │ │ │└INNER JOIN HASH ON info_type_id = id 3 3 │ │ │ │ │ │└TABLE SEEK info_type AS it2 240729 193895 │ │ │ │ │ TABLE SCAN movie_info_idx AS mi_idx WHERE mi_idx.info > '7.0' 121572 121572 │ │ │ │ TABLE SEEK title AS t WHERE (t.production_year >= 2000) AND (t.production_year <= 2010) 52304 52304 │ │ │ TABLE SEEK movie_info AS mi WHERE mi.info IN('Drama','Horror','Western','Family') 39820 44996 │ │ TABLE SEEK movie_companies AS mc 6 6 │ TABLE SEEK company_type AS ct WHERE ct.kind = 'production companies' 15537 15537 TABLE SEEK company_name AS cn WHERE cn.country_code = 'us'