Planner — IMDB — JOB-13A

SELECT MIN(mi.info) AS release_date,
       MIN(miidx.info) AS rating,
       MIN(t.title) AS german_movie
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 ='[de]'
  AND ct.kind ='production companies'
  AND it.info ='rating'
  AND it2.info ='release dates'
  AND kt.kind ='movie'
  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 21,362,975 21M Rank		Estimation Error Est Err 25,076,555 25M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,215,564 4.2M Rank	Estimation Error Est Err 111,101 111K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 21,353,200 21M Rank		Estimation Error Est Err 19,827,664 20M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 11,348,840 11M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,604,783 6.6M Rank		Estimation Error Est Err 3,724,451 3.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 554,039 554K Rank	Estimation Error Est Err 1,763,620 1.8M Rank	Estimation Error Est Err 3,435,431 3.4M Rank
Estimation Error Est Err 7,501,618 7.5M Rank		Estimation Error Est Err 8,057,627 8.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,292,116 1.3M Rank	Estimation Error Est Err 111,111 111K Rank	Estimation Error Est Err 3,068,614 3.1M Rank
Estimation Error Est Err 1,657,325 1.7M Rank		Estimation Error Est Err 1,893,057 1.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 515,574 516K Rank	Estimation Error Est Err 111,101 111K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,431,270 3.4M Rank	Estimation Error Est Err 2,051,233 2.1M Rank	Estimation Error Est Err 5,181,324 5.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,211,378 2.2M Rank	Estimation Error Est Err 37,036 37K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,420,316 1.4M Rank	Estimation Error Est Err 1,200,548 1.2M Rank	Estimation Error Est Err 2,214,971 2.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,078,646 3.1M Rank	Estimation Error Est Err 111,111 111K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 5,619,849 5.6M Rank		Estimation Error Est Err 1,058,273 1.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,103,834 6.1M Rank	Estimation Error Est Err 111,117 111K Rank	Estimation Error Est Err 5,611,649 5.6M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 9869 111101 INNER JOIN HASH ON id = kind_id 1 1 │└TABLE SCAN kind_type WHERE kind = movie 39189 134189 INNER JOIN HASH ON movie_id48 = id55 10066 134189 │└INNER JOIN HASH ON id6 = info_type_id49 1 1 │ │└TABLE SCAN info_type WHERE info = rating 46621 404319 │ INNER JOIN HASH ON movie_id48 = movie_id 9406 175621 │ │└INNER JOIN HASH ON movie_id40 = movie_id 36461 71294 │ │ │└INNER JOIN HASH ON id11 = company_type_id 1 1 │ │ │ │└TABLE SCAN company_type WHERE kind = production companies 77574 148132 │ │ │ INNER JOIN HASH ON id16 = company_id 7343 9775 │ │ │ │└TABLE SCAN company_name WHERE country_code = de 2609129 2609129 │ │ │ TABLE SCAN movie_companies 131290 3036719 │ │ INNER JOIN HASH ON id34 = info_type_id 1 1 │ │ │└TABLE SCAN info_type WHERE info = release dates 14835720 14835720 │ │ TABLE SCAN movie_info 1380035 1380035 │ TABLE SCAN movie_info_idx 2528312 2528312 TABLE SCAN title
	Estimate Actual Operator - 1 PROJECT a1 AS release_date, a2 AS rating, a3 AS german_movie - 1 AGGREGATE MIN(info_right) AS a1, MIN(info_left) AS a2, MIN(title) AS a3 - 0 PROJECT info, info, title - 0 PROJECT info, info, title - 0 INNER JOIN HASH ON tuple(PROJECTION_484.movie_id,PROJECTION_484.movie_id,PROJECTION_484.movie_id,PROJECTION_484.movie_id) = tuple(PROJECTION_463.movie_id,PROJECTION_463.movie_id,PROJECTION_463.id,PROJECTION_463.id) - 1083597 │└PROJECT movie_id AS movie_id_right, id, info AS info_right, title - 1083597 │ PROJECT info, movie_id, title, id - 1083597 │ INNER JOIN HASH ON PROJECTION_475.id = PROJECTION_466.movie_id - 3036719 │ │└PROJECT movie_id, info - 3036719 │ │ PROJECT info, movie_id - 3036719 │ │ INNER JOIN HASH ON PROJECTION_472.info_type_id = PROJECTION_469.id - 1 │ │ │└PROJECT id - 1 │ │ │ PROJECT id - 1 │ │ │ TABLE SCAN info_type WHERE info = 'release dates' - 14835720 │ │ PROJECT info_type_id, info, movie_id - 14835720 │ │ PROJECT info, movie_id, info_type_id - 14835720 │ │ TABLE SCAN movie_info - 662825 │ PROJECT id, title - 662825 │ PROJECT title, id - 662825 │ INNER JOIN HASH ON PROJECTION_481.kind_id = PROJECTION_478.id - 1 │ │└PROJECT id AS id_right - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN kind_type WHERE kind = 'movie' - 2528312 │ PROJECT kind_id, title, id AS id_left - 2528312 │ PROJECT title, id, kind_id - 2528312 │ TABLE SCAN title - 0 PROJECT movie_id AS movie_id_left, movie_id AS movie_id_left_2, info AS info_left - 0 PROJECT movie_id, info, movie_id - 0 INNER JOIN HASH ON PROJECTION_496.movie_id = PROJECTION_487.movie_id - 459925 │└PROJECT movie_id AS movie_id_right, info - 459925 │ PROJECT info, movie_id - 459925 │ INNER JOIN HASH ON PROJECTION_493.info_type_id = PROJECTION_490.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 - 0 PROJECT movie_id - 0 INNER JOIN HASH ON PROJECTION_502.company_type_id = PROJECTION_499.id - 1 │└PROJECT id - 1 │ PROJECT id - 1 │ TABLE SCAN company_type WHERE kind = 'production companies' - 0 PROJECT company_type_id, movie_id - 0 PROJECT company_type_id, movie_id - 0 INNER JOIN HASH ON PROJECTION_508.id = PROJECTION_505.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 - 0 PROJECT id - 0 TABLE SCAN company_name WHERE country_code = 'de'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 0 111101 PROJECT info, info, title 0 111101 INNER JOIN HASH ON info_type_id = id 2 1 │└FILTER id <= 110 2 1 │ TABLE SCAN info_type WHERE info = 'release dates' 38 111101 INNER JOIN HASH ON movie_id = movie_id 6 16470 │└INNER JOIN HASH ON kind_id = id 1 1 │ │└TABLE SCAN kind_type WHERE kind = 'movie' 45 16470 │ INNER JOIN HASH ON id = movie_id 44 31510 │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │└FILTER id >= 99 2 1 │ │ │ TABLE SCAN info_type WHERE info = 'rating' 2535 31510 │ │ INNER JOIN HASH ON movie_id = movie_id 4563 71294 │ │ │└INNER JOIN HASH ON company_type_id = id 1 1 │ │ │ │└FILTER id <= 2 1 1 │ │ │ │ TABLE SCAN company_type WHERE kind = 'production companies' 18253 71294 │ │ │ INNER JOIN HASH ON company_id = id 1644 9775 │ │ │ │└TABLE SCAN company_name WHERE country_code = 'de' 2609129 1314838 │ │ │ TABLE SCAN movie_companies 1380035 21540 │ │ TABLE SCAN movie_info_idx WHERE movie_id <= 2525745 2528312 11304 │ TABLE SCAN title WHERE id >= 2 AND id <= 2525745 14835720 299864 TABLE SCAN movie_info WHERE movie_id >= 2 AND movie_id <= 2525745
	Estimate Actual Operator 1 1 PROJECT release_date, rating, german_movie 1 1 AGGREGATE MIN(info), MIN(info), MIN(title) 91531 10 DISTRIBUTE GATHER 91531 10 AGGREGATE MIN(info), MIN(info), MIN(title) 91531 111101 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 125521 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id 320359 134189 │└DISTRIBUTE HASH ON movie_id, movie_id, movie_id 320359 134189 │ 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 404319 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 320359 175621 │ │└DISTRIBUTE HASH ON movie_id, movie_id 320359 175621 │ │ 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 187177 │ │ INNER JOIN HASH ON movie_id = movie_id 260916 71294 │ │ │└DISTRIBUTE GATHER 260916 71294 │ │ │ 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 74226 │ │ │ INNER JOIN HASH ON id = company_id 47000 9775 │ │ │ │└DISTRIBUTE GATHER 47000 9775 │ │ │ │ FILTER country_code = 'de' 234997 234997 │ │ │ │ TABLE SCAN company_name WHERE country_code = 'de' 2609129 1376261 │ │ │ TABLE SCAN movie_companies WHERE (((company_id >= 30) AND (company_id <= 234990)) AND TRUE) AND (((company_type_id >= 2) AND (company_type_id <= 2)) AND company_type_id IN 2) 14835720 3212639 │ │ TABLE SCAN movie_info WHERE (((movie_id >= 820) AND (movie_id <= 2525727)) AND TRUE) AND (((info_type_id >= 16) AND (info_type_id <= 16)) AND info_type_id IN 16) 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 >= 1538) AND (movie_id <= 2525502)) AND ((movie_id >= 1538) AND (movie_id <= 2525502))) AND TRUE) WHEN 3 THEN ((((movie_id >= 821) AND (movie_id <= 2525500)) AND ((movie_id >= 821) AND (movie_id <= 2525500))) AND TRUE) WHEN 5 THEN ((((movie_id >= 820) AND (movie_id <= 2525720)) AND ((movie_id >= 820) AND (movie_id <= 2525720))) AND TRUE) WHEN 7 THEN ((((movie_id >=... 2528312 1297449 DISTRIBUTE HASH ON id, id, id 2528312 1297449 TABLE SCAN title WHERE CASE MOD(HASH_REPARTITION(id,id,id),10) WHEN 0 THEN (((((id >= 1537) AND (id <= 2525464)) AND ((id >= 1537) AND (id <= 2525464))) AND ((id >= 1537) AND (id <= 2525464))) AND TRUE) WHEN 1 THEN (((((id >= 34267) AND (id <= 2525435)) AND ((id >= 34267) AND (id <= 2525435))) AND ((id >= 34267) AND (id <= 2525435))) AND TRUE) WHEN 2 THEN (((((id >= 5187) AND (id <= 2525496)) AND ((id >= 5187) AND (id <= 2525496))) AND ((id >= 5187) AND (id <= 2525496))) A...
	Estimate Actual Operator 1050 0 SEQUENCE 1 1 ├─AGGREGATE MIN(mi.info), MIN(miidx.info), MIN(t.title) 1 4 │ DISTRIBUTE GATHER 1 4 │ AGGREGATE MIN(mi.info), MIN(miidx.info), MIN(t.title) 203000 111101 │ INNER JOIN HASH ON mi.info_type_id = it2.id 203000 1 │ │└DISTRIBUTE GATHER 2610000 1 │ │ TABLE SCAN info_type WHERE it2.info = 'release dates' 39600 130365 │ INNER JOIN HASH ON mc.movie_id = mi.movie_id 39600 16470 │ │└DISTRIBUTE GATHER 12700 16470 │ │ INNER JOIN HASH ON miidx.info_type_id = it.id 12700 1 │ │ │└DISTRIBUTE GATHER 7 1 │ │ │ TABLE SCAN info_type WHERE it.info = 'rating' 12700 49368 │ │ INNER JOIN HASH ON mc.movie_id = miidx.movie_id 12700 32853 │ │ │└DISTRIBUTE GATHER 12100 32853 │ │ │ INNER JOIN HASH ON t.kind_id = kt.id 12100 1 │ │ │ │└DISTRIBUTE GATHER 113 1 │ │ │ │ TABLE SCAN kind_type WHERE kt.kind = 'movie' 12100 53085 │ │ │ INNER JOIN HASH ON mc.movie_id = t.id 12100 71294 │ │ │ │└DISTRIBUTE GATHER 276000 71294 │ │ │ │ INNER JOIN HASH ON mc.company_type_id = ct.id 276000 1 │ │ │ │ │└DISTRIBUTE GATHER 14800000 1 │ │ │ │ │ TABLE SCAN company_type WHERE ct.kind = 'production companies' 203000 71294 │ │ │ │ INNER JOIN HASH ON cn.id = mc.company_id 203000 9775 │ │ │ │ │└DISTRIBUTE GATHER - 9775 │ │ │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'de') 113 1331236 │ │ │ │ TABLE SCAN movie_companies 1380000 1119343 │ │ │ TABLE SCAN title 2530000 839395 │ │ TABLE SCAN movie_info_idx 1 111543 │ FILTER 4 1652515 │ TABLE SCAN movie_info 1050 0 └─FILTER 235000 1 DISTRIBUTE HASH 235000 1 AGGREGATE bloom_filter_agg(bloom_expr(mc.movie_id,mc.movie_id),71294L,1048576L) 235000 1652515 DISTRIBUTE HASH 235000 1652515 DISTRIBUTE HASH 235000 1652515 TABLE SCAN movie_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1087) AS Expr1018, MIN(partialagg1088) AS Expr1019, MIN(partialagg1089) AS Expr1020 5 10 AGGREGATE MIN(info as info) AS partialagg1087, MIN(info as info) AS partialagg1088, MIN(title as title) AS partialagg1089 13212 111101 INNER JOIN HASH ON mc.company_type_id = ct.id 1 1 │└FILTER kind as kind = 'production companies' 4 4 │ INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 4 │ │└TABLE SEEK company_type AS ct 4 4 │ TABLE SEEK company_type AS ct 26425 111101 INNER JOIN HASH ON miidx.info_type_id = it.id 1 1 │└FILTER info as info = 'rating' 113 113 │ INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 113 │ │└TABLE SEEK info_type AS it 113 113 │ TABLE SEEK info_type AS it 13212 111101 INNER JOIN HASH ON t.kind_id = kt.id 1 1 │└FILTER kind as kind = 'movie' 7 7 │ INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 7 │ │└TABLE SEEK kind_type AS kt 7 7 │ TABLE SEEK kind_type AS kt 7927 111101 INNER JOIN HASH ON mc.company_id = cn.id 9828 9775 │└TABLE SCAN company_name AS cn WHERE country_code as country_code = 'de' 4691 111101 INNER JOIN HASH ON mc.movie_id = t.id 4732 724731 │└INNER JOIN HASH ON mi.info_type_id = it2.id 1 1 │ │└FILTER info as info = 'release dates' 113 113 │ │ TABLE SCAN info_type AS it2 3313 724731 │ INNER JOIN LOOP ON t.id = mi.movie_id 56 724731 │ │└TABLE SEEK movie_info AS mi WHERE BLOOM(info_type_id as info_type_id) 13800 209880 │ INNER JOIN HASH ON t.id = miidx.movie_id 138004 459925 │ │└TABLE SEEK movie_info_idx AS miidx WHERE BLOOM(info_type_id as info_type_id) 2528 209880 │ TABLE SCAN title AS t WHERE BLOOM(id as id) AND BLOOM(kind_id as kind_id) 2609 15644 TABLE SEEK movie_companies AS mc WHERE BLOOM(company_type_id as company_type_id) AND BLOOM(company_id as company_id) AND BLOOM(movie_id as movie_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS release_date, min_43 AS rating, min_44 AS german_movie 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(info_24) AS min_45, MIN(info_32) AS min_46, MIN(title) AS min_47 - 111101 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 - 111910 INNER JOIN HASH ON movie_id = id_37 2528312 679504 │└DISTRIBUTE HASH ON id_37 2528312 679504 │ PROJECT id AS id_37, title, kind_id 2528312 679504 │ TABLE SCAN title - 134189 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 - 134189 INNER JOIN HASH ON movie_id = movie_id_30 1380035 459925 │└DISTRIBUTE HASH ON movie_id_30 1380035 459925 │ PROJECT movie_id AS movie_id_30, info_type_id AS info_type_id_31, info AS info_32 1380035 459925 │ TABLE SCAN movie_info_idx - 175621 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 - 179888 INNER JOIN HASH ON movie_id = movie_id_23 14835720 3137317 │└DISTRIBUTE HASH ON movie_id_23 14835720 3137317 │ PROJECT movie_id AS movie_id_23, info_type_id, info AS info_24 14835720 3137317 │ TABLE SCAN movie_info - 71294 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 - 71294 INNER JOIN HASH ON id = company_id 2609129 1334883 │└DISTRIBUTE HASH ON company_id 2609129 1334883 │ TABLE SCAN movie_companies 211497 8216 FILTER country_code = 'de' 211497 8216 TABLE SCAN company_name
	Estimate Actual Operator 1 1 AGGREGATE MIN(info), MIN(info), MIN(title) 3 3 AGGREGATE PARTIAL MIN(info), PARTIAL MIN(info), PARTIAL MIN(title) 10 37033 INNER JOIN HASH ON info_type_id = id 3 3 │└TABLE SEEK info_type AS it2 3582 516327 INNER JOIN LOOP ON movie_id = id 114 16470 │└INNER JOIN LOOP ON id = company_id 925 100870 │ │└INNER JOIN HASH ON company_type_id = id 1 1 │ │ │└TABLE SCAN company_type AS ct WHERE ct.kind = 'production companies' 11100 773084 │ │ INNER JOIN LOOP ON movie_id = id 727 69960 │ │ │└INNER JOIN HASH ON kind_id = id 1 1 │ │ │ │└TABLE SCAN kind_type AS kt WHERE kt.kind = 'movie' 15267 459924 │ │ │ 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 1049400 772358 │ │ TABLE SEEK movie_companies AS mc 302610 302610 │ TABLE SEEK company_name AS cn WHERE cn.country_code = 'de' 658800 516334 TABLE SEEK movie_info AS mi