Planner — IMDB — JOB-18A

SELECT MIN(mi.info) AS movie_budget,
       MIN(mi_idx.info) AS movie_votes,
       MIN(t.title) AS movie_title
FROM job.cast_info AS ci,
     job.info_type AS it1,
     job.info_type AS it2,
     job.movie_info AS mi,
     job.movie_info_idx AS mi_idx,
     job.name AS n,
     job.title AS t
WHERE ci.note IN ('(producer)',
                  '(executive producer)')
  AND it1.info = 'budget'
  AND it2.info = 'votes'
  AND n.gender = 'm'
  AND n.name LIKE '%Tim%'
  AND t.id = mi.movie_id
  AND t.id = mi_idx.movie_id
  AND t.id = ci.movie_id
  AND ci.movie_id = mi.movie_id
  AND ci.movie_id = mi_idx.movie_id
  AND mi.movie_id = mi_idx.movie_id
  AND n.id = ci.person_id
  AND it1.id = mi.info_type_id
  AND it2.id = mi_idx.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 18,759,850 19M Rank		Estimation Error Est Err 18,847,917 19M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 107,712 108K Rank	Estimation Error Est Err 410 410 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 54,997,475 55M Rank		Estimation Error Est Err 927,835,635 928M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 466,919,377 467M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 7,631,443 7.6M Rank		Estimation Error Est Err 5,279,773 5.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,417,438 2.4M Rank	Estimation Error Est Err 414 414 Rank	Estimation Error Est Err 2,396,150 2.4M Rank
