Planner — IMDB — JOB-18B

SELECT MIN(mi.info) AS movie_budget,
       MIN(mi_idx.info) AS movie_votes,
       MIN(t.title) AS movie_title
FROM job.cast_info AS ci,
     job.info_type AS it1,
     job.info_type AS it2,
     job.movie_info AS mi,
     job.movie_info_idx AS mi_idx,
     job.name AS n,
     job.title AS t
WHERE ci.note IN ('(writer)',
                  '(head writer)',
                  '(written by)',
                  '(story)',
                  '(story editor)')
  AND it1.info = 'genres'
  AND it2.info = 'rating'
  AND mi.info IN ('Horror',
                  'Thriller')
  AND mi.note IS NULL
  AND mi_idx.info > '8.0'
  AND n.gender IS NOT NULL
  AND n.gender = 'f'
  AND t.production_year BETWEEN 2008 AND 2014
  AND t.id = mi.movie_id
  AND t.id = mi_idx.movie_id
  AND t.id = ci.movie_id
  AND ci.movie_id = mi.movie_id
  AND ci.movie_id = mi_idx.movie_id
  AND mi.movie_id = mi_idx.movie_id
  AND n.id = ci.person_id
  AND it1.id = mi.info_type_id
  AND it2.id = mi_idx.info_type_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 44,784 45K Rank		Estimation Error Est Err 44,870 45K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 33,145 33K Rank	Estimation Error Est Err 11 11 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 59,156,128 59M Rank		Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 3,107,018 3.1M Rank		Estimation Error Est Err 1,962,158 2M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,424,849 1.4M Rank	Estimation Error Est Err 13 13 Rank	Estimation Error Est Err 1,352,237 1.4M Rank
