Planner — IMDB — JOB-13B

SELECT MIN(cn.name) AS producing_company,
       MIN(miidx.info) AS rating,
       MIN(t.title) AS movie_about_winning
FROM job.company_name AS cn,
     job.company_type AS ct,
     job.info_type AS it,
     job.info_type AS it2,
     job.kind_type AS kt,
     job.movie_companies AS mc,
     job.movie_info AS mi,
     job.movie_info_idx AS miidx,
     job.title AS t
WHERE cn.country_code ='[us]'
  AND ct.kind ='production companies'
  AND it.info ='rating'
  AND it2.info ='release dates'
  AND kt.kind ='movie'
  AND t.title != ''
  AND (t.title LIKE '%Champion%'
       OR t.title LIKE '%Loser%')
  AND mi.movie_id = t.id
  AND it2.id = mi.info_type_id
  AND kt.id = t.kind_id
  AND mc.movie_id = t.id
  AND cn.id = mc.company_id
  AND ct.id = mc.company_type_id
  AND miidx.movie_id = t.id
  AND it.id = miidx.info_type_id
  AND mi.movie_id = miidx.movie_id
  AND mi.movie_id = mc.movie_id
  AND miidx.movie_id = mc.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,825,574 19M Rank		Estimation Error Est Err 18,833,591 19M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 10,133 10K Rank	Estimation Error Est Err 372 372 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 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,179,396 6.2M Rank		Estimation Error Est Err 15,839,895 16M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 11,794,497 12M Rank	Estimation Error Est Err 373 373 Rank	Estimation Error Est Err 3,581,582 3.6M Rank
