Planner — IMDB — JOB-31C

SELECT MIN(mi.info) AS movie_budget,
       MIN(mi_idx.info) AS movie_votes,
       MIN(n.name) AS writer,
       MIN(t.title) AS violent_liongate_movie
FROM job.cast_info AS ci,
     job.company_name AS cn,
     job.info_type AS it1,
     job.info_type AS it2,
     job.keyword AS k,
     job.movie_companies AS mc,
     job.movie_info AS mi,
     job.movie_info_idx AS mi_idx,
     job.movie_keyword AS mk,
     job.name AS n,
     job.title AS t
WHERE ci.note IN ('(writer)',
                  '(head writer)',
                  '(written by)',
                  '(story)',
                  '(story editor)')
  AND cn.name LIKE 'Lionsgate%'
  AND it1.info = 'genres'
  AND it2.info = 'votes'
  AND k.keyword IN ('murder',
                    'violence',
                    'blood',
                    'gore',
                    'death',
                    'female-nudity',
                    'hospital')
  AND mi.info IN ('Horror',
                  'Action',
                  'Sci-Fi',
                  'Thriller',
                  'Crime',
                  'War')
  AND t.id = mi.movie_id
  AND t.id = mi_idx.movie_id
  AND t.id = ci.movie_id
  AND t.id = mk.movie_id
  AND t.id = mc.movie_id
  AND ci.movie_id = mi.movie_id
  AND ci.movie_id = mi_idx.movie_id
  AND ci.movie_id = mk.movie_id
  AND ci.movie_id = mc.movie_id
  AND mi.movie_id = mi_idx.movie_id
  AND mi.movie_id = mk.movie_id
  AND mi.movie_id = mc.movie_id
  AND mi_idx.movie_id = mk.movie_id
  AND mi_idx.movie_id = mc.movie_id
  AND mk.movie_id = mc.movie_id
  AND n.id = ci.person_id
  AND it1.id = mi.info_type_id
  AND it2.id = mi_idx.info_type_id
  AND k.id = mk.keyword_id
  AND cn.id = mc.company_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 15,209,732 15M Rank		Estimation Error Est Err 15,303,293 15M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 106,367 106K Rank	Estimation Error Est Err 2,825 2.8K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 66,423,143 66M Rank		Estimation Error Est Err 186,533,980 187M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 101,008,219 101M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 17,828,412 18M Rank		Estimation Error Est Err 11,645,480 12M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,875,028 1.9M Rank	Estimation Error Est Err 1,247,546 1.2M Rank	Estimation Error Est Err 4,282,343 4.3M Rank