Estimation Error Est Err 21,942,351 22M Rank		Estimation Error Est Err 2,287,879 2.3M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 949,863 950K Rank	Estimation Error Est Err 21 21 Rank	Estimation Error Est Err 3,155,960 3.2M Rank
Estimation Error Est Err 104,763 105K Rank		Estimation Error Est Err 90,787 91K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 100,379 100K Rank	Estimation Error Est Err 11 11 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 38,478 38K Rank	Estimation Error Est Err 38,478 38K Rank	Estimation Error Est Err 38,834 39K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 38,834 39K Rank	Estimation Error Est Err 11 11 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 43,752 44K Rank	Estimation Error Est Err 43,639 44K Rank	Estimation Error Est Err 59,671 60K Rank	Estimation Error Est Err 13,297 13K Rank	Estimation Error Est Err 44,258 44K Rank	Estimation Error Est Err 11 11 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,762,640 1.8M Rank		Estimation Error Est Err 96 96 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,762,656 1.8M Rank	Estimation Error Est Err 27 27 Rank	Estimation Error Est Err 1,762,642 1.8M Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min, min 477 11 INNER JOIN HASH ON id = info_type_id21 1 1 │└TABLE SCAN info_type WHERE info = genres 477 11 INNER JOIN HASH ON id52 = person_id 1927 268 │└INNER JOIN HASH ON id27 = movie_id44 3559 358 │ │└INNER JOIN HASH ON id27 = movie_id 3576 810 │ │ │└INNER JOIN HASH ON movie_id20 = movie_id 24234 15849 │ │ │ │└INNER JOIN HASH ON id6 = info_type_id 1 1 │ │ │ │ │└TABLE SCAN info_type WHERE info = rating 53907 15849 │ │ │ │ TABLE SCAN movie_info_idx WHERE info > 8.0 144880 28373 │ │ │ TABLE SCAN movie_info WHERE note IS NULL AND info22 IN(Horror,Thriller) 770345 311 │ │ TABLE SCAN title WHERE production_year BETWEEN 2008 AND 2014 1274215 240 │ TABLE SCAN cast_info WHERE note BETWEEN (head writer) AND (written by) AND note46 IN((head writer),(story editor),story,writer,(written by)) 931987 9 TABLE SCAN name WHERE gender = f
	Estimate Actual Operator - ∞ PROJECT a1 AS movie_budget, a2 AS movie_votes, a3 AS movie_title - ∞ AGGREGATE MIN(info_left) AS a1, MIN(info_right) AS a2, MIN(title) AS a3 - ∞ PROJECT info, info, title - ∞ FILTER 0 - ∞ PROJECT info, info, title - ∞ INNER JOIN HASH ON TRUE - 0 │└PROJECT title - 0 │ FILTER 0 - 2528312 │ TABLE SCAN title - ∞ PROJECT info, info - ∞ FILTER 0 - ∞ PROJECT info, info - ∞ INNER JOIN HASH ON TRUE - 0 │└PROJECT imdb_id - 0 │ FILTER 0 - 4167491 │ TABLE SCAN name - ∞ PROJECT info, info - ∞ FILTER 0 - ∞ PROJECT info, info - ∞ INNER JOIN HASH ON TRUE - 0 │└PROJECT info AS info_right - 0 │ FILTER 0 - 1380035 │ TABLE SCAN movie_info_idx - ∞ PROJECT info AS info_left - ∞ FILTER 0 - ∞ PROJECT info - ∞ INNER JOIN HASH ON TRUE - 0 │└PROJECT info - 0 │ FILTER 0 - 14835720 │ PROJECT info - 14835720 │ TABLE SCAN movie_info - ∞ PROJECT __join_result_dummy - ∞ FILTER 0 - ∞ PROJECT __join_result_dummy - ∞ INNER JOIN HASH ON PROJECTION_1536.__lhs_const = PROJECTION_1533.__rhs_const - ∞ │└PROJECT __rhs_const, id - 113 │ PROJECT id - 113 │ TABLE SCAN info_type - ∞ PROJECT __lhs_const, __join_result_dummy, role_id - 4095M PROJECT role_id - 4095M INNER JOIN HASH ON TRUE - 113 │└PROJECT id - 113 │ PROJECT id - 113 │ TABLE SCAN info_type - 36244344 PROJECT role_id - 36244344 PROJECT role_id - 36244344 TABLE SCAN cast_info
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1), MIN(#2) 7 11 PROJECT info, info, title 7 11 INNER JOIN HASH ON id = person_id 12 268 │└INNER JOIN HASH ON movie_id = id 4 358 │ │└INNER JOIN HASH ON id = movie_id 20 732 │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │└FILTER id <= 110 2 1 │ │ │ │ TABLE SCAN info_type WHERE info = 'genres' 1167 732 │ │ │ INNER JOIN HASH ON movie_id = movie_id 4885 15849 │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │└FILTER id >= 99 2 1 │ │ │ │ │ TABLE SCAN info_type WHERE info = 'rating' 276007 15849 │ │ │ │ TABLE SCAN movie_info_idx WHERE info > '8.0' 2967144 1066 │ │ │ FILTER (movie_id BETWEEN 2 AND 2525793) AND ((info = 'Horror') OR (info = 'Thriller')) 2967144 17898 │ │ │ TABLE SCAN movie_info WHERE info IN('Horror','Thriller') AND (note IS NULL) 505662 1652 │ │ FILTER id BETWEEN 2 AND 2525793 505662 1652 │ │ TABLE SCAN title WHERE production_year >= 2008 AND production_year <= 2014 7248868 2131 │ FILTER movie_id BETWEEN 2 AND 2525793 7248868 2131 │ FILTER IN ... 36244344 66591 │ INNER JOIN HASH ON note = #0 0 5 │ │└SCAN MATERIALISED 36244344 66591 │ TABLE SCAN cast_info WHERE note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 2083746 2766 FILTER id <= 4061926 2083746 2766 TABLE SCAN "name" WHERE gender = 'f' AND (gender IS NOT NULL)
	Estimate Actual Operator 1 1 PROJECT movie_budget, movie_votes, movie_title 1 1 AGGREGATE MIN(info), MIN(info), MIN(title) 126416 10 DISTRIBUTE GATHER 126416 10 AGGREGATE MIN(info), MIN(info), MIN(title) 126416 11 PROJECT info, info, title 126416 11 INNER JOIN HASH ON id = movie_id AND id = movie_id AND id = movie_id 126416 784488 │└DISTRIBUTE GATHER 126416 784488 │ FILTER (production_year >= 2008) AND (production_year <= 2014) 2528312 2528312 │ TABLE SCAN title WHERE (production_year >= 2008) AND (production_year <= 2014) 276007 16 INNER JOIN HASH ON person_id = id 276007 412 │└DISTRIBUTE HASH ON person_id 276007 412 │ 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' 276007 2264 │ PROJECT person_id, movie_id, movie_id, info, movie_id, info_type_id, info 276007 2264 │ INNER JOIN HASH ON movie_id = movie_id AND movie_id = movie_id 276007 52629 │ │└DISTRIBUTE HASH ON movie_id, movie_id 276007 52629 │ │ FILTER info > '8.0' 1380035 1380035 │ │ TABLE SCAN movie_info_idx WHERE ((info > '8.0') AND (((info_type_id >= 101) AND (info_type_id <= 101)) AND info_type_id IN 101)) AND (((((movie_id >= 11) AND (movie_id <= 2528298)) AND ((movie_id >= 11) AND (movie_id <= 2528298))) AND ((movie_id >= 11) AND (movie_id <= 2528298))) AND TRUE) 1780068 39664 │ DISTRIBUTE HASH ON movie_id, movie_id 1780068 39664 │ 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' 8513371 39664 │ PROJECT person_id, movie_id, movie_id, info_type_id, info 8513371 39664 │ INNER JOIN HASH ON movie_id = movie_id 2967144 72258 │ │└DISTRIBUTE HASH ON movie_id 2967144 72258 │ │ FILTER ((info = 'Horror') OR (info = 'Thriller')) AND note IS NULL 14835720 1724005 │ │ TABLE SCAN movie_info WHERE ((((info = 'Horror') OR (info = 'Thriller')) AND note IS NULL) AND (((info_type_id >= 3) AND (info_type_id <= 3)) AND info_type_id IN 3)) AND CASE MOD(HASH_REPARTITION(movie_id,movie_id),10) WHEN 1 THEN ((((movie_id >= 300) AND (movie_id <= 2525569)) AND ((movie_id >= 300) AND (movie_id <= 2525569))) AND TRUE) WHEN 3 THEN ((((movie_id >= 284) AND (movie_id <= 2525726)) AND ((movie_id >= 284) AND (movie_id <= 2525726))) AND TRUE) WHEN 5 T... 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 >= 8459) AND (movie_id <= 2525735)) AND TRUE) WHEN 1 THEN (((movie_id >= 4661) AND (movie_id <= 2525766)) AND TRUE) WHEN 2 THEN (((movie_id >= 979) AND (movie_id <= 2525719)) AND TRUE) WHEN 3 THEN (((movie_id >= 679) AND (movie_id <= 2525793)) AND TRUE) WHEN 4 THEN (((movie_id >= 17089) AND (movie_id... 833499 961555 DISTRIBUTE HASH ON id 833499 961555 FILTER gender IS NOT NULL AND (gender = 'f') 4167491 983636 TABLE SCAN name WHERE (gender IS NOT NULL AND (gender = 'f')) AND CASE MOD(HASH_REPARTITION id,10) WHEN 0 THEN (((id >= 23107) AND (id <= 3223119)) AND id IN(229195,3102736,2903795,23107,697371,245205,1336135,400460,1228459,1567954,2858379,829983,2902893,1442914,1442914,3010710,2848273,697194,697194,3106704,2980085,697371,697371,697371,731166,278337,829983,829983,3022178,1641400,683500,1192830,3223119,3139169,2902752,2902752,2902752,2902752,829983,829983,1336135,262948,262...
	Estimate Actual Operator 1 1 AGGREGATE MIN(mi.info), MIN(mi_idx.info), MIN(t.title) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE MIN(mi.info), MIN(mi_idx.info), MIN(t.title) 254 11 INNER JOIN HASH ON mi.movie_id = ci.movie_id 254 358 │└DISTRIBUTE GATHER 12 358 │ INNER JOIN HASH ON mi_idx.info_type_id = it2.id 12 1 │ │└DISTRIBUTE GATHER 1380000 1 │ │ TABLE SCAN info_type WHERE it2.info = 'rating' 11 1355 │ INNER JOIN HASH ON mi.movie_id = mi_idx.movie_id 11 34901 │ │└DISTRIBUTE GATHER 276000 34901 │ │ INNER JOIN HASH ON mi.movie_id = t.id 276000 72258 │ │ │└DISTRIBUTE GATHER 254 72258 │ │ │ INNER JOIN HASH ON mi.info_type_id = it1.id 254 1 │ │ │ │└DISTRIBUTE GATHER 4170000 1 │ │ │ │ TABLE SCAN info_type WHERE it1.info = 'genres' 36200000 72258 │ │ │ TABLE SCAN movie_info WHERE (mi.info IN('Horror','Thriller')) AND (mi.note IS NULL) 113 782069 │ │ TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year >= 2008L) AND (t.production_year <= 2014L) 113 51669 │ TABLE SCAN movie_info_idx WHERE mi_idx.info > '8.0' 38 98503 INNER JOIN HASH ON ci.person_id = n.id 38 1244716 │└DISTRIBUTE GATHER 2530000 1244716 │ TABLE SCAN cast_info WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 14800000 956304 TABLE SCAN name WHERE (n.gender IS NOT NULL) AND (n.gender = 'f')
	Estimate Actual Operator 1 1 AGGREGATE MIN(info as info) AS Expr1014, MIN(info as info) AS Expr1015, MIN(title as title) AS Expr1016 1 11 FILTER gender as gender = 'f' 4 268 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 268 │└TABLE SEEK name AS n 4 268 PROJECT BmkToPage Bmk1010 AS Expr1064 4 268 INNER JOIN LOOP ON ci.person_id = n.id 1 268 │└TABLE SEEK name AS n 4 268 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)' 161 12564 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 12564 │└TABLE SEEK cast_info AS ci 161 12564 SORT Expr1045 161 12564 PROJECT BmkToPage Bmk1000 AS Expr1045 161 12564 INNER JOIN LOOP ON t.id = ci.movie_id 30 12564 │└TABLE SEEK cast_info AS ci 5 358 INNER JOIN LOOP ON Bmk1012 = Bmk1012 0 358 │└TABLE SEEK title AS t WHERE production_year as production_year >= 2008 AND production_year as production_year <= 2014 17 732 PROJECT BmkToPage Bmk1012 AS Expr1062 17 732 INNER JOIN LOOP ON mi_idx.movie_id = t.id 1 732 │└TABLE SEEK title AS t 17 732 INNER JOIN MERGE ON id as id = info_type_id as info_type_id 1 1 │└SORT id 1 1 │ FILTER info as info = 'genres' 113 113 │ TABLE SCAN info_type AS it1 1206 732 SORT info_type_id 1206 810 INNER JOIN LOOP ON mi_idx.movie_id = mi.movie_id 1 810 │└TABLE SEEK movie_info AS mi WHERE note as note IS NULL AND (info as info = 'Horror' OR info as info = 'Thriller') 10726 15849 INNER JOIN HASH ON mi_idx.info_type_id = it2.id 1 1 │└FILTER info as info = 'rating' 113 113 │ INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 113 │ │└TABLE SEEK info_type AS it2 113 113 │ TABLE SEEK info_type AS it2 53631 15849 TABLE SEEK movie_info_idx AS mi_idx WHERE (PROBE(Bitmap1059,info_type_id as info_type_id,N'IN ROW')) AND (info as info > '8.0')
	Estimate Actual Operator 1 ∞ PROJECT min AS movie_budget, min_35 AS movie_votes, min_36 AS movie_title 1 1 AGGREGATE MIN(min_37) AS min, MIN(min_38) AS min_35, MIN(min_39) AS min_36 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(info_11) AS min_37, MIN(info_19) AS min_38, MIN(title) AS min_39 - 11 INNER JOIN HASH ON movie_id = id_28 106024 784488 │└DISTRIBUTE HASH ON id_28 106024 784488 │ PROJECT id AS id_28, title 106024 784488 │ FILTER production_year BETWEEN 2008 AND 2014 106024 784488 │ TABLE SCAN title - 16 INNER JOIN HASH ON person_id = id_24 4167491 961555 │└DISTRIBUTE HASH ON id_24 4167491 961555 │ PROJECT id AS id_24 4167491 961555 │ FILTER gender = 'f' 4167491 961555 │ TABLE SCAN name - 16 INNER JOIN HASH ON info_type_id_18 = id_4 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_4 113 1 │ FILTER info = 'rating' 113 1 │ TABLE SCAN info_type - 16 INNER JOIN HASH ON movie_id = movie_id_17 1380035 15849 │└DISTRIBUTE HASH ON movie_id_17 1380035 15849 │ PROJECT movie_id AS movie_id_17, info_type_id AS info_type_id_18, info AS info_19 1380035 15849 │ FILTER info > '8.0' 1380035 15849 │ TABLE SCAN movie_info_idx - 16 INNER JOIN HASH ON info_type_id = id_0 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_0 113 1 │ FILTER info = 'genres' 113 1 │ TABLE SCAN info_type - 16 INNER JOIN HASH ON movie_id = movie_id_10 12058871 732 │└DISTRIBUTE HASH ON movie_id_10 12058871 732 │ PROJECT movie_id AS movie_id_10, info_type_id, info AS info_11 12058871 732 │ FILTER (info IN('Horror','Thriller')) AND (note IS NULL) 12058871 732 │ TABLE SCAN movie_info 32619910 14 FILTER note IN('(head writer)','(story editor)','(story)','(writer)','(written by)') 32619910 14 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(info), MIN(info), MIN(title) 1 11 INNER JOIN LOOP ON id = person_id 1 268 │└INNER JOIN LOOP ON movie_id = id 1 358 │ │└INNER JOIN LOOP ON info = 'rating' AND id = info_type_id AND (id = info_type_id) 1 1 │ │ │└TABLE SEEK info_type AS it1 20 358 │ │ INNER JOIN LOOP ON movie_id = id 142 6152 │ │ │└INNER JOIN LOOP ON id = movie_id 459 15849 │ │ │ │└INNER JOIN LOOP ON info_type_id = id 1 1 │ │ │ │ │└TABLE SEEK info_type AS it2 12961 15849 │ │ │ │ TABLE SEEK movie_info_idx AS mi_idx WHERE mi_idx.info > '8.0' 15849 15849 │ │ │ TABLE SEEK title AS t WHERE (t.production_year >= 2008) AND (t.production_year <= 2014) 6152 6152 │ │ TABLE SEEK movie_info AS mi WHERE (mi.note IS NULL) AND (mi.info IN('Horror','Thriller')) 358 358 │ TABLE SEEK cast_info AS ci WHERE ci.note IN('(writer)','(head writer)','(written by)','(story)','(story editor)') 268 268 TABLE SEEK name AS n WHERE (n.gender IS NOT NULL) AND (n.gender = 'f')