Estimation Error Est Err 7,501,618 7.5M Rank		Estimation Error Est Err 12,990,434 13M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 9,237,759 9.2M Rank	Estimation Error Est Err 382 382 Rank	Estimation Error Est Err 7,638,701 7.6M Rank
Estimation Error Est Err 3,358,339 3.4M Rank		Estimation Error Est Err 3,274,832 3.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,309,241 2.3M Rank	Estimation Error Est Err 372 372 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,845,012 1.8M Rank	Estimation Error Est Err 464,977 465K Rank	Estimation Error Est Err 1,845,081 1.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 311,999 312K Rank	Estimation Error Est Err 372 372 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 15,337 15K Rank	Estimation Error Est Err 12,676 13K Rank	Estimation Error Est Err 15,007 15K Rank	Estimation Error Est Err 8,086 8.1K Rank	Estimation Error Est Err 15,007 15K Rank	Estimation Error Est Err 493 493 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,284 2.3K Rank		Estimation Error Est Err 3,206 3.2K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,925 3.9K Rank	Estimation Error Est Err 388 388 Rank	Estimation Error Est Err 2,199 2.2K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 31 372 INNER JOIN HASH ON id60 = company_id 3 626 │└INNER JOIN HASH ON id = company_type_id 1 1 │ │└TABLE SCAN company_type WHERE kind = production companies 6 2474 │ INNER JOIN HASH ON movie_id53 = movie_id 5 272 │ │└INNER JOIN HASH ON id6 = info_type_id46 1 1 │ │ │└TABLE SCAN info_type WHERE info = rating 24 816 │ │ INNER JOIN HASH ON movie_id = movie_id45 27 510 │ │ │└INNER JOIN HASH ON id11 = info_type_id 1 1 │ │ │ │└TABLE SCAN info_type WHERE info = release dates 3075 4414 │ │ │ INNER JOIN HASH ON movie_id = id21 448 395 │ │ │ │└INNER JOIN HASH ON id16 = kind_id 1 1 │ │ │ │ │└TABLE SCAN kind_type WHERE kind = movie 1711 631 │ │ │ │ TABLE SCAN title WHERE title LIKE '%Champion%' OR title LIKE '%Loser%' 14835720 14835720 │ │ │ TABLE SCAN movie_info 1380035 1380035 │ │ TABLE SCAN movie_info_idx 2609129 2609129 │ TABLE SCAN movie_companies 90648 55 TABLE SCAN company_name WHERE country_code = us
	Estimate Actual Operator - ∞ PROJECT a1 AS producing_company, a2 AS rating, a3 AS movie_about_winning - ∞ AGGREGATE MIN(name) AS a1, MIN(info) AS a2, MIN(title) AS a3 - ∞ PROJECT name, info, title - ∞ PROJECT name, info, title - ∞ INNER JOIN HASH ON tuple(PROJECTION_537.movie_id,PROJECTION_537.movie_id,PROJECTION_537.movie_id,PROJECTION_537.movie_id) = tuple(PROJECTION_516.movie_id,PROJECTION_516.movie_id,PROJECTION_516.id,PROJECTION_516.id) - ∞ │└PROJECT movie_id AS movie_id_right, id, title - ∞ │ PROJECT movie_id, title, id - ∞ │ INNER JOIN HASH ON PROJECTION_528.id = PROJECTION_519.movie_id - 3036719 │ │└PROJECT movie_id - 3036719 │ │ PROJECT movie_id - 3036719 │ │ INNER JOIN HASH ON PROJECTION_525.info_type_id = PROJECTION_522.id - 1 │ │ │└PROJECT id - 1 │ │ │ PROJECT id - 1 │ │ │ TABLE SCAN info_type WHERE info = 'release dates' - 14835720 │ │ PROJECT info_type_id, movie_id - 14835720 │ │ PROJECT movie_id, info_type_id - 14835720 │ │ TABLE SCAN movie_info - ∞ │ PROJECT id, title - ∞ │ PROJECT title, id - ∞ │ INNER JOIN HASH ON PROJECTION_534.kind_id = PROJECTION_531.id - 1 │ │└PROJECT id AS id_right - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN kind_type WHERE kind = 'movie' - ∞ │ PROJECT kind_id, title, id AS id_left - ∞ │ PROJECT id, title, kind_id - ∞ │ TABLE SCAN title WHERE notEmpty title AND (title LIKE '%Champion%' OR title LIKE '%Loser%') - 0 PROJECT movie_id AS movie_id_left, movie_id AS movie_id_left_2, name, info - 0 PROJECT name, movie_id, info, movie_id - 0 INNER JOIN HASH ON PROJECTION_549.movie_id = PROJECTION_540.movie_id - 459925 │└PROJECT movie_id AS movie_id_right, info - 459925 │ PROJECT info, movie_id - 459925 │ INNER JOIN HASH ON PROJECTION_546.info_type_id = PROJECTION_543.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'rating' - 1380035 │ PROJECT info_type_id, info, movie_id - 1380035 │ PROJECT info, movie_id, info_type_id - 1380035 │ TABLE SCAN movie_info_idx - 0 PROJECT movie_id AS movie_id_left, name - 0 PROJECT name, movie_id - 0 INNER JOIN HASH ON PROJECTION_555.company_type_id = PROJECTION_552.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_561.id = PROJECTION_558.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 372 PROJECT name, info, title 0 372 INNER JOIN HASH ON info_type_id = id 2 1 │└FILTER id <= 110 2 1 │ TABLE SCAN info_type WHERE info = 'release dates' 7 372 INNER JOIN HASH ON movie_id = movie_id 1 61 │└INNER JOIN HASH ON info_type_id = id 2 1 │ │└FILTER id >= 99 2 1 │ │ TABLE SCAN info_type WHERE info = 'rating' 73 61 │ INNER JOIN HASH ON movie_id = movie_id 132 203 │ │└INNER JOIN HASH ON company_type_id = id 1 1 │ │ │└FILTER id <= 2 1 1 │ │ │ TABLE SCAN company_type WHERE kind = 'production companies' 530 434 │ │ INNER JOIN HASH ON kind_id = id 1 1 │ │ │└TABLE SCAN kind_type WHERE kind = 'movie' 3716 434 │ │ 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 395 │ │ FILTER id BETWEEN 2 AND 2525745 505662 395 │ │ TABLE SCAN title WHERE title != '' AND (contains(title,'Champion') OR contains(title,'Loser')) 1380035 572 │ TABLE SCAN movie_info_idx WHERE movie_id <= 2525745 14835720 663396 TABLE SCAN movie_info WHERE movie_id >= 2 AND movie_id <= 2525745
	Estimate Actual Operator 1 1 PROJECT producing_company, rating, movie_about_winning 1 1 AGGREGATE MIN(name), MIN(info), MIN(title) 91531 10 DISTRIBUTE GATHER 91531 10 AGGREGATE MIN(name), MIN(info), MIN(title) 91531 372 INNER JOIN HASH ON id = kind_id 2 1 │└DISTRIBUTE GATHER 2 1 │ FILTER kind = 'movie' 7 7 │ DISTRIBUTE ROUND ROBIN 7 7 │ TABLE SCAN kind_type WHERE kind = 'movie' 320359 490 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id 320359 1051802 │└DISTRIBUTE HASH ON movie_id, movie_id, movie_id 320359 1051802 │ 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' 320359 3173906 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 320359 1355560 │ │└DISTRIBUTE HASH ON movie_id, movie_id 320359 1355560 │ │ 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' 1532152 1481857 │ │ 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) 14835720 3212639 │ │ DISTRIBUTE HASH ON movie_id 14835720 3212639 │ │ TABLE SCAN movie_info WHERE 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 (((movie_id >= 54) AND (movie_id <= 2525715)) AND T... 1380035 1380035 │ DISTRIBUTE HASH ON movie_id, movie_id 1380035 1380035 │ TABLE SCAN movie_info_idx WHERE CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 57) AND (movie_id <= 2525740)) AND ((movie_id >= 57) AND (movie_id <= 2525740))) AND TRUE) WHEN 3 THEN ((((movie_id >= 45) AND (movie_id <= 2525737)) AND ((movie_id >= 45) AND (movie_id <= 2525737))) AND TRUE) WHEN 5 THEN ((((movie_id >= 46) AND (movie_id <= 2525744)) AND ((movie_id >= 46) AND (movie_id <= 2525744))) AND TRUE) WHEN 7 THEN ((((movie_id >= 24) AND... 505663 1243 DISTRIBUTE HASH ON id, id, id 505663 1243 FILTER (title <> '') AND (title LIKE '%Champion%' OR title LIKE '%Loser%') 2528312 1297449 TABLE SCAN title WHERE (((title <> '') AND (title LIKE '%Champion%' OR title LIKE '%Loser%')) AND CASE MOD(HASH_REPARTITION(id,id,id),10) WHEN 0 THEN (((((id >= 645) AND (id <= 2521833)) AND ((id >= 645) AND (id <= 2521833))) AND ((id >= 645) AND (id <= 2521833))) AND TRUE) WHEN 1 THEN (((((id >= 61) AND (id <= 2525593)) AND ((id >= 61) AND (id <= 2525593))) AND ((id >= 61) AND (id <= 2525593))) AND TRUE) WHEN 2 THEN (((((id >= 24) AND (id <= 2523357)) AND ((id >= 24) AND ...
	Estimate Actual Operator 1 1 AGGREGATE MIN(cn.name), MIN(miidx.info), MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(cn.name), MIN(miidx.info), MIN(t.title) 39600 372 INNER JOIN HASH ON mc.movie_id = miidx.movie_id 39600 90 │└DISTRIBUTE GATHER 519000 90 │ INNER JOIN HASH ON t.kind_id = kt.id 519000 1 │ │└DISTRIBUTE GATHER 2610000 1 │ │ TABLE SCAN kind_type WHERE kt.kind = 'movie' 519000 213 │ INNER JOIN HASH ON miidx.movie_id = t.id 519000 459925 │ │└DISTRIBUTE GATHER 290000 459925 │ │ INNER JOIN HASH ON miidx.info_type_id = it.id 290000 1 │ │ │└DISTRIBUTE GATHER 2530000 1 │ │ │ TABLE SCAN info_type WHERE it.info = 'rating' 1380000 1377304 │ │ TABLE SCAN movie_info_idx 14800000 881 │ TABLE SCAN title WHERE ( NOT (t.title = '')) AND (contains(t.title,'Champion') OR contains(t.title,'Loser')) 12100 1355560 INNER JOIN HASH ON mc.company_id = cn.id 12100 84843 │└DISTRIBUTE GATHER 113 84843 │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 203000 3240953 INNER JOIN HASH ON mc.company_type_id = ct.id 203000 1 │└DISTRIBUTE GATHER 235000 1 │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 290000 3240953 INNER JOIN HASH ON mi.movie_id = mc.movie_id 290000 3036719 │└DISTRIBUTE GATHER 276000 3036719 │ INNER JOIN HASH ON mi.info_type_id = it2.id 276000 1 │ │└DISTRIBUTE GATHER 7 1 │ │ TABLE SCAN info_type WHERE it2.info = 'release dates' 113 3383078 │ TABLE SCAN movie_info 4 1333286 TABLE SCAN movie_companies
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1018, MIN(info as info) AS Expr1019, MIN(title as title) AS Expr1020 83 493 FILTER country_code as country_code = 'us' 568 912 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 912 │└TABLE SEEK company_name AS cn 568 912 SORT Expr1098 568 912 PROJECT BmkToPage Bmk1000 AS Expr1098 568 912 INNER JOIN LOOP ON mc.company_id = cn.id 1 912 │└TABLE SEEK company_name AS cn 569 912 SORT company_id 569 912 INNER JOIN MERGE ON id as id = company_type_id as company_type_id 1 1 │└SORT id 1 1 │ FILTER kind as kind = 'production companies' 4 4 │ TABLE SCAN company_type AS ct 1138 912 SORT company_type_id 1138 4423 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 4423 │└TABLE SEEK movie_companies AS mc 1138 4423 SORT Expr1094 1138 4423 PROJECT BmkToPage Bmk1010 AS Expr1094 1138 4423 INNER JOIN LOOP ON t.id = mc.movie_id 4 4423 │└TABLE SEEK movie_companies AS mc 114 429 INNER JOIN MERGE ON id as id = info_type_id as info_type_id 1 1 │└SORT id 1 1 │ FILTER info as info = 'rating' 113 113 │ TABLE SCAN info_type AS it 574 429 SORT info_type_id 574 1287 INNER JOIN LOOP ON t.id = miidx.movie_id 2 1287 │└TABLE SEEK movie_info_idx AS miidx 257 719 INNER JOIN LOOP ON it2.id = mi.info_type_id AND t.id = mi.movie_id 1 719 │└TABLE SEEK movie_info AS mi 302 495 INNER JOIN LOOP ON info as info = 'release dates' 1 1 │└MATERIALISE AS m53 1 1 │ TABLE SCAN info_type AS it2 WHERE info as info = 'release dates' 302 495 INNER JOIN MERGE ON id as id = kind_id as kind_id 1 1 │└SORT id 1 1 │ FILTER kind as kind = 'movie' 7 7 │ TABLE SCAN kind_type AS kt 1816 495 SORT kind_id 1816 2536 TABLE SCAN title AS t WHERE (title as title LIKE '%Champion%' OR title as title LIKE '%Loser%') AND title as title <> ''
	Estimate Actual Operator 1 ∞ PROJECT min AS producing_company, min_43 AS rating, min_44 AS movie_about_winning 1 1 AGGREGATE MIN(min_45) AS min, MIN(min_46) AS min_43, MIN(min_47) AS min_44 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name) AS min_45, MIN(info_32) AS min_46, MIN(title) AS min_47 - 372 INNER JOIN HASH ON kind_id = id_13 7 1 │└DISTRIBUTE GATHER 7 1 │ PROJECT id AS id_13 7 1 │ FILTER kind = 'movie' 7 1 │ TABLE SCAN kind_type - 490 INNER JOIN HASH ON movie_id = id_37 1137740 1243 │└DISTRIBUTE HASH ON id_37 1137740 1243 │ PROJECT id AS id_37, title, kind_id 1137740 1243 │ FILTER (title <> '') AND ((title LIKE '%Champion%') OR (title LIKE '%Loser%')) 1137740 1243 │ TABLE SCAN title - 490 INNER JOIN HASH ON info_type_id_31 = id_4 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_4 113 1 │ FILTER info = 'rating' 113 1 │ TABLE SCAN info_type - 490 INNER JOIN HASH ON movie_id = movie_id_30 1380035 256 │└DISTRIBUTE HASH ON movie_id_30 1380035 256 │ PROJECT movie_id AS movie_id_30, info_type_id AS info_type_id_31, info AS info_32 1380035 256 │ TABLE SCAN movie_info_idx - 490 INNER JOIN HASH ON info_type_id = id_8 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_8 113 1 │ FILTER info = 'release dates' 113 1 │ TABLE SCAN info_type - 490 INNER JOIN HASH ON movie_id = movie_id_23 14835720 463 │└DISTRIBUTE HASH ON movie_id_23 14835720 463 │ PROJECT movie_id AS movie_id_23, info_type_id 14835720 463 │ TABLE SCAN movie_info - 133 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 - 133 INNER JOIN HASH ON id = company_id 2609129 217 │└DISTRIBUTE HASH ON company_id 2609129 217 │ TABLE SCAN movie_companies 211497 101 FILTER country_code = 'us' 211497 101 TABLE SCAN company_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(info), MIN(title) 3 372 INNER JOIN LOOP ON id = info_type_id 6 2121 │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 3 60 │ │└INNER JOIN LOOP ON id = company_type_id 3 161 │ │ │└INNER JOIN LOOP ON id = company_id 3 312 │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 3 90 │ │ │ │ │└INNER JOIN LOOP ON id = kind_id 3 351 │ │ │ │ │ │└INNER JOIN LOOP ON id = movie_id 5089 153308 │ │ │ │ │ │ │└INNER JOIN HASH ON info_type_id = id 3 3 │ │ │ │ │ │ │ │└TABLE SEEK info_type AS it 1725045 1380035 │ │ │ │ │ │ │ TABLE SCAN movie_info_idx AS miidx 459925 459925 │ │ │ │ │ │ TABLE SEEK title AS t WHERE (t.title <> '') AND ((t.title LIKE '%Champion%') OR (t.title LIKE '%Loser%')) 18 18 │ │ │ │ │ TABLE SEEK kind_type AS kt WHERE kt.kind = 'movie' 450 313 │ │ │ │ TABLE SEEK movie_companies AS mc 313 313 │ │ │ TABLE SEEK company_name AS cn WHERE cn.country_code = 'us' 161 161 │ │ TABLE SEEK company_type AS ct WHERE ct.kind = 'production companies' 2440 2122 │ TABLE SEEK movie_info AS mi 2122 2122 TABLE SEEK info_type AS it2 WHERE it2.info = 'release dates'