Planner — IMDB — JOB-7A

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 BETWEEN 'A' AND 'F'
  AND (n.gender='m'
       OR (n.gender = 'f'
           AND n.name LIKE 'B%'))
  AND pi.note ='Volker Boehm'
  AND t.production_year BETWEEN 1980 AND 1995
  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,438 36M Rank		Estimation Error Est Err 36,274,812 36M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 5,706 5.7K Rank	Estimation Error Est Err 32 32 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 37,832,576 38M Rank		Estimation Error Est Err 59,740,356 60M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 41,132,505 41M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 34,348,941 34M Rank		Estimation Error Est Err 34,349,491 34M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 17,228 17K Rank	Estimation Error Est Err 34 34 Rank	Estimation Error Est Err 8,645 8.6K Rank
Estimation Error Est Err 42,664,165 43M Rank		Estimation Error Est Err 65,766,136 66M Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 29,552,119 30M Rank	Estimation Error Est Err 42 42 Rank	Estimation Error Est Err 1,090,769 1.1M Rank
Estimation Error Est Err 7,809 7.8K Rank		Estimation Error Est Err 7,282 7.3K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 7,513 7.5K Rank	Estimation Error Est Err 32 32 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 238,515 239K Rank	Estimation Error Est Err 238,514 239K Rank	Estimation Error Est Err 238,519 239K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 238,519 239K Rank	Estimation Error Est Err 32 32 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 10,827 11K Rank	Estimation Error Est Err 10,763 11K Rank	Estimation Error Est Err 14,319 14K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 10,763 11K Rank	Estimation Error Est Err 37 37 Rank	Estimation Error Est Err 0 Rank
Estimation Error Est Err 345,531 346K Rank		Estimation Error Est Err 1,002 1K Rank	Estimation Error Est Err 0 0 Rank	Estimation Error Est Err 346,072 346K Rank	Estimation Error Est Err 48 48 Rank	Estimation Error Est Err 345,491 345K Rank

Actual Query Plans

