Planner — IMDB — JOB-7B

SELECT MIN(n.name) AS of_person,
       MIN(t.title) AS biography_movie
FROM job.aka_name AS an,
     job.cast_info AS ci,
     job.info_type AS it,
     job.link_type AS lt,
     job.movie_link AS ml,
     job.name AS n,
     job.person_info AS pi,
     job.title AS t
WHERE an.name LIKE '%a%'
  AND it.info ='mini biography'
  AND lt.link ='features'
  AND n.name_pcode_cf LIKE 'D%'
  AND n.gender='m'
  AND pi.note ='Volker Boehm'
  AND t.production_year BETWEEN 1980 AND 1984
  AND n.id = an.person_id
  AND n.id = pi.person_id
  AND ci.person_id = n.id
  AND t.id = ci.movie_id
  AND ml.linked_movie_id = t.id
  AND lt.id = ml.link_type_id
  AND it.id = pi.info_type_id
  AND pi.person_id = an.person_id
  AND pi.person_id = ci.person_id
  AND an.person_id = ci.person_id
  AND ci.movie_id = ml.linked_movie_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 36,274,422 36M Rank		Estimation Error Est Err 36,274,469 36M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 727 727 Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 37,117,140 37M Rank		Estimation Error Est Err 55,483,761 55M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 37,327,211 37M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 22,348,851 22M Rank		Estimation Error Est Err 22,348,881 22M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 11,209 11K Rank	Estimation Error Est Err 18 18 Rank	Estimation Error Est Err 5,613 5.6K Rank
