Planner — IMDB — JOB-28A

SELECT MIN(cn.name) AS movie_company,
       MIN(mi_idx.info) AS rating,
       MIN(t.title) AS complete_euro_dark_movie
FROM job.complete_cast AS cc,
     job.comp_cast_type AS cct1,
     job.comp_cast_type AS cct2,
     job.company_name AS cn,
     job.company_type AS ct,
     job.info_type AS it1,
     job.info_type AS it2,
     job.keyword AS k,
     job.kind_type AS kt,
     job.movie_companies AS mc,
     job.movie_info AS mi,
     job.movie_info_idx AS mi_idx,
     job.movie_keyword AS mk,
     job.title AS t
WHERE cct1.kind = 'crew'
  AND cct2.kind != 'complete+verified'
  AND cn.country_code != '[us]'
  AND it1.info = 'countries'
  AND it2.info = 'rating'
  AND k.keyword IN ('murder',
                    'murder-in-title',
                    'blood',
                    'violence')
  AND kt.kind IN ('movie',
                  'episode')
  AND mc.note NOT LIKE '%(USA)%'
  AND mc.note LIKE '%(200%)%'
  AND mi.info IN ('Sweden',
                  'Norway',
                  'Germany',
                  'Denmark',
                  'Swedish',
                  'Danish',
                  'Norwegian',
                  'German',
                  'USA',
                  'American')
  AND mi_idx.info < '8.5'
  AND t.production_year > 2000
  AND kt.id = t.kind_id
  AND t.id = mi.movie_id
  AND t.id = mk.movie_id
  AND t.id = mi_idx.movie_id
  AND t.id = mc.movie_id
  AND t.id = cc.movie_id
  AND mk.movie_id = mi.movie_id
  AND mk.movie_id = mi_idx.movie_id
  AND mk.movie_id = mc.movie_id
  AND mk.movie_id = cc.movie_id
  AND mi.movie_id = mi_idx.movie_id
  AND mi.movie_id = mc.movie_id
  AND mi.movie_id = cc.movie_id
  AND mc.movie_id = mi_idx.movie_id
  AND mc.movie_id = cc.movie_id
  AND mi_idx.movie_id = cc.movie_id
  AND k.id = mk.keyword_id
  AND it1.id = mi.info_type_id
  AND it2.id = mi_idx.info_type_id
  AND ct.id = mc.company_type_id
  AND cn.id = mc.company_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 4,537,364 4.5M Rank		Estimation Error Est Err 4,583,508 4.6M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 129,204 129K Rank	Estimation Error Est Err 4,803 4.8K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 22,862,354 23M Rank		Estimation Error Est Err 27,606,421 28M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 10,175,140 10M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 8,207,232 8.2M Rank		Estimation Error Est Err 9,831,574 9.8M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 3,022,954 3M Rank	Estimation Error Est Err 4,805 4.8K Rank	Estimation Error Est Err 737,175 737K Rank
