Planner — IMDB — JOB-30A

SELECT MIN(mi.info) AS movie_budget,
       MIN(mi_idx.info) AS movie_votes,
       MIN(n.name) AS writer,
       MIN(t.title) AS complete_violent_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.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,923,716 5.9M Rank		Estimation Error Est Err 5,975,853 6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 225,316 225K Rank	Estimation Error Est Err 757 757 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 60,374,324 60M Rank		Estimation Error Est Err 29,318,182 29M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 52,608,957 53M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 10,263,446 10M Rank		Estimation Error Est Err 7,489,620 7.5M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,539,413 3.5M Rank	Estimation Error Est Err 759 759 Rank	Estimation Error Est Err 3,201,925 3.2M Rank
Estimation Error Est Err 27,598,670 28M Rank		Estimation Error Est Err 10,865,898 11M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 474,341 474K Rank	Estimation Error Est Err 767 767 Rank	Estimation Error Est Err 327,344 327K Rank
Estimation Error Est Err 849,113 849K Rank		Estimation Error Est Err 889,442 889K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,325,439 1.3M Rank	Estimation Error Est Err 757 757 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 422,380 422K Rank	Estimation Error Est Err 422,378 422K Rank	Estimation Error Est Err 628,519 629K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 493,485 493K Rank	Estimation Error Est Err 757 757 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 793,641 794K Rank	Estimation Error Est Err 793,521 794K Rank	Estimation Error Est Err 851,991 852K Rank	Estimation Error Est Err 333,946 334K Rank	Estimation Error Est Err 882,707 883K Rank	Estimation Error Est Err 757 757 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,262,142 3.3M Rank		Estimation Error Est Err 39,356 39K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,275,268 3.3M Rank	Estimation Error Est Err 773 773 Rank	Estimation Error Est Err 3,260,582 3.3M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min, min 71 757 INNER JOIN HASH ON id = info_type_id87 1 1 │└TABLE SCAN info_type WHERE info = votes 169 2281 INNER JOIN HASH ON movie_id86 = movie_id34 71 757 │└INNER JOIN HASH ON id6 = info_type_id 1 1 │ │└TABLE SCAN info_type WHERE info = genres 71 757 │ INNER JOIN HASH ON id73 = person_id 150 1021 │ │└INNER JOIN HASH ON id58 = movie_id34 111 7769 │ │ │└INNER JOIN HASH ON movie_id50 = movie_id34 148 10295 │ │ │ │└INNER JOIN HASH ON movie_id41 = movie_id34 590 16336 │ │ │ │ │└INNER JOIN HASH ON id11 = subject_id 2 2 │ │ │ │ │ │└TABLE SCAN comp_cast_type WHERE kind IN(cast,crew) 669 16336 │ │ │ │ │ INNER JOIN HASH ON id16 = status_id 1 1 │ │ │ │ │ │└TABLE SCAN comp_cast_type WHERE kind = complete + verified 2676 16336 │ │ │ │ │ INNER JOIN HASH ON movie_id = movie_id34 4236 76714 │ │ │ │ │ │└INNER JOIN HASH ON id21 = 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 135086 14134 │ │ │ │ │ TABLE SCAN complete_cast 144880 3390 │ │ │ │ TABLE SCAN movie_info WHERE info43 IN(Horror,Thriller) 1274215 1860 │ │ │ TABLE SCAN cast_info WHERE note BETWEEN (head writer) AND (written by) AND note52 IN((head writer),(story editor),story,writer,(written by)) 1402423 195 │ │ TABLE SCAN title WHERE production_year >= 2001 1778509 160 │ TABLE SCAN name WHERE gender = m 1380035 1380035 TABLE SCAN movie_info_idx
	Estimate Actual Operator - 1 PROJECT a1 AS movie_budget, a2 AS movie_votes, a3 AS writer, a4 AS complete_violent_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_4192.movie_id,PROJECTION_4192.movie_id,PROJECTION_4192.movie_id,PROJECTION_4192.movie_id,PROJECTION_4192.movie_id,PROJECTION_4192.movie_id,PROJECTION_4192.id,PROJECTION_4192.id) = tuple(PROJECTION_4171.movie_id,PROJECTION_4171.movie_id,PROJECTION_4171.movie_id,PROJECTION_4171.movie_id,PROJECTION_4171.movie_id,PROJECTION_4171.movie_id,PROJECTION_4171.movie_id,PROJECTION_4171.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_4189.id = PROJECTION_4174.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_4186.movie_id = PROJECTION_4177.movie_id - 459925 │ │ │└PROJECT movie_id AS movie_id_right, info - 459925 │ │ │ PROJECT info, movie_id - 459925 │ │ │ INNER JOIN HASH ON PROJECTION_4183.info_type_id = PROJECTION_4180.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_4210.movie_id,PROJECTION_4210.movie_id,PROJECTION_4210.movie_id,PROJECTION_4210.movie_id) = tuple(PROJECTION_4195.movie_id,PROJECTION_4195.movie_id,PROJECTION_4195.id,PROJECTION_4195.id) - 815187 │└PROJECT movie_id AS movie_id_right, id, info, title - 815187 │ PROJECT info, movie_id, title, id - 815187 │ INNER JOIN HASH ON PROJECTION_4201.movie_id = PROJECTION_4198.id - 1381453 │ │└PROJECT id, title - 1381453 │ │ PROJECT id, title - 1381453 │ │ TABLE SCAN title WHERE production_year > 2000 - 1533909 │ PROJECT movie_id, info - 1533909 │ PROJECT info, movie_id - 1533909 │ INNER JOIN HASH ON PROJECTION_4207.info_type_id = PROJECTION_4204.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 - 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_4216.person_id = PROJECTION_4213.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_4222.movie_id = PROJECTION_4219.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_4234.id = PROJECTION_4225.subject_id - 24592 │└PROJECT subject_id, movie_id - 24592 │ PROJECT subject_id, movie_id - 24592 │ INNER JOIN HASH ON PROJECTION_4231.status_id = PROJECTION_4228.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 757 PROJECT info, info, name, title 0 757 INNER JOIN HASH ON id = person_id 0 1021 │└INNER JOIN HASH ON movie_id = id 0 1161 │ │└INNER JOIN HASH ON id = keyword_id 0 51561 │ │ │└INNER JOIN HASH ON movie_id = id 0 432 │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │└FILTER id <= 110 2 1 │ │ │ │ │ TABLE SCAN info_type WHERE info = 'genres' 20 432 │ │ │ │ INNER JOIN HASH ON movie_id = id 16 1875 │ │ │ │ │└INNER JOIN HASH ON status_id = id 1 1 │ │ │ │ │ │└FILTER id >= 3 1 1 │ │ │ │ │ │ TABLE SCAN comp_cast_type WHERE kind = 'complete+verified' 67 19824 │ │ │ │ │ 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 19824 │ │ │ │ │ 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 14941 │ │ │ │ │ FILTER id BETWEEN 285 AND 2525793 505662 14943 │ │ │ │ │ TABLE SCAN title WHERE production_year > 2000 14835720 658 │ │ │ │ FILTER (movie_id BETWEEN 285 AND 2525793) AND ((info = 'Horror') OR (info = 'Thriller')) 14835720 7870 │ │ │ │ TABLE SCAN movie_info WHERE info IN('Horror','Thriller') 4523930 52663 │ │ │ TABLE SCAN movie_keyword WHERE movie_id >= 285 AND movie_id <= 2525793 26834 7 │ │ FILTER IN ... 134170 133765 │ │ INNER JOIN HASH ON keyword = #0 0 7 │ │ │└SCAN MATERIALISED 134170 133765 │ │ TABLE SCAN keyword WHERE keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 7248868 491 │ FILTER movie_id BETWEEN 285 AND 2525793 7248868 491 │ FILTER IN ... 36244344 47183 │ INNER JOIN HASH ON note = #0 0 5 │ │└SCAN MATERIALISED 36244344 47183 │ TABLE SCAN cast_info WHERE note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 2083746 340 FILTER id <= 4061926 2083746 340 TABLE SCAN "name" WHERE gender = 'm'
	Estimate Actual Operator 1 1 PROJECT movie_budget, movie_votes, writer, complete_violent_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 757 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) 343129 1381453 FILTER production_year > 2000 2528312 2528312 TABLE SCAN title WHERE (production_year > 2000) 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) 875 757 INNER JOIN HASH ON cc.movie_id = mi_idx.movie_id 875 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 18 76714 │ │ INNER JOIN HASH ON mk.keyword_id = k.id 18 7 │ │ │└DISTRIBUTE GATHER 36200000 7 │ │ │ TABLE SCAN keyword WHERE k.keyword IN('murder','violence','blood','gore','death','female-nudity','hospital') 2530000 4519852 │ │ TABLE SCAN movie_keyword 14800000 1375949 │ TABLE SCAN movie_info_idx 254 274 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') 19 384 INNER JOIN HASH ON cc.movie_id = ci.movie_id 19 1244716 │└DISTRIBUTE GATHER 4 1244716 │ TABLE SCAN cast_info WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 18 432 INNER JOIN HASH ON cc.movie_id = t.id 18 4943 │└DISTRIBUTE GATHER 12 4943 │ INNER JOIN HASH ON cc.subject_id = cct1.id 12 2 │ │└DISTRIBUTE GATHER 4170000 2 │ │ TABLE SCAN comp_cast_type WHERE cct1.kind IN('cast','crew') 875 4943 │ INNER JOIN HASH ON cc.status_id = cct2.id 875 1 │ │└DISTRIBUTE GATHER 134000 1 │ │ TABLE SCAN comp_cast_type WHERE cct2.kind = 'complete+verified' 875 12308 │ INNER JOIN HASH ON mi.movie_id = cc.movie_id 875 72258 │ │└DISTRIBUTE GATHER 281 72258 │ │ INNER JOIN HASH ON mi.info_type_id = it1.id 281 1 │ │ │└DISTRIBUTE GATHER 113 1 │ │ │ TABLE SCAN info_type WHERE it1.info = 'genres' 1380000 72258 │ │ TABLE SCAN movie_info WHERE mi.info IN('Horror','Thriller') 4520000 131138 │ TABLE SCAN complete_cast WHERE cc.movie_id IS NOT NULL 4 1179942 TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2000L)
	Estimate Actual Operator 1 1 AGGREGATE MIN(info as info) AS Expr1024, MIN(info as info) AS Expr1025, MIN(name as name) AS Expr1026, MIN(title as title) AS Expr1027 1 757 INNER JOIN HASH ON mk.keyword_id = k.id 7 7 │└TABLE SCAN keyword AS k WHERE 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 757 INNER JOIN LOOP ON Bmk1018 = Bmk1018 1 757 │└TABLE SEEK movie_keyword AS mk WHERE BLOOM(keyword_id as keyword_id) 1154 34335 INNER JOIN LOOP ON t.id = mk.movie_id 49 34335 │└TABLE SEEK movie_keyword AS mk 23 274 FILTER gender as gender = 'm' 56 384 INNER JOIN LOOP ON Bmk1020 = Bmk1020 1 384 │└TABLE SEEK name AS n 56 384 SORT Expr1086 56 384 PROJECT BmkToPage Bmk1020 AS Expr1086 56 384 INNER JOIN LOOP ON ci.person_id = n.id 1 384 │└TABLE SEEK name AS n 56 384 SORT person_id 56 384 INNER JOIN LOOP ON Bmk1022 = Bmk1022 0 384 │└TABLE SEEK title AS t WHERE production_year as production_year > 2000 103 3409 SORT Expr1093 103 3409 PROJECT BmkToPage Bmk1022 AS Expr1093 103 3409 INNER JOIN LOOP ON mi_idx.movie_id = t.id 1 3409 │└TABLE SEEK title AS t 103 3409 SORT movie_id 103 3409 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)' 3758 321421 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 321421 │└TABLE SEEK cast_info AS ci 3758 321421 SORT Expr1083 3758 321421 PROJECT BmkToPage Bmk1006 AS Expr1083 3758 321421 INNER JOIN LOOP ON mi_idx.movie_id = ci.movie_id 30 321421 │└TABLE SEEK cast_info AS ci 124 4939 SORT movie_id 124 4939 INNER JOIN HASH ON cc.subject_id = cct1.id 2 2 │└FILTER kind as kind = 'cast' OR kind as kind = 'crew' 4 4 │ INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 4 │ │└TABLE SEEK comp_cast_type AS cct1 4 4 │ TABLE SEEK comp_cast_type AS cct1 124 4939 INNER JOIN HASH ON cc.status_id = cct2.id 1 1 │└FILTER kind as kind = 'complete+verified' 4 4 │ INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 4 │ │└TABLE SEEK comp_cast_type AS cct2 4 4 │ TABLE SEEK comp_cast_type AS cct2 248 4939 INNER JOIN HASH ON movie_id as movie_id = movie_id as movie_id 467 39066 │└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 233 39066 │ INNER JOIN HASH ON mi.info_type_id = it1.id 1 1 │ │└FILTER info as info = 'genres' 113 113 │ │ INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 113 │ │ │└TABLE SEEK info_type AS it1 113 113 │ │ TABLE SEEK info_type AS it1 163 39066 │ INNER JOIN HASH ON mi_idx.movie_id = mi.movie_id 730 72258 │ │└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) 138004 34156 │ TABLE SEEK movie_info_idx AS mi_idx WHERE BLOOM(movie_id as movie_id) AND BLOOM(info_type_id as info_type_id) 135 4370 TABLE SEEK complete_cast AS cc WHERE BLOOM(status_id as status_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 complete_violent_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 - 757 INNER JOIN HASH ON movie_id = id_51 335742 1381453 │└DISTRIBUTE HASH ON id_51 335742 1381453 │ PROJECT id AS id_51, title 335742 1381453 │ FILTER production_year > 2000 335742 1381453 │ TABLE SCAN title - 4394 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 - 6488 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 - 6488 INNER JOIN HASH ON movie_id = movie_id_43 4523930 76714 │└DISTRIBUTE HASH ON movie_id_43 4523930 76714 │ PROJECT movie_id AS movie_id_43, keyword_id 4523930 76714 │ TABLE SCAN movie_keyword - 2537 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 - 2537 INNER JOIN HASH ON movie_id = movie_id_35 1380035 40111 │└DISTRIBUTE HASH ON movie_id_35 1380035 40111 │ PROJECT movie_id AS movie_id_35, info_type_id AS info_type_id_36, info AS info_37 1380035 40111 │ TABLE SCAN movie_info_idx - 2537 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 - 2537 INNER JOIN HASH ON movie_id = movie_id_28 13352148 14781 │└DISTRIBUTE HASH ON movie_id_28 13352148 14781 │ PROJECT movie_id AS movie_id_28, info_type_id, info AS info_29 13352148 14781 │ FILTER info IN('Horror','Thriller') 13352148 14781 │ TABLE SCAN movie_info - 2198 INNER JOIN HASH ON movie_id = movie_id_10 32619910 7916 │└DISTRIBUTE HASH ON movie_id_10 32619910 7916 │ PROJECT person_id, movie_id AS movie_id_10 32619910 7916 │ FILTER note IN('(head writer)','(story editor)','(story)','(writer)','(written by)') 32619910 7916 │ TABLE SCAN cast_info - 1576 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 - 1576 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 1576 TABLE SCAN complete_cast
	Estimate Actual Operator 1 1 AGGREGATE MIN(info), MIN(info), MIN(name), MIN(title) 1 757 INNER JOIN LOOP ON id = person_id 1 1021 │└INNER JOIN LOOP ON id = subject_id AND (id = subject_id) 1 1021 │ │└INNER JOIN LOOP ON id = movie_id AND movie_id = id AND (movie_id = id) 1 6488 │ │ │└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 13438 │ │ │ INNER JOIN LOOP ON movie_id = movie_id AND movie_id = movie_id AND (movie_id = movie_id) 1 21209 │ │ │ │└INNER JOIN LOOP ON id = info_type_id 1 22462 │ │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = movie_id AND (movie_id = movie_id) 8 38991 │ │ │ │ │ │└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 63701 │ │ │ │ │ │ TABLE SEEK cast_info AS ci WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 38991 38991 │ │ │ │ │ TABLE SEEK movie_info AS mi WHERE mi.info IN('Horror','Thriller') 22462 22462 │ │ │ │ TABLE SEEK info_type AS it1 WHERE it1.info = 'genres' 42418 21209 │ │ │ TABLE SEEK complete_cast AS cc 6488 6488 │ │ TABLE SEEK title AS t WHERE t.production_year > 2000 2 1 │ TABLE SCAN comp_cast_type AS cct1 WHERE cct1.kind IN('cast','crew') 1021 1021 TABLE SEEK name AS n WHERE n.gender = 'm'