Planner — IMDB — JOB-5A

SELECT MIN(t.title) AS typical_european_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 '%(theatrical)%'
  AND mc.note LIKE '%(France)%'
  AND mi.info IN ('Sweden',
                  'Norway',
                  'Germany',
                  'Denmark',
                  'Swedish',
                  'Denish',
                  'Norwegian',
                  'German')
  AND t.production_year > 2005
  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	Join Probe Join	Sort Sort	Hash Build Hash	Aggregate Agg	Distribute Dist
Estimation Error Est Err 24,026 24K Rank	Estimation Error Est Err 24,025 24K 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,872,779 16M Rank	Estimation Error Est Err 14,863,674 15M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 30,684,361 31M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,163,598 1.2M Rank	Estimation Error Est Err 1,163,590 1.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 355,963 356K Rank	Estimation Error Est Err 1 1 Rank	Estimation Error Est Err 355,964 356K Rank
Estimation Error Est Err 12,947,637 13M Rank	Estimation Error Est Err 154,857 155K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,013,034 1M Rank	Estimation Error Est Err 10 10 Rank	Estimation Error Est Err 1,012,935 1M Rank
Estimation Error Est Err 122,194 122K Rank	Estimation Error Est Err 103,157 103K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 116,296 116K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 24,026 24K Rank	Estimation Error Est Err 24,037 24K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 9 9 Rank	Estimation Error Est Err 4 4 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 27,878 28K Rank	Estimation Error Est Err 7,858 7.9K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 24,026 24K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,168,861 1.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,168,861 1.2M Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 1,168,877 1.2M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min 979 0 INNER JOIN HASH ON id27 = movie_id20 889 0 │└INNER JOIN HASH ON id = info_type_id 113 0 │ │└TABLE SCAN info_type 889 0 │ INNER JOIN HASH ON movie_id = movie_id20 4459 0 │ │└INNER JOIN HASH ON id6 = company_type_id 1 1 │ │ │└TABLE SCAN company_type WHERE kind = production companies 17835 24025 │ │ TABLE SCAN movie_companies WHERE note LIKE '%(France)%' AND note LIKE '%(theatrical)%' 101416 0 │ TABLE SCAN movie_info WHERE info BETWEEN Denish AND Swedish AND info22 IN(Denish,Denmark,German,Germany,Norway,Norwegian,Sweden,Swedish) 1049348 0 TABLE SCAN title WHERE production_year >= 2006
	Estimate Actual Operator - 1 PROJECT a1 AS typical_european_movie - 1 AGGREGATE MIN(title) AS a1 - 0 PROJECT title - 0 PROJECT title - 0 INNER JOIN HASH ON tuple(PROJECTION_5063.movie_id,PROJECTION_5063.id) = tuple(PROJECTION_5054.movie_id,PROJECTION_5054.movie_id) - 14835720 │└PROJECT movie_id AS movie_id_right - 14835720 │ PROJECT movie_id - 14835720 │ INNER JOIN HASH ON PROJECTION_5060.info_type_id = PROJECTION_5057.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_5069.company_type_id = PROJECTION_5066.id - 1 │└PROJECT id AS id_right - 1 │ PROJECT id - 1 │ TABLE SCAN company_type WHERE kind = 'production companies' - 3929 PROJECT company_type_id, movie_id, title, id AS id_left - 3929 PROJECT company_type_id, movie_id, title, id - 3929 INNER JOIN HASH ON PROJECTION_5075.movie_id = PROJECTION_5072.id - 1012920 │└PROJECT id, title - 1012920 │ PROJECT id, title - 1012920 │ TABLE SCAN title WHERE production_year > 2005 - 24025 PROJECT movie_id, company_type_id - 24025 PROJECT company_type_id, movie_id - 24025 TABLE SCAN movie_companies WHERE note LIKE '%(theatrical)%' AND note LIKE '%(France)%'
	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 3929 │ INNER JOIN HASH ON id = movie_id 521825 24025 │ │└TABLE SCAN movie_companies WHERE contains(note,'(theatrical)') AND contains(note,'(France)') 505662 5890 │ FILTER id BETWEEN 2 AND 2525745 505662 5890 │ TABLE SCAN title WHERE production_year > 2005 2967144 1178 FILTER movie_id BETWEEN 2 AND 2525745 2967144 1178 FILTER IN ... 14835720 92160 INNER JOIN HASH ON info = #0 0 8 │└SCAN MATERIALISED 14835720 92160 TABLE SCAN movie_info WHERE info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German')
	Estimate Actual Operator 1 1 PROJECT typical_european_movie 1 1 AGGREGATE MIN(title) 252832 10 DISTRIBUTE GATHER 252832 10 AGGREGATE MIN(title) 252832 0 INNER JOIN HASH ON id = movie_id AND id = movie_id 252832 1012920 │└DISTRIBUTE HASH ON id, id 252832 1012920 │ FILTER production_year > 2005 2528312 2528312 │ TABLE SCAN title WHERE (production_year > 2005) 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 960 │ FILTER note LIKE '%(theatrical)%' AND note LIKE '%(France)%' 2609129 1376261 │ TABLE SCAN movie_companies WHERE ((note LIKE '%(theatrical)%' AND note LIKE '%(France)%') 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 >= 4) AND (movie_id <= 2528297)) AND ((movie_id >= 4) AND (movie_id <= 2528297))) AND TRUE) WHEN 3 THEN ((((movie_id >= 3) AND (movie_id <= 2528185)) AND ((movie_id >= 3) AND (movie_id <= 2528185))) AND TRUE) WHEN 5 THEN ... 2967144 153897 FILTER info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German') 14835720 9042947 TABLE SCAN movie_info WHERE (info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German') 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...
	Estimate Actual Operator 1 1 AGGREGATE MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(t.title) 121 0 INNER JOIN HASH ON mc.company_type_id = ct.id 121 1 │└DISTRIBUTE GATHER 4 1 │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 115 0 INNER JOIN HASH ON mi.movie_id = mc.movie_id 115 48168 │└DISTRIBUTE GATHER 115 48168 │ INNER JOIN HASH ON mi.movie_id = t.id 115 153897 │ │└DISTRIBUTE GATHER 655000 153897 │ │ INNER JOIN HASH ON mi.info_type_id = it.id 655000 153897 │ │ │└DISTRIBUTE GATHER 2530000 153897 │ │ │ TABLE SCAN movie_info WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German') 113 7 │ │ TABLE SCAN info_type 14800000 1009693 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2005L) 2610000 0 TABLE SCAN movie_companies WHERE (mc.note IS NOT NULL) AND contains(mc.note,'(theatrical)') AND contains(mc.note,'(France)')
	Estimate Actual Operator 1 1 AGGREGATE MIN(title as title) AS Expr1010 2296 0 INNER JOIN LOOP ON t.id = mi.movie_id 1 0 │└TABLE SEEK movie_info AS mi WHERE info as info = 'Denish' OR info as info = 'Denmark' OR info as info = 'German' OR info as info = 'Germany' OR info as info = 'Norway' OR info as info = 'Norwegian' OR info as info = 'Sweden' OR info as info = 'Swedish' 4328 0 INNER JOIN HASH ON mc.company_type_id = ct.id 1 1 │└TABLE SCAN company_type AS ct WHERE kind as kind = 'production companies' 8657 3929 INNER JOIN HASH ON t.id = mc.movie_id 21742 24025 │└TABLE SCAN movie_companies AS mc WHERE note as note LIKE '%(theatrical)%' AND note as note LIKE '%(France)%' 10067 3852 TABLE SCAN title AS t WHERE (production_year as production_year > 2005) AND (BLOOM(id as id))
	Estimate Actual Operator 1 ∞ PROJECT min AS typical_european_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 247389 1012920 │└DISTRIBUTE HASH ON id_15 247389 1012920 │ PROJECT id AS id_15, title 247389 1012920 │ FILTER production_year > 2005 247389 1012920 │ 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 153897 │└DISTRIBUTE HASH ON movie_id_9 13352148 153897 │ PROJECT movie_id AS movie_id_9, info_type_id 13352148 153897 │ FILTER info IN('Denish','Denmark','German','Germany','Norway','Norwegian','Sweden','Swedish') 13352148 153897 │ TABLE SCAN movie_info - 0 INNER JOIN HASH ON id = company_type_id 2348216 1931 │└DISTRIBUTE HASH ON company_type_id 2348216 1931 │ FILTER (note LIKE '%(theatrical)%') AND (note LIKE '%(France)%') 2348216 1931 │ 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 AGGREGATE PARTIAL MIN(title) 40 0 INNER JOIN HASH ON info_type_id = id 113 0 │└TABLE SCAN info_type AS it 160 4 INNER JOIN LOOP ON movie_id = id 600 4 │└INNER JOIN LOOP ON id = movie_id 378 0 │ │└INNER JOIN HASH ON company_type_id = id 1 1 │ │ │└TABLE SCAN company_type AS ct WHERE ct.kind = 'production companies' 6056 24025 │ │ TABLE SCAN movie_companies AS mc WHERE (mc.note LIKE '%(theatrical)%') AND (mc.note LIKE '%(France)%') 0 0 │ TABLE SEEK title AS t WHERE t.production_year > 2005 0 0 TABLE SEEK movie_info AS mi WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German')