Estimation Error Est Err 13,535,502 14M Rank		Estimation Error Est Err 7,520,655 7.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,089,102 4.1M Rank	Estimation Error Est Err 420 420 Rank	Estimation Error Est Err 7,361,245 7.4M Rank
Estimation Error Est Err 2,450,147 2.5M Rank		Estimation Error Est Err 713,054 713K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,225,390 1.2M Rank	Estimation Error Est Err 410 410 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 8,526,076 8.5M Rank	Estimation Error Est Err 7,146,041 7.1M Rank	Estimation Error Est Err 15,456,526 15M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 7,313,874 7.3M Rank	Estimation Error Est Err 410 410 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 28,673 29K Rank	Estimation Error Est Err 4,264 4.3K Rank	Estimation Error Est Err 26,790 27K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 22,264 22K Rank	Estimation Error Est Err 416 416 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,119,860 3.1M Rank		Estimation Error Est Err 3,327 3.3K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,120,271 3.1M Rank	Estimation Error Est Err 426 426 Rank	Estimation Error Est Err 3,119,180 3.1M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 172 410 INNER JOIN HASH ON id49 = movie_id34 165 410 │└INNER JOIN HASH ON id = info_type_id43 1 1 │ │└TABLE SCAN info_type WHERE info = votes 395 1235 │ INNER JOIN HASH ON movie_id34 = movie_id42 281 699 │ │└INNER JOIN HASH ON id6 = info_type_id 1 1 │ │ │└TABLE SCAN info_type WHERE info = budget 31788 95896 │ │ INNER JOIN HASH ON movie_id = movie_id34 2600 6719 │ │ │└INNER JOIN HASH ON id11 = person_id 4069 9062 │ │ │ │└TABLE SCAN name WHERE gender = m AND name LIKE '%Tim%' 2406850 6719 │ │ │ TABLE SCAN cast_info WHERE note IN((executive producer),producer) 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 movie_budget, a2 AS movie_votes, a3 AS movie_title - 1 AGGREGATE MIN(info_left) AS a1, MIN(info_right) AS a2, MIN(title) AS a3 - 0 PROJECT info, info, title - 0 PROJECT info, info, title - 0 INNER JOIN HASH ON tuple(PROJECTION_1471.movie_id,PROJECTION_1471.movie_id,PROJECTION_1471.id) = tuple(PROJECTION_1462.movie_id,PROJECTION_1462.movie_id,PROJECTION_1462.movie_id) - 459925 │└PROJECT movie_id AS movie_id_right, info AS info_right - 459925 │ PROJECT info, movie_id - 459925 │ INNER JOIN HASH ON PROJECTION_1468.info_type_id = PROJECTION_1465.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'votes' - 1380035 │ PROJECT info_type_id, info, movie_id - 1380035 │ PROJECT info, movie_id, info_type_id - 1380035 │ TABLE SCAN movie_info_idx - 9626 PROJECT movie_id_left, movie_id AS movie_id_left_2, id, info AS info_left, title - 9626 PROJECT movie_id, info, movie_id, title, id - 9626 INNER JOIN HASH ON PROJECTION_1477.person_id = PROJECTION_1474.id - 9062 │└PROJECT id AS id_right - 9062 │ PROJECT id - 9062 │ TABLE SCAN name WHERE (gender = 'm') AND name LIKE '%Tim%' - 2766914 PROJECT person_id, movie_id, info, movie_id, title, id AS id_left - 2766914 PROJECT movie_id, person_id, info, movie_id, title, id - 2766914 INNER JOIN HASH ON tuple(PROJECTION_1483.movie_id,PROJECTION_1483.movie_id) = tuple(PROJECTION_1480.id,PROJECTION_1480.id) - 2528312 │└PROJECT id, title - 2528312 │ PROJECT title, id - 2528312 │ TABLE SCAN title - 2766914 PROJECT movie_id, movie_id, person_id, info - 2766914 PROJECT movie_id, person_id, info, movie_id - 2766914 INNER JOIN HASH ON PROJECTION_1489.info_type_id = PROJECTION_1486.id - 1 │└PROJECT id - 1 │ PROJECT id - 1 │ TABLE SCAN info_type WHERE info = 'budget' - 460M PROJECT info_type_id, movie_id, person_id, info, movie_id - 460M PROJECT movie_id, person_id, info, movie_id, info_type_id - 460M INNER JOIN HASH ON PROJECTION_1495.movie_id = PROJECTION_1492.movie_id - 14835720 │└PROJECT movie_id AS movie_id_right, info, info_type_id - 14835720 │ PROJECT info, movie_id, info_type_id - 14835720 │ TABLE SCAN movie_info - 36244344 PROJECT movie_id AS movie_id_left, person_id - 36244344 PROJECT movie_id, person_id - 36244344 TABLE SCAN cast_info WHERE TRUE
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 4447 410 PROJECT info, info, title 4447 410 INNER JOIN HASH ON id = person_id 7670 107339 │└INNER JOIN HASH ON movie_id = id 2628 45431 │ │└INNER JOIN HASH ON id = movie_id 2582 45431 │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │└FILTER id <= 110 2 1 │ │ │ │ TABLE SCAN info_type WHERE info = 'budget' 145892 45431 │ │ │ INNER JOIN HASH ON movie_id = movie_id 24425 459925 │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ │ TABLE SCAN info_type WHERE info = 'votes' 1380035 459925 │ │ │ │ TABLE SCAN movie_info_idx 14835720 45722 │ │ │ TABLE SCAN movie_info WHERE movie_id >= 2 AND movie_id <= 2525793 2528312 50185 │ │ TABLE SCAN title WHERE id >= 2 AND id <= 2525793 7248868 111381 │ FILTER movie_id BETWEEN 2 AND 2525793 7248868 111381 │ FILTER (note = '(producer)') OR (note = '(executive producer)') 36244344 1894141 │ TABLE SCAN cast_info WHERE note IN('(producer)','(executive producer)') 2083746 172 FILTER id <= 4061926 2083746 172 TABLE SCAN "name" WHERE gender = 'm' AND contains(name,'Tim')
	Estimate Actual Operator 1 1 PROJECT movie_budget, movie_votes, movie_title 1 1 AGGREGATE MIN(info), MIN(info), MIN(title) 997614 10 DISTRIBUTE GATHER 997614 10 AGGREGATE MIN(info), MIN(info), MIN(title) 997614 410 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id 997614 410 │└DISTRIBUTE HASH ON movie_id, movie_id, movie_id 997614 410 │ INNER JOIN HASH ON id = person_id 833499 9062 │ │└DISTRIBUTE HASH ON id 833499 9062 │ │ FILTER (gender = 'm') AND name LIKE '%Tim%' 4167491 1846761 │ │ TABLE SCAN name WHERE (gender = 'm') AND name LIKE '%Tim%' 4861715 107339 │ DISTRIBUTE HASH ON person_id 4861715 107339 │ INNER JOIN HASH ON id = info_type_id 23 1 │ │└DISTRIBUTE GATHER 23 1 │ │ FILTER info = 'votes' 113 113 │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ TABLE SCAN info_type WHERE info = 'votes' 4861715 322625 │ PROJECT person_id, movie_id, movie_id, info, movie_id, info_type_id, info 4861715 322625 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 1380035 1380035 │ │└DISTRIBUTE HASH ON movie_id, movie_id 1380035 1380035 │ │ TABLE SCAN movie_info_idx WHERE ((info_type_id >= 100) AND (info_type_id <= 100)) AND info_type_id IN 100 8900343 212985 │ DISTRIBUTE HASH ON movie_id, movie_id 8900343 212985 │ INNER JOIN HASH ON id = info_type_id 23 1 │ │└DISTRIBUTE GATHER 23 1 │ │ FILTER info = 'budget' 113 113 │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ TABLE SCAN info_type WHERE info = 'budget' 42566859 2119877 │ INNER JOIN HASH ON movie_id = movie_id 7248869 2379271 │ │└DISTRIBUTE HASH ON movie_id 7248869 2379271 │ │ FILTER (note = '(producer)') OR (note = '(executive producer)') 36244344 7036575 │ │ TABLE SCAN cast_info WHERE (((note = '(producer)') OR (note = '(executive producer)')) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 57) AND (movie_id <= 2525790)) AND ((movie_id >= 57) AND (movie_id <= 2525790))) AND TRUE) WHEN 3 THEN ((((movie_id >= 11) AND (movie_id <= 2525784)) AND ((movie_id >= 11) AND (movie_id <= 2525784))) AND TRUE) WHEN 5 THEN ((((movie_id >= 52) AND (movie_id <= 2525793)) AND ((movie_id >= 52) AND (movie... 14835720 743593 │ DISTRIBUTE HASH ON movie_id 14835720 743593 │ TABLE SCAN movie_info WHERE CASE MOD(HASH_REPARTITION movie_id,10) WHEN 0 THEN (((movie_id >= 5) AND (movie_id <= 2525968)) AND TRUE) WHEN 1 THEN (((movie_id >= 6) AND (movie_id <= 2525963)) AND TRUE) WHEN 2 THEN (((movie_id >= 10) AND (movie_id <= 2525956)) AND TRUE) WHEN 3 THEN (((movie_id >= 11) AND (movie_id <= 2525951)) AND TRUE) WHEN 4 THEN (((movie_id >= 55) AND (movie_id <= 2525966)) AND TRUE) WHEN 5 THEN (((movie_id >= 8) AND (movie_id <= 2525934)) AND TRUE) W... 2528312 2528312 DISTRIBUTE HASH ON id, id, id 2528312 2528312 TABLE SCAN title WHERE CASE MOD(HASH_REPARTITION(id,id,id),10) WHEN 0 THEN (((((id >= 822350) AND (id <= 2470262)) AND ((id >= 822350) AND (id <= 2470262))) AND ((id >= 822350) AND (id <= 2470262))) AND struct(id,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 > , ...
	Estimate Actual Operator 1 1 AGGREGATE MIN(mi.info), MIN(mi_idx.info), MIN(t.title) 1 4 DISTRIBUTE GATHER 1 4 AGGREGATE MIN(mi.info), MIN(mi_idx.info), MIN(t.title) 2120 410 INNER JOIN HASH ON mi.info_type_id = it1.id 2120 1 │└DISTRIBUTE GATHER 36200000 1 │ TABLE SCAN info_type WHERE it1.info = 'budget' 337 17545 INNER JOIN HASH ON ci.movie_id = mi.movie_id 337 3435 │└DISTRIBUTE GATHER 108 3435 │ INNER JOIN HASH ON mi_idx.info_type_id = it2.id 108 1 │ │└DISTRIBUTE GATHER 1380000 1 │ │ TABLE SCAN info_type WHERE it2.info = 'votes' 102 10058 │ INNER JOIN HASH ON ci.movie_id = mi_idx.movie_id 102 6719 │ │└DISTRIBUTE GATHER 276000 6719 │ │ INNER JOIN HASH ON ci.movie_id = t.id 276000 6719 │ │ │└DISTRIBUTE GATHER 203000 6719 │ │ │ INNER JOIN HASH ON ci.person_id = n.id 203000 2379271 │ │ │ │└DISTRIBUTE GATHER 113 2379271 │ │ │ │ TABLE SCAN cast_info WHERE ci.note IN('(producer)','(executive producer)') 14800000 9051 │ │ │ TABLE SCAN name WHERE (n.gender IS NOT NULL) AND (n.gender = 'm') AND contains(n.name,'Tim') 113 2520155 │ │ TABLE SCAN title 2530000 1376065 │ TABLE SCAN movie_info_idx 4170000 1346899 TABLE SCAN movie_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(info as info) AS Expr1014, MIN(info as info) AS Expr1015, MIN(title as title) AS Expr1016 37310 416 INNER JOIN HASH ON mi_idx.info_type_id = it2.id 1 1 │└TABLE SCAN info_type AS it2 WHERE info as info = 'votes' 18655 416 INNER JOIN HASH ON mi.info_type_id = it1.id 1 1 │└TABLE SCAN info_type AS it1 WHERE info as info = 'budget' 13058 416 INNER JOIN LOOP ON t.id = mi.movie_id 24 416 │└TABLE SEEK movie_info AS mi WHERE BLOOM(info_type_id as info_type_id) 54176 3848 INNER JOIN LOOP ON t.id = mi_idx.movie_id 1 3848 │└TABLE SEEK movie_info_idx AS mi_idx WHERE BLOOM(info_type_id as info_type_id) 470845 7370 INNER JOIN HASH ON t.id = ci.movie_id 470847 7370 │└INNER JOIN HASH ON ci.person_id = n.id 12244 9798 │ │└TABLE SCAN name AS n WHERE name as name LIKE '%Tim%' AND gender as gender = 'm' 240301 7370 │ TABLE SCAN cast_info AS ci WHERE (note as note = '(executive producer)' OR note as note = '(producer)') AND BLOOM(person_id as person_id) 2528310 7239 TABLE SCAN title AS t WHERE BLOOM(id as id)
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_budget, min_35 AS movie_votes, min_36 AS movie_title 1 1 AGGREGATE MIN(min_37) AS min, MIN(min_38) AS min_35, MIN(min_39) AS min_36 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(info_11) AS min_37, MIN(info_19) AS min_38, MIN(title) AS min_39 - 410 INNER JOIN HASH ON movie_id = id_28 2528312 2528312 │└DISTRIBUTE HASH ON id_28 2528312 2528312 │ PROJECT id AS id_28, title 2528312 2528312 │ TABLE SCAN title - 410 INNER JOIN HASH ON person_id = id_24 4167491 9062 │└DISTRIBUTE HASH ON id_24 4167491 9062 │ PROJECT id AS id_24 4167491 9062 │ FILTER (gender = 'm') AND (name LIKE '%Tim%') 4167491 9062 │ TABLE SCAN name - 410 INNER JOIN HASH ON info_type_id_18 = id_4 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_4 113 1 │ FILTER info = 'votes' 113 1 │ TABLE SCAN info_type - 410 INNER JOIN HASH ON movie_id = movie_id_17 1380035 459925 │└DISTRIBUTE HASH ON movie_id_17 1380035 459925 │ PROJECT movie_id AS movie_id_17, info_type_id AS info_type_id_18, info AS info_19 1380035 459925 │ TABLE SCAN movie_info_idx - 699 INNER JOIN HASH ON info_type_id = id_0 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_0 113 1 │ FILTER info = 'budget' 113 1 │ TABLE SCAN info_type - 699 INNER JOIN HASH ON movie_id = movie_id_10 14835720 121863 │└DISTRIBUTE HASH ON movie_id_10 14835720 121863 │ PROJECT movie_id AS movie_id_10, info_type_id, info AS info_11 14835720 121863 │ TABLE SCAN movie_info 32619910 696 FILTER note IN('(executive producer)','(producer)') 32619910 696 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(info), MIN(info), MIN(title) 1 410 INNER JOIN LOOP ON id = movie_id 3 410 │└INNER JOIN LOOP ON id = person_id 11397 107339 │ │└INNER JOIN LOOP ON movie_id = movie_id 1406 15143 │ │ │└INNER JOIN HASH ON info_type_id = id 3 3 │ │ │ │└TABLE SEEK info_type AS it1 476724 6930333 │ │ │ INNER JOIN LOOP ON movie_id = movie_id 5089 153308 │ │ │ │└INNER JOIN HASH ON info_type_id = id 3 3 │ │ │ │ │└TABLE SEEK info_type AS it2 1725045 1380035 │ │ │ │ TABLE SCAN movie_info_idx AS mi_idx 18397000 6931069 │ │ │ TABLE SEEK movie_info AS mi 136293 107217 │ │ TABLE SEEK cast_info AS ci WHERE ci.note IN('(producer)','(executive producer)') 107339 107339 │ TABLE SEEK name AS n WHERE (n.name LIKE '%Tim%') AND (n.gender = 'm') 410 410 TABLE SEEK title AS t