Planner — IMDB — JOB-5B

SELECT MIN(t.title) AS american_vhs_movie
FROM job.company_type AS ct,
     job.info_type AS it,
     job.movie_companies AS mc,
     job.movie_info AS mi,
     job.title AS t
WHERE ct.kind = 'production companies'
  AND mc.note LIKE '%(VHS)%'
  AND mc.note LIKE '%(USA)%'
  AND mc.note LIKE '%(1994)%'
  AND mi.info IN ('USA',
                  'America')
  AND t.production_year > 2010
  AND t.id = mi.movie_id
  AND t.id = mc.movie_id
  AND mc.movie_id = mi.movie_id
  AND ct.id = mc.company_type_id
  AND it.id = mi.info_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 1,419 1.4K Rank		Estimation Error Est Err 1,418 1.4K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 15,228,918 15M Rank		Estimation Error Est Err 14,837,138 15M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 30,063,107 30M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 943,513 944K Rank		Estimation Error Est Err 943,507 944K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,187,909 1.2M Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 1,187,910 1.2M Rank
Estimation Error Est Err 11,688,718 12M Rank		Estimation Error Est Err 552,815 553K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 391,780 392K Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 391,681 392K Rank
Estimation Error Est Err 9,040,909 9M Rank		Estimation Error Est Err 5,360 5.4K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,529 1.5K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,422 1.4K Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,430 1.4K Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,422 1.4K Rank		Estimation Error Est Err 0 Rank	Estimation Error Est Err 1,419 1.4K Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 945,974 946K Rank		Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 945,974 946K Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 945,990 946K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min 378 0 INNER JOIN HASH ON id = info_type_id 113 0 │└TABLE SCAN info_type 45 0 INNER JOIN HASH ON id19 = movie_id35 213 0 │└INNER JOIN HASH ON id19 = movie_id 1274 0 │ │└INNER JOIN HASH ON id6 = company_type_id 1 1 │ │ │└TABLE SCAN company_type WHERE kind = production companies 5095 1418 │ │ TABLE SCAN movie_companies WHERE note LIKE '%(1994)%' AND note LIKE '%(VHS)%' AND note LIKE '%(USA)%' 365420 0 │ TABLE SCAN title WHERE production_year >= 2011 507080 0 TABLE SCAN movie_info WHERE info37 IN(America,USA)
	Estimate Actual Operator - 1 PROJECT a1 AS american_vhs_movie - 1 AGGREGATE MIN(title) AS a1 - 0 PROJECT title - 0 PROJECT title - 0 INNER JOIN HASH ON tuple(PROJECTION_5092.movie_id,PROJECTION_5092.id) = tuple(PROJECTION_5083.movie_id,PROJECTION_5083.movie_id) - 14835720 │└PROJECT movie_id AS movie_id_right - 14835720 │ PROJECT movie_id - 14835720 │ INNER JOIN HASH ON PROJECTION_5089.info_type_id = PROJECTION_5086.id - 113 │ │└PROJECT id - 113 │ │ PROJECT id - 113 │ │ TABLE SCAN info_type - 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, id, title - 0 PROJECT movie_id, title, id - 0 INNER JOIN HASH ON PROJECTION_5098.company_type_id = PROJECTION_5095.id - 1 │└PROJECT id AS id_right - 1 │ PROJECT id - 1 │ TABLE SCAN company_type WHERE kind = 'production companies' - 0 PROJECT company_type_id, movie_id, title, id AS id_left - 0 PROJECT company_type_id, movie_id, title, id - 0 INNER JOIN HASH ON PROJECTION_5104.movie_id = PROJECTION_5101.id - 391666 │└PROJECT id, title - 391666 │ PROJECT id, title - 391666 │ TABLE SCAN title WHERE production_year > 2010 - 1418 PROJECT movie_id, company_type_id - 1418 PROJECT company_type_id, movie_id - 1418 TABLE SCAN movie_companies WHERE note LIKE '%(VHS)%' AND note LIKE '%(USA)%' AND note LIKE '%(1994)%'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0) 31726 0 PROJECT title 31726 0 INNER JOIN HASH ON info_type_id = id 113 110 │└TABLE SCAN info_type WHERE id <= 110 31726 0 INNER JOIN HASH ON movie_id = movie_id 26558 0 │└INNER JOIN HASH ON company_type_id = id 1 1 │ │└FILTER id <= 2 1 1 │ │ TABLE SCAN company_type WHERE kind = 'production companies' 106234 0 │ INNER JOIN HASH ON id = movie_id 521825 1418 │ │└TABLE SCAN movie_companies WHERE contains(note,'(VHS)') AND contains(note,'(USA)') AND contains(note,'(1994)') 505662 689 │ FILTER id BETWEEN 2 AND 2525745 505662 690 │ TABLE SCAN title WHERE production_year > 2010 2967144 4671 FILTER movie_id BETWEEN 2 AND 2525745 2967144 4672 FILTER (info = 'USA') OR (info = 'America') 14835720 9038690 TABLE SCAN movie_info WHERE info IN('USA','America')
	Estimate Actual Operator 1 1 PROJECT american_vhs_movie 1 1 AGGREGATE MIN(title) 162535 10 DISTRIBUTE GATHER 162535 10 AGGREGATE MIN(title) 162535 0 INNER JOIN HASH ON id = movie_id AND id = movie_id 162535 391666 │└DISTRIBUTE HASH ON id, id 162535 391666 │ FILTER production_year > 2010 2528312 2528312 │ TABLE SCAN title WHERE (production_year > 2010) AND TRUE 306427 0 DISTRIBUTE HASH ON movie_id, movie_id 306427 0 INNER JOIN HASH ON id = info_type_id 113 113 │└TABLE SCAN info_type 306427 0 INNER JOIN HASH ON movie_id = movie_id 260913 0 │└DISTRIBUTE GATHER 260913 0 │ 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' 521826 38 │ FILTER (note LIKE '%(VHS)%' AND note LIKE '%(USA)%') AND note LIKE '%(1994)%' 2609129 1376261 │ TABLE SCAN movie_companies WHERE (((note LIKE '%(VHS)%' AND note LIKE '%(USA)%') AND note LIKE '%(1994)%') AND (((company_type_id >= 2) AND (company_type_id <= 2)) AND company_type_id IN 2)) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 13) AND (movie_id <= 2528194)) AND ((movie_id >= 13) AND (movie_id <= 2528194))) AND TRUE) WHEN 3 THEN ((((movie_id >= 11) AND (movie_id <= 2528183)) AND ((movie_id >= 11) AND (movie_id <= 2528183))) A... 2967144 552777 FILTER (info = 'USA') OR (info = 'America') 14835720 7784028 TABLE SCAN movie_info WHERE (((info = 'USA') OR (info = 'America')) AND TRUE) AND (((info_type_id >= 1) AND (info_type_id <= 113)) AND info_type_id IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,10...
	Estimate Actual Operator 1 1 AGGREGATE MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(t.title) 33 0 INNER JOIN HASH ON mc.company_type_id = ct.id 33 1 │└DISTRIBUTE GATHER 4 1 │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 31 0 INNER JOIN HASH ON mi.movie_id = mc.movie_id 31 82354 │└DISTRIBUTE GATHER 31 82354 │ INNER JOIN HASH ON mi.movie_id = t.id 31 552777 │ │└DISTRIBUTE GATHER 459000 552777 │ │ INNER JOIN HASH ON mi.info_type_id = it.id 459000 552777 │ │ │└DISTRIBUTE GATHER 2530000 552777 │ │ │ TABLE SCAN movie_info WHERE mi.info IN('USA','America') 113 5 │ │ TABLE SCAN info_type 14800000 390730 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2010L) 2610000 0 TABLE SCAN movie_companies WHERE (mc.note IS NOT NULL) AND contains(mc.note,'(VHS)') AND contains(mc.note,'(USA)') AND contains(mc.note,'(1994)')
	Estimate Actual Operator 1 1 AGGREGATE MIN(title as title) AS Expr1010 144 0 INNER JOIN LOOP ON t.id = mi.movie_id 1 0 │└TABLE SEEK movie_info AS mi WHERE info as info = 'America' OR info as info = 'USA' 72 0 INNER JOIN LOOP ON Bmk1008 = Bmk1008 0 0 │└TABLE SEEK title AS t WHERE production_year as production_year > 2010 472 ∞ PROJECT BmkToPage Bmk1008 AS Expr1026 472 0 INNER JOIN LOOP ON mc.movie_id = t.id 1 0 │└TABLE SEEK title AS t 472 0 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 945 1418 SORT company_type_id 945 1418 TABLE SCAN movie_companies AS mc WHERE note as note LIKE '%(VHS)%' AND note as note LIKE '%(USA)%' AND note as note LIKE '%(1994)%'
	Estimate Actual Operator 1 ∞ PROJECT min AS american_vhs_movie 1 1 AGGREGATE MIN(min_19) AS min - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(title) AS min_19 - 0 INNER JOIN HASH ON movie_id = id_15 159036 391666 │└DISTRIBUTE HASH ON id_15 159036 391666 │ PROJECT id AS id_15, title 159036 391666 │ FILTER production_year > 2010 159036 391666 │ TABLE SCAN title - 0 INNER JOIN HASH ON info_type_id = id_0 113 113 │└DISTRIBUTE GATHER 113 113 │ PROJECT id AS id_0 113 113 │ TABLE SCAN info_type - 0 INNER JOIN HASH ON movie_id = movie_id_9 13352148 552777 │└DISTRIBUTE HASH ON movie_id_9 13352148 552777 │ PROJECT movie_id AS movie_id_9, info_type_id 13352148 552777 │ FILTER info IN('America','USA') 13352148 552777 │ TABLE SCAN movie_info - 0 INNER JOIN HASH ON id = company_type_id 2348216 1418 │└DISTRIBUTE HASH ON company_type_id 2348216 1418 │ FILTER (note LIKE '%(VHS)%') AND (note LIKE '%(USA)%') AND (note LIKE '%(1994)%') 2348216 1418 │ TABLE SCAN movie_companies 4 0 FILTER kind = 'production companies' 4 0 TABLE SCAN company_type
	Estimate Actual Operator 1 1 AGGREGATE MIN(title) 4 4 INNER JOIN LOOP ON id = info_type_id 4 4 │└INNER JOIN LOOP ON movie_id = id 4 4 │ │└INNER JOIN LOOP ON id = movie_id 24 4 │ │ │└INNER JOIN LOOP ON id = company_type_id 100 1418 │ │ │ │└TABLE SCAN movie_companies AS mc WHERE (mc.note LIKE '%(VHS)%') AND (mc.note LIKE '%(USA)%') AND (mc.note LIKE '%(1994)%') 4 4 │ │ │ TABLE SEEK company_type AS ct WHERE ct.kind = 'production companies' 0 0 │ │ TABLE SEEK title AS t WHERE t.production_year > 2010 0 0 │ TABLE SEEK movie_info AS mi WHERE mi.info IN('USA','America') 0 0 TABLE SEEK info_type AS it