Planner — IMDB — JOB-5C

SELECT MIN(t.title) AS american_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 NOT LIKE '%(TV)%'
  AND mc.note LIKE '%(USA)%'
  AND mi.info IN ('Sweden',
                  'Norway',
                  'Germany',
                  'Denmark',
                  'Swedish',
                  'Denish',
                  'Norwegian',
                  'German',
                  'USA',
                  'American')
  AND t.production_year > 1990
  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,537,330 1.5M Rank		Estimation Error Est Err 1,537,885 1.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,032 3K Rank	Estimation Error Est Err 669 669 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 16,880,494 17M Rank		Estimation Error Est Err 39,059,753 39M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 23,930,001 24M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,412,026 2.4M Rank		Estimation Error Est Err 3,118,691 3.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 709,037 709K Rank	Estimation Error Est Err 670 670 Rank	Estimation Error Est Err 708,241 708K Rank
Estimation Error Est Err 12,947,637 13M Rank		Estimation Error Est Err 2,468,923 2.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,978 3K Rank	Estimation Error Est Err 679 679 Rank	Estimation Error Est Err 1,750,536 1.8M Rank
Estimation Error Est Err 422,239 422K Rank		Estimation Error Est Err 252,319 252K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 336,246 336K Rank	Estimation Error Est Err 669 669 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 297,307 297K Rank	Estimation Error Est Err 1,565 1.6K Rank	Estimation Error Est Err 297,862 298K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,900 1.9K Rank	Estimation Error Est Err 171 171 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,111 2.1K Rank	Estimation Error Est Err 677 677 Rank	Estimation Error Est Err 2,158 2.2K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,287 2.3K Rank	Estimation Error Est Err 669 669 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,751,448 2.8M Rank		Estimation Error Est Err 2,978 3K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,752,025 2.8M Rank	Estimation Error Est Err 685 685 Rank	Estimation Error Est Err 2,751,463 2.8M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min 66304 669 INNER JOIN HASH ON id = info_type_id 113 113 │└TABLE SCAN info_type 15420 669 INNER JOIN HASH ON id19 = movie_id35 61410 767 │└INNER JOIN HASH ON id19 = movie_id 76439 798 │ │└INNER JOIN HASH ON id6 = company_type_id 1 1 │ │ │└TABLE SCAN company_type WHERE kind = production companies 305757 295628 │ │ TABLE SCAN movie_companies WHERE note LIKE '%(USA)%' AND NOT (note LIKE '%(TV)%') 1757966 893164 │ TABLE SCAN title WHERE production_year >= 1991 608496 348424 TABLE SCAN movie_info WHERE info BETWEEN American AND USA AND info37 IN(American,Denish,Denmark,German,Germany,Norway,Norwegian,Sweden,Swedish,USA)
	Estimate Actual Operator - 1 PROJECT a1 AS american_movie - 1 AGGREGATE MIN(title) AS a1 - 0 PROJECT title - 0 PROJECT title - 0 INNER JOIN HASH ON tuple(PROJECTION_5121.movie_id,PROJECTION_5121.id) = tuple(PROJECTION_5112.movie_id,PROJECTION_5112.movie_id) - 798 │└PROJECT movie_id AS movie_id_right - 798 │ PROJECT movie_id - 798 │ INNER JOIN HASH ON PROJECTION_5118.company_type_id = PROJECTION_5115.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN company_type WHERE kind = 'production companies' - 295628 │ PROJECT company_type_id, movie_id - 295628 │ PROJECT company_type_id, movie_id - 295628 │ TABLE SCAN movie_companies WHERE notLike(note,'%(TV)%') AND note LIKE '%(USA)%' - 9092685 PROJECT movie_id AS movie_id_left, id, title - 9092685 PROJECT movie_id, title, id - 9092685 INNER JOIN HASH ON PROJECTION_5127.movie_id = PROJECTION_5124.id - 1749032 │└PROJECT id, title - 1749032 │ PROJECT id, title - 1749032 │ TABLE SCAN title WHERE production_year > 1990 - 14835720 PROJECT movie_id - 14835720 PROJECT movie_id - 14835720 INNER JOIN HASH ON PROJECTION_5133.info_type_id = PROJECTION_5130.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
	Estimate Actual Operator - 1 AGGREGATE MIN(#0) 31726 669 PROJECT title 31726 669 INNER JOIN HASH ON info_type_id = id 113 110 │└TABLE SCAN info_type WHERE id <= 110 31726 669 INNER JOIN HASH ON movie_id = movie_id 26558 767 │└INNER JOIN HASH ON company_type_id = id 1 1 │ │└FILTER id <= 2 1 1 │ │ TABLE SCAN company_type WHERE kind = 'production companies' 106234 105501 │ INNER JOIN HASH ON id = movie_id 521825 295628 │ │└TABLE SCAN movie_companies WHERE ( NOT contains(note,'(TV)')) AND contains(note,'(USA)') 505662 88072 │ FILTER id BETWEEN 2 AND 2525745 505662 88075 │ TABLE SCAN title WHERE production_year > 1990 2967144 2233 FILTER movie_id BETWEEN 2 AND 2525745 2967144 2233 FILTER IN ... 14835720 38415 INNER JOIN HASH ON info = #0 0 10 │└SCAN MATERIALISED 14835720 38415 TABLE SCAN movie_info WHERE info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American')
	Estimate Actual Operator 1 1 PROJECT american_movie 1 1 AGGREGATE MIN(title) 306427 10 DISTRIBUTE GATHER 306427 10 AGGREGATE MIN(title) 306427 669 INNER JOIN HASH ON movie_id = id AND movie_id = id 306427 691 │└DISTRIBUTE HASH ON movie_id, movie_id 306427 691 │ INNER JOIN HASH ON id = info_type_id 113 113 │ │└TABLE SCAN info_type 306427 691 │ INNER JOIN HASH ON movie_id = movie_id 260913 798 │ │└DISTRIBUTE GATHER 260913 798 │ │ 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 12526 │ │ FILTER note NOT LIKE '%(TV)%' AND note LIKE '%(USA)%' 2609129 1376261 │ │ TABLE SCAN movie_companies WHERE (note NOT LIKE '%(TV)%' AND note LIKE '%(USA)%') AND (((company_type_id >= 2) AND (company_type_id <= 2)) AND company_type_id IN 2) 2967144 706674 │ FILTER info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American') 14835720 9042947 │ TABLE SCAN movie_info WHERE (info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American') AND (((movie_id >= 67175) AND (movie_id <= 2518573)) 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... 523722 1749032 DISTRIBUTE HASH ON id, id 523722 1749032 FILTER production_year > 1990 2528312 2528312 TABLE SCAN title WHERE ((production_year > 1990) AND CASE MOD(HASH_REPARTITION(id,id),10) WHEN 1 THEN ((((id >= 293492) AND (id <= 2506376)) AND ((id >= 293492) AND (id <= 2506376))) AND struct(id,id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr >...
	Estimate Actual Operator 1 1 AGGREGATE MIN(t.title) 1 1 DISTRIBUTE GATHER 1 1 AGGREGATE MIN(t.title) 154 669 INNER JOIN HASH ON mc.movie_id = mi.movie_id 154 767 │└DISTRIBUTE GATHER 154 767 │ INNER JOIN HASH ON mc.movie_id = t.id 154 798 │ │└DISTRIBUTE GATHER 281000 798 │ │ INNER JOIN HASH ON ct.id = mc.company_type_id 281000 1 │ │ │└DISTRIBUTE GATHER 113 1 │ │ │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 14800000 798 │ │ TABLE SCAN movie_companies WHERE (mc.note IS NOT NULL) AND ( NOT contains(mc.note,'(TV)')) AND contains(mc.note,'(USA)') 2610000 1704545 │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 1990L) 295000 706674 INNER JOIN HASH ON mi.info_type_id = it.id 295000 706674 │└DISTRIBUTE GATHER 2530000 706674 │ TABLE SCAN movie_info WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American') 4 8 TABLE SCAN info_type
	Estimate Actual Operator 1 1 AGGREGATE MIN(title as title) AS Expr1010 255493 669 INNER JOIN HASH ON t.id = mi.movie_id 370125 691 │└INNER JOIN HASH ON mi.movie_id = mc.movie_id 146527 798 │ │└INNER JOIN HASH ON mc.company_type_id = ct.id 1 1 │ │ │└TABLE SCAN company_type AS ct WHERE kind as kind = 'production companies' 293054 798 │ │ TABLE SCAN movie_companies AS mc WHERE NOT note as note LIKE '%(TV)%' AND note as note LIKE '%(USA)%' AND BLOOM(company_type_id as company_type_id) 665275 677 │ TABLE SEEK movie_info AS mi WHERE (info as info = 'American' OR 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' OR info as info = 'USA') AND BLOOM(movie_id as movie_id) 174569 635 TABLE SCAN title AS t WHERE (production_year as production_year > 1990) AND (BLOOM(id as id))
	Estimate Actual Operator 1 ∞ PROJECT min AS american_movie 1 1 AGGREGATE MIN(min_19) AS min - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(title) AS min_19 - 669 INNER JOIN HASH ON movie_id = id_15 512448 1749032 │└DISTRIBUTE HASH ON id_15 512448 1749032 │ PROJECT id AS id_15, title 512448 1749032 │ FILTER production_year > 1990 512448 1749032 │ TABLE SCAN title - 691 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 - 691 INNER JOIN HASH ON movie_id = movie_id_9 13352148 706674 │└DISTRIBUTE HASH ON movie_id_9 13352148 706674 │ PROJECT movie_id AS movie_id_9, info_type_id 13352148 706674 │ FILTER info IN('American','Denish','Denmark','German','Germany','Norway','Norwegian','Sweden','Swedish','USA') 13352148 706674 │ TABLE SCAN movie_info - 798 INNER JOIN HASH ON id = company_type_id 2348216 295628 │└DISTRIBUTE HASH ON company_type_id 2348216 295628 │ FILTER NOT (note LIKE '%(TV)%') AND (note LIKE '%(USA)%') 2348216 295628 │ TABLE SCAN movie_companies 4 1 FILTER kind = 'production companies' 4 1 TABLE SCAN company_type
	Estimate Actual Operator 1 1 AGGREGATE MIN(title) 4 4 AGGREGATE PARTIAL MIN(title) 13112 167 INNER JOIN HASH ON info_type_id = id 113 113 │└TABLE SCAN info_type AS it 52448 669 INNER JOIN LOOP ON movie_id = id 38652 767 │└INNER JOIN LOOP ON id = movie_id 14024 199 │ │└INNER JOIN HASH ON company_type_id = id 1 1 │ │ │└TABLE SCAN company_type AS ct WHERE ct.kind = 'production companies' 224380 295628 │ │ TABLE SCAN movie_companies AS mc WHERE (mc.note NOT LIKE '%(TV)%') AND (mc.note LIKE '%(USA)%') 798 798 │ TABLE SEEK title AS t WHERE t.production_year > 1990 1534 767 TABLE SEEK movie_info AS mi WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Denish','Norwegian','German','USA','American')