Estimation Error Est Err 42,399,933 42M Rank		Estimation Error Est Err 65,162,769 65M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 29,496,353 29M Rank	Estimation Error Est Err 26 26 Rank	Estimation Error Est Err 1,035,108 1M Rank
Estimation Error Est Err 3,111 3.1K Rank		Estimation Error Est Err 3,359 3.4K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,182 1.2K Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 78,312 78K Rank	Estimation Error Est Err 78,311 78K Rank	Estimation Error Est Err 78,311 78K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 78,310 78K Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 1,800 1.8K Rank	Estimation Error Est Err 1,736 1.7K Rank	Estimation Error Est Err 1,943 1.9K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 1,736 1.7K Rank	Estimation Error Est Err 16 16 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 76,575 77K Rank		Estimation Error Est Err 102 102 Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 76,618 77K Rank	Estimation Error Est Err 32 32 Rank	Estimation Error Est Err 76,588 77K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 1 16 INNER JOIN HASH ON person_id64 = person_id32 1 8 │└INNER JOIN HASH ON id = link_type_id 1 1 │ │└TABLE SCAN link_type WHERE link = features 1 23 │ INNER JOIN HASH ON linked_movie_id = id41 1 22 │ │└INNER JOIN HASH ON id41 = movie_id 76 276 │ │ │└INNER JOIN HASH ON person_id = person_id32 1 3 │ │ │ │└INNER JOIN HASH ON id19 = person_id 5 64 │ │ │ │ │└INNER JOIN HASH ON id6 = info_type_id 1 1 │ │ │ │ │ │└TABLE SCAN info_type WHERE info = mini biography 58 64 │ │ │ │ │ TABLE SCAN person_info WHERE note = Volker Boehm 93605 3 │ │ │ │ TABLE SCAN name WHERE name_pcode_cf) D AND gender = m 36244344 36244344 │ │ │ TABLE SCAN cast_info 69133 10 │ │ TABLE SCAN title WHERE production_year BETWEEN 1980 AND 1984 29997 29997 │ TABLE SCAN movie_link 684809 2 TABLE SCAN aka_name WHERE name65 LIKE '%a%'
	Estimate Actual Operator - 1 PROJECT a1 AS of_person, a2 AS biography_movie - 1 AGGREGATE MIN(name) AS a1, MIN(title) AS a2 - 0 PROJECT name, title - 0 PROJECT name, title - 0 INNER JOIN HASH ON tuple(PROJECTION_5377.person_id,PROJECTION_5377.person_id,PROJECTION_5377.person_id,PROJECTION_5377.person_id) = tuple(PROJECTION_5362.id,PROJECTION_5362.id,PROJECTION_5362.person_id,PROJECTION_5362.person_id) - 3 │└PROJECT id, person_id AS person_id_right, name - 3 │ PROJECT name, id, person_id - 3 │ INNER JOIN HASH ON PROJECTION_5368.info_type_id = PROJECTION_5365.id - 1 │ │└PROJECT id AS id_right - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'mini biography' - 3 │ PROJECT info_type_id, name, id AS id_left, person_id - 3 │ PROJECT name, id, person_id, info_type_id - 3 │ INNER JOIN HASH ON PROJECTION_5374.id = PROJECTION_5371.person_id - 64 │ │└PROJECT person_id, info_type_id - 64 │ │ PROJECT person_id, info_type_id - 64 │ │ TABLE SCAN person_info WHERE note = 'Volker Boehm' - 91976 │ PROJECT id, name - 91976 │ PROJECT id, name - 91976 │ TABLE SCAN name WHERE (gender = 'm') AND startsWith(name_pcode_cf,'D') - 0 PROJECT person_id_left, person_id_right AS person_id_left_2, title - 0 PROJECT person_id_left, person_id_right, title - 0 INNER JOIN HASH ON tuple(PROJECTION_5389.movie_id,PROJECTION_5389.id) = tuple(PROJECTION_5380.linked_movie_id,PROJECTION_5380.linked_movie_id) - 5186 │└PROJECT linked_movie_id - 5186 │ PROJECT linked_movie_id - 5186 │ INNER JOIN HASH ON PROJECTION_5386.link_type_id = PROJECTION_5383.id - 1 │ │└PROJECT id - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN link_type WHERE link = 'features' - 29997 │ PROJECT link_type_id, linked_movie_id - 29997 │ PROJECT linked_movie_id, link_type_id - 29997 │ TABLE SCAN movie_link - 1072425 PROJECT movie_id, id, person_id, person_id, title - 1072425 PROJECT person_id, person_id, movie_id, title, id - 1072425 INNER JOIN HASH ON PROJECTION_5395.movie_id = PROJECTION_5392.id - 76065 │└PROJECT id, title - 76065 │ PROJECT id, title - 76065 │ TABLE SCAN title WHERE (production_year >= 1980) AND (production_year <= 1984) - 27144680 PROJECT movie_id, person_id, person_id - 27144680 PROJECT person_id, person_id, movie_id - 27144680 INNER JOIN HASH ON PROJECTION_5401.person_id = PROJECTION_5398.person_id - 36244344 │└PROJECT person_id AS person_id_right, movie_id - 36244344 │ PROJECT person_id, movie_id - 36244344 │ TABLE SCAN cast_info - 674692 PROJECT person_id AS person_id_left - 674692 PROJECT person_id - 674692 TABLE SCAN aka_name WHERE name LIKE '%a%'
	Estimate Actual Operator - 1 AGGREGATE MIN(#0), MIN(#1) 0 16 PROJECT name, title 0 16 INNER JOIN HASH ON id = movie_id 0 46 │└INNER JOIN HASH ON id = link_type_id 0 176 │ │└INNER JOIN HASH ON linked_movie_id = movie_id 3 353 │ │ │└INNER JOIN HASH ON person_id = person_id 0 3 │ │ │ │└INNER JOIN HASH ON id = person_id 0 96 │ │ │ │ │└INNER JOIN HASH ON person_id = person_id 10 62 │ │ │ │ │ │└INNER JOIN HASH ON info_type_id = id 2 1 │ │ │ │ │ │ │└FILTER id BETWEEN 15 AND 39 2 1 │ │ │ │ │ │ │ TABLE SCAN info_type WHERE info = 'mini biography' 621 62 │ │ │ │ │ │ FILTER person_id <= 4061926 621 64 │ │ │ │ │ │ TABLE SCAN person_info WHERE note = 'Volker Boehm' 180268 2632 │ │ │ │ │ FILTER person_id <= 4061926 180268 2644 │ │ │ │ │ TABLE SCAN aka_name WHERE contains(name,'a') 2083746 74 │ │ │ │ FILTER id BETWEEN 4 AND 4061926 2083746 74 │ │ │ │ TABLE SCAN "name" WHERE name_pcode_cf >= 'D' AND name_pcode_cf < 'E' AND gender = 'm' 36244344 230 │ │ │ FILTER movie_id BETWEEN 284 AND 2524994 36244344 230 │ │ │ TABLE SCAN cast_info WHERE person_id >= 4 29997 94 │ │ TABLE SCAN movie_link 2 1 │ FILTER id <= 17 2 1 │ TABLE SCAN link_type WHERE link = 'features' 505662 3 FILTER id BETWEEN 284 AND 2524994 505662 3 TABLE SCAN title WHERE production_year >= 1980 AND production_year <= 1984
	Estimate Actual Operator 1 1 PROJECT of_person, biography_movie 1 1 AGGREGATE MIN(name), MIN(title) 6493 10 DISTRIBUTE GATHER 6493 10 AGGREGATE MIN(name), MIN(title) 6493 16 INNER JOIN HASH ON movie_id = id AND linked_movie_id = id 6493 46 │└DISTRIBUTE GATHER 6493 46 │ INNER JOIN HASH ON id = info_type_id 23 1 │ │└DISTRIBUTE GATHER 23 1 │ │ FILTER info = 'mini biography' 113 113 │ │ DISTRIBUTE ROUND ROBIN 113 113 │ │ TABLE SCAN info_type WHERE info = 'mini biography' 7058 46 │ INNER JOIN HASH ON id = person_id AND person_id = person_id AND person_id = person_id 7058 11752 │ │└DISTRIBUTE GATHER 7058 11752 │ │ INNER JOIN HASH ON person_id = id AND person_id = id 7058 348475 │ │ │└DISTRIBUTE GATHER 7058 348475 │ │ │ INNER JOIN HASH ON id = link_type_id 4 1 │ │ │ │└DISTRIBUTE GATHER 4 1 │ │ │ │ FILTER link = 'features' 18 18 │ │ │ │ DISTRIBUTE ROUND ROBIN 18 18 │ │ │ │ TABLE SCAN link_type WHERE link = 'features' 29997 1642879 │ │ │ PROJECT person_id, person_id, movie_id, linked_movie_id, link_type_id 29997 1642879 │ │ │ INNER JOIN HASH ON linked_movie_id = movie_id 29997 29997 │ │ │ │└TABLE SCAN movie_link WHERE ((link_type_id >= 9) AND (link_type_id <= 9)) AND link_type_id IN 9 1608530 27144680 │ │ │ INNER JOIN HASH ON person_id = person_id 180269 674692 │ │ │ │└DISTRIBUTE GATHER 180269 674692 │ │ │ │ FILTER name LIKE '%a%' 901343 901343 │ │ │ │ TABLE SCAN aka_name WHERE name LIKE '%a%' 36244344 36244344 │ │ │ TABLE SCAN cast_info WHERE (((person_id >= 5) AND (person_id <= 4167489)) AND TRUE) AND (((movie_id >= 284) AND (movie_id <= 2524994)) AND TRUE) 833499 91976 │ │ FILTER name_pcode_cf LIKE 'D%' AND (gender = 'm') 4167491 3306735 │ │ TABLE SCAN name WHERE (name_pcode_cf LIKE 'D%' AND (gender = 'm')) AND ((((id >= 56) AND (id <= 4061723)) AND ((id >= 56) AND (id <= 4061723))) AND TRUE) 592733 25 │ FILTER note = 'Volker Boehm' 2963664 742814 │ TABLE SCAN person_info WHERE ((note = 'Volker Boehm') AND (((((person_id >= 341321) AND (person_id <= 432801)) AND ((person_id >= 341321) AND (person_id <= 432801))) AND ((person_id >= 341321) AND (person_id <= 432801))) AND TRUE)) AND (((info_type_id >= 19) AND (info_type_id <= 19)) AND info_type_id IN 19) 90297 38798 FILTER (production_year >= 1980) AND (production_year <= 1984) 2528312 1174569 TABLE SCAN title WHERE ((production_year >= 1980) AND (production_year <= 1984)) AND ((((id >= 1664924) AND (id <= 2423307)) AND ((id >= 1664924) AND (id <= 2423307))) AND struct(id,id) IN( < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , < expr > , <...
	Estimate Actual Operator 1 1 AGGREGATE MIN(n.name), MIN(t.title) 1 2 DISTRIBUTE GATHER 1 2 AGGREGATE MIN(n.name), MIN(t.title) 1750 16 INNER JOIN HASH ON ci.movie_id = ml.linked_movie_id 1750 5186 │└DISTRIBUTE GATHER 848 5186 │ INNER JOIN HASH ON ml.link_type_id = lt.id 848 1 │ │└DISTRIBUTE GATHER 901000 1 │ │ TABLE SCAN link_type WHERE lt.link = 'features' 113 27105 │ TABLE SCAN movie_link 892 32 INNER JOIN HASH ON ci.movie_id = t.id 892 353 │└DISTRIBUTE GATHER 96 353 │ INNER JOIN HASH ON an.person_id = ci.person_id 96 3 │ │└DISTRIBUTE GATHER 60 3 │ │ INNER JOIN HASH ON n.id = an.person_id 60 3 │ │ │└DISTRIBUTE GATHER 60 3 │ │ │ INNER JOIN HASH ON pi.person_id = n.id 60 64 │ │ │ │└DISTRIBUTE GATHER 1880 64 │ │ │ │ INNER JOIN HASH ON pi.info_type_id = it.id 1880 1 │ │ │ │ │└DISTRIBUTE GATHER 18 1 │ │ │ │ │ TABLE SCAN info_type WHERE it.info = 'mini biography' 30000 64 │ │ │ │ TABLE SCAN person_info WHERE (pi.note IS NOT NULL) AND (pi.note = 'Volker Boehm') 2530000 60493 │ │ │ TABLE SCAN name WHERE (n.name_pcode_cf IS NOT NULL) AND (n.gender IS NOT NULL) AND startswith(n.name_pcode_cf,'D') AND (n.gender = 'm') 36200000 558305 │ │ TABLE SCAN aka_name WHERE contains(an.name,'a') 4170000 21675913 │ TABLE SCAN cast_info 2960000 26969 TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year >= 1980L) AND (t.production_year <= 1984L)
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1016, MIN(title as title) AS Expr1017 1 16 INNER JOIN LOOP ON Bmk1014 = Bmk1014 0 16 │└TABLE SEEK title AS t WHERE production_year as production_year >= 1980 AND production_year as production_year <= 1984 3 46 PROJECT BmkToPage Bmk1014 AS Expr1077 3 46 INNER JOIN LOOP ON ml.linked_movie_id = t.id 1 46 │└TABLE SEEK title AS t 3 46 FILTER link as link = 'features' 52 176 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 176 │└TABLE SEEK link_type AS lt 52 176 INNER JOIN LOOP ON ml.link_type_id = lt.id 1 176 │└TABLE SEEK link_type AS lt 2 176 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 176 │└TABLE SEEK movie_link AS ml 52 176 INNER JOIN LOOP ON ci.movie_id = ml.linked_movie_id 1 176 │└TABLE SEEK movie_link AS ml 2118 353 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 353 │└TABLE SEEK cast_info AS ci 2118 353 PROJECT BmkToPage Bmk1002 AS Expr1073 2118 353 INNER JOIN LOOP ON pi.person_id = ci.person_id 564 353 │└TABLE SEEK cast_info AS ci 3 3 FILTER name as name LIKE '%a%' 4 4 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 4 │└TABLE SEEK aka_name AS an 4 4 INNER JOIN LOOP ON pi.person_id = an.person_id 4 4 │└TABLE SEEK aka_name AS an 1 3 FILTER name_pcode_cf as name_pcode_cf LIKE 'D%' AND gender as gender = 'm' 1 64 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 64 │└TABLE SEEK name AS n 1 64 PROJECT BmkToPage Bmk1010 AS Expr1072 1 64 INNER JOIN LOOP ON pi.person_id = n.id 1 64 │└TABLE SEEK name AS n 1 64 FILTER info as info = 'mini biography' 32 64 INNER JOIN LOOP ON Bmk1004 = Bmk1004 1 64 │└TABLE SEEK info_type AS it 32 64 INNER JOIN LOOP ON pi.info_type_id = it.id 1 64 │└TABLE SEEK info_type AS it 32 64 TABLE SCAN person_info AS pi WHERE note as note = 'Volker Boehm'
	Estimate Actual Operator 1 ∞ PROJECT min AS of_person, min_42 AS biography_movie 1 1 AGGREGATE MIN(min_43) AS min, MIN(min_44) AS min_42 - 16 DISTRIBUTE GATHER - 16 AGGREGATE MIN(name_19) AS min_43, MIN(title) AS min_44 - 16 INNER JOIN HASH ON movie_id = id_35 70683 76065 │└DISTRIBUTE HASH ON id_35 70683 76065 │ PROJECT id AS id_35, title 70683 76065 │ FILTER production_year BETWEEN 1980 AND 1984 70683 76065 │ TABLE SCAN title - 16 INNER JOIN HASH ON info_type_id = id_5 113 1 │└DISTRIBUTE GATHER 113 1 │ PROJECT id AS id_5 113 1 │ FILTER info = 'mini biography' 113 1 │ TABLE SCAN info_type - 16 INNER JOIN HASH ON person_id_1 = person_id_29 2667298 64 │└DISTRIBUTE HASH ON person_id_29 2667298 64 │ PROJECT person_id AS person_id_29, info_type_id 2667298 64 │ FILTER note = 'Volker Boehm' 2667298 64 │ TABLE SCAN person_info - 16 INNER JOIN HASH ON person_id_1 = id_18 3750736 3 │└DISTRIBUTE HASH ON id_18 3750736 3 │ PROJECT id AS id_18, name AS name_19 3750736 3 │ FILTER (gender = 'm') AND (name_pcode_cf >= 'D') AND (name_pcode_cf < 'E') AND (name_pcode_cf LIKE 'D%') 3750736 3 │ TABLE SCAN name - 16 INNER JOIN HASH ON link_type_id = id_9 18 1 │└DISTRIBUTE GATHER 18 1 │ PROJECT id AS id_9 18 1 │ FILTER link = 'features' 18 1 │ TABLE SCAN link_type - 16 INNER JOIN HASH ON movie_id = linked_movie_id 29997 435 │└DISTRIBUTE GATHER 29997 435 │ TABLE SCAN movie_link - 6 INNER JOIN HASH ON person_id_1 = person_id 811209 3 │└DISTRIBUTE GATHER 811209 3 │ FILTER name LIKE '%a%' 811209 3 │ TABLE SCAN aka_name 36244344 3 PROJECT person_id AS person_id_1, movie_id 36244344 3 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 1 16 INNER JOIN LOOP ON id = info_type_id 1 16 │└INNER JOIN LOOP ON person_id = person_id AND person_id = id AND (person_id = id) 1 8 │ │└INNER JOIN LOOP ON person_id = person_id AND id = person_id AND (id = person_id) 39 1076 │ │ │└INNER JOIN LOOP ON id = person_id 1990 33415 │ │ │ │└INNER JOIN LOOP ON movie_id = id 51 435 │ │ │ │ │└INNER JOIN LOOP ON id = linked_movie_id 1666 5186 │ │ │ │ │ │└INNER JOIN LOOP ON link_type_id = id 1 1 │ │ │ │ │ │ │└TABLE SCAN link_type AS lt WHERE lt.link = 'features' 1875 5186 │ │ │ │ │ │ TABLE SEEK movie_link AS ml 5186 5186 │ │ │ │ │ TABLE SEEK title AS t WHERE (t.production_year >= 1980) AND (t.production_year <= 1984) 16965 33416 │ │ │ │ TABLE SEEK cast_info AS ci 33415 33415 │ │ │ TABLE SEEK name AS n WHERE (n.name_pcode_cf LIKE 'D%') AND (n.gender = 'm') 1076 1076 │ │ TABLE SEEK person_info AS pi WHERE pi.note = 'Volker Boehm' 16 16 │ TABLE SEEK aka_name AS an WHERE an.name LIKE '%a%' 16 16 TABLE SEEK info_type AS it WHERE it.info = 'mini biography'