Planner — IMDB — JOB-17E

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 cn.country_code ='[us]'
  AND k.keyword ='character-name-in-title'
  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 50,082,643 50M Rank		Estimation Error Est Err 50,141,522 50M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,072,370 6.1M Rank	Estimation Error Est Err 2,832,555 2.8M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 50,073,207 50M Rank		Estimation Error Est Err 50,073,206 50M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 380,784 381K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 86,346,618 86M Rank		Estimation Error Est Err 16,786,102 17M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,982,178 6M Rank	Estimation Error Est Err 2,874,397 2.9M Rank	Estimation Error Est Err 75,526,170 76M Rank
Estimation Error Est Err 50,442,373 50M Rank		Estimation Error Est Err 2,000,353,368 2G Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,037,463,555 1G Rank	Estimation Error Est Err 2,832,565 2.8M Rank	Estimation Error Est Err 88,246,569 88M Rank
Estimation Error Est Err 1,412,895 1.4M Rank		Estimation Error Est Err 6,033,720 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,072,368 6.1M Rank	Estimation Error Est Err 2,832,555 2.8M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,045,701 6M Rank	Estimation Error Est Err 6,045,701 6M Rank	Estimation Error Est Err 6,045,894 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,045,894 6M Rank	Estimation Error Est Err 2,832,555 2.8M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 11,720,503 12M Rank	Estimation Error Est Err 11,711,004 12M Rank	Estimation Error Est Err 11,779,320 12M Rank	Estimation Error Est Err 5,733,426 5.7M Rank	Estimation Error Est Err 11,859,556 12M Rank	Estimation Error Est Err 2,832,565 2.8M Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 7,638,288 7.6M Rank		Estimation Error Est Err 16,993,510 17M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 18,328,446 18M Rank	Estimation Error Est Err 2,832,571 2.8M Rank	Estimation Error Est Err 6,890,778 6.9M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min 1798 2832555 INNER JOIN HASH ON id56 = person_id 1625 2832555 │└INNER JOIN HASH ON movie_id48 = id31 109 68316 │ │└INNER JOIN HASH ON id31 = movie_id 104 68316 │ │ │└INNER JOIN HASH ON company_id = id21 284 148552 │ │ │ │└INNER JOIN HASH ON movie_id14 = 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 2609129 2609129 │ │ │ │ TABLE SCAN movie_companies 90648 9436 │ │ │ TABLE SCAN company_name WHERE country_code = us 2528312 2528312 │ │ TABLE SCAN title 36244344 36244344 │ TABLE SCAN cast_info 4167491 4167491 TABLE SCAN name
	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_1413.id = PROJECTION_1380.person_id - 0 │└PROJECT person_id - 0 │ PROJECT person_id - 0 │ INNER JOIN HASH ON tuple(PROJECTION_1410.movie_id,PROJECTION_1410.movie_id,PROJECTION_1410.movie_id) = tuple(PROJECTION_1383.movie_id,PROJECTION_1383.movie_id,PROJECTION_1383.id) - 0 │ │└PROJECT movie_id_right, movie_id AS movie_id_right_2, id - 0 │ │ PROJECT movie_id, movie_id, id - 0 │ │ INNER JOIN HASH ON PROJECTION_1407.id = PROJECTION_1386.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_1404.movie_id,PROJECTION_1404.movie_id) = tuple(PROJECTION_1389.movie_id,PROJECTION_1389.id) - 41840 │ │ │ │└PROJECT movie_id AS movie_id_right, id - 41840 │ │ │ │ PROJECT movie_id, id - 41840 │ │ │ │ INNER JOIN HASH ON PROJECTION_1401.id = PROJECTION_1392.movie_id - 41840 │ │ │ │ │└PROJECT movie_id - 41840 │ │ │ │ │ PROJECT movie_id - 41840 │ │ │ │ │ INNER JOIN HASH ON PROJECTION_1398.keyword_id = PROJECTION_1395.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 - 0 │ │ PROJECT id AS id_left - 0 │ │ PROJECT id - 0 │ │ TABLE SCAN company_name WHERE country_code = 'us' - 36244344 │ PROJECT movie_id AS movie_id_left, person_id - 36244344 │ PROJECT movie_id, person_id - 36244344 │ TABLE SCAN cast_info - 4167491 PROJECT id, name - 4167491 PROJECT name, id - 4167491 TABLE SCAN name
	Estimate Actual Operator - 1 AGGREGATE MIN(#0) 8 2832555 PROJECT name 8 2832555 INNER JOIN HASH ON id = person_id 7 2832555 │└INNER JOIN HASH ON movie_id = id 0 68316 │ │└INNER JOIN HASH ON id = movie_id 0 68316 │ │ │└INNER JOIN HASH ON id = company_id 72 148552 │ │ │ │└INNER JOIN HASH ON movie_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 2609129 173613 │ │ │ │ TABLE SCAN movie_companies 1644 84843 │ │ │ TABLE SCAN company_name WHERE country_code = 'us' 2528312 25740 │ │ TABLE SCAN title WHERE id >= 2 AND id <= 2525745 36244344 764636 │ TABLE SCAN cast_info WHERE movie_id >= 2 AND movie_id <= 2525745 4167491 322224 TABLE SCAN "name" WHERE id <= 4061926
	Estimate Actual Operator 1 1 PROJECT member_in_charnamed_movie 1 1 AGGREGATE MIN(name) 2682007 10 DISTRIBUTE GATHER 2682007 10 AGGREGATE MIN(name) 2682007 2832555 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 2682007 2832555 DISTRIBUTE HASH ON movie_id, movie_id, movie_id 2682007 2832555 INNER JOIN HASH ON person_id = id 2682007 2832555 │└DISTRIBUTE HASH ON person_id 2682007 2832555 │ 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' 13410035 916M │ PROJECT person_id, movie_id, movie_id, movie_id, keyword_id 13410035 916M │ 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 ... 7487593 32289229 │ DISTRIBUTE HASH ON movie_id, movie_id 7487593 32289229 │ INNER JOIN HASH ON id = company_id 47000 84843 │ │└DISTRIBUTE GATHER 47000 84843 │ │ FILTER country_code = 'us' 234997 234997 │ │ TABLE SCAN company_name WHERE country_code = 'us' 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 ... 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) ... 4167491 4167491 DISTRIBUTE HASH ON id 4167491 4167491 TABLE SCAN name WHERE CASE MOD(HASH_REPARTITION id,10) WHEN 0 THEN (((id >= 186) AND (id <= 4061693)) AND TRUE) WHEN 1 THEN (((id >= 109) AND (id <= 4061725)) AND TRUE) WHEN 2 THEN (((id >= 402) AND (id <= 4061684)) AND TRUE) WHEN 3 THEN (((id >= 820) AND (id <= 4061751)) AND TRUE) WHEN 4 THEN (((id >= 295) AND (id <= 4061904)) AND TRUE) WHEN 5 THEN (((id >= 274) AND (id <= 4061723)) AND TRUE) WHEN 6 THEN (((id >= 512) AND (id <= 4061710)) AND TRUE) WHEN 7 THEN (((id >= 4)...
	Estimate Actual Operator 2 0 SEQUENCE 1 1 ├─AGGREGATE MIN(n.name) 1 2 │ DISTRIBUTE GATHER 1 2 │ AGGREGATE MIN(n.name) 3280 2832555 │ INNER JOIN HASH ON ci.person_id = n.id 3280 2832555 │ │└DISTRIBUTE GATHER 3280 2832555 │ │ INNER JOIN HASH ON mc.movie_id = ci.movie_id 3280 68316 │ │ │└DISTRIBUTE GATHER 199 68316 │ │ │ INNER JOIN HASH ON mc.company_id = cn.id 199 84843 │ │ │ │└DISTRIBUTE GATHER 235000 84843 │ │ │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND (cn.country_code = 'us') 192 148545 │ │ │ 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 - 1 │ │ │ │ │ │ TABLE SCAN keyword WHERE k.keyword = 'character-name-in-title' 4170000 4519861 │ │ │ │ │ TABLE SCAN movie_keyword 36200000 2520276 │ │ │ │ TABLE SCAN title 2610000 2605052 │ │ │ TABLE SCAN movie_companies 1050 1179185 │ │ FILTER 2530000 36228386 │ │ TABLE SCAN cast_info 4520000 4159813 │ TABLE SCAN name 2 0 └─FILTER 3280 1 DISTRIBUTE HASH 3280 1 AGGREGATE bloom_filter_agg(bloom_expr(mk.movie_id,mk.movie_id),41840L,524288L) 3280 41840 SELECT 134000 36228386 DISTRIBUTE HASH 134000 36228386 DISTRIBUTE HASH 134000 36228386 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(partialagg1025) AS Expr1014 5 10 AGGREGATE MIN(name as name) AS partialagg1025 9770 2832555 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 2832555 │└TABLE SEEK name AS n 9770 2832555 SORT Expr1029 9770 2832555 PROJECT BmkToPage Bmk1010 AS Expr1029 9770 2832555 INNER JOIN LOOP ON ci.person_id = n.id 1 2832555 │└TABLE SEEK name AS n 9770 2832555 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 2832555 │└TABLE SEEK cast_info AS ci 9770 2832555 SORT Expr1026 9770 2832555 PROJECT BmkToPage Bmk1000 AS Expr1026 9770 2832555 INNER JOIN LOOP ON mc.movie_id = ci.movie_id 30 2832555 │└TABLE SEEK cast_info AS ci 323 68316 SORT movie_id 323 68316 INNER JOIN HASH ON cn.id = mc.company_id 899 148552 │└INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 148552 │ │└TABLE SEEK movie_companies AS mc 899 148552 │ PROJECT BmkToPage Bmk1006 AS Expr1039 899 148552 │ INNER JOIN LOOP ON mk.movie_id = mc.movie_id 9 148552 │ │└TABLE SEEK movie_companies AS mc 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' 84576 9498 TABLE SCAN company_name AS cn WHERE (PROBE(Bitmap1041,id as id,N'IN ROW')) AND (country_code as country_code = 'us')
	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 - 2832555 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 - 2832555 INNER JOIN HASH ON person_id = id_19 4167491 4167491 │└DISTRIBUTE HASH ON id_19 4167491 4167491 │ PROJECT id AS id_19, name AS name_20 4167491 4167491 │ TABLE SCAN name - 2832555 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 - 2832555 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 - 2831645 INNER JOIN HASH ON company_id = id_0 234997 84843 │└DISTRIBUTE GATHER 234997 84843 │ PROJECT id AS id_0 234997 84843 │ FILTER country_code = 'us' 234997 84843 │ TABLE SCAN company_name - 2831645 INNER JOIN HASH ON movie_id = movie_id_9 2609129 68275 │└DISTRIBUTE HASH ON movie_id_9 2609129 68275 │ PROJECT movie_id AS movie_id_9, company_id 2609129 68275 │ TABLE SCAN movie_companies 36244344 747526 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name) 1380 2832555 INNER JOIN LOOP ON id = person_id 1380 2832555 │└INNER JOIN LOOP ON movie_id = id 63 68316 │ │└INNER JOIN LOOP ON id = company_id 173 148552 │ │ │└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 209200 148532 │ │ │ TABLE SEEK movie_companies AS mc 148552 148552 │ │ TABLE SEEK company_name AS cn WHERE cn.country_code = 'us' 2664324 2832381 │ TABLE SEEK cast_info AS ci 2832555 2832555 TABLE SEEK name AS n