Planner — IMDB — JOB-31B

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 mc.note LIKE '%(Blu-ray)%'
  AND mi.info IN ('Horror',
                  'Thriller')
  AND n.gender = 'm'
  AND t.production_year > 2000
  AND (t.title LIKE '%Freddy%'
       OR t.title LIKE '%Jason%'
       OR t.title LIKE 'Saw%')
  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 5,904,149 5.9M Rank		Estimation Error Est Err 5,904,381 5.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,851 5.9K Rank	Estimation Error Est Err 84 84 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 58,866,286 59M Rank		Estimation Error Est Err 71,138,749 71M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 31,719,695 32M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 8,702,034 8.7M Rank		Estimation Error Est Err 5,718,611 5.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,283,229 3.3M Rank	Estimation Error Est Err 86 86 Rank	Estimation Error Est Err 3,133,738 3.1M Rank
Estimation Error Est Err 27,845,180 28M Rank		Estimation Error Est Err 8,117,506 8.1M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 20,807 21K Rank	Estimation Error Est Err 94 94 Rank	Estimation Error Est Err 1,460,109 1.5M Rank
Estimation Error Est Err 462,006 462K Rank		Estimation Error Est Err 462,979 463K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 924,334 924K Rank	Estimation Error Est Err 84 84 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 154,505 155K Rank	Estimation Error Est Err 154,505 155K Rank	Estimation Error Est Err 154,499 154K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 154,499 154K Rank	Estimation Error Est Err 84 84 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 220,144 220K Rank	Estimation Error Est Err 220,134 220K Rank	Estimation Error Est Err 237,897 238K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 220,134 220K Rank	Estimation Error Est Err 84 84 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,740,228 1.7M Rank		Estimation Error Est Err 436 436 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,740,380 1.7M Rank	Estimation Error Est Err 100 100 Rank	Estimation Error Est Err 1,740,234 1.7M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min, min 1 84 INNER JOIN HASH ON id = info_type_id 1 1 │└TABLE SCAN info_type WHERE info = genres 1 84 INNER JOIN HASH ON id88 = keyword_id 7 2686 │└INNER JOIN HASH ON movie_id75 = movie_id83 1 19 │ │└INNER JOIN HASH ON id6 = info_type_id76 1 1 │ │ │└TABLE SCAN info_type WHERE info = votes 2 57 │ │ INNER JOIN HASH ON movie_id75 = movie_id 1 19 │ │ │└INNER JOIN HASH ON id62 = person_id 1 19 │ │ │ │└INNER JOIN HASH ON movie_id54 = movie_id 1 11 │ │ │ │ │└INNER JOIN HASH ON movie_id45 = movie_id 1 7 │ │ │ │ │ │└INNER JOIN HASH ON id29 = movie_id 7 135 │ │ │ │ │ │ │└INNER JOIN HASH ON id11 = company_id 159 10 │ │ │ │ │ │ │ │└TABLE SCAN company_name WHERE name) Lionsgate 10191 135 │ │ │ │ │ │ │ TABLE SCAN movie_companies WHERE note LIKE '%(Blu-ray)%' 1330 6 │ │ │ │ │ │ TABLE SCAN title WHERE production_year >= 2001 AND title LIKE '%Freddy%' OR title LIKE '%Jason%' 144880 9 │ │ │ │ │ TABLE SCAN movie_info WHERE info47 IN(Horror,Thriller) 1274215 10 │ │ │ │ TABLE SCAN cast_info WHERE note BETWEEN (head writer) AND (written by) AND note56 IN((head writer),(story editor),story,writer,(written by)) 1778509 6 │ │ │ TABLE SCAN name WHERE gender = m 1380035 1380035 │ │ TABLE SCAN movie_info_idx 4523930 4523930 │ TABLE SCAN movie_keyword 131 6 TABLE SCAN keyword WHERE keyword BETWEEN blood AND violence AND keyword IN(blood,death,female - nudity,gore,hospital,murder,violence)
	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_right) AS a1, MIN(info_left) 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_4470.movie_id,PROJECTION_4470.movie_id,PROJECTION_4470.movie_id,PROJECTION_4470.movie_id,PROJECTION_4470.movie_id,PROJECTION_4470.movie_id,PROJECTION_4470.id,PROJECTION_4470.id) = tuple(PROJECTION_4449.movie_id,PROJECTION_4449.movie_id,PROJECTION_4449.movie_id,PROJECTION_4449.movie_id,PROJECTION_4449.movie_id,PROJECTION_4449.movie_id,PROJECTION_4449.movie_id,PROJECTION_4449.movie_id) - 8855087 │└PROJECT movie_id_right, movie_id AS movie_id_right_2, info AS info_right - 8855087 │ PROJECT info, movie_id, movie_id - 8855087 │ INNER JOIN HASH ON PROJECTION_4455.keyword_id = PROJECTION_4452.id - 134170 │ │└PROJECT id - 134170 │ │ PROJECT id - 134170 │ │ TABLE SCAN keyword WHERE TRUE - 8855087 │ PROJECT keyword_id, info, movie_id, movie_id - 8855087 │ PROJECT info, movie_id, movie_id, keyword_id - 8855087 │ INNER JOIN HASH ON PROJECTION_4461.movie_id = PROJECTION_4458.movie_id - 4523930 │ │└PROJECT movie_id AS movie_id_right, keyword_id - 4523930 │ │ PROJECT movie_id, keyword_id - 4523930 │ │ TABLE SCAN movie_keyword - 1533909 │ PROJECT movie_id AS movie_id_left, info - 1533909 │ PROJECT info, movie_id - 1533909 │ INNER JOIN HASH ON PROJECTION_4467.info_type_id = PROJECTION_4464.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 - 307 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 - 307 PROJECT movie_id, movie_id, info, movie_id, name, title, id - 307 INNER JOIN HASH ON tuple(PROJECTION_4488.movie_id,PROJECTION_4488.movie_id,PROJECTION_4488.movie_id,PROJECTION_4488.movie_id) = tuple(PROJECTION_4473.movie_id,PROJECTION_4473.movie_id,PROJECTION_4473.id,PROJECTION_4473.id) - 101 │└PROJECT movie_id AS movie_id_right, id, info, title - 101 │ PROJECT info, movie_id, title, id - 101 │ INNER JOIN HASH ON PROJECTION_4485.id = PROJECTION_4476.movie_id - 459925 │ │└PROJECT movie_id, info - 459925 │ │ PROJECT info, movie_id - 459925 │ │ INNER JOIN HASH ON PROJECTION_4482.info_type_id = PROJECTION_4479.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 - 533 │ PROJECT id, title - 533 │ PROJECT id, title - 533 │ TABLE SCAN title WHERE (production_year > 2000) AND (title LIKE '%Freddy%' OR title LIKE '%Jason%' OR startsWith(title,'Saw')) - 6847 PROJECT movie_id_left, movie_id AS movie_id_left_2, name - 6847 PROJECT movie_id, movie_id, name - 6847 INNER JOIN HASH ON PROJECTION_4494.person_id = PROJECTION_4491.id - 1739579 │└PROJECT id, name - 1739579 │ PROJECT id, name - 1739579 │ TABLE SCAN name WHERE gender = 'm' - 15527 PROJECT person_id, movie_id, movie_id - 15527 PROJECT movie_id, person_id, movie_id - 15527 INNER JOIN HASH ON PROJECTION_4500.company_id = PROJECTION_4497.id - 10 │└PROJECT id - 10 │ PROJECT id - 10 │ TABLE SCAN company_name WHERE startsWith(name,'Lionsgate') - 945262 PROJECT company_id, movie_id, person_id, movie_id - 945262 PROJECT movie_id, person_id, movie_id, company_id - 945262 INNER JOIN HASH ON PROJECTION_4506.movie_id = PROJECTION_4503.movie_id - 7963 │└PROJECT movie_id AS movie_id_right, company_id - 7963 │ PROJECT movie_id, company_id - 7963 │ TABLE SCAN movie_companies WHERE note LIKE '%(Blu-ray)%' - 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) 2 84 PROJECT info, info, name, title 2 84 INNER JOIN HASH ON id = person_id 4 84 │└INNER JOIN HASH ON movie_id = id 1 46 │ │└INNER JOIN HASH ON id = keyword_id 8 1504 │ │ │└INNER JOIN HASH ON movie_id = movie_id 4 11 │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │└FILTER id <= 110 2 1 │ │ │ │ │ TABLE SCAN info_type WHERE info = 'genres' 249 11 │ │ │ │ INNER JOIN HASH ON movie_id = movie_id 208 7 │ │ │ │ │└INNER JOIN HASH ON id = company_id 1044 19 │ │ │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 4972 101 │ │ │ │ │ │ │└INNER JOIN HASH ON 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 505662 102 │ │ │ │ │ │ │ FILTER id BETWEEN 2 AND 2525745 505662 102 │ │ │ │ │ │ │ TABLE SCAN title WHERE production_year > 2000 AND (contains(title,'Freddy') OR contains(title,'Jason') OR prefix(title,'Saw')) 521825 31 │ │ │ │ │ │ TABLE SCAN movie_companies WHERE contains(note,'(Blu-ray)') 46999 3 │ │ │ │ │ TABLE SCAN company_name WHERE name >= 'Lionsgate' AND name < 'Lionsgatf' 14835720 9 │ │ │ │ FILTER (movie_id BETWEEN 2 AND 2525745) AND ((info = 'Horror') OR (info = 'Thriller')) 14835720 19 │ │ │ │ TABLE SCAN movie_info WHERE info IN('Horror','Thriller') 4523930 714 │ │ │ TABLE SCAN movie_keyword WHERE movie_id <= 2525745 26834 6 │ │ FILTER IN ... 134170 520 │ │ INNER JOIN HASH ON keyword = #0 0 7 │ │ │└SCAN MATERIALISED 134170 520 │ │ TABLE SCAN keyword WHERE keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 7248868 15 │ FILTER movie_id BETWEEN 2 AND 2525745 7248868 15 │ FILTER IN ... 36244344 662 │ INNER JOIN HASH ON note = #0 0 5 │ │└SCAN MATERIALISED 36244344 662 │ TABLE SCAN cast_info WHERE note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 2083746 24 FILTER id <= 4061926 2083746 24 TABLE SCAN "name" WHERE gender = 'm'
	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) 26834 10 DISTRIBUTE GATHER 26834 10 AGGREGATE MIN(info), MIN(info), MIN(name), MIN(title) 26834 84 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id AND movie_id = id AND movie_id = id 26834 227 │└DISTRIBUTE GATHER 26834 227 │ INNER JOIN HASH ON person_id = id 26834 254 │ │└DISTRIBUTE GATHER 26834 254 │ │ 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') 131720 11486 │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id 73547 83 │ │ │└DISTRIBUTE GATHER 73547 83 │ │ │ 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' 73547 257 │ │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id 73547 83 │ │ │ │└DISTRIBUTE GATHER 73547 83 │ │ │ │ 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' 351748 83 │ │ │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 299503 100 │ │ │ │ │└DISTRIBUTE HASH ON movie_id, movie_id 299503 100 │ │ │ │ │ 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%' 1497500 7151 │ │ │ │ │ PROJECT person_id, movie_id, movie_id, company_id 1497500 7151 │ │ │ │ │ INNER JOIN HASH ON movie_id = movie_id 521826 7963 │ │ │ │ │ │└DISTRIBUTE HASH ON movie_id 521826 7963 │ │ │ │ │ │ FILTER note LIKE '%(Blu-ray)%' 2609129 2609129 │ │ │ │ │ │ TABLE SCAN movie_companies WHERE note LIKE '%(Blu-ray)%' AND (((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 >= 73574) AND (movie_id <= 2523338)) AND TRUE) WHEN 1 THEN (((movie_id >= 8969) AND (movie_id <= 2517488)) AND TRUE) WHEN 2 THEN (((movie_id >= 8982) AND (movie_id <= 2524105)) AND TRUE) WHEN 3 THEN (((movie_id >= 8962) AND (movie_id <= 2525010)) AND TRUE) WHEN 4 THEN (((movie_id >= 8... 2967144 72258 │ │ │ │ DISTRIBUTE HASH ON movie_id, movie_id 2967144 72258 │ │ │ │ FILTER (info = 'Horror') OR (info = 'Thriller') 14835720 1724005 │ │ │ │ TABLE SCAN movie_info WHERE (((info = 'Horror') OR (info = 'Thriller')) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 1673153) AND (movie_id <= 2452149)) AND ((movie_id >= 1673153) AND (movie_id <= 2452149))) AND struct(movie_id,movie_id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr ... 1380035 1010660 │ │ │ TABLE SCAN movie_info_idx WHERE (((((movie_id >= 1693872) AND (movie_id <= 2487658)) AND ((movie_id >= 1693872) AND (movie_id <= 2487658))) AND ((movie_id >= 1693872) AND (movie_id <= 2487658))) AND struct(movie_id,movie_id,movie_id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr ... 4523930 4031165 │ │ TABLE SCAN movie_keyword WHERE ((((((movie_id >= 1693872) AND (movie_id <= 2487658)) AND ((movie_id >= 1693872) AND (movie_id <= 2487658))) AND ((movie_id >= 1693872) AND (movie_id <= 2487658))) AND ((movie_id >= 1693872) AND (movie_id <= 2487658))) AND struct(movie_id,movie_id,movie_id,movie_id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < ... 833499 1739579 │ FILTER gender = 'm' 4167491 1846761 │ TABLE SCAN name WHERE (gender = 'm') AND (((id >= 71482) AND (id <= 3201552)) AND id IN(400502,400502,1806914,1806914,1806914,1806914,2978121,3201552,1505509,2978121,3201552,1505509,1125977,1125977,238235,386222,238235,386222,228370,228370,228370,228370,291356,857898,291356,857898,291356,857898,1692203,655400,655405,1692203,655400,655405,1692203,655400,655405,1692203,655400,655405,1185775,1185775,3187874,3187874,1532980,1532980,1532980,1532980,71482,71482,1532980,15329... 505663 151 FILTER (production_year > 2000) AND ((title LIKE '%Freddy%' OR title LIKE '%Jason%') OR title LIKE 'Saw%') 2528312 927930 TABLE SCAN title WHERE ((production_year > 2000) AND ((title LIKE '%Freddy%' OR title LIKE '%Jason%') OR title LIKE 'Saw%')) AND (((((((id >= 1751226) AND (id <= 2487658)) AND ((id >= 1751226) AND (id <= 2487658))) AND ((id >= 1751226) AND (id <= 2487658))) AND ((id >= 1751226) AND (id <= 2487658))) AND ((id >= 1751226) AND (id <= 2487658))) AND struct(id,id,id,id,id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < e...
	Estimate Actual Operator 1 1 AGGREGATE MIN(mi.info), MIN(mi_idx.info), MIN(n.name), MIN(t.title) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE MIN(mi.info), MIN(mi_idx.info), MIN(n.name), MIN(t.title) 2230 84 INNER JOIN HASH ON mc.movie_id = ci.movie_id 2230 315 │└DISTRIBUTE GATHER 2230 315 │ INNER JOIN HASH ON mc.movie_id = mk.movie_id 2230 76714 │ │└DISTRIBUTE GATHER 207 76714 │ │ INNER JOIN HASH ON mk.keyword_id = k.id 207 7 │ │ │└DISTRIBUTE GATHER 14800000 7 │ │ │ TABLE SCAN keyword WHERE k.keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 235000 4519852 │ │ TABLE SCAN movie_keyword 936000 134 │ INNER JOIN HASH ON mi_idx.info_type_id = it2.id 936000 1 │ │└DISTRIBUTE GATHER 134000 1 │ │ TABLE SCAN info_type WHERE it2.info = 'votes' 207 406 │ INNER JOIN HASH ON mc.movie_id = mi_idx.movie_id 207 135 │ │└DISTRIBUTE GATHER 12 135 │ │ INNER JOIN HASH ON mc.company_id = cn.id 12 10 │ │ │└DISTRIBUTE GATHER 4170000 10 │ │ │ TABLE SCAN company_name WHERE startswith(cn.name,'Lionsgate') 2530000 7591 │ │ TABLE SCAN movie_companies WHERE (mc.note IS NOT NULL) AND contains(mc.note,'(Blu-ray)') 1380000 1117892 │ TABLE SCAN movie_info_idx 582000 37 INNER JOIN HASH ON ci.person_id = n.id 582000 1739579 │└DISTRIBUTE GATHER 2610000 1739579 │ TABLE SCAN name WHERE (n.gender IS NOT NULL) AND (n.gender = 'm') 2910000 41 INNER JOIN HASH ON mi.movie_id = ci.movie_id 2910000 1244716 │└DISTRIBUTE GATHER 113 1244716 │ TABLE SCAN cast_info WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 281 45 INNER JOIN HASH ON mi.movie_id = t.id 281 72258 │└DISTRIBUTE GATHER 13 72258 │ INNER JOIN HASH ON mi.info_type_id = it1.id 13 1 │ │└DISTRIBUTE GATHER 36200000 1 │ │ TABLE SCAN info_type WHERE it1.info = 'genres' 113 72258 │ TABLE SCAN movie_info WHERE mi.info IN('Horror','Thriller') 4520000 127 TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2000L) AND ((contains(t.title,'Freddy') OR contains(t.title,'Jason')) OR startswith(t.title,'Saw'))
	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 84 FILTER info as info = 'votes' 1 252 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 252 │└TABLE SEEK info_type AS it2 1 252 INNER JOIN LOOP ON mi_idx.info_type_id = it2.id 1 252 │└TABLE SEEK info_type AS it2 1 252 FILTER info as info = 'genres' 1 252 INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 252 │└TABLE SEEK info_type AS it1 1 252 INNER JOIN LOOP ON mi.info_type_id = it1.id 1 252 │└TABLE SEEK info_type AS it1 1 252 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' 1 8058 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 8058 │└TABLE SEEK keyword AS k 1 8058 INNER JOIN LOOP ON mk.keyword_id = k.id 1 8058 │└TABLE SEEK keyword AS k 1 8058 FILTER title as title LIKE '%Freddy%' OR title as title LIKE '%Jason%' OR title as title LIKE 'Saw%' 1 20232 INNER JOIN LOOP ON Bmk1020 = Bmk1020 0 20232 │└TABLE SEEK title AS t WHERE production_year as production_year > 2000 11 35453 PROJECT BmkToPage Bmk1020 AS Expr1064 11 35453 INNER JOIN LOOP ON mk.movie_id = t.id 1 35453 │└TABLE SEEK title AS t 11 35453 INNER JOIN LOOP ON mk.movie_id = mi.movie_id 1 35453 │└TABLE SEEK movie_info AS mi WHERE info as info = 'Horror' OR info as info = 'Thriller' 38 37995 INNER JOIN LOOP ON Bmk1016 = Bmk1016 1 37995 │└TABLE SEEK movie_keyword AS mk 38 37995 INNER JOIN LOOP ON mi_idx.movie_id = mk.movie_id 17 37995 │└TABLE SEEK movie_keyword AS mk 2 264 INNER JOIN LOOP ON mc.movie_id = mi_idx.movie_id 2 264 │└TABLE SEEK movie_info_idx AS mi_idx 1 84 FILTER gender as gender = 'm' 1 103 INNER JOIN LOOP ON Bmk1018 = Bmk1018 1 103 │└TABLE SEEK name AS n 1 103 PROJECT BmkToPage Bmk1018 AS Expr1061 1 103 INNER JOIN LOOP ON ci.person_id = n.id 1 103 │└TABLE SEEK name AS n 1 103 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)' 32 15892 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 15892 │└TABLE SEEK cast_info AS ci 32 15892 PROJECT BmkToPage Bmk1000 AS Expr1059 32 15892 INNER JOIN LOOP ON mc.movie_id = ci.movie_id 30 15892 │└TABLE SEEK cast_info AS ci 1 138 FILTER note as note LIKE '%(Blu-ray)%' 375 1814 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 1814 │└TABLE SEEK movie_companies AS mc 375 1814 PROJECT BmkToPage Bmk1010 AS Expr1057 375 1814 INNER JOIN LOOP ON cn.id = mc.company_id 44 1814 │└TABLE SEEK movie_companies AS mc 8 10 TABLE SCAN company_name AS cn WHERE name as name LIKE 'Lionsgate%'
	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 - 84 INNER JOIN HASH ON movie_id = id_51 302168 533 │└DISTRIBUTE HASH ON id_51 302168 533 │ PROJECT id AS id_51, title 302168 533 │ FILTER (production_year > 2000) AND ((title LIKE '%Freddy%') OR (title LIKE '%Jason%') OR (title LIKE 'Saw%')) 302168 533 │ TABLE SCAN title - 84 INNER JOIN HASH ON person_id = id_43 4167491 1739579 │└DISTRIBUTE HASH ON id_43 4167491 1739579 │ PROJECT id AS id_43, name AS name_44 4167491 1739579 │ FILTER gender = 'm' 4167491 1739579 │ TABLE SCAN name - 84 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 - 84 INNER JOIN HASH ON movie_id = movie_id_39 4523930 49 │└DISTRIBUTE HASH ON movie_id_39 4523930 49 │ PROJECT movie_id AS movie_id_39, keyword_id 4523930 49 │ TABLE SCAN movie_keyword - 19 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 - 19 INNER JOIN HASH ON movie_id = movie_id_31 1380035 14 │└DISTRIBUTE HASH ON movie_id_31 1380035 14 │ PROJECT movie_id AS movie_id_31, info_type_id AS info_type_id_32, info AS info_33 1380035 14 │ TABLE SCAN movie_info_idx - 19 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 - 19 INNER JOIN HASH ON movie_id = movie_id_24 13352148 17 │└DISTRIBUTE HASH ON movie_id_24 13352148 17 │ PROJECT movie_id AS movie_id_24, info_type_id, info AS info_25 13352148 17 │ FILTER info IN('Horror','Thriller') 13352148 17 │ TABLE SCAN movie_info - 12 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 - 12 INNER JOIN HASH ON movie_id = movie_id_18 2348216 7 │└DISTRIBUTE HASH ON movie_id_18 2348216 7 │ PROJECT movie_id AS movie_id_18, company_id 2348216 7 │ FILTER note LIKE '%(Blu-ray)%' 2348216 7 │ TABLE SCAN movie_companies 32619910 10 FILTER note IN('(head writer)','(story editor)','(story)','(writer)','(written by)') 32619910 10 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(info), MIN(info), MIN(name), MIN(title) 1 84 INNER JOIN LOOP ON id = person_id 1 84 │└INNER JOIN LOOP ON id = info_type_id 1 252 │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 84 │ │ │└INNER JOIN LOOP ON id = info_type_id 1 84 │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 53 │ │ │ │ │└INNER JOIN LOOP ON id = company_id 1 119 │ │ │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 77 │ │ │ │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 49 │ │ │ │ │ │ │ │└INNER JOIN LOOP ON 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 76714 76714 │ │ │ │ │ │ │ │ TABLE SEEK title AS t WHERE (t.production_year > 2000) AND ((t.title LIKE '%Freddy%') OR (t.title LIKE '%Jason%') OR (t.title LIKE 'Saw%')) 49 76 │ │ │ │ │ │ │ TABLE SEEK cast_info AS ci WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 77 119 │ │ │ │ │ │ TABLE SEEK movie_companies AS mc WHERE mc.note LIKE '%(Blu-ray)%' 119 119 │ │ │ │ │ TABLE SEEK company_name AS cn WHERE cn.name LIKE 'Lionsgate%' 53 83 │ │ │ │ TABLE SEEK movie_info AS mi WHERE mi.info IN('Horror','Thriller') 84 84 │ │ │ TABLE SEEK info_type AS it1 WHERE it1.info = 'genres' 252 252 │ │ TABLE SEEK movie_info_idx AS mi_idx 252 252 │ TABLE SEEK info_type AS it2 WHERE it2.info = 'votes' 84 84 TABLE SEEK name AS n WHERE n.gender = 'm'