Estimation Error Est Err 12,666,732 13M Rank		Estimation Error Est Err 8,421,085 8.4M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,423,657 2.4M Rank	Estimation Error Est Err 4,813 4.8K Rank	Estimation Error Est Err 1,462,427 1.5M Rank
Estimation Error Est Err 1,009,182 1M Rank		Estimation Error Est Err 1,652,750 1.7M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 2,791,209 2.8M Rank	Estimation Error Est Err 4,803 4.8K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 599,185 599K Rank	Estimation Error Est Err 599,178 599K Rank	Estimation Error Est Err 708,944 709K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 706,060 706K Rank	Estimation Error Est Err 4,803 4.8K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 382,901 383K Rank	Estimation Error Est Err 382,896 383K Rank	Estimation Error Est Err 447,045 447K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 425,072 425K Rank	Estimation Error Est Err 4,803 4.8K Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,887,788 1.9M Rank		Estimation Error Est Err 63,058 63K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,914,445 1.9M Rank	Estimation Error Est Err 4,819 4.8K Rank	Estimation Error Est Err 1,886,139 1.9M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 1 4803 INNER JOIN HASH ON id = company_type_id 4 4 │└TABLE SCAN company_type 1 4803 INNER JOIN HASH ON id6 = status_id 3 3 │└TABLE SCAN comp_cast_type WHERE kind != complete + verified 1 4803 INNER JOIN HASH ON id11 = info_type_id93 1 1 │└TABLE SCAN info_type WHERE info = rating 1 4803 INNER JOIN HASH ON id43 = movie_id92 1 5113 │└INNER JOIN HASH ON id81 = company_id 1 5271 │ │└INNER JOIN HASH ON id43 = movie_id74 1 488 │ │ │└INNER JOIN HASH ON id16 = info_type_id 1 1 │ │ │ │└TABLE SCAN info_type WHERE info = countries 3 529 │ │ │ INNER JOIN HASH ON id43 = movie_id66 1 549 │ │ │ │└INNER JOIN HASH ON id21 = subject_id 1 1 │ │ │ │ │└TABLE SCAN comp_cast_type WHERE kind = crew 1 2208 │ │ │ │ INNER JOIN HASH ON id43 = movie_id59 24 11686 │ │ │ │ │└INNER JOIN HASH ON id26 = kind_id 2 2 │ │ │ │ │ │└TABLE SCAN kind_type WHERE kind27 IN(episode,movie) 75 12254 │ │ │ │ │ INNER JOIN HASH ON id43 = movie_id 129 37091 │ │ │ │ │ │└INNER JOIN HASH ON id31 = keyword_id 4 3 │ │ │ │ │ │ │└TABLE SCAN keyword WHERE keyword IN(blood,murder,murder - in - title,violence) 4523930 4523930 │ │ │ │ │ │ TABLE SCAN movie_keyword 1402423 8747 │ │ │ │ │ TABLE SCAN title WHERE production_year >= 2001 99302 1397 │ │ │ │ TABLE SCAN complete_cast 622984 330 │ │ │ TABLE SCAN movie_info WHERE info BETWEEN American AND USA AND info68 IN(American,Danish,Denmark,German,Germany,Norway,Norwegian,Sweden,Swedish,USA) 248981 2140 │ │ TABLE SCAN movie_companies WHERE note77 LIKE '%(200%)%' AND NOT (note77 LIKE '%(USA)%') 120711 638 │ TABLE SCAN company_name WHERE country_code <> us 1342299 167 TABLE SCAN movie_info_idx WHERE info < 8.5
	Estimate Actual Operator - 1 PROJECT a1 AS movie_company, a2 AS rating, a3 AS complete_euro_dark_movie - 1 AGGREGATE MIN(name) AS a1, MIN(info) AS a2, MIN(title) AS a3 - 0 PROJECT name, info, title - 0 PROJECT name, info, title - 0 INNER JOIN HASH ON tuple(PROJECTION_3524.movie_id,PROJECTION_3524.movie_id,PROJECTION_3524.movie_id,PROJECTION_3524.movie_id,PROJECTION_3524.movie_id,PROJECTION_3524.movie_id,PROJECTION_3524.id,PROJECTION_3524.id) = tuple(PROJECTION_3503.movie_id,PROJECTION_3503.movie_id,PROJECTION_3503.movie_id,PROJECTION_3503.movie_id,PROJECTION_3503.movie_id,PROJECTION_3503.movie_id,PROJECTION_3503.movie_id,PROJECTION_3503.movie_id) - 420182 │└PROJECT movie_id_right, movie_id AS movie_id_right_2, info - 420182 │ PROJECT movie_id, info, movie_id - 420182 │ INNER JOIN HASH ON PROJECTION_3515.movie_id = PROJECTION_3506.movie_id - 1325361 │ │└PROJECT movie_id AS movie_id_right - 1325361 │ │ PROJECT movie_id - 1325361 │ │ INNER JOIN HASH ON PROJECTION_3512.info_type_id = PROJECTION_3509.id - 1 │ │ │└PROJECT id - 1 │ │ │ PROJECT id - 1 │ │ │ TABLE SCAN info_type WHERE info = 'countries' - 14835720 │ │ PROJECT info_type_id, movie_id - 14835720 │ │ PROJECT movie_id, info_type_id - 14835720 │ │ TABLE SCAN movie_info WHERE TRUE - 454230 │ PROJECT movie_id AS movie_id_left, info - 454230 │ PROJECT info, movie_id - 454230 │ INNER JOIN HASH ON PROJECTION_3521.info_type_id = PROJECTION_3518.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'rating' - 1337634 │ PROJECT info_type_id, info, movie_id - 1337634 │ PROJECT movie_id, info, info_type_id - 1337634 │ TABLE SCAN movie_info_idx WHERE info < '8.5' - 0 PROJECT movie_id AS movie_id_left, movie_id AS movie_id_left_2, movie_id AS movie_id_left_3, id, name, title - 0 PROJECT movie_id, movie_id, name, movie_id, title, id - 0 INNER JOIN HASH ON tuple(PROJECTION_3560.movie_id,PROJECTION_3560.movie_id,PROJECTION_3560.id,PROJECTION_3560.id) = tuple(PROJECTION_3527.movie_id,PROJECTION_3527.movie_id,PROJECTION_3527.movie_id,PROJECTION_3527.movie_id) - 20546 │└PROJECT movie_id_right, movie_id AS movie_id_right_2, name - 20546 │ PROJECT movie_id, movie_id, name - 20546 │ INNER JOIN HASH ON PROJECTION_3533.company_type_id = PROJECTION_3530.id - 4 │ │└PROJECT id - 4 │ │ PROJECT id - 4 │ │ TABLE SCAN company_type - 20546 │ PROJECT company_type_id, movie_id, movie_id, name - 20546 │ PROJECT movie_id, movie_id, company_type_id, name - 20546 │ INNER JOIN HASH ON PROJECTION_3545.movie_id = PROJECTION_3536.movie_id - 299742 │ │└PROJECT movie_id AS movie_id_right, company_type_id, name - 299742 │ │ PROJECT movie_id, company_type_id, name - 299742 │ │ INNER JOIN HASH ON PROJECTION_3542.company_id = PROJECTION_3539.id - 211073 │ │ │└PROJECT id, name - 211073 │ │ │ PROJECT id, name - 211073 │ │ │ TABLE SCAN company_name WHERE country_code <> 'us' - 303271 │ │ PROJECT company_id, movie_id, company_type_id - 303271 │ │ PROJECT movie_id, company_id, company_type_id - 303271 │ │ TABLE SCAN movie_companies WHERE notLike(note,'%(USA)%') AND note LIKE '%(200%)%' - 42432 │ PROJECT movie_id AS movie_id_left - 42432 │ PROJECT movie_id - 42432 │ INNER JOIN HASH ON PROJECTION_3557.id = PROJECTION_3548.status_id - 49145 │ │└PROJECT status_id, movie_id - 49145 │ │ PROJECT status_id, movie_id - 49145 │ │ INNER JOIN HASH ON PROJECTION_3554.subject_id = PROJECTION_3551.id - 1 │ │ │└PROJECT id - 1 │ │ │ PROJECT id - 1 │ │ │ TABLE SCAN comp_cast_type WHERE kind = 'crew' - 135086 │ │ PROJECT subject_id, status_id, movie_id - 135086 │ │ PROJECT subject_id, status_id, movie_id - 135086 │ │ TABLE SCAN complete_cast - 3 │ PROJECT id - 3 │ PROJECT id - 3 │ TABLE SCAN comp_cast_type WHERE kind <> 'complete+verified' - 1970380 PROJECT movie_id AS movie_id_left, id, title - 1970380 PROJECT movie_id, title, id - 1970380 INNER JOIN HASH ON PROJECTION_3566.kind_id = PROJECTION_3563.id - 7 │└PROJECT id AS id_right - 7 │ PROJECT id - 7 │ TABLE SCAN kind_type WHERE TRUE - 1970380 PROJECT kind_id, movie_id, title, id_left - 1970380 PROJECT movie_id, title, id, kind_id - 1970380 INNER JOIN HASH ON PROJECTION_3572.keyword_id = PROJECTION_3569.id - 134170 │└PROJECT id AS id_right - 134170 │ PROJECT id - 134170 │ TABLE SCAN keyword WHERE TRUE - 1970380 PROJECT keyword_id, movie_id, title, id AS id_left, kind_id - 1970380 PROJECT movie_id, keyword_id, title, id, kind_id - 1970380 INNER JOIN HASH ON PROJECTION_3578.movie_id = PROJECTION_3575.id - 1381453 │└PROJECT id, title, kind_id - 1381453 │ PROJECT id, title, kind_id - 1381453 │ TABLE SCAN title WHERE production_year > 2000 - 4523930 PROJECT movie_id, keyword_id - 4523930 PROJECT movie_id, keyword_id - 4523930 TABLE SCAN movie_keyword
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 0 4803 PROJECT name, info, title 0 4803 INNER JOIN HASH ON id = keyword_id 0 738406 │└INNER JOIN HASH ON movie_id = id 0 8650 │ │└INNER JOIN HASH ON id = info_type_id 0 9355 │ │ │└INNER JOIN HASH ON movie_id = id 0 9491 │ │ │ │└INNER JOIN HASH ON id = company_type_id 0 9491 │ │ │ │ │└INNER JOIN HASH ON id = status_id 0 10551 │ │ │ │ │ │└INNER JOIN HASH ON id = subject_id 0 48252 │ │ │ │ │ │ │└INNER JOIN HASH ON id = company_id 1 50243 │ │ │ │ │ │ │ │└INNER JOIN HASH ON movie_id = id 7 16813 │ │ │ │ │ │ │ │ │└INNER JOIN HASH ON kind_id = id 1 2 │ │ │ │ │ │ │ │ │ │└FILTER (kind = 'movie') OR (kind = 'episode') 7 7 │ │ │ │ │ │ │ │ │ │ TABLE SCAN kind_type WHERE kind IN('movie','episode') 54 19121 │ │ │ │ │ │ │ │ │ INNER JOIN HASH ON id = movie_id 265 94130 │ │ │ │ │ │ │ │ │ │└INNER JOIN HASH ON movie_id = movie_id 4885 454188 │ │ │ │ │ │ │ │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │ │ │ │ │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ │ │ │ │ │ │ │ │ TABLE SCAN info_type WHERE info = 'rating' 276007 454188 │ │ │ │ │ │ │ │ │ │ │ FILTER movie_id BETWEEN 285 AND 2525745 276007 454230 │ │ │ │ │ │ │ │ │ │ │ TABLE SCAN movie_info_idx WHERE info < '8.5' 135086 132439 │ │ │ │ │ │ │ │ │ │ FILTER movie_id <= 2525745 135086 132439 │ │ │ │ │ │ │ │ │ │ TABLE SCAN complete_cast WHERE movie_id <= 2525793 505662 14456 │ │ │ │ │ │ │ │ │ FILTER id BETWEEN 285 AND 2525745 505662 14458 │ │ │ │ │ │ │ │ │ TABLE SCAN title WHERE production_year > 2000 521825 40191 │ │ │ │ │ │ │ │ FILTER movie_id >= 285 521825 40191 │ │ │ │ │ │ │ │ TABLE SCAN movie_companies WHERE ( NOT contains(note,'(USA)')) AND (note LIKE '%(200%)%') 46999 79439 │ │ │ │ │ │ │ TABLE SCAN company_name WHERE country_code != 'us' 1 1 │ │ │ │ │ │ FILTER id <= 2 1 1 │ │ │ │ │ │ TABLE SCAN comp_cast_type WHERE kind = 'crew' 1 1 │ │ │ │ │ FILTER id >= 3 1 1 │ │ │ │ │ TABLE SCAN comp_cast_type WHERE kind != 'complete+verified' 4 1 │ │ │ │ TABLE SCAN company_type WHERE id <= 2 2967144 2234 │ │ │ FILTER movie_id BETWEEN 285 AND 2525745 2967144 2234 │ │ │ FILTER IN ... 14835720 107452 │ │ │ INNER JOIN HASH ON info = #0 0 10 │ │ │ │└SCAN MATERIALISED 14835720 107452 │ │ │ TABLE SCAN movie_info WHERE info IN('Sweden','Norway','Germany','Denmark','Swedish','Danish','Norwegian','German','USA','American') 2 1 │ │ FILTER id <= 110 2 1 │ │ TABLE SCAN info_type WHERE info = 'countries' 4523930 47509 │ TABLE SCAN movie_keyword WHERE movie_id >= 285 AND movie_id <= 2525745 26834 3 FILTER (keyword = 'murder') OR (keyword = 'murder-in-title') OR (keyword = 'blood') OR (keyword = 'violence') 134170 133442 TABLE SCAN keyword WHERE keyword IN('murder','murder-in-title','blood','violence')
	Estimate Actual Operator 1 1 PROJECT movie_company, rating, complete_euro_dark_movie 1 1 AGGREGATE MIN(name), MIN(info), MIN(title) 4242 10 DISTRIBUTE GATHER 4242 10 AGGREGATE MIN(name), MIN(info), MIN(title) 4242 4803 INNER JOIN HASH ON id = kind_id 2 2 │└DISTRIBUTE GATHER 2 2 │ FILTER (kind = 'movie') OR (kind = 'episode') 7 7 │ DISTRIBUTE ROUND ROBIN 7 7 │ TABLE SCAN kind_type WHERE (kind = 'movie') OR (kind = 'episode') 14850 4979 INNER JOIN HASH ON movie_id = id AND movie_id = id AND movie_id = id AND movie_id = id AND movie_id = id 14850 8121 │└DISTRIBUTE GATHER 14850 8121 │ INNER JOIN HASH ON keyword_id = id 14850 1074761 │ │└DISTRIBUTE GATHER 14850 1074761 │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id 8292 13591 │ │ │└DISTRIBUTE GATHER 8292 13591 │ │ │ INNER JOIN HASH ON id = info_type_id 23 1 │ │ │ │└DISTRIBUTE GATHER 23 1 │ │ │ │ FILTER info = 'rating' 113 113 │ │ │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ │ │ TABLE SCAN info_type WHERE info = 'rating' 8292 40860 │ │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id AND movie_id = movie_id 8292 14660 │ │ │ │└DISTRIBUTE GATHER 8292 14660 │ │ │ │ INNER JOIN HASH ON id = info_type_id 23 1 │ │ │ │ │└DISTRIBUTE GATHER 23 1 │ │ │ │ │ FILTER info = 'countries' 113 113 │ │ │ │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ │ │ │ TABLE SCAN info_type WHERE info = 'countries' 39662 14660 │ │ │ │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 33771 18744 │ │ │ │ │└DISTRIBUTE GATHER 33771 18744 │ │ │ │ │ INNER JOIN HASH ON id = company_type_id 4 4 │ │ │ │ │ │└TABLE SCAN company_type 33771 18744 │ │ │ │ │ INNER JOIN HASH ON company_id = id 33771 20603 │ │ │ │ │ │└DISTRIBUTE GATHER 33771 20603 │ │ │ │ │ │ INNER JOIN HASH ON movie_id = movie_id 33771 42432 │ │ │ │ │ │ │└DISTRIBUTE GATHER 33771 42432 │ │ │ │ │ │ │ INNER JOIN HASH ON id = status_id 1 3 │ │ │ │ │ │ │ │└DISTRIBUTE GATHER 1 3 │ │ │ │ │ │ │ │ FILTER kind <> 'complete+verified' 4 4 │ │ │ │ │ │ │ │ DISTRIBUTE ROUND ROBIN 4 4 │ │ │ │ │ │ │ │ TABLE SCAN comp_cast_type WHERE kind <> 'complete+verified' 67543 49145 │ │ │ │ │ │ │ INNER JOIN HASH ON id = subject_id 1 1 │ │ │ │ │ │ │ │└DISTRIBUTE GATHER 1 1 │ │ │ │ │ │ │ │ FILTER kind = 'crew' 4 4 │ │ │ │ │ │ │ │ DISTRIBUTE ROUND ROBIN 4 4 │ │ │ │ │ │ │ │ TABLE SCAN comp_cast_type WHERE kind = 'crew' 135086 135086 │ │ │ │ │ │ │ DISTRIBUTE ROUND ROBIN 135086 135086 │ │ │ │ │ │ │ TABLE SCAN complete_cast WHERE (((subject_id >= 2) AND (subject_id <= 2)) AND subject_id IN 2) AND (((status_id >= 1) AND (status_id <= 3)) AND status_id IN(1,2,3)) 521826 303271 │ │ │ │ │ │ FILTER note NOT LIKE '%(USA)%' AND note LIKE '%(200%)%' 2609129 2609129 │ │ │ │ │ │ TABLE SCAN movie_companies WHERE ((note NOT LIKE '%(USA)%' AND note LIKE '%(200%)%') AND (((movie_id >= 285) AND (movie_id <= 2528312)) AND TRUE)) AND (((company_type_id >= 1) AND (company_type_id <= 4)) AND company_type_id IN(1,2,3,4)) 47000 66758 │ │ │ │ │ FILTER country_code <> 'us' 234997 122880 │ │ │ │ │ TABLE SCAN company_name WHERE (country_code <> 'us') AND (((id >= 2) AND (id <= 70660)) AND TRUE) 2967144 614251 │ │ │ │ FILTER info IN('Sweden','Norway','Germany','Denmark','Swedish','Danish','Norwegian','German','USA','American') 14835720 1355825 │ │ │ │ TABLE SCAN movie_info WHERE (info IN('Sweden','Norway','Germany','Denmark','Swedish','Danish','Norwegian','German','USA','American') AND ((((movie_id >= 1577) AND (movie_id <= 2525009)) AND ((movie_id >= 1577) AND (movie_id <= 2525009))) AND TRUE)) AND (((info_type_id >= 8) AND (info_type_id <= 8)) AND info_type_id IN 8) 276007 1337634 │ │ │ FILTER info < '8.5' 1380035 1380035 │ │ │ TABLE SCAN movie_info_idx WHERE ((info < '8.5') AND (((((movie_id >= 1577) AND (movie_id <= 2524109)) AND ((movie_id >= 1577) AND (movie_id <= 2524109))) AND ((movie_id >= 1577) AND (movie_id <= 2524109))) AND TRUE)) AND (((info_type_id >= 101) AND (info_type_id <= 101)) AND info_type_id IN 101) 4523930 4523930 │ │ TABLE SCAN movie_keyword WHERE (((((movie_id >= 1577) AND (movie_id <= 2524038)) AND ((movie_id >= 1577) AND (movie_id <= 2524038))) AND ((movie_id >= 1577) AND (movie_id <= 2524038))) AND ((movie_id >= 1577) AND (movie_id <= 2524038))) AND TRUE 26834 3 │ FILTER keyword IN('murder','murder-in-title','blood','violence') 134170 134170 │ DISTRIBUTE ROUND ROBIN 134170 134170 │ TABLE SCAN keyword WHERE keyword IN('murder','murder-in-title','blood','violence') AND (((id >= 1) AND (id <= 134123)) AND TRUE) 343129 1303646 FILTER production_year > 2000 2528312 2405432 TABLE SCAN title WHERE ((production_year > 2000) AND (((((((id >= 203873) AND (id <= 2521247)) AND ((id >= 203873) AND (id <= 2521247))) AND ((id >= 203873) AND (id <= 2521247))) AND ((id >= 203873) AND (id <= 2521247))) AND ((id >= 203873) AND (id <= 2521247))) AND TRUE)) AND (((kind_id >= 1) AND (kind_id <= 7)) AND kind_id IN(1,7))
	Estimate Actual Operator 1 1 AGGREGATE MIN(cn.name), MIN(mi_idx.info), MIN(t.title) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE MIN(cn.name), MIN(mi_idx.info), MIN(t.title) 724 4803 INNER JOIN HASH ON mi_idx.movie_id = t.id 724 8121 │└DISTRIBUTE GATHER 688 8121 │ INNER JOIN HASH ON mi.movie_id = mk.movie_id 688 37091 │ │└DISTRIBUTE GATHER 178000 37091 │ │ INNER JOIN HASH ON mk.keyword_id = k.id 178000 3 │ │ │└DISTRIBUTE GATHER 235000 3 │ │ │ TABLE SCAN keyword WHERE k.keyword IN('murder','murder-in-title','blood','violence') 4 4519843 │ │ TABLE SCAN movie_keyword 221 13591 │ INNER JOIN HASH ON mi.movie_id = mi_idx.movie_id 221 14660 │ │└DISTRIBUTE GATHER 178000 14660 │ │ INNER JOIN HASH ON mc.movie_id = mi.movie_id 178000 614251 │ │ │└DISTRIBUTE GATHER 67500 614251 │ │ │ INNER JOIN HASH ON mi.info_type_id = it1.id 67500 1 │ │ │ │└DISTRIBUTE GATHER 1380000 1 │ │ │ │ TABLE SCAN info_type WHERE it1.info = 'countries' 113 628871 │ │ │ TABLE SCAN movie_info WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Danish','Norwegian','German','USA','American') 67500 18744 │ │ INNER JOIN HASH ON mc.company_type_id = ct.id 67500 4 │ │ │└DISTRIBUTE GATHER 14800000 4 │ │ │ TABLE SCAN company_type 57 18744 │ │ INNER JOIN HASH ON mc.company_id = cn.id 57 20603 │ │ │└DISTRIBUTE GATHER 276000 20603 │ │ │ INNER JOIN HASH ON cc.movie_id = mc.movie_id 276000 42432 │ │ │ │└DISTRIBUTE GATHER 176 42432 │ │ │ │ INNER JOIN HASH ON cc.status_id = cct2.id 176 3 │ │ │ │ │└DISTRIBUTE GATHER 134000 3 │ │ │ │ │ TABLE SCAN comp_cast_type WHERE NOT (cct2.kind = 'complete+verified') 346000 49136 │ │ │ │ INNER JOIN HASH ON cc.subject_id = cct1.id 346000 1 │ │ │ │ │└DISTRIBUTE GATHER 7 1 │ │ │ │ │ TABLE SCAN comp_cast_type WHERE cct1.kind = 'crew' 2530000 49136 │ │ │ │ TABLE SCAN complete_cast WHERE cc.movie_id IS NOT NULL 4520000 301689 │ │ │ TABLE SCAN movie_companies WHERE (mc.note IS NOT NULL) AND ( NOT contains(mc.note,'(USA)')) AND mc.note LIKE '%(200%)%' 113 124084 │ │ TABLE SCAN company_name WHERE (cn.country_code IS NOT NULL) AND ( NOT (cn.country_code = 'us')) 173000 453008 │ INNER JOIN HASH ON mi_idx.info_type_id = it2.id 173000 1 │ │└DISTRIBUTE GATHER 4 1 │ │ TABLE SCAN info_type WHERE it2.info = 'rating' 2610000 1333760 │ TABLE SCAN movie_info_idx WHERE mi_idx.info < '8.5' 688 1071134 INNER JOIN HASH ON t.kind_id = kt.id 688 2 │└DISTRIBUTE GATHER 4 2 │ TABLE SCAN kind_type WHERE kt.kind IN('movie','episode') 135000 1249834 TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year > 2000L)
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1028, MIN(info as info) AS Expr1029, MIN(title as title) AS Expr1030 1 4803 FILTER kind as kind = 'episode' OR kind as kind = 'movie' 1 4979 INNER JOIN LOOP ON Bmk1016 = Bmk1016 1 4979 │└TABLE SEEK kind_type AS kt 1 4979 INNER JOIN LOOP ON t.kind_id = kt.id 1 4979 │└TABLE SEEK kind_type AS kt 1 4979 INNER JOIN LOOP ON Bmk1026 = Bmk1026 0 4979 │└TABLE SEEK title AS t WHERE production_year as production_year > 2000 2 8121 PROJECT BmkToPage Bmk1026 AS Expr1529 2 8121 INNER JOIN LOOP ON mk.movie_id = t.id 1 8121 │└TABLE SEEK title AS t 2 8121 FILTER info as info = 'rating' 10 24867 INNER JOIN LOOP ON Bmk1012 = Bmk1012 1 24867 │└TABLE SEEK info_type AS it2 10 24867 INNER JOIN LOOP ON mi_idx.info_type_id = it2.id 1 24867 │└TABLE SEEK info_type AS it2 10 24867 INNER JOIN LOOP ON mk.movie_id = mi_idx.movie_id 2 24867 │└TABLE SEEK movie_info_idx AS mi_idx WHERE info as info < '8.5' 4 8440 FILTER country_code as country_code <> 'us' 8 9065 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 9065 │└TABLE SEEK company_name AS cn 8 9065 INNER JOIN LOOP ON mc.company_id = cn.id 1 9065 │└TABLE SEEK company_name AS cn 8 9065 FILTER NOT note as note LIKE '%(USA)%' AND note as note LIKE '%(200%)%' 75 36406 INNER JOIN LOOP ON Bmk1018 = Bmk1018 1 36406 │└TABLE SEEK movie_companies AS mc 75 36406 PROJECT BmkToPage Bmk1018 AS Expr1527 75 36406 INNER JOIN LOOP ON mk.movie_id = mc.movie_id 9 36406 │└TABLE SEEK movie_companies AS mc 7 2945 FILTER kind as kind <> 'complete+verified' 10 5085 INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 5085 │└TABLE SEEK comp_cast_type AS cct2 10 5085 INNER JOIN LOOP ON cc.status_id = cct2.id 1 5085 │└TABLE SEEK comp_cast_type AS cct2 10 5085 INNER JOIN HASH ON cc.subject_id = cct1.id 1 1 │└TABLE SCAN comp_cast_type AS cct1 WHERE kind as kind = 'crew' 20 5085 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 5085 │└TABLE SEEK complete_cast AS cc WHERE BLOOM(subject_id as subject_id) 20 16264 INNER JOIN LOOP ON mk.movie_id = cc.movie_id 1 16264 │└TABLE SEEK complete_cast AS cc 14 23916 FILTER info as info = 'American' OR info as info = 'Danish' OR info as info = 'Denmark' OR info as info = 'German' OR info as info = 'Germany' OR info as info = 'Norway' OR info as info = 'Norwegian' OR info as info = 'Sweden' OR info as info = 'Swedish' OR info as info = 'USA' 308 44297 INNER JOIN LOOP ON mi.movie_id = mi.movie_id AND Uniq1021 = Uniq1021 1 44297 │└TABLE SEEK movie_info AS mi 308 44297 INNER JOIN LOOP ON it1.id = mi.info_type_id AND mk.movie_id = mi.movie_id 1 44297 │└TABLE SEEK movie_info AS mi 362 37091 INNER JOIN LOOP ON info as info = 'countries' 1 1 │└MATERIALISE AS m48 1 1 │ TABLE SCAN info_type AS it1 WHERE info as info = 'countries' 362 37091 INNER JOIN LOOP ON Bmk1024 = Bmk1024 1 37091 │└TABLE SEEK movie_keyword AS mk 362 37091 INNER JOIN LOOP ON k.id = mk.keyword_id 90 37091 │└TABLE SEEK movie_keyword AS mk 4 3 TABLE SCAN keyword AS k WHERE keyword as keyword = 'blood' OR keyword as keyword = 'murder' OR keyword as keyword = 'murder-in-title' OR keyword as keyword = 'violence'
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_company, min_68 AS rating, min_69 AS complete_euro_dark_movie 1 1 AGGREGATE MIN(min_70) AS min, MIN(min_71) AS min_68, MIN(min_72) AS min_69 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name) AS min_70, MIN(info_51) AS min_71, MIN(title) AS min_72 - 4803 INNER JOIN HASH ON kind_id = id_31 7 2 │└DISTRIBUTE GATHER 7 2 │ PROJECT id AS id_31 7 2 │ FILTER kind IN('episode','movie') 7 2 │ TABLE SCAN kind_type - 4979 INNER JOIN HASH ON movie_id = id_61 335742 1381102 │└DISTRIBUTE HASH ON id_61 335742 1381102 │ PROJECT id AS id_61, title, kind_id 335742 1381102 │ FILTER production_year > 2000 335742 1381102 │ TABLE SCAN title - 8121 INNER JOIN HASH ON keyword_id = id_27 134170 3 │└DISTRIBUTE GATHER 134170 3 │ PROJECT id AS id_27 134170 3 │ FILTER keyword IN('blood','murder','murder-in-title','violence') 134170 3 │ TABLE SCAN keyword - 8121 INNER JOIN HASH ON movie_id = movie_id_57 4523930 37091 │└DISTRIBUTE HASH ON movie_id_57 4523930 37091 │ PROJECT movie_id AS movie_id_57, keyword_id 4523930 37091 │ TABLE SCAN movie_keyword - 4525 INNER JOIN HASH ON info_type_id_50 = id_22 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_22 113 1 │ FILTER info = 'rating' 113 1 │ TABLE SCAN info_type - 4525 INNER JOIN HASH ON movie_id = movie_id_49 1380035 23199 │└DISTRIBUTE HASH ON movie_id_49 1380035 23199 │ PROJECT movie_id AS movie_id_49, info_type_id AS info_type_id_50, info AS info_51 1380035 23199 │ FILTER info < '8.5' 1380035 23199 │ TABLE SCAN movie_info_idx - 4525 INNER JOIN HASH ON info_type_id = id_18 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_18 113 1 │ FILTER info = 'countries' 113 1 │ TABLE SCAN info_type - 4525 INNER JOIN HASH ON movie_id = movie_id_42 13352148 15215 │└DISTRIBUTE HASH ON movie_id_42 13352148 15215 │ PROJECT movie_id AS movie_id_42, info_type_id 13352148 15215 │ FILTER info IN('American','Danish','Denmark','German','Germany','Norway','Norwegian','Sweden','Swedish','USA') 13352148 15215 │ TABLE SCAN movie_info - 3802 INNER JOIN HASH ON company_type_id = id_13 4 4 │└DISTRIBUTE GATHER 4 4 │ PROJECT id AS id_13 4 4 │ TABLE SCAN company_type - 3802 INNER JOIN HASH ON company_id = id_9 105537 126230 │└DISTRIBUTE GATHER 105537 126230 │ PROJECT id AS id_9, name 105537 126230 │ FILTER country_code <> 'us' 105537 126230 │ TABLE SCAN company_name - 4242 INNER JOIN HASH ON movie_id = movie_id_37 2348216 303271 │└DISTRIBUTE HASH ON movie_id_37 2348216 303271 │ PROJECT movie_id AS movie_id_37, company_id, company_type_id 2348216 303271 │ FILTER NOT (note LIKE '%(USA)%') AND (note LIKE '%(200%)%') 2348216 303271 │ TABLE SCAN movie_companies - 896 INNER JOIN HASH ON status_id = id_4 2 3 │└DISTRIBUTE GATHER 2 3 │ PROJECT id AS id_4 2 3 │ FILTER kind <> 'complete+verified' 2 3 │ TABLE SCAN comp_cast_type - 1665 INNER JOIN HASH ON subject_id = id_0 4 1 │└DISTRIBUTE GATHER 4 1 │ PROJECT id AS id_0 4 1 │ FILTER kind = 'crew' 4 1 │ TABLE SCAN comp_cast_type 135086 1665 TABLE SCAN complete_cast
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(info), MIN(title) 1 4803 INNER JOIN LOOP ON keyword IN('murder','murder-in-title','blood','violence') AND id = subject_id AND (id = subject_id) 1 1 │└TABLE SCAN comp_cast_type AS cct1 WHERE cct1.kind = 'crew' 1 22733 INNER JOIN LOOP ON id = status_id AND (id = status_id) 1 41034 │└INNER JOIN LOOP ON movie_id = id 1 45102 │ │└INNER JOIN LOOP ON id = info_type_id 1 134739 │ │ │└INNER JOIN LOOP ON movie_id = id 1 45636 │ │ │ │└INNER JOIN LOOP ON id = info_type_id 1 52722 │ │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 1 49419 │ │ │ │ │ │└INNER JOIN LOOP ON id = company_type_id AND (id = company_type_id) 1 49419 │ │ │ │ │ │ │└INNER JOIN LOOP ON id = company_id 1 52456 │ │ │ │ │ │ │ │└INNER JOIN LOOP ON movie_id = movie_id AND movie_id = id AND (movie_id = id) 21 11686 │ │ │ │ │ │ │ │ │└INNER JOIN HASH ON kind_id = id 2 2 │ │ │ │ │ │ │ │ │ │└TABLE SCAN kind_type AS kt WHERE kt.kind IN('movie','episode') 73 14771 │ │ │ │ │ │ │ │ │ INNER JOIN LOOP ON id = movie_id 135 37091 │ │ │ │ │ │ │ │ │ │└INNER JOIN LOOP ON keyword_id = id 4 3 │ │ │ │ │ │ │ │ │ │ │└TABLE SEEK keyword AS k 915 37091 │ │ │ │ │ │ │ │ │ │ TABLE SEEK movie_keyword AS mk 37091 37091 │ │ │ │ │ │ │ │ │ TABLE SEEK title AS t WHERE t.production_year > 2000 11686 52470 │ │ │ │ │ │ │ │ TABLE SEEK movie_companies AS mc WHERE (mc.note NOT LIKE '%(USA)%') AND (mc.note LIKE '%(200%)%') 52456 52456 │ │ │ │ │ │ │ TABLE SEEK company_name AS cn WHERE cn.country_code <> 'us' 4 1 │ │ │ │ │ │ TABLE SCAN company_type AS ct 98838 52878 │ │ │ │ │ TABLE SEEK movie_info AS mi WHERE mi.info IN('Sweden','Norway','Germany','Denmark','Swedish','Danish','Norwegian','German','USA','American') 52722 52722 │ │ │ │ TABLE SEEK info_type AS it1 WHERE it1.info = 'countries' 136908 134626 │ │ │ TABLE SEEK movie_info_idx AS mi_idx WHERE mi_idx.info < '8.5' 134739 134739 │ │ TABLE SEEK info_type AS it2 WHERE it2.info = 'rating' 90204 45102 │ TABLE SEEK complete_cast AS cc 3 3 TABLE SCAN comp_cast_type AS cct2 WHERE cct2.kind <> 'complete+verified'