Planner — IMDB — JOB-17F

SELECT MIN(n.name) AS member_in_charnamed_movie
FROM job.cast_info AS ci,
     job.company_name AS cn,
     job.keyword AS k,
     job.movie_companies AS mc,
     job.movie_keyword AS mk,
     job.name AS n,
     job.title AS t
WHERE k.keyword ='character-name-in-title'
  AND n.name LIKE '%B%'
  AND n.id = ci.person_id
  AND ci.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND t.id = mc.movie_id
  AND mc.company_id = cn.id
  AND ci.movie_id = mc.movie_id
  AND ci.movie_id = mk.movie_id
  AND mc.movie_id = mk.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 46,201,993 46M Rank		Estimation Error Est Err 47,168,329 47M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,457,986 5.5M Rank	Estimation Error Est Err 1,113,120 1.1M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 46,677,429 47M Rank		Estimation Error Est Err 46,677,428 47M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 529,336 529K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 46,641,765 47M Rank		Estimation Error Est Err 47,218,476 47M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 15,974,636 16M Rank	Estimation Error Est Err 1,113,122 1.1M Rank	Estimation Error Est Err 8,177,713 8.2M Rank
Estimation Error Est Err 50,442,373 50M Rank		Estimation Error Est Err 6,372,159,614 6.4G Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,273,337,383 3.3G Rank	Estimation Error Est Err 1,113,130 1.1M Rank	Estimation Error Est Err 135,995,896 136M Rank
Estimation Error Est Err 1,825,815 1.8M Rank		Estimation Error Est Err 9,341,119 9.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 15,974,632 16M Rank	Estimation Error Est Err 1,113,120 1.1M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,387,393 4.4M Rank	Estimation Error Est Err 4,387,393 4.4M Rank	Estimation Error Est Err 4,387,392 4.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 4,386,706 4.4M Rank	Estimation Error Est Err 1,113,120 1.1M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,063,665 6.1M Rank	Estimation Error Est Err 6,063,664 6.1M Rank	Estimation Error Est Err 6,063,664 6.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,063,664 6.1M Rank	Estimation Error Est Err 1,826,413 1.8M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 4,465,582 4.5M Rank		Estimation Error Est Err 40,094,668 40M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 34,826,054 35M Rank	Estimation Error Est Err 1,113,136 1.1M Rank	Estimation Error Est Err 3,490,369 3.5M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min 300 1113120 INNER JOIN HASH ON id53 = movie_id36 287 1113120 │└INNER JOIN HASH ON company_id = id43 302 1113120 │ │└INNER JOIN HASH ON movie_id15 = movie_id36 147 149494 │ │ │└INNER JOIN HASH ON id23 = person_id 1017 1038393 │ │ │ │└INNER JOIN HASH ON movie_id15 = movie_id 32 41840 │ │ │ │ │└INNER JOIN HASH ON keyword_id = id 1 1 │ │ │ │ │ │└TABLE SCAN keyword WHERE keyword = character - name - in - title 4523930 4523930 │ │ │ │ │ TABLE SCAN movie_keyword 36244344 36244344 │ │ │ │ TABLE SCAN cast_info 545355 61280 │ │ │ TABLE SCAN name WHERE name LIKE '%B%' 2609129 2609129 │ │ TABLE SCAN movie_companies 234997 234997 │ TABLE SCAN company_name 2528312 2528312 TABLE SCAN title
	Estimate Actual Operator - 1 PROJECT a1 AS member_in_charnamed_movie - 1 AGGREGATE MIN(name) AS a1 - 0 PROJECT name - 0 PROJECT name - 0 INNER JOIN HASH ON PROJECTION_1454.id = PROJECTION_1421.person_id - 0 │└PROJECT person_id - 0 │ PROJECT person_id - 0 │ INNER JOIN HASH ON tuple(PROJECTION_1451.movie_id,PROJECTION_1451.movie_id,PROJECTION_1451.movie_id) = tuple(PROJECTION_1424.movie_id,PROJECTION_1424.movie_id,PROJECTION_1424.id) - 148552 │ │└PROJECT movie_id_right, movie_id AS movie_id_right_2, id - 148552 │ │ PROJECT movie_id, movie_id, id - 148552 │ │ INNER JOIN HASH ON PROJECTION_1448.id = PROJECTION_1427.company_id - 148552 │ │ │└PROJECT company_id, movie_id, movie_id, id AS id_right - 148552 │ │ │ PROJECT movie_id, company_id, movie_id, id - 148552 │ │ │ INNER JOIN HASH ON tuple(PROJECTION_1445.movie_id,PROJECTION_1445.movie_id) = tuple(PROJECTION_1430.movie_id,PROJECTION_1430.id) - 41840 │ │ │ │└PROJECT movie_id AS movie_id_right, id - 41840 │ │ │ │ PROJECT movie_id, id - 41840 │ │ │ │ INNER JOIN HASH ON PROJECTION_1442.id = PROJECTION_1433.movie_id - 41840 │ │ │ │ │└PROJECT movie_id - 41840 │ │ │ │ │ PROJECT movie_id - 41840 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_1439.keyword_id = PROJECTION_1436.id - 1 │ │ │ │ │ │└PROJECT id - 1 │ │ │ │ │ │ PROJECT id - 1 │ │ │ │ │ │ TABLE SCAN keyword WHERE keyword = 'character-name-in-title' - 4523930 │ │ │ │ │ PROJECT keyword_id, movie_id - 4523930 │ │ │ │ │ PROJECT movie_id, keyword_id - 4523930 │ │ │ │ │ TABLE SCAN movie_keyword - 2528312 │ │ │ │ PROJECT id - 2528312 │ │ │ │ PROJECT id - 2528312 │ │ │ │ TABLE SCAN title - 2609129 │ │ │ PROJECT movie_id AS movie_id_left, company_id - 2609129 │ │ │ PROJECT movie_id, company_id - 2609129 │ │ │ TABLE SCAN movie_companies - 234997 │ │ PROJECT id AS id_left - 234997 │ │ PROJECT id - 234997 │ │ TABLE SCAN company_name - 36244344 │ PROJECT movie_id AS movie_id_left, person_id - 36244344 │ PROJECT movie_id, person_id - 36244344 │ TABLE SCAN cast_info - 536716 PROJECT id, name - 536716 PROJECT id, name - 536716 TABLE SCAN name WHERE name LIKE '%B%'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0) 251 1113120 PROJECT name 251 1113120 INNER JOIN HASH ON id = person_id 1083 7796926 │└INNER JOIN HASH ON movie_id = id 74 148552 │ │└INNER JOIN HASH ON id = company_id 74 148552 │ │ │└INNER JOIN HASH ON movie_id = movie_id 70 41838 │ │ │ │└INNER JOIN HASH ON id = movie_id 69 41838 │ │ │ │ │└INNER JOIN HASH ON keyword_id = id 2 1 │ │ │ │ │ │└TABLE SCAN keyword WHERE keyword = 'character-name-in-title' 4523930 41838 │ │ │ │ │ TABLE SCAN movie_keyword WHERE movie_id <= 2525745 2528312 67070 │ │ │ │ FILTER id <= 2525745 2528312 67073 │ │ │ │ TABLE SCAN title WHERE id >= 2 AND id <= 2525971 2609129 173613 │ │ │ TABLE SCAN movie_companies 234997 28077 │ │ TABLE SCAN company_name 36244344 982659 │ TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525745 833498 522511 FILTER id <= 4061926 833498 532554 TABLE SCAN "name" WHERE contains(name,'B')
	Estimate Actual Operator 1 1 PROJECT member_in_charnamed_movie 1 1 AGGREGATE MIN(name) 2751677 10 DISTRIBUTE GATHER 2751677 10 AGGREGATE MIN(name) 2751677 1113120 INNER JOIN HASH ON id = movie_id AND id = movie_id AND id = movie_id 2528312 2528312 │└DISTRIBUTE HASH ON id, id, id 2528312 2528312 │ TABLE SCAN title 2751677 1113120 DISTRIBUTE HASH ON movie_id, movie_id, movie_id 2751677 1113120 PROJECT movie_id, movie_id, movie_id, name 2751677 1113120 INNER JOIN HASH ON id = person_id 833499 536716 │└DISTRIBUTE HASH ON id 833499 536716 │ FILTER name LIKE '%B%' 4167491 4167491 │ TABLE SCAN name WHERE name LIKE '%B%' AND (name < '''El Galgo PornoStar'', Blanquito') 13409865 7796926 DISTRIBUTE HASH ON person_id 13409865 7796926 INNER JOIN HASH ON id = keyword_id 26834 1 │└DISTRIBUTE GATHER 26834 1 │ FILTER keyword = 'character-name-in-title' 134170 134170 │ DISTRIBUTE ROUND ROBIN 134170 134170 │ TABLE SCAN keyword WHERE keyword = 'character-name-in-title' 67049328 3101M PROJECT person_id, movie_id, movie_id, movie_id, keyword_id 67049328 3101M INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 4523930 4523930 │└DISTRIBUTE HASH ON movie_id, movie_id 4523930 4523930 │ TABLE SCAN movie_keyword WHERE (((keyword_id >= 117) AND (keyword_id <= 117)) AND keyword_id IN 117) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id,movie_id),10) WHEN 0 THEN (((((movie_id >= 10) AND (movie_id <= 2528311)) AND ((movie_id >= 10) AND (movie_id <= 2528311))) AND ((movie_id >= 10) AND (movie_id <= 2528311))) AND TRUE) WHEN 1 THEN (((((movie_id >= 28) AND (movie_id <= 2528294)) AND ((movie_id >= 28) AND (movie_id <= 2528294))) AND ((movie_id >= 28) AND (mov... 37437492 80274241 DISTRIBUTE HASH ON movie_id, movie_id 37437492 80274241 INNER JOIN HASH ON id = company_id 234997 234997 │└DISTRIBUTE GATHER 234997 234997 │ TABLE SCAN company_name 37437492 80274241 PROJECT person_id, movie_id, movie_id, company_id 37437492 80274241 INNER JOIN HASH ON movie_id = movie_id 2609129 2609129 │└DISTRIBUTE HASH ON movie_id 2609129 2609129 │ TABLE SCAN movie_companies WHERE (((company_id >= 1) AND (company_id <= 234997)) AND TRUE) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 64) AND (movie_id <= 2525771)) AND ((movie_id >= 64) AND (movie_id <= 2525771))) AND TRUE) WHEN 3 THEN ((((movie_id >= 11) AND (movie_id <= 2525785)) AND ((movie_id >= 11) AND (movie_id <= 2525785))) AND TRUE) WHEN 5 THEN ((((movie_id >= 211) AND (movie_id <= 2525833)) AND ((movie_id >= 211) AND (mov... 36244344 36244344 DISTRIBUTE HASH ON movie_id 36244344 36244344 TABLE SCAN cast_info WHERE CASE MOD(HASH_REPARTITION movie_id,10) WHEN 0 THEN (((movie_id >= 67) AND (movie_id <= 2525735)) AND TRUE) WHEN 1 THEN (((movie_id >= 45) AND (movie_id <= 2525732)) AND TRUE) WHEN 2 THEN (((movie_id >= 24) AND (movie_id <= 2525742)) AND TRUE) WHEN 3 THEN (((movie_id >= 2) AND (movie_id <= 2525729)) AND TRUE) WHEN 4 THEN (((movie_id >= 55) AND (movie_id <= 2525738)) AND TRUE) WHEN 5 THEN (((movie_id >= 54) AND (movie_id <= 2525731)) AND TRUE) WHEN...
	Estimate Actual Operator 1 1 AGGREGATE MIN(n.name) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE MIN(n.name) 3280 1113120 INNER JOIN HASH ON ci.person_id = n.id 3280 7796926 │└DISTRIBUTE GATHER 3280 7796926 │ INNER JOIN HASH ON mc.movie_id = ci.movie_id 3280 148552 │ │└DISTRIBUTE GATHER 199 148552 │ │ INNER JOIN HASH ON mc.company_id = cn.id 199 148552 │ │ │└DISTRIBUTE GATHER 192 148552 │ │ │ INNER JOIN HASH ON mk.movie_id = mc.movie_id 192 41840 │ │ │ │└DISTRIBUTE GATHER 75 41840 │ │ │ │ INNER JOIN HASH ON mk.movie_id = t.id 75 41840 │ │ │ │ │└DISTRIBUTE GATHER 71 41840 │ │ │ │ │ INNER JOIN HASH ON mk.keyword_id = k.id 71 1 │ │ │ │ │ │└DISTRIBUTE GATHER 4170000 1 │ │ │ │ │ │ TABLE SCAN keyword WHERE k.keyword = 'character-name-in-title' 36200000 4519861 │ │ │ │ │ TABLE SCAN movie_keyword 235000 2520276 │ │ │ │ TABLE SCAN title 2610000 2605059 │ │ │ TABLE SCAN movie_companies 2530000 231711 │ │ TABLE SCAN company_name 134000 36228449 │ TABLE SCAN cast_info 4520000 536408 TABLE SCAN name WHERE contains(n.name,'B')
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1026) AS Expr1014 5 1 AGGREGATE MIN(name as name) AS partialagg1026 5899 1826412 INNER JOIN LOOP ON mk.movie_id = mc.movie_id 9 1826412 │└TABLE SEEK movie_companies AS mc 595 243364 FILTER name as name LIKE '%B%' 2741 1038393 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 1038393 │└TABLE SEEK name AS n 2741 1038393 PROJECT BmkToPage Bmk1010 AS Expr1039 2741 1038393 INNER JOIN LOOP ON ci.person_id = n.id 1 1038393 │└TABLE SEEK name AS n 2741 1038393 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 1038393 │└TABLE SEEK cast_info AS ci 2741 1038393 PROJECT BmkToPage Bmk1000 AS Expr1036 2741 1038393 INNER JOIN LOOP ON mk.movie_id = ci.movie_id 30 1038393 │└TABLE SEEK cast_info AS ci 90 41840 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 41840 │└TABLE SEEK movie_keyword AS mk 90 41840 INNER JOIN LOOP ON k.id = mk.keyword_id 90 41840 │└TABLE SEEK movie_keyword AS mk 1 1 TABLE SCAN keyword AS k WHERE keyword as keyword = 'character-name-in-title'
	Estimate Actual Operator 1 ∞ PROJECT min AS member_in_charnamed_movie 1 1 AGGREGATE MIN(min_35) AS min - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name_20) AS min_35 - 1113120 INNER JOIN HASH ON movie_id = id_27 2528312 2528312 │└DISTRIBUTE HASH ON id_27 2528312 2528312 │ PROJECT id AS id_27 2528312 2528312 │ TABLE SCAN title - 1113120 INNER JOIN HASH ON person_id = id_19 4167491 536716 │└DISTRIBUTE HASH ON id_19 4167491 536716 │ PROJECT id AS id_19, name AS name_20 4167491 536716 │ FILTER name LIKE '%B%' 4167491 536716 │ TABLE SCAN name - 7796926 INNER JOIN HASH ON keyword_id = id_4 134170 1 │└DISTRIBUTE GATHER 134170 1 │ PROJECT id AS id_4 134170 1 │ FILTER keyword = 'character-name-in-title' 134170 1 │ TABLE SCAN keyword - 7796926 INNER JOIN HASH ON movie_id = movie_id_15 4523930 41840 │└DISTRIBUTE HASH ON movie_id_15 4523930 41840 │ PROJECT movie_id AS movie_id_15, keyword_id 4523930 41840 │ TABLE SCAN movie_keyword - 7795385 INNER JOIN HASH ON company_id = id_0 234997 234997 │└DISTRIBUTE GATHER 234997 234997 │ PROJECT id AS id_0 234997 234997 │ TABLE SCAN company_name - 7795385 INNER JOIN HASH ON movie_id = movie_id_9 2609129 148487 │└DISTRIBUTE HASH ON movie_id_9 2609129 148487 │ PROJECT movie_id AS movie_id_9, company_id 2609129 148487 │ TABLE SCAN movie_companies 36244344 975229 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name) 496 1113120 INNER JOIN LOOP ON id = company_id 496 1113120 │└INNER JOIN LOOP ON movie_id = movie_id 174 149494 │ │└INNER JOIN LOOP ON id = person_id 1325 1038393 │ │ │└INNER JOIN LOOP ON movie_id = id 34 41840 │ │ │ │└INNER JOIN LOOP ON id = movie_id 34 41840 │ │ │ │ │└INNER JOIN LOOP ON keyword_id = id 1 1 │ │ │ │ │ │└TABLE SEEK keyword AS k 305 41840 │ │ │ │ │ TABLE SEEK movie_keyword AS mk 41840 41840 │ │ │ │ TABLE SEEK title AS t 1631760 1038468 │ │ │ TABLE SEEK cast_info AS ci 1038393 1038393 │ │ TABLE SEEK name AS n WHERE n.name LIKE '%B%' 747470 1113730 │ TABLE SEEK movie_companies AS mc 1113120 1113120 TABLE SEEK company_name AS cn