Query Plan per Engine ?
	Query Plan
	Estimate Actual Operator 1 1 AGGREGATE min, min 1 32 INNER JOIN HASH ON id19 = person_id64 1 14 │└INNER JOIN HASH ON id48 = movie_id 1 103 │ │└INNER JOIN HASH ON id = link_type_id 1 1 │ │ │└TABLE SCAN link_type WHERE link = features 6 344 │ │ INNER JOIN HASH ON movie_id = linked_movie_id 76 2670 │ │ │└INNER JOIN HASH ON id19 = person_id32 1 17 │ │ │ │└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 594193 17 │ │ │ │ TABLE SCAN name WHERE name_pcode_cf BETWEEN A AND F AND gender = m OR gender = f 36244344 36244344 │ │ │ TABLE SCAN cast_info 29997 29997 │ │ TABLE SCAN movie_link 355543 7 │ TABLE SCAN title WHERE production_year BETWEEN 1980 AND 1995 684809 7 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_5330.person_id,PROJECTION_5330.person_id,PROJECTION_5330.person_id,PROJECTION_5330.person_id) = tuple(PROJECTION_5315.id,PROJECTION_5315.id,PROJECTION_5315.person_id,PROJECTION_5315.person_id) - 17 │└PROJECT id, person_id AS person_id_right, name - 17 │ PROJECT name, id, person_id - 17 │ INNER JOIN HASH ON PROJECTION_5321.info_type_id = PROJECTION_5318.id - 1 │ │└PROJECT id AS id_right - 1 │ │ PROJECT id - 1 │ │ TABLE SCAN info_type WHERE info = 'mini biography' - 17 │ PROJECT info_type_id, name, id AS id_left, person_id - 17 │ PROJECT name, id, person_id, info_type_id - 17 │ INNER JOIN HASH ON PROJECTION_5327.id = PROJECTION_5324.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' - 543291 │ PROJECT id, name - 543291 │ PROJECT id, name - 543291 │ TABLE SCAN name WHERE (name_pcode_cf >= 'A') AND (name_pcode_cf <= 'F') AND ((gender = 'm') OR ((gender = 'f') AND startsWith(name,'B'))) - 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_5342.movie_id,PROJECTION_5342.id) = tuple(PROJECTION_5333.linked_movie_id,PROJECTION_5333.linked_movie_id) - 5186 │└PROJECT linked_movie_id - 5186 │ PROJECT linked_movie_id - 5186 │ INNER JOIN HASH ON PROJECTION_5339.link_type_id = PROJECTION_5336.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 - 4877691 PROJECT movie_id, id, person_id, person_id, title - 4877691 PROJECT person_id, person_id, movie_id, title, id - 4877691 INNER JOIN HASH ON PROJECTION_5348.movie_id = PROJECTION_5345.id - 340186 │└PROJECT id, title - 340186 │ PROJECT id, title - 340186 │ TABLE SCAN title WHERE (production_year >= 1980) AND (production_year <= 1995) - 27144680 PROJECT movie_id, person_id, person_id - 27144680 PROJECT person_id, person_id, movie_id - 27144680 INNER JOIN HASH ON PROJECTION_5354.person_id = PROJECTION_5351.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 32 PROJECT name, title 0 32 INNER JOIN HASH ON id = movie_id 0 151 │└INNER JOIN HASH ON id = link_type_id 0 510 │ │└INNER JOIN HASH ON linked_movie_id = movie_id 0 3354 │ │ │└INNER JOIN HASH ON person_id = person_id 0 20 │ │ │ │└INNER JOIN HASH ON person_id = person_id 0 17 │ │ │ │ │└INNER JOIN HASH ON 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' 166699 2603 │ │ │ │ │ FILTER id BETWEEN 4 AND 4061926 166699 2603 │ │ │ │ │ FILTER (gender = 'm') OR ((gender = 'f') AND prefix(name,'B')) 833498 4741 │ │ │ │ │ TABLE SCAN "name" WHERE name_pcode_cf >= 'A' AND name_pcode_cf <= 'F' 180268 570 │ │ │ │ FILTER person_id <= 4061926 180268 570 │ │ │ │ TABLE SCAN aka_name WHERE contains(name,'a') 36244344 2196 │ │ │ FILTER movie_id BETWEEN 284 AND 2524994 36244344 2196 │ │ │ TABLE SCAN cast_info WHERE person_id >= 4 29997 222 │ │ TABLE SCAN movie_link 2 1 │ FILTER id <= 17 2 1 │ TABLE SCAN link_type WHERE link = 'features' 505662 14 FILTER id BETWEEN 284 AND 2524994 505662 14 TABLE SCAN title WHERE production_year >= 1980 AND production_year <= 1995
	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 32 INNER JOIN HASH ON movie_id = id AND linked_movie_id = id 6493 151 │└DISTRIBUTE GATHER 6493 151 │ 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 151 │ INNER JOIN HASH ON id = person_id AND person_id = person_id AND person_id = person_id 7058 67308 │ │└DISTRIBUTE GATHER 7058 67308 │ │ 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 543291 │ │ FILTER ((name_pcode_cf >= 'A') AND (name_pcode_cf <= 'F')) AND ((gender = 'm') OR ((gender = 'f') AND name LIKE 'B%')) 4167491 1599013 │ │ TABLE SCAN name WHERE (((name_pcode_cf >= 'A') AND (name_pcode_cf <= 'F')) AND ((gender = 'm') OR ((gender = 'f') AND name LIKE 'B%'))) AND ((((id >= 56) AND (id <= 4061723)) AND ((id >= 56) AND (id <= 4061723))) AND TRUE) 592733 64 │ FILTER note = 'Volker Boehm' 2963664 2467766 │ TABLE SCAN person_info WHERE ((note = 'Volker Boehm') AND (((((person_id >= 339) AND (person_id <= 1860240)) AND ((person_id >= 339) AND (person_id <= 1860240))) AND ((person_id >= 339) AND (person_id <= 1860240))) AND TRUE)) AND (((info_type_id >= 19) AND (info_type_id <= 19)) AND info_type_id IN 19) 288950 190640 FILTER (production_year >= 1980) AND (production_year <= 1995) 2528312 1421571 TABLE SCAN title WHERE ((production_year >= 1980) AND (production_year <= 1995)) AND ((((id >= 1664924) AND (id <= 2434746)) AND ((id >= 1664924) AND (id <= 2434746))) 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 32 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 552 INNER JOIN HASH ON ci.movie_id = t.id 892 3354 │└DISTRIBUTE GATHER 96 3354 │ INNER JOIN HASH ON an.person_id = ci.person_id 96 20 │ │└DISTRIBUTE GATHER 60 20 │ │ INNER JOIN HASH ON n.id = an.person_id 60 17 │ │ │└DISTRIBUTE GATHER 60 17 │ │ │ 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 494433 │ │ │ TABLE SCAN name WHERE (n.name_pcode_cf IS NOT NULL) AND (n.name_pcode_cf >= 'A') AND (n.name_pcode_cf <= 'F') AND ((n.gender = 'm') OR ((n.gender = 'f') AND startswith(n.name,'B'))) 36200000 659588 │ │ TABLE SCAN aka_name WHERE contains(an.name,'a') 4170000 32828522 │ TABLE SCAN cast_info 2960000 339227 TABLE SCAN title WHERE (t.production_year IS NOT NULL) AND (t.production_year >= 1980L) AND (t.production_year <= 1995L)
	Estimate Actual Operator 1 1 AGGREGATE MIN(name as name) AS Expr1016, MIN(title as title) AS Expr1017 1 37 INNER JOIN LOOP ON Bmk1014 = Bmk1014 1 37 │└TABLE SEEK title AS t WHERE production_year as production_year >= 1980 AND production_year as production_year <= 1995 1 172 PROJECT BmkToPage Bmk1014 AS Expr1085 1 172 INNER JOIN LOOP ON ml.linked_movie_id = t.id 1 172 │└TABLE SEEK title AS t 1 172 FILTER link as link = 'features' 14 565 INNER JOIN LOOP ON Bmk1006 = Bmk1006 1 565 │└TABLE SEEK link_type AS lt 14 565 INNER JOIN LOOP ON ml.link_type_id = lt.id 1 565 │└TABLE SEEK link_type AS lt 14 565 INNER JOIN LOOP ON Bmk1008 = Bmk1008 1 565 │└TABLE SEEK movie_link AS ml 14 565 INNER JOIN LOOP ON ci.movie_id = ml.linked_movie_id 1 565 │└TABLE SEEK movie_link AS ml 569 3986 INNER JOIN LOOP ON Bmk1002 = Bmk1002 1 3986 │└TABLE SEEK cast_info AS ci 569 3986 PROJECT BmkToPage Bmk1002 AS Expr1082 569 3986 INNER JOIN LOOP ON pi.person_id = ci.person_id 564 3986 │└TABLE SEEK cast_info AS ci 1 26 FILTER name as name LIKE '%a%' 4 33 INNER JOIN LOOP ON Bmk1000 = Bmk1000 1 33 │└TABLE SEEK aka_name AS an 4 33 INNER JOIN LOOP ON pi.person_id = an.person_id 4 33 │└TABLE SEEK aka_name AS an 1 17 FILTER (gender as gender = 'm' OR gender as gender = 'f' AND name as name LIKE 'B%') AND name_pcode_cf as name_pcode_cf >= 'A' AND name_pcode_cf as name_pcode_cf <= 'F' 1 64 INNER JOIN LOOP ON Bmk1010 = Bmk1010 1 64 │└TABLE SEEK name AS n 1 64 PROJECT BmkToPage Bmk1010 AS Expr1081 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 - 32 INNER JOIN HASH ON movie_id = id_35 265059 340186 │└DISTRIBUTE HASH ON id_35 265059 340186 │ PROJECT id AS id_35, title 265059 340186 │ FILTER production_year BETWEEN 1980 AND 1995 265059 340186 │ TABLE SCAN title - 151 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 - 151 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 - 151 INNER JOIN HASH ON person_id_1 = id_18 3750742 17 │└DISTRIBUTE HASH ON id_18 3750742 17 │ PROJECT id AS id_18, name AS name_19 3750742 17 │ FILTER (gender IN('f','m')) AND (name_pcode_cf BETWEEN 'A' AND 'F') AND ((gender = 'm') OR ((gender = 'f') AND (name LIKE 'B%'))) 3750742 17 │ TABLE SCAN name - 151 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 - 151 INNER JOIN HASH ON movie_id = linked_movie_id 29997 5186 │└DISTRIBUTE GATHER 29997 5186 │ TABLE SCAN movie_link - 96 INNER JOIN HASH ON person_id_1 = person_id 811209 20 │└DISTRIBUTE GATHER 811209 20 │ FILTER name LIKE '%a%' 811209 20 │ TABLE SCAN aka_name 36244344 56 PROJECT person_id AS person_id_1, movie_id 36244344 56 TABLE SCAN cast_info
	Estimate Actual Operator 1 1 AGGREGATE MIN(name), MIN(title) 1 32 INNER JOIN LOOP ON id = info_type_id 1 32 │└INNER JOIN LOOP ON person_id = person_id AND person_id = id AND (person_id = id) 1 14 │ │└INNER JOIN LOOP ON person_id = person_id AND id = person_id AND (id = person_id) 1030 17559 │ │ │└INNER JOIN LOOP ON id = person_id 8943 105262 │ │ │ │└INNER JOIN LOOP ON movie_id = id 227 1467 │ │ │ │ │└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 <= 1995) 57213 105257 │ │ │ │ TABLE SEEK cast_info AS ci 105262 105262 │ │ │ TABLE SEEK name AS n WHERE (n.name_pcode_cf >= 'A') AND (n.name_pcode_cf <= 'F') AND ((n.gender = 'm') OR ((n.gender = 'f') AND (n.name LIKE 'B%'))) 17559 17559 │ │ TABLE SEEK person_info AS pi WHERE pi.note = 'Volker Boehm' 28 32 │ TABLE SEEK aka_name AS an WHERE an.name LIKE '%a%' 32 32 TABLE SEEK info_type AS it WHERE it.info = 'mini biography'