Estimation Error Est Err 32,628,432 33M Rank		Estimation Error Est Err 16,012,351 16M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,733,050 2.7M Rank	Estimation Error Est Err 2,835 2.8K Rank	Estimation Error Est Err 16,783,785 17M Rank
Estimation Error Est Err 1,941,368 1.9M Rank		Estimation Error Est Err 2,413,214 2.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,422,409 3.4M Rank	Estimation Error Est Err 2,825 2.8K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 2,279,277 2.3M Rank	Estimation Error Est Err 2,279,277 2.3M Rank	Estimation Error Est Err 2,471,167 2.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,342,885 2.3M Rank	Estimation Error Est Err 2,825 2.8K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,330,791 6.3M Rank	Estimation Error Est Err 6,330,668 6.3M Rank	Estimation Error Est Err 6,232,644 6.2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,418,221 6.4M Rank	Estimation Error Est Err 2,825 2.8K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 6,844,941 6.8M Rank		Estimation Error Est Err 15,394 15K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 6,849,553 6.8M Rank	Estimation Error Est Err 2,841 2.8K Rank	Estimation Error Est Err 6,844,716 6.8M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min, min 5 2825 INNER JOIN HASH ON id82 = person_id 4 2825 │└INNER JOIN HASH ON id = info_type_id76 1 1 │ │└TABLE SCAN info_type WHERE info = votes 11 8607 │ INNER JOIN HASH ON movie_id75 = movie_id42 5 2827 │ │└INNER JOIN HASH ON id59 = movie_id42 5 2827 │ │ │└INNER JOIN HASH ON id6 = info_type_id 1 1 │ │ │ │└TABLE SCAN info_type WHERE info = genres 5 3009 │ │ │ INNER JOIN HASH ON movie_id51 = movie_id42 12 3057 │ │ │ │└INNER JOIN HASH ON movie_id42 = movie_id36 24 1551 │ │ │ │ │└INNER JOIN HASH ON movie_id36 = movie_id 1680 1814 │ │ │ │ │ │└INNER JOIN HASH ON id11 = company_id 159 10 │ │ │ │ │ │ │└TABLE SCAN company_name WHERE name) Lionsgate 2609129 2609129 │ │ │ │ │ │ TABLE SCAN movie_companies 4236 76714 │ │ │ │ │ INNER JOIN HASH ON id29 = keyword_id 131 7 │ │ │ │ │ │└TABLE SCAN keyword WHERE keyword BETWEEN blood AND violence AND keyword IN(blood,death,female - nudity,gore,hospital,murder,violence) 4523930 4523930 │ │ │ │ │ TABLE SCAN movie_keyword 246296 574 │ │ │ │ TABLE SCAN movie_info WHERE info BETWEEN Action AND War AND info44 IN(Action,Crime,Horror,Sci - Fi,Thriller,War) 1274215 242 │ │ │ TABLE SCAN cast_info WHERE note BETWEEN (head writer) AND (written by) AND note53 IN((head writer),(story editor),story,writer,(written by)) 2528312 2528312 │ │ TABLE SCAN title 1380035 1380035 │ TABLE SCAN movie_info_idx 4167491 4167491 TABLE SCAN name
	Estimate Actual Operator - 1 PROJECT a1 AS movie_budget, a2 AS movie_votes, a3 AS writer, a4 AS violent_liongate_movie - 1 AGGREGATE MIN(info_left) AS a1, MIN(info_right) AS a2, MIN(name) AS a3, MIN(title) AS a4 - 0 PROJECT info, info, name, title - 0 PROJECT info, info, name, title - 0 INNER JOIN HASH ON tuple(PROJECTION_4535.movie_id,PROJECTION_4535.movie_id,PROJECTION_4535.movie_id,PROJECTION_4535.movie_id,PROJECTION_4535.movie_id,PROJECTION_4535.movie_id,PROJECTION_4535.id,PROJECTION_4535.id) = tuple(PROJECTION_4514.movie_id,PROJECTION_4514.movie_id,PROJECTION_4514.movie_id,PROJECTION_4514.movie_id,PROJECTION_4514.movie_id,PROJECTION_4514.movie_id,PROJECTION_4514.movie_id,PROJECTION_4514.movie_id) - 3461792 │└PROJECT movie_id_right, movie_id AS movie_id_right_2, info AS info_right - 3461792 │ PROJECT info, movie_id, movie_id - 3461792 │ INNER JOIN HASH ON PROJECTION_4532.id = PROJECTION_4517.keyword_id - 3461792 │ │└PROJECT keyword_id, info, movie_id, movie_id - 3461792 │ │ PROJECT info, movie_id, movie_id, keyword_id - 3461792 │ │ INNER JOIN HASH ON PROJECTION_4529.movie_id = PROJECTION_4520.movie_id - 459925 │ │ │└PROJECT movie_id AS movie_id_right, info - 459925 │ │ │ PROJECT info, movie_id - 459925 │ │ │ INNER JOIN HASH ON PROJECTION_4526.info_type_id = PROJECTION_4523.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 - 4523930 │ │ PROJECT movie_id AS movie_id_left, keyword_id - 4523930 │ │ PROJECT movie_id, keyword_id - 4523930 │ │ TABLE SCAN movie_keyword - 134170 │ PROJECT id - 134170 │ PROJECT id - 134170 │ TABLE SCAN keyword WHERE TRUE - 0 PROJECT movie_id AS movie_id_left, movie_id AS movie_id_left_2, movie_id AS movie_id_left_3, id, info AS info_left, name, title - 0 PROJECT movie_id, movie_id, info, movie_id, name, title, id - 0 INNER JOIN HASH ON tuple(PROJECTION_4553.movie_id,PROJECTION_4553.movie_id,PROJECTION_4553.movie_id,PROJECTION_4553.movie_id) = tuple(PROJECTION_4538.movie_id,PROJECTION_4538.movie_id,PROJECTION_4538.id,PROJECTION_4538.id) - 1533909 │└PROJECT movie_id AS movie_id_right, id, info, title - 1533909 │ PROJECT info, movie_id, title, id - 1533909 │ INNER JOIN HASH ON PROJECTION_4544.movie_id = PROJECTION_4541.id - 2528312 │ │└PROJECT id, title - 2528312 │ │ PROJECT title, id - 2528312 │ │ TABLE SCAN title - 1533909 │ PROJECT movie_id, info - 1533909 │ PROJECT info, movie_id - 1533909 │ INNER JOIN HASH ON PROJECTION_4550.info_type_id = PROJECTION_4547.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'genres' - 14835720 │ PROJECT info_type_id, info, movie_id - 14835720 │ PROJECT movie_id, info, info_type_id - 14835720 │ TABLE SCAN movie_info WHERE TRUE - 125056 PROJECT movie_id_left, movie_id AS movie_id_left_2, name - 125056 PROJECT movie_id, movie_id, name - 125056 INNER JOIN HASH ON PROJECTION_4559.person_id = PROJECTION_4556.id - 4167491 │└PROJECT id, name - 4167491 │ PROJECT name, id - 4167491 │ TABLE SCAN name - 125056 PROJECT person_id, movie_id, movie_id - 125056 PROJECT movie_id, person_id, movie_id - 125056 INNER JOIN HASH ON PROJECTION_4565.company_id = PROJECTION_4562.id - 10 │└PROJECT id - 10 │ PROJECT id - 10 │ TABLE SCAN company_name WHERE startsWith(name,'Lionsgate') - 80274241 PROJECT company_id, movie_id, person_id, movie_id - 80274241 PROJECT movie_id, person_id, movie_id, company_id - 80274241 INNER JOIN HASH ON PROJECTION_4571.movie_id = PROJECTION_4568.movie_id - 2609129 │└PROJECT movie_id AS movie_id_right, company_id - 2609129 │ PROJECT movie_id, company_id - 2609129 │ TABLE SCAN movie_companies - 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), MIN(#3) 140 2825 PROJECT info, info, name, title 140 2825 INNER JOIN HASH ON id = person_id 120 2825 │└INNER JOIN HASH ON movie_id = id 41 2840 │ │└INNER JOIN HASH ON id = movie_id 40 2840 │ │ │└INNER JOIN HASH ON id = keyword_id 197 110436 │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 108 1235 │ │ │ │ │└INNER JOIN HASH ON id = company_id 542 574510 │ │ │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 516 102513 │ │ │ │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │ │ │ │└FILTER id <= 110 2 1 │ │ │ │ │ │ │ │ TABLE SCAN info_type WHERE info = 'genres' 29178 102513 │ │ │ │ │ │ │ INNER JOIN HASH ON movie_id = movie_id 24425 459917 │ │ │ │ │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │ │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ │ │ │ │ │ TABLE SCAN info_type WHERE info = 'votes' 1380035 459917 │ │ │ │ │ │ │ │ TABLE SCAN movie_info_idx WHERE movie_id <= 2525745 2967144 131608 │ │ │ │ │ │ │ FILTER movie_id BETWEEN 2 AND 2525745 2967144 131609 │ │ │ │ │ │ │ FILTER IN ... 14835720 862633 │ │ │ │ │ │ │ INNER JOIN HASH ON info = #0 0 6 │ │ │ │ │ │ │ │└SCAN MATERIALISED 14835720 862633 │ │ │ │ │ │ │ TABLE SCAN movie_info WHERE info IN('Horror','Action','Sci-Fi','Thriller','Crime','War') 2609129 429566 │ │ │ │ │ │ TABLE SCAN movie_companies 46999 10 │ │ │ │ │ TABLE SCAN company_name WHERE name >= 'Lionsgate' AND name < 'Lionsgatf' 4523930 29645 │ │ │ │ TABLE SCAN movie_keyword WHERE movie_id <= 2525745 26834 7 │ │ │ FILTER IN ... 134170 133568 │ │ │ INNER JOIN HASH ON keyword = #0 0 7 │ │ │ │└SCAN MATERIALISED 134170 133568 │ │ │ TABLE SCAN keyword WHERE keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 2528312 291 │ │ TABLE SCAN title WHERE id >= 2 AND id <= 2525745 7248868 246 │ FILTER movie_id BETWEEN 2 AND 2525745 7248868 246 │ FILTER IN ... 36244344 25464 │ INNER JOIN HASH ON note = #0 0 5 │ │└SCAN MATERIALISED 36244344 25464 │ TABLE SCAN cast_info WHERE note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 4167491 254 TABLE SCAN "name" WHERE id <= 4061926
	Estimate Actual Operator 1 1 PROJECT movie_budget, movie_votes, writer, violent_liongate_movie 1 1 AGGREGATE MIN(info), MIN(info), MIN(name), MIN(title) 131721 10 DISTRIBUTE GATHER 131721 10 AGGREGATE MIN(info), MIN(info), MIN(name), MIN(title) 131721 2825 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id AND movie_id = id AND movie_id = id 131721 2825 │└DISTRIBUTE HASH ON movie_id, movie_id, movie_id, movie_id, movie_id 131721 2825 │ INNER JOIN HASH ON person_id = id 131721 2825 │ │└DISTRIBUTE HASH ON person_id 131721 2825 │ │ INNER JOIN HASH ON id = keyword_id 26834 7 │ │ │└DISTRIBUTE GATHER 26834 7 │ │ │ FILTER keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 134170 134170 │ │ │ DISTRIBUTE ROUND ROBIN 134170 134170 │ │ │ TABLE SCAN keyword WHERE keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 658606 107406 │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id 367738 1017 │ │ │└DISTRIBUTE HASH ON movie_id, movie_id, movie_id, movie_id 367738 1017 │ │ │ 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' 367738 3083 │ │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id 367738 1032 │ │ │ │└DISTRIBUTE HASH ON movie_id, movie_id, movie_id 367738 1032 │ │ │ │ INNER JOIN HASH ON id = info_type_id 23 1 │ │ │ │ │└DISTRIBUTE GATHER 23 1 │ │ │ │ │ FILTER info = 'genres' 113 113 │ │ │ │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ │ │ │ TABLE SCAN info_type WHERE info = 'genres' 1758747 1032 │ │ │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 1497518 1454 │ │ │ │ │└DISTRIBUTE HASH ON movie_id, movie_id 1497518 1454 │ │ │ │ │ INNER JOIN HASH ON id = company_id 47000 10 │ │ │ │ │ │└DISTRIBUTE GATHER 47000 10 │ │ │ │ │ │ FILTER name LIKE 'Lionsgate%' 234997 234997 │ │ │ │ │ │ TABLE SCAN company_name WHERE name LIKE 'Lionsgate%' 7487498 1557234 │ │ │ │ │ PROJECT person_id, movie_id, movie_id, company_id 7487498 1557234 │ │ │ │ │ INNER JOIN HASH ON movie_id = movie_id 2609129 2609129 │ │ │ │ │ │└DISTRIBUTE HASH ON movie_id 2609129 2609129 │ │ │ │ │ │ TABLE SCAN movie_companies WHERE ((company_id >= 2653) AND (company_id <= 194377)) AND company_id IN(9863,2653,39981,65153,7253,7435,57326,3016,6473,194377) 7248869 1244716 │ │ │ │ │ DISTRIBUTE HASH ON movie_id 7248869 1244716 │ │ │ │ │ FILTER note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 36244344 15326137 │ │ │ │ │ TABLE SCAN cast_info WHERE note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') AND 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 (mo... 2967144 186594 │ │ │ │ DISTRIBUTE HASH ON movie_id, movie_id 2967144 186594 │ │ │ │ FILTER info IN('Horror','Action','Sci-Fi','Thriller','Crime','War') 14835720 1724005 │ │ │ │ TABLE SCAN movie_info WHERE (info IN('Horror','Action','Sci-Fi','Thriller','Crime','War') AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 166565) AND (movie_id <= 2513880)) AND ((movie_id >= 166565) AND (movie_id <= 2513880))) AND TRUE) WHEN 3 THEN ((((movie_id >= 161117) AND (movie_id <= 2499999)) AND ((movie_id >= 161117) AND (movie_id <= 2499999))) AND struct(movie_id,movie_id) IN( < expr > , < expr > , < expr > , < expr ... 1380035 1380035 │ │ │ DISTRIBUTE HASH ON movie_id, movie_id, movie_id 1380035 1380035 │ │ │ TABLE SCAN movie_info_idx WHERE CASE MOD(HASH_REPARTITION(movie_id,movie_id,movie_id),10) WHEN 0 THEN (((((movie_id >= 1089730) AND (movie_id <= 2447527)) AND ((movie_id >= 1089730) AND (movie_id <= 2447527))) AND ((movie_id >= 1089730) AND (movie_id <= 2447527))) AND struct(movie_id,movie_id,movie_id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < e... 4523930 4523930 │ │ DISTRIBUTE HASH ON movie_id, movie_id, movie_id, movie_id 4523930 4523930 │ │ TABLE SCAN movie_keyword WHERE CASE MOD(HASH_REPARTITION(movie_id,movie_id,movie_id,movie_id),10) WHEN 1 THEN ((((((movie_id >= 1732019) AND (movie_id <= 2452149)) AND ((movie_id >= 1732019) AND (movie_id <= 2452149))) AND ((movie_id >= 1732019) AND (movie_id <= 2452149))) AND ((movie_id >= 1732019) AND (movie_id <= 2452149))) AND TRUE) WHEN 3 THEN ((((((movie_id >= 1089730) AND (movie_id <= 2504139)) AND ((movie_id >= 1089730) AND (movie_id <= 2504139))) AND ((mov... 4167491 4167491 │ DISTRIBUTE HASH ON id 4167491 4167491 │ TABLE SCAN name WHERE CASE MOD(HASH_REPARTITION id,10) WHEN 0 THEN (((id >= 58789) AND (id <= 3181291)) AND id IN(3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640,3155640... 2528312 2528312 DISTRIBUTE HASH ON id, id, id, id, id 2528312 2528312 TABLE SCAN title WHERE CASE MOD(HASH_REPARTITION(id,id,id,id,id),10) WHEN 0 THEN (((((((id >= 1089730) AND (id <= 2467823)) AND ((id >= 1089730) AND (id <= 2467823))) AND ((id >= 1089730) AND (id <= 2467823))) AND ((id >= 1089730) AND (id <= 2467823))) AND ((id >= 1089730) AND (id <= 2467823))) AND TRUE) WHEN 1 THEN (((((((id >= 1635608) AND (id <= 2446060)) AND ((id >= 1635608) AND (id <= 2446060))) AND ((id >= 1635608) AND (id <= 2446060))) AND ((id >= 1635608) AND (id <...
	Estimate Actual Operator 164000 0 SEQUENCE 1 1 ├─AGGREGATE MIN(mi.info), MIN(mi_idx.info), MIN(n.name), MIN(t.title) 1 5 │ DISTRIBUTE GATHER 1 5 │ AGGREGATE MIN(mi.info), MIN(mi_idx.info), MIN(n.name), MIN(t.title) 2230 2825 │ INNER JOIN HASH ON mc.movie_id = ci.movie_id 2230 1547 │ │└DISTRIBUTE HASH ON mc.movie_id, mc.movie_id, mi_idx.movie_id, mi_idx.movie_id, mk.movie_id, mk.movie_id, mc.movie_id, mi_idx.movie_id, mk.movie_id 2230 1547 │ │ INNER JOIN HASH ON mc.movie_id = mk.movie_id 2230 76714 │ │ │└DISTRIBUTE GATHER 281 76714 │ │ │ INNER JOIN HASH ON mk.keyword_id = k.id 281 7 │ │ │ │└DISTRIBUTE GATHER 113 7 │ │ │ │ TABLE SCAN keyword WHERE k.keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 1380000 4519852 │ │ │ TABLE SCAN movie_keyword 3530000 1449 │ │ INNER JOIN HASH ON mi_idx.info_type_id = it2.id 3530000 1 │ │ │└DISTRIBUTE GATHER 134000 1 │ │ │ TABLE SCAN info_type WHERE it2.info = 'votes' 254 4368 │ │ INNER JOIN HASH ON mc.movie_id = mi_idx.movie_id 254 1814 │ │ │└DISTRIBUTE GATHER 36 1814 │ │ │ INNER JOIN HASH ON mc.company_id = cn.id 36 10 │ │ │ │└DISTRIBUTE GATHER 4170000 10 │ │ │ │ TABLE SCAN company_name WHERE startswith(cn.name,'Lionsgate') 2530000 2572286 │ │ │ TABLE SCAN movie_companies 113 1371844 │ │ TABLE SCAN movie_info_idx 705000 93240 │ INNER JOIN HASH ON ci.person_id = n.id 705000 93240 │ │└DISTRIBUTE HASH ON ci.person_id 1890000 93240 │ │ INNER JOIN HASH ON mi.movie_id = ci.movie_id 1890000 1244716 │ │ │└DISTRIBUTE GATHER 14800000 1244716 │ │ │ TABLE SCAN cast_info WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 254 186594 │ │ INNER JOIN HASH ON mi.movie_id = t.id 254 186594 │ │ │└DISTRIBUTE GATHER 34 186594 │ │ │ INNER JOIN HASH ON mi.info_type_id = it1.id 34 1 │ │ │ │└DISTRIBUTE GATHER - 1 │ │ │ │ TABLE SCAN info_type WHERE it1.info = 'genres' 36200000 186594 │ │ │ TABLE SCAN movie_info WHERE mi.info IN('Horror','Action','Sci-Fi','Thriller','Crime','War') 4520000 2520894 │ │ TABLE SCAN title 705000 188261 │ DISTRIBUTE HASH ON n.id 1 188261 │ FILTER 235000 4167491 │ TABLE SCAN name 164000 0 └─FILTER 705000 1 DISTRIBUTE HASH 705000 1 AGGREGATE bloom_filter_agg(bloom_expr(ci.person_id,ci.person_id),1244716L,16777216L) 2610000 1244716 DISTRIBUTE HASH 2610000 1244716 DISTRIBUTE HASH 2610000 1244716 TABLE SCAN cast_info WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)')
	Estimate Actual Operator 1 1 AGGREGATE MIN(info as info) AS Expr1022, MIN(info as info) AS Expr1023, MIN(name as name) AS Expr1024, MIN(title as title) AS Expr1025 1 2825 FILTER keyword as keyword = 'blood' OR keyword as keyword = 'death' OR keyword as keyword = 'female-nudity' OR keyword as keyword = 'gore' OR keyword as keyword = 'hospital' OR keyword as keyword = 'murder' OR keyword as keyword = 'violence' 21 107406 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 107406 │└TABLE SEEK keyword AS k 21 107406 INNER JOIN LOOP ON mk.keyword_id = k.id 1 107406 │└TABLE SEEK keyword AS k 21 107406 INNER JOIN LOOP ON Bmk1016 = Bmk1016 1 107406 │└TABLE SEEK movie_keyword AS mk 21 107406 INNER JOIN LOOP ON t.id = mk.movie_id 18 107406 │└TABLE SEEK movie_keyword AS mk 1 1017 INNER JOIN LOOP ON Bmk1018 = Bmk1018 1 1017 │└TABLE SEEK name AS n 1 1017 PROJECT BmkToPage Bmk1018 AS Expr1524 1 1017 INNER JOIN LOOP ON ci.person_id = n.id 1 1017 │└TABLE SEEK name AS n 1 1017 INNER JOIN HASH ON mc.company_id = cn.id 8 10 │└TABLE SCAN company_name AS cn WHERE name as name LIKE 'Lionsgate%' 1 1017 INNER JOIN HASH ON mc.movie_id = t.id 829 54155 │└INNER JOIN LOOP ON Bmk1020 = Bmk1020 1 54155 │ │└TABLE SEEK title AS t 829 54155 │ PROJECT BmkToPage Bmk1020 AS Expr1522 829 54155 │ INNER JOIN LOOP ON mi_idx.movie_id = t.id 1 54155 │ │└TABLE SEEK title AS t 826 54155 │ INNER JOIN HASH ON mi_idx.info_type_id = it2.id 1 1 │ │└FILTER info as info = 'votes' 113 113 │ │ TABLE SCAN info_type AS it2 413 54155 │ INNER JOIN HASH ON mi.info_type_id = it1.id 1 1 │ │└FILTER info as info = 'genres' 113 113 │ │ INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 113 │ │ │└TABLE SEEK info_type AS it1 113 113 │ │ TABLE SEEK info_type AS it1 289 54155 │ INNER JOIN HASH ON mi_idx.movie_id = mi.movie_id 1560 186594 │ │└TABLE SEEK movie_info AS mi WHERE (info as info = 'Action' OR info as info = 'Crime' OR info as info = 'Horror' OR info as info = 'Sci-Fi' OR info as info = 'Thriller' OR info as info = 'War') AND BLOOM(info_type_id as info_type_id) 114 38275 │ FILTER note as note = '(head writer)' OR note as note = '(story editor)' OR note as note = '(story)' OR note as note = '(writer)' OR note as note = '(written by)' 4170 2764032 │ INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 2764032 │ │└TABLE SEEK cast_info AS ci 4170 2764032 │ PROJECT BmkToPage Bmk1000 AS Expr1519 4170 2764032 │ INNER JOIN LOOP ON mi_idx.movie_id = ci.movie_id 30 2764032 │ │└TABLE SEEK cast_info AS ci 1380 75493 │ TABLE SEEK movie_info_idx AS mi_idx WHERE BLOOM(movie_id as movie_id) AND BLOOM(info_type_id as info_type_id) 1 323 TABLE SEEK movie_companies AS mc WHERE BLOOM(company_id as company_id) AND BLOOM(movie_id as movie_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_budget, min_59 AS movie_votes, min_60 AS writer, min_61 AS violent_liongate_movie 1 1 AGGREGATE MIN(min_62) AS min, MIN(min_63) AS min_59, MIN(min_64) AS min_60, MIN(min_65) AS min_61 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(info_25) AS min_62, MIN(info_33) AS min_63, MIN(name_44) AS min_64, MIN(title) AS min_65 - 2825 INNER JOIN HASH ON movie_id = id_51 2528312 2528312 │└DISTRIBUTE HASH ON id_51 2528312 2528312 │ PROJECT id AS id_51, title 2528312 2528312 │ TABLE SCAN title - 2825 INNER JOIN HASH ON person_id = id_43 4167491 4167491 │└DISTRIBUTE HASH ON id_43 4167491 4167491 │ PROJECT id AS id_43, name AS name_44 4167491 4167491 │ TABLE SCAN name - 2825 INNER JOIN HASH ON keyword_id = id_13 134170 7 │└DISTRIBUTE GATHER 134170 7 │ PROJECT id AS id_13 134170 7 │ FILTER keyword IN('blood','death','female-nudity','gore','hospital','murder','violence') 134170 7 │ TABLE SCAN keyword - 2825 INNER JOIN HASH ON movie_id = movie_id_39 4523930 76714 │└DISTRIBUTE HASH ON movie_id_39 4523930 76714 │ PROJECT movie_id AS movie_id_39, keyword_id 4523930 76714 │ TABLE SCAN movie_keyword - 823 INNER JOIN HASH ON info_type_id_32 = id_8 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_8 113 1 │ FILTER info = 'votes' 113 1 │ TABLE SCAN info_type - 823 INNER JOIN HASH ON movie_id = movie_id_31 1380035 40111 │└DISTRIBUTE HASH ON movie_id_31 1380035 40111 │ PROJECT movie_id AS movie_id_31, info_type_id AS info_type_id_32, info AS info_33 1380035 40111 │ TABLE SCAN movie_info_idx - 823 INNER JOIN HASH ON info_type_id = id_4 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_4 113 1 │ FILTER info = 'genres' 113 1 │ TABLE SCAN info_type - 823 INNER JOIN HASH ON movie_id = movie_id_24 13352148 31606 │└DISTRIBUTE HASH ON movie_id_24 13352148 31606 │ PROJECT movie_id AS movie_id_24, info_type_id, info AS info_25 13352148 31606 │ FILTER info IN('Action','Crime','Horror','Sci-Fi','Thriller','War') 13352148 31606 │ TABLE SCAN movie_info - 401 INNER JOIN HASH ON company_id = id_0 211497 10 │└DISTRIBUTE GATHER 211497 10 │ PROJECT id AS id_0 211497 10 │ FILTER (name >= 'Lionsgate') AND (name < 'Lionsgatf') AND (name LIKE 'Lionsgate%') 211497 10 │ TABLE SCAN company_name - 401 INNER JOIN HASH ON movie_id = movie_id_18 2609129 447 │└DISTRIBUTE HASH ON movie_id_18 2609129 447 │ PROJECT movie_id AS movie_id_18, company_id 2609129 447 │ TABLE SCAN movie_companies 32619910 241 FILTER note IN('(head writer)','(story editor)','(story)','(writer)','(written by)') 32619910 241 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(info), MIN(info), MIN(name), MIN(title) 1 2825 INNER JOIN LOOP ON id = company_id 1 589677 │└INNER JOIN LOOP ON id = person_id 1 589677 │ │└INNER JOIN LOOP ON movie_id = id 1 42900 │ │ │└INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 1 42900 │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = movie_id AND (movie_id = movie_id) 1 63386 │ │ │ │ │└INNER JOIN LOOP ON id = info_type_id 3 67732 │ │ │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = movie_id AND (movie_id = movie_id) 6 63701 │ │ │ │ │ │ │└INNER JOIN HASH ON info_type_id = id 1 1 │ │ │ │ │ │ │ │└TABLE SEEK info_type AS it2 686 191689 │ │ │ │ │ │ │ INNER JOIN LOOP ON movie_id = movie_id 236 76714 │ │ │ │ │ │ │ │└INNER JOIN LOOP ON keyword_id = id 7 7 │ │ │ │ │ │ │ │ │└TABLE SEEK keyword AS k 2135 76713 │ │ │ │ │ │ │ │ TABLE SEEK movie_keyword AS mk 230142 191785 │ │ │ │ │ │ │ TABLE SEEK movie_info_idx AS mi_idx 63701 67523 │ │ │ │ │ │ TABLE SEEK movie_info AS mi WHERE mi.info IN('Horror','Action','Sci-Fi','Thriller','Crime','War') 67732 67732 │ │ │ │ │ TABLE SEEK info_type AS it1 WHERE it1.info = 'genres' 63386 63386 │ │ │ │ TABLE SEEK cast_info AS ci WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 42900 42900 │ │ │ TABLE SEEK title AS t 214500 589875 │ │ TABLE SEEK movie_companies AS mc 589677 589677 │ TABLE SEEK name AS n 589677 589677 TABLE SEEK company_name AS cn WHERE cn.name LIKE 'Lionsgate%'