Planner — IMDB — JOB-30B

SELECT MIN(mi.info) AS movie_budget,
       MIN(mi_idx.info) AS movie_votes,
       MIN(n.name) AS writer,
       MIN(t.title) AS complete_gore_movie
FROM job.complete_cast AS cc,
     job.comp_cast_type AS cct1,
     job.comp_cast_type AS cct2,
     job.cast_info AS ci,
     job.info_type AS it1,
     job.info_type AS it2,
     job.keyword AS k,
     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 cct1.kind IN ('cast',
                    'crew')
  AND cct2.kind ='complete+verified'
  AND ci.note IN ('(writer)',
                  '(head writer)',
                  '(written by)',
                  '(story)',
                  '(story editor)')
  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',
                  '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 = cc.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 = cc.movie_id
  AND mi.movie_id = mi_idx.movie_id
  AND mi.movie_id = mk.movie_id
  AND mi.movie_id = cc.movie_id
  AND mi_idx.movie_id = mk.movie_id
  AND mi_idx.movie_id = cc.movie_id
  AND mk.movie_id = cc.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 cct1.id = cc.subject_id
  AND cct2.id = cc.status_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,523 5.9M Rank		Estimation Error Est Err 5,904,053 5.9M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,714 2.7K Rank	Estimation Error Est Err 28 28 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 58,993,404 59M Rank		Estimation Error Est Err 37,509,337 38M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 67,052,228 67M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 9,083,615 9.1M Rank		Estimation Error Est Err 6,308,258 6.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,539,413 3.5M Rank	Estimation Error Est Err 30 30 Rank	Estimation Error Est Err 3,201,925 3.2M Rank
Estimation Error Est Err 27,598,670 28M Rank		Estimation Error Est Err 9,484,978 9.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 474,341 474K Rank	Estimation Error Est Err 38 38 Rank	Estimation Error Est Err 327,344 327K Rank
Estimation Error Est Err 1,145,442 1.1M Rank		Estimation Error Est Err 618,477 618K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,016,812 1M Rank	Estimation Error Est Err 28 28 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 153,613 154K Rank	Estimation Error Est Err 153,611 154K Rank	Estimation Error Est Err 153,651 154K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 153,621 154K Rank	Estimation Error Est Err 28 28 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,948 3.9K Rank	Estimation Error Est Err 3,406 3.4K Rank	Estimation Error Est Err 3,413 3.4K Rank	Estimation Error Est Err 1,060 1.1K Rank	Estimation Error Est Err 3,949 3.9K Rank	Estimation Error Est Err 1,065 1.1K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,740,221 1.7M Rank		Estimation Error Est Err 149 149 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,740,253 1.7M Rank	Estimation Error Est Err 44 44 Rank	Estimation Error Est Err 1,740,235 1.7M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min, min 1 28 INNER JOIN HASH ON id = info_type_id 1 1 │└TABLE SCAN info_type WHERE info = genres 1 28 INNER JOIN HASH ON id87 = keyword_id 10 1060 │└INNER JOIN HASH ON movie_id52 = movie_id82 6 7 │ │└INNER JOIN HASH ON id69 = person_id 12 7 │ │ │└INNER JOIN HASH ON movie_id61 = movie_id52 18 3 │ │ │ │└INNER JOIN HASH ON id6 = info_type_id53 1 1 │ │ │ │ │└TABLE SCAN info_type WHERE info = votes 42 9 │ │ │ │ INNER JOIN HASH ON movie_id52 = movie_id 18 3 │ │ │ │ │└INNER JOIN HASH ON movie_id44 = movie_id 16 2 │ │ │ │ │ │└INNER JOIN HASH ON id11 = subject_id 2 2 │ │ │ │ │ │ │└TABLE SCAN comp_cast_type WHERE kind IN(cast,crew) 19 2 │ │ │ │ │ │ INNER JOIN HASH ON id16 = status_id 1 1 │ │ │ │ │ │ │└TABLE SCAN comp_cast_type WHERE kind = complete + verified 75 2 │ │ │ │ │ │ INNER JOIN HASH ON id21 = movie_id 1330 533 │ │ │ │ │ │ │└TABLE SCAN title WHERE production_year >= 2001 AND title LIKE '%Freddy%' OR title LIKE '%Jason%' 135086 2 │ │ │ │ │ │ TABLE SCAN complete_cast 144880 3 │ │ │ │ │ TABLE SCAN movie_info WHERE info46 IN(Horror,Thriller) 1380035 1380035 │ │ │ │ TABLE SCAN movie_info_idx 1274215 5 │ │ │ TABLE SCAN cast_info WHERE note BETWEEN (head writer) AND (written by) AND note63 IN((head writer),(story editor),story,writer,(written by)) 1778509 4 │ │ TABLE SCAN name WHERE gender = m 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 complete_gore_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_4263.movie_id,PROJECTION_4263.movie_id,PROJECTION_4263.movie_id,PROJECTION_4263.movie_id,PROJECTION_4263.movie_id,PROJECTION_4263.movie_id,PROJECTION_4263.id,PROJECTION_4263.id) = tuple(PROJECTION_4242.movie_id,PROJECTION_4242.movie_id,PROJECTION_4242.movie_id,PROJECTION_4242.movie_id,PROJECTION_4242.movie_id,PROJECTION_4242.movie_id,PROJECTION_4242.movie_id,PROJECTION_4242.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_4248.keyword_id = PROJECTION_4245.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_4254.movie_id = PROJECTION_4251.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_4260.info_type_id = PROJECTION_4257.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 - 79 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 - 79 PROJECT movie_id, movie_id, info, movie_id, name, title, id - 79 INNER JOIN HASH ON tuple(PROJECTION_4281.movie_id,PROJECTION_4281.movie_id,PROJECTION_4281.movie_id,PROJECTION_4281.movie_id) = tuple(PROJECTION_4266.movie_id,PROJECTION_4266.movie_id,PROJECTION_4266.id,PROJECTION_4266.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_4278.id = PROJECTION_4269.movie_id - 459925 │ │└PROJECT movie_id, info - 459925 │ │ PROJECT info, movie_id - 459925 │ │ INNER JOIN HASH ON PROJECTION_4275.info_type_id = PROJECTION_4272.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')) - 761316 PROJECT movie_id_left, movie_id AS movie_id_left_2, name - 761316 PROJECT movie_id, movie_id, name - 761316 INNER JOIN HASH ON PROJECTION_4287.person_id = PROJECTION_4284.id - 1739579 │└PROJECT id, name - 1739579 │ PROJECT id, name - 1739579 │ TABLE SCAN name WHERE gender = 'm' - 1330901 PROJECT person_id, movie_id, movie_id - 1330901 PROJECT movie_id, movie_id, person_id - 1330901 INNER JOIN HASH ON PROJECTION_4293.movie_id = PROJECTION_4290.movie_id - 36244344 │└PROJECT movie_id AS movie_id_right, person_id - 36244344 │ PROJECT movie_id, person_id - 36244344 │ TABLE SCAN cast_info WHERE TRUE - 24592 PROJECT movie_id AS movie_id_left - 24592 PROJECT movie_id - 24592 INNER JOIN HASH ON PROJECTION_4305.id = PROJECTION_4296.subject_id - 24592 │└PROJECT subject_id, movie_id - 24592 │ PROJECT subject_id, movie_id - 24592 │ INNER JOIN HASH ON PROJECTION_4302.status_id = PROJECTION_4299.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN comp_cast_type WHERE kind = 'complete+verified' - 135086 │ PROJECT status_id, subject_id, movie_id - 135086 │ PROJECT subject_id, status_id, movie_id - 135086 │ TABLE SCAN complete_cast - 4 PROJECT id - 4 PROJECT id - 4 TABLE SCAN comp_cast_type WHERE TRUE
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2), MIN(#3) 0 28 PROJECT info, info, name, title 0 28 INNER JOIN HASH ON id = person_id 0 28 │└INNER JOIN HASH ON movie_id = id 0 12 │ │└INNER JOIN HASH ON id = keyword_id 0 478 │ │ │└INNER JOIN HASH ON movie_id = id 0 3 │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │└FILTER id <= 110 2 1 │ │ │ │ │ TABLE SCAN info_type WHERE info = 'genres' 20 3 │ │ │ │ INNER JOIN HASH ON movie_id = id 16 2 │ │ │ │ │└INNER JOIN HASH ON status_id = id 1 1 │ │ │ │ │ │└FILTER id >= 3 1 1 │ │ │ │ │ │ TABLE SCAN comp_cast_type WHERE kind = 'complete+verified' 67 12 │ │ │ │ │ INNER JOIN HASH ON subject_id = id 1 2 │ │ │ │ │ │└FILTER id <= 2 1 2 │ │ │ │ │ │ FILTER (kind = 'cast') OR (kind = 'crew') 4 4 │ │ │ │ │ │ TABLE SCAN comp_cast_type WHERE kind IN('cast','crew') 270 12 │ │ │ │ │ INNER JOIN HASH ON id = movie_id 1328 95399 │ │ │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 24425 459891 │ │ │ │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ │ │ │ │ TABLE SCAN info_type WHERE info = 'votes' 1380035 459891 │ │ │ │ │ │ │ TABLE SCAN movie_info_idx WHERE movie_id >= 285 135086 132439 │ │ │ │ │ │ TABLE SCAN complete_cast WHERE movie_id <= 2525793 505662 11 │ │ │ │ │ FILTER id BETWEEN 285 AND 2525793 505662 11 │ │ │ │ │ TABLE SCAN title WHERE production_year > 2000 AND (contains(title,'Freddy') OR contains(title,'Jason') OR prefix(title,'Saw')) 14835720 24963 │ │ │ │ FILTER (movie_id BETWEEN 285 AND 2525793) AND ((info = 'Horror') OR (info = 'Thriller')) 14835720 552182 │ │ │ │ TABLE SCAN movie_info WHERE info IN('Horror','Thriller') 4523930 291 │ │ │ TABLE SCAN movie_keyword WHERE movie_id >= 285 AND movie_id <= 2525793 26834 5 │ │ FILTER IN ... 134170 387 │ │ INNER JOIN HASH ON keyword = #0 0 7 │ │ │└SCAN MATERIALISED 134170 387 │ │ TABLE SCAN keyword WHERE keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 7248868 5 │ FILTER movie_id BETWEEN 285 AND 2525793 7248868 5 │ FILTER IN ... 36244344 212 │ INNER JOIN HASH ON note = #0 0 5 │ │└SCAN MATERIALISED 36244344 212 │ TABLE SCAN cast_info WHERE note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 2083746 10 FILTER id <= 4061926 2083746 10 TABLE SCAN "name" WHERE gender = 'm'
	Estimate Actual Operator 1 1 PROJECT movie_budget, movie_votes, writer, complete_gore_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 28 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id AND movie_id = id AND movie_id = id 26834 4394 │└DISTRIBUTE GATHER 26834 4394 │ INNER JOIN HASH ON person_id = id 26834 6488 │ │└DISTRIBUTE GATHER 26834 6488 │ │ 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') 42621 240076 │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id 23798 3409 │ │ │└DISTRIBUTE GATHER 23798 3409 │ │ │ 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' 23798 10267 │ │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id 23798 3412 │ │ │ │└DISTRIBUTE GATHER 23798 3412 │ │ │ │ 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' 113818 3412 │ │ │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 96913 15537 │ │ │ │ │└DISTRIBUTE GATHER 96913 15537 │ │ │ │ │ INNER JOIN HASH ON movie_id = movie_id 33771 24592 │ │ │ │ │ │└DISTRIBUTE GATHER 33771 24592 │ │ │ │ │ │ INNER JOIN HASH ON id = status_id 1 1 │ │ │ │ │ │ │└DISTRIBUTE GATHER 1 1 │ │ │ │ │ │ │ FILTER kind = 'complete+verified' 4 4 │ │ │ │ │ │ │ DISTRIBUTE ROUND ROBIN 4 4 │ │ │ │ │ │ │ TABLE SCAN comp_cast_type WHERE kind = 'complete+verified' 67543 135086 │ │ │ │ │ │ INNER JOIN HASH ON id = subject_id 1 2 │ │ │ │ │ │ │└DISTRIBUTE GATHER 1 2 │ │ │ │ │ │ │ FILTER (kind = 'cast') OR (kind = 'crew') 4 4 │ │ │ │ │ │ │ DISTRIBUTE ROUND ROBIN 4 4 │ │ │ │ │ │ │ TABLE SCAN comp_cast_type WHERE (kind = 'cast') OR (kind = 'crew') 135086 135086 │ │ │ │ │ │ DISTRIBUTE ROUND ROBIN 135086 135086 │ │ │ │ │ │ TABLE SCAN complete_cast WHERE (((subject_id >= 1) AND (subject_id <= 2)) AND subject_id IN(1,2)) AND (((status_id >= 4) AND (status_id <= 4)) AND status_id IN 4) 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 (((movie_id >= 608) AND (movie_id <= 2528176)) AND TRUE) 2967144 72258 │ │ │ │ FILTER (info = 'Horror') OR (info = 'Thriller') 14835720 1724005 │ │ │ │ TABLE SCAN movie_info WHERE (((info = 'Horror') OR (info = 'Thriller')) AND ((((movie_id >= 608) AND (movie_id <= 2525364)) AND ((movie_id >= 608) AND (movie_id <= 2525364))) AND TRUE)) AND (((info_type_id >= 3) AND (info_type_id <= 3)) AND info_type_id IN 3) 1380035 1380035 │ │ │ TABLE SCAN movie_info_idx WHERE (((((movie_id >= 138935) AND (movie_id <= 2525069)) AND ((movie_id >= 138935) AND (movie_id <= 2525069))) AND ((movie_id >= 138935) AND (movie_id <= 2525069))) AND TRUE) AND (((info_type_id >= 100) AND (info_type_id <= 100)) AND info_type_id IN 100) 4523930 4523930 │ │ TABLE SCAN movie_keyword WHERE ((((((movie_id >= 138935) AND (movie_id <= 2525069)) AND ((movie_id >= 138935) AND (movie_id <= 2525069))) AND ((movie_id >= 138935) AND (movie_id <= 2525069))) AND ((movie_id >= 138935) AND (movie_id <= 2525069))) AND TRUE) AND (((keyword_id >= 137) AND (keyword_id <= 875)) AND keyword_id IN(870,382,875,872,865,460,137)) 833499 1739579 │ FILTER gender = 'm' 4167491 1846761 │ TABLE SCAN name WHERE (gender = 'm') AND (((id >= 3656) AND (id <= 3986082)) AND TRUE) 505663 533 FILTER (production_year > 2000) AND ((title LIKE '%Freddy%' OR title LIKE '%Jason%') OR title LIKE 'Saw%') 2528312 2528312 TABLE SCAN title WHERE ((production_year > 2000) AND ((title LIKE '%Freddy%' OR title LIKE '%Jason%') OR title LIKE 'Saw%')) AND (((((((id >= 138935) AND (id <= 2520939)) AND ((id >= 138935) AND (id <= 2520939))) AND ((id >= 138935) AND (id <= 2520939))) AND ((id >= 138935) AND (id <= 2520939))) AND ((id >= 138935) AND (id <= 2520939))) AND TRUE)
	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) 920 28 INNER JOIN HASH ON cc.movie_id = mi_idx.movie_id 920 63701 │└DISTRIBUTE GATHER 254 63701 │ INNER JOIN HASH ON mi_idx.info_type_id = it2.id 254 1 │ │└DISTRIBUTE GATHER 135000 1 │ │ TABLE SCAN info_type WHERE it2.info = 'votes' 18 191657 │ INNER JOIN HASH ON mk.movie_id = mi_idx.movie_id 18 76714 │ │└DISTRIBUTE GATHER 12 76714 │ │ INNER JOIN HASH ON mk.keyword_id = k.id 12 7 │ │ │└DISTRIBUTE GATHER 4170000 7 │ │ │ TABLE SCAN keyword WHERE k.keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 36200000 4519852 │ │ TABLE SCAN movie_keyword 14800000 1375949 │ TABLE SCAN movie_info_idx 254 7 INNER JOIN HASH ON ci.person_id = n.id 254 1739579 │└DISTRIBUTE GATHER 113 1739579 │ TABLE SCAN name WHERE (n.gender IS NOT NULL) AND (n.gender = 'm') 18 7 INNER JOIN HASH ON cc.movie_id = ci.movie_id 18 1244716 │└DISTRIBUTE GATHER 4 1244716 │ TABLE SCAN cast_info WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 18 3 INNER JOIN HASH ON cc.movie_id = t.id 18 4943 │└DISTRIBUTE GATHER 920 4943 │ INNER JOIN HASH ON cc.subject_id = cct1.id 920 2 │ │└DISTRIBUTE GATHER 1380000 2 │ │ TABLE SCAN comp_cast_type WHERE cct1.kind IN('cast','crew') 265000 4943 │ INNER JOIN HASH ON cc.status_id = cct2.id 265000 1 │ │└DISTRIBUTE GATHER 113 1 │ │ TABLE SCAN comp_cast_type WHERE cct2.kind = 'complete+verified' 1060000 12308 │ INNER JOIN HASH ON mi.movie_id = cc.movie_id 1060000 72258 │ │└DISTRIBUTE GATHER 281 72258 │ │ INNER JOIN HASH ON mi.info_type_id = it1.id 281 1 │ │ │└DISTRIBUTE GATHER 134000 1 │ │ │ TABLE SCAN info_type WHERE it1.info = 'genres' 4520000 72258 │ │ TABLE SCAN movie_info WHERE mi.info IN('Horror','Thriller') 2530000 131138 │ TABLE SCAN complete_cast WHERE cc.movie_id IS NOT NULL 4 111 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(partialagg1093) AS Expr1024, MIN(partialagg1094) AS Expr1025, MIN(partialagg1095) AS Expr1026, MIN(partialagg1096) AS Expr1027 1 5 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 275 INNER JOIN LOOP ON Bmk1012 = Bmk1012 1 275 │└TABLE SEEK keyword AS k 1 275 INNER JOIN LOOP ON mk.keyword_id = k.id 1 275 │└TABLE SEEK keyword AS k 1 275 AGGREGATE MIN(info as info) AS partialagg1093, MIN(info as info) AS partialagg1094, MIN(name as name) AS partialagg1095, MIN(title as title) AS partialagg1096 GROUP BY SORT keyword_id 49 1060 SORT keyword_id 49 1060 INNER JOIN LOOP ON Bmk1018 = Bmk1018 1 1060 │└TABLE SEEK movie_keyword AS mk 49 1060 INNER JOIN LOOP ON t.id = mk.movie_id 49 1060 │└TABLE SEEK movie_keyword AS mk 1 7 FILTER gender as gender = 'm' 1 7 INNER JOIN LOOP ON Bmk1020 = Bmk1020 1 7 │└TABLE SEEK name AS n 1 7 PROJECT BmkToPage Bmk1020 AS Expr1110 1 7 INNER JOIN LOOP ON ci.person_id = n.id 1 7 │└TABLE SEEK name AS n 1 7 FILTER info as info = 'votes' 2 21 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 21 │└TABLE SEEK info_type AS it2 2 21 INNER JOIN LOOP ON mi_idx.info_type_id = it2.id 1 21 │└TABLE SEEK info_type AS it2 2 21 INNER JOIN LOOP ON t.id = mi_idx.movie_id 2 21 │└TABLE SEEK movie_info_idx AS mi_idx 1 7 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)' 30 327 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 327 │└TABLE SEEK cast_info AS ci 30 327 PROJECT BmkToPage Bmk1006 AS Expr1108 30 327 INNER JOIN LOOP ON t.id = ci.movie_id 30 327 │└TABLE SEEK cast_info AS ci 1 3 INNER JOIN HASH ON mi.info_type_id = it1.id 1 1 │└TABLE SCAN info_type AS it1 WHERE info as info = 'genres' 1 3 INNER JOIN LOOP ON t.id = mi.movie_id 1 3 │└TABLE SEEK movie_info AS mi WHERE (info as info = 'Horror' OR info as info = 'Thriller') AND BLOOM(info_type_id as info_type_id) 483 2 INNER JOIN HASH ON id as id = movie_id as movie_id 669 538 │└TABLE SCAN title AS t WHERE (production_year as production_year > 2000) AND (title as title LIKE '%Freddy%' OR title as title LIKE '%Jason%' OR title as title LIKE 'Saw%') 675 2 INNER JOIN HASH ON cc.subject_id = cct1.id 2 2 │└TABLE SCAN comp_cast_type AS cct1 WHERE kind as kind = 'cast' OR kind as kind = 'crew' 675 2 INNER JOIN HASH ON cc.status_id = cct2.id 1 1 │└TABLE SCAN comp_cast_type AS cct2 WHERE kind as kind = 'complete+verified' 1350 2 TABLE SEEK complete_cast AS cc WHERE BLOOM(movie_id as movie_id) AND BLOOM(status_id as status_id)
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_budget, min_59 AS movie_votes, min_60 AS writer, min_61 AS complete_gore_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_29) AS min_62, MIN(info_37) AS min_63, MIN(name) AS min_64, MIN(title) AS min_65 - 28 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 - 28 INNER JOIN HASH ON person_id = id_47 4167491 1739579 │└DISTRIBUTE HASH ON id_47 4167491 1739579 │ PROJECT id AS id_47, name 4167491 1739579 │ FILTER gender = 'm' 4167491 1739579 │ TABLE SCAN name - 28 INNER JOIN HASH ON keyword_id = id_23 134170 7 │└DISTRIBUTE GATHER 134170 7 │ PROJECT id AS id_23 134170 7 │ FILTER keyword IN('blood','death','female-nudity','gore','hospital','murder','violence') 134170 7 │ TABLE SCAN keyword - 28 INNER JOIN HASH ON movie_id = movie_id_43 4523930 49 │└DISTRIBUTE HASH ON movie_id_43 4523930 49 │ PROJECT movie_id AS movie_id_43, keyword_id 4523930 49 │ TABLE SCAN movie_keyword - 7 INNER JOIN HASH ON info_type_id_36 = id_18 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_18 113 1 │ FILTER info = 'votes' 113 1 │ TABLE SCAN info_type - 7 INNER JOIN HASH ON movie_id = movie_id_35 1380035 14 │└DISTRIBUTE HASH ON movie_id_35 1380035 14 │ PROJECT movie_id AS movie_id_35, info_type_id AS info_type_id_36, info AS info_37 1380035 14 │ TABLE SCAN movie_info_idx - 7 INNER JOIN HASH ON info_type_id = id_14 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_14 113 1 │ FILTER info = 'genres' 113 1 │ TABLE SCAN info_type - 7 INNER JOIN HASH ON movie_id = movie_id_28 13352148 17 │└DISTRIBUTE HASH ON movie_id_28 13352148 17 │ PROJECT movie_id AS movie_id_28, info_type_id, info AS info_29 13352148 17 │ FILTER info IN('Horror','Thriller') 13352148 17 │ TABLE SCAN movie_info - 5 INNER JOIN HASH ON movie_id = movie_id_10 32619910 15 │└DISTRIBUTE HASH ON movie_id_10 32619910 15 │ PROJECT person_id, movie_id AS movie_id_10 32619910 15 │ FILTER note IN('(head writer)','(story editor)','(story)','(writer)','(written by)') 32619910 15 │ TABLE SCAN cast_info - 2 INNER JOIN HASH ON status_id = id_4 4 1 │└DISTRIBUTE GATHER 4 1 │ PROJECT id AS id_4 4 1 │ FILTER kind = 'complete+verified' 4 1 │ TABLE SCAN comp_cast_type - 2 INNER JOIN HASH ON subject_id = id_0 4 2 │└DISTRIBUTE GATHER 4 2 │ PROJECT id AS id_0 4 2 │ FILTER kind IN('cast','crew') 4 2 │ TABLE SCAN comp_cast_type 135086 2 TABLE SCAN complete_cast
	Estimate Actual Operator 1 1 AGGREGATE MIN(info), MIN(info), MIN(name), MIN(title) 1 28 INNER JOIN LOOP ON id = person_id 1 28 │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 12 │ │└INNER JOIN LOOP ON id = info_type_id 1 12 │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 8 │ │ │ │└INNER JOIN LOOP ON id = info_type_id 1 24 │ │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 8 │ │ │ │ │ │└INNER JOIN LOOP ON id = subject_id AND (id = subject_id) 1 8 │ │ │ │ │ │ │└INNER JOIN LOOP ON keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') AND id = status_id AND (id = status_id) 1 1 │ │ │ │ │ │ │ │└TABLE SCAN comp_cast_type AS cct2 WHERE cct2.kind = 'complete+verified' 1 38 │ │ │ │ │ │ │ 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%')) 98 49 │ │ │ │ │ │ │ TABLE SEEK complete_cast AS cc 2 1 │ │ │ │ │ │ TABLE SCAN comp_cast_type AS cct1 WHERE cct1.kind IN('cast','crew') 24 24 │ │ │ │ │ TABLE SEEK movie_info_idx AS mi_idx 24 24 │ │ │ │ TABLE SEEK info_type AS it2 WHERE it2.info = 'votes' 8 12 │ │ │ TABLE SEEK movie_info AS mi WHERE mi.info IN('Horror','Thriller') 12 12 │ │ TABLE SEEK info_type AS it1 WHERE it1.info = 'genres' 12 27 │ TABLE SEEK cast_info AS ci WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 28 28 TABLE SEEK name AS n WHERE n.gender = 'm'