Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Optimizer effectiveness on IMDB dataset(Join-Order-Benchmark) Test Report #18644

Open
mahjonp opened this issue Jul 16, 2020 · 5 comments
Open

Optimizer effectiveness on IMDB dataset(Join-Order-Benchmark) Test Report #18644

mahjonp opened this issue Jul 16, 2020 · 5 comments
Assignees
@qw4990 @winoros
Labels
sig/planner SIG: Planner

Comments

@mahjonp
Copy link
Contributor

mahjonp commented Jul 16, 2020 •
edited by Hexilee
Loading

Reproduce Steps

TiDB Server Configuration

mem-quota-query: 34359738368
oom-action: "log"
tikv-client.copr-cache.enable: false

Prepare IMDB:

Refer to https://github.com/gregrahn/join-order-benchmark#step-by-step-instructions

Run Horo:

git clone https://github.com/chaos-mesh/horoscope.git
cd horoscope
make
./bin/horo --round 4 -d root:@tcp(localhost:4000)/imdb?charset=utf8 bench -p -w benchmark/job -c=false

Test Report

+---------+-------------+------------------------+--------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID      | #PLAN SPACE | DEFAULT EXECUTION TIME | BEST PLAN EXECUTION TIME | EFFECTIVENESS | BETTER OPTIMAL PLANS                                                                                                                                                                                                                                                                                                                                                                                                                                                | ESTROW Q-ERROR                                   | QUERY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+---------+-------------+------------------------+--------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 20a.sql |          79 | 2268.8ms ± 9%         | 1955.2ms ± 3%           | 98.7%         | #47(86.2%)                                                                                                                                                                                                                                                                                                                                                                                                                                                          | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS complete_downey_ironman_movie FROM (((((((((complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN comp_cast_type AS cct2) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN keyword AS k) JOIN kind_type AS kt) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE cct1.kind="cast" AND cct2.kind LIKE "%complete%" AND chn.name NOT LIKE "%Sherlock%" AND (chn.name LIKE "%Tony%Stark%" OR chn.name LIKE "%Iron%Man%") AND k.keyword IN ("superhero","sequel","second-part","marvel-comics","based-on-comic","tv-special","fight","violence") AND kt.kind="movie" AND t.production_year>1950 AND kt.id=t.kind_id AND t.id=mk.movie_id AND t.id=ci.movie_id AND t.id=cc.movie_id AND mk.movie_id=ci.movie_id AND mk.movie_id=cc.movie_id AND ci.movie_id=cc.movie_id AND chn.id=ci.person_role_id AND n.id=ci.person_id AND k.id=mk.keyword_id AND cct1.id=cc.subject_id AND cct2.id=cc.status_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| 20b.sql |          72 | 941.0ms ±13%          | 941.0ms ±13%            | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS complete_downey_ironman_movie FROM (((((((((complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN comp_cast_type AS cct2) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN keyword AS k) JOIN kind_type AS kt) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE cct1.kind="cast" AND cct2.kind LIKE "%complete%" AND chn.name NOT LIKE "%Sherlock%" AND (chn.name LIKE "%Tony%Stark%" OR chn.name LIKE "%Iron%Man%") AND k.keyword IN ("superhero","sequel","second-part","marvel-comics","based-on-comic","tv-special","fight","violence") AND kt.kind="movie" AND n.name LIKE "%Downey%Robert%" AND t.production_year>2000 AND kt.id=t.kind_id AND t.id=mk.movie_id AND t.id=ci.movie_id AND t.id=cc.movie_id AND mk.movie_id=ci.movie_id AND mk.movie_id=cc.movie_id AND ci.movie_id=cc.movie_id AND chn.id=ci.person_role_id AND n.id=ci.person_id AND k.id=mk.keyword_id AND cct1.id=cc.subject_id AND cct2.id=cc.status_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 20c.sql |          77 | 905.2ms ± 7%          | 905.2ms ± 7%            | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS cast_member,MIN(t.title) AS complete_dynamic_hero_movie FROM (((((((((complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN comp_cast_type AS cct2) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN keyword AS k) JOIN kind_type AS kt) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE cct1.kind="cast" AND cct2.kind LIKE "%complete%" AND chn.name IS NOT NULL AND (chn.name LIKE "%man%" OR chn.name LIKE "%Man%") AND k.keyword IN ("superhero","marvel-comics","based-on-comic","tv-special","fight","violence","magnet","web","claw","laser") AND kt.kind="movie" AND t.production_year>2000 AND kt.id=t.kind_id AND t.id=mk.movie_id AND t.id=ci.movie_id AND t.id=cc.movie_id AND mk.movie_id=ci.movie_id AND mk.movie_id=cc.movie_id AND ci.movie_id=cc.movie_id AND chn.id=ci.person_role_id AND n.id=ci.person_id AND k.id=mk.keyword_id AND cct1.id=cc.subject_id AND cct2.id=cc.status_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| 21a.sql |          84 | 1964.5ms ±10%         | 1574.5ms ± 3%           | 85.7%         | #57(80.1%),#59(81.5%),#63(80.6%),#64(81.3%),#65(81.2%),#66(83.4%),#67(83.3%),#68(84.3%),#69(83.4%),#70(84.5%),#71(82.6%),#72(80.9%)                                                                                                                                                                                                                                                                                                                                 | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(cn.name) AS company_name,MIN(lt.link) AS link_type,MIN(t.title) AS western_follow_up FROM ((((((((company_name AS cn) JOIN company_type AS ct) JOIN keyword AS k) JOIN link_type AS lt) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t WHERE cn.country_code!="[pl]" AND (cn.name LIKE "%Film%" OR cn.name LIKE "%Warner%") AND ct.kind="production companies" AND k.keyword="sequel" AND lt.link LIKE "%follow%" AND mc.note IS NULL AND mi.info IN ("Sweden","Norway","Germany","Denmark","Swedish","Denish","Norwegian","German") AND t.production_year BETWEEN 1950 AND 2000 AND lt.id=ml.link_type_id AND ml.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_type_id=ct.id AND mc.company_id=cn.id AND mi.movie_id=t.id AND ml.movie_id=mk.movie_id AND ml.movie_id=mc.movie_id AND mk.movie_id=mc.movie_id AND ml.movie_id=mi.movie_id AND mk.movie_id=mi.movie_id AND mc.movie_id=mi.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 21b.sql |          84 | 1920.8ms ± 6%         | 1497.5ms ± 8%           | 85.7%         | #57(83.0%),#59(80.2%),#60(80.3%),#63(82.1%),#64(82.8%),#65(78.0%),#66(83.1%),#67(81.4%),#68(82.2%),#70(82.7%),#71(80.9%),#72(82.4%)                                                                                                                                                                                                                                                                                                                                 | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(cn.name) AS company_name,MIN(lt.link) AS link_type,MIN(t.title) AS german_follow_up FROM ((((((((company_name AS cn) JOIN company_type AS ct) JOIN keyword AS k) JOIN link_type AS lt) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t WHERE cn.country_code!="[pl]" AND (cn.name LIKE "%Film%" OR cn.name LIKE "%Warner%") AND ct.kind="production companies" AND k.keyword="sequel" AND lt.link LIKE "%follow%" AND mc.note IS NULL AND mi.info IN ("Germany","German") AND t.production_year BETWEEN 2000 AND 2010 AND lt.id=ml.link_type_id AND ml.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_type_id=ct.id AND mc.company_id=cn.id AND mi.movie_id=t.id AND ml.movie_id=mk.movie_id AND ml.movie_id=mc.movie_id AND mk.movie_id=mc.movie_id AND ml.movie_id=mi.movie_id AND mk.movie_id=mi.movie_id AND mc.movie_id=mi.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| 21c.sql |          82 | 3044.8ms ± 4%         | 2723.8ms ± 7%           | 97.6%         | #67(90.0%),#69(89.5%)                                                                                                                                                                                                                                                                                                                                                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(cn.name) AS company_name,MIN(lt.link) AS link_type,MIN(t.title) AS western_follow_up FROM ((((((((company_name AS cn) JOIN company_type AS ct) JOIN keyword AS k) JOIN link_type AS lt) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t WHERE cn.country_code!="[pl]" AND (cn.name LIKE "%Film%" OR cn.name LIKE "%Warner%") AND ct.kind="production companies" AND k.keyword="sequel" AND lt.link LIKE "%follow%" AND mc.note IS NULL AND mi.info IN ("Sweden","Norway","Germany","Denmark","Swedish","Denish","Norwegian","German","English") AND t.production_year BETWEEN 1950 AND 2010 AND lt.id=ml.link_type_id AND ml.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_type_id=ct.id AND mc.company_id=cn.id AND mi.movie_id=t.id AND ml.movie_id=mk.movie_id AND ml.movie_id=mc.movie_id AND mk.movie_id=mc.movie_id AND ml.movie_id=mi.movie_id AND mk.movie_id=mi.movie_id AND mc.movie_id=mi.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| 23a.sql |         100 | 2407.2ms ± 2%         | 1702.0ms ± 3%           | 95.0%         | #45(70.8%),#46(70.7%),#47(71.3%),#48(80.2%),#49(84.5%)                                                                                                                                                                                                                                                                                                                                                                                                              | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(kt.kind) AS movie_kind,MIN(t.title) AS complete_us_internet_movie FROM ((((((((((complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN company_name AS cn) JOIN company_type AS ct) JOIN info_type AS it1) JOIN keyword AS k) JOIN kind_type AS kt) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE cct1.kind="complete+verified" AND cn.country_code="[us]" AND it1.info="release dates" AND kt.kind IN ("movie") AND mi.note LIKE "%internet%" AND mi.info IS NOT NULL AND (mi.info LIKE "USA:% 199%" OR mi.info LIKE "USA:% 200%") 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=mc.movie_id AND t.id=cc.movie_id AND mk.movie_id=mi.movie_id AND mk.movie_id=mc.movie_id AND mk.movie_id=cc.movie_id AND mi.movie_id=mc.movie_id AND mi.movie_id=cc.movie_id AND mc.movie_id=cc.movie_id AND k.id=mk.keyword_id AND it1.id=mi.info_type_id AND cn.id=mc.company_id AND ct.id=mc.company_type_id AND cct1.id=cc.status_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| 23b.sql |         100 | 2399.0ms ± 4%         | 1702.5ms ± 5%           | 96.0%         | #45(71.3%),#46(71.0%),#47(72.0%),#48(80.5%)                                                                                                                                                                                                                                                                                                                                                                                                                         | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(kt.kind) AS movie_kind,MIN(t.title) AS complete_nerdy_internet_movie FROM ((((((((((complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN company_name AS cn) JOIN company_type AS ct) JOIN info_type AS it1) JOIN keyword AS k) JOIN kind_type AS kt) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE cct1.kind="complete+verified" AND cn.country_code="[us]" AND it1.info="release dates" AND k.keyword IN ("nerd","loner","alienation","dignity") AND kt.kind IN ("movie") AND mi.note LIKE "%internet%" AND mi.info LIKE "USA:% 200%" 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=mc.movie_id AND t.id=cc.movie_id AND mk.movie_id=mi.movie_id AND mk.movie_id=mc.movie_id AND mk.movie_id=cc.movie_id AND mi.movie_id=mc.movie_id AND mi.movie_id=cc.movie_id AND mc.movie_id=cc.movie_id AND k.id=mk.keyword_id AND it1.id=mi.info_type_id AND cn.id=mc.company_id AND ct.id=mc.company_type_id AND cct1.id=cc.status_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 23c.sql |         100 | 2377.8ms ± 2%         | 1717.0ms ± 5%           | 96.0%         | #45(73.5%),#46(73.0%),#47(72.2%),#48(80.0%)                                                                                                                                                                                                                                                                                                                                                                                                                         | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(kt.kind) AS movie_kind,MIN(t.title) AS complete_us_internet_movie FROM ((((((((((complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN company_name AS cn) JOIN company_type AS ct) JOIN info_type AS it1) JOIN keyword AS k) JOIN kind_type AS kt) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE cct1.kind="complete+verified" AND cn.country_code="[us]" AND it1.info="release dates" AND kt.kind IN ("movie","tv movie","video movie","video game") AND mi.note LIKE "%internet%" AND mi.info IS NOT NULL AND (mi.info LIKE "USA:% 199%" OR mi.info LIKE "USA:% 200%") AND t.production_year>1990 AND kt.id=t.kind_id AND t.id=mi.movie_id AND t.id=mk.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=mc.movie_id AND mk.movie_id=cc.movie_id AND mi.movie_id=mc.movie_id AND mi.movie_id=cc.movie_id AND mc.movie_id=cc.movie_id AND k.id=mk.keyword_id AND it1.id=mi.info_type_id AND cn.id=mc.company_id AND ct.id=mc.company_type_id AND cct1.id=cc.status_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| 24a.sql |         100 | 7535.0ms ± 4%         | 6477.2ms ± 6%           | 80.0%         | #59(88.8%),#60(89.0%),#61(87.5%),#62(87.2%),#63(86.8%),#64(87.2%),#65(87.7%),#66(86.0%),#68(88.0%),#69(87.8%),#70(87.3%),#71(87.2%),#72(87.5%),#73(88.8%),#74(88.4%),#75(88.1%),#79(87.2%),#80(88.0%),#83(88.9%),#84(89.0%)                                                                                                                                                                                                                                         | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(chn.name) AS voiced_char_name,MIN(n.name) AS voicing_actress_name,MIN(t.title) AS voiced_action_movie_jap_eng FROM (((((((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN info_type AS it) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note IN ("(voice)","(voice: Japanese version)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND it.info="release dates" AND k.keyword IN ("hero","martial-arts","hand-to-hand-combat") AND mi.info IS NOT NULL AND (mi.info LIKE "Japan:%201%" OR mi.info LIKE "USA:%201%") AND n.gender="f" AND n.name LIKE "%An%" AND rt.role="actress" AND t.production_year>2010 AND t.id=mi.movie_id AND t.id=mc.movie_id AND t.id=ci.movie_id AND t.id=mk.movie_id AND mc.movie_id=ci.movie_id AND mc.movie_id=mi.movie_id AND mc.movie_id=mk.movie_id AND mi.movie_id=ci.movie_id AND mi.movie_id=mk.movie_id AND ci.movie_id=mk.movie_id AND cn.id=mc.company_id AND it.id=mi.info_type_id AND n.id=ci.person_id AND rt.id=ci.role_id AND n.id=an.person_id AND ci.person_id=an.person_id AND chn.id=ci.person_role_id AND k.id=mk.keyword_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| 24b.sql |         100 | 39.5ms ±22%           | 39.5ms ±22%             | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(chn.name) AS voiced_char_name,MIN(n.name) AS voicing_actress_name,MIN(t.title) AS kung_fu_panda FROM (((((((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN info_type AS it) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note IN ("(voice)","(voice: Japanese version)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND cn.name="DreamWorks Animation" AND it.info="release dates" AND k.keyword IN ("hero","martial-arts","hand-to-hand-combat","computer-animated-movie") AND mi.info IS NOT NULL AND (mi.info LIKE "Japan:%201%" OR mi.info LIKE "USA:%201%") AND n.gender="f" AND n.name LIKE "%An%" AND rt.role="actress" AND t.production_year>2010 AND t.title LIKE "Kung Fu Panda%" AND t.id=mi.movie_id AND t.id=mc.movie_id AND t.id=ci.movie_id AND t.id=mk.movie_id AND mc.movie_id=ci.movie_id AND mc.movie_id=mi.movie_id AND mc.movie_id=mk.movie_id AND mi.movie_id=ci.movie_id AND mi.movie_id=mk.movie_id AND ci.movie_id=mk.movie_id AND cn.id=mc.company_id AND it.id=mi.info_type_id AND n.id=ci.person_id AND rt.id=ci.role_id AND n.id=an.person_id AND ci.person_id=an.person_id AND chn.id=ci.person_role_id AND k.id=mk.keyword_id                                                                                                                                                                                                                                                                                                                                                                                           |
| 27a.sql |         100 | 1915.0ms ±12%         | 1410.8ms ± 8%           | 93.0%         | #94(74.4%),#95(77.4%),#96(73.9%),#97(75.1%),#98(74.5%),#99(73.7%),#100(76.8%)                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(cn.name) AS producing_company,MIN(lt.link) AS link_type,MIN(t.title) AS complete_western_sequel FROM (((((((((((complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN comp_cast_type AS cct2) JOIN company_name AS cn) JOIN company_type AS ct) JOIN keyword AS k) JOIN link_type AS lt) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t WHERE cct1.kind IN ("cast","crew") AND cct2.kind="complete" AND cn.country_code!="[pl]" AND (cn.name LIKE "%Film%" OR cn.name LIKE "%Warner%") AND ct.kind="production companies" AND k.keyword="sequel" AND lt.link LIKE "%follow%" AND mc.note IS NULL AND mi.info IN ("Sweden","Germany","Swedish","German") AND t.production_year BETWEEN 1950 AND 2000 AND lt.id=ml.link_type_id AND ml.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_type_id=ct.id AND mc.company_id=cn.id AND mi.movie_id=t.id AND t.id=cc.movie_id AND cct1.id=cc.subject_id AND cct2.id=cc.status_id AND ml.movie_id=mk.movie_id AND ml.movie_id=mc.movie_id AND mk.movie_id=mc.movie_id AND ml.movie_id=mi.movie_id AND mk.movie_id=mi.movie_id AND mc.movie_id=mi.movie_id AND ml.movie_id=cc.movie_id AND mk.movie_id=cc.movie_id AND mc.movie_id=cc.movie_id AND mi.movie_id=cc.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 27b.sql |         100 | 1873.0ms ± 9%         | 1278.8ms ± 7%           | 95.0%         | #95(80.9%),#96(79.1%),#98(80.4%),#99(80.1%),#100(68.3%)                                                                                                                                                                                                                                                                                                                                                                                                             | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(cn.name) AS producing_company,MIN(lt.link) AS link_type,MIN(t.title) AS complete_western_sequel FROM (((((((((((complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN comp_cast_type AS cct2) JOIN company_name AS cn) JOIN company_type AS ct) JOIN keyword AS k) JOIN link_type AS lt) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t WHERE cct1.kind IN ("cast","crew") AND cct2.kind="complete" AND cn.country_code!="[pl]" AND (cn.name LIKE "%Film%" OR cn.name LIKE "%Warner%") AND ct.kind="production companies" AND k.keyword="sequel" AND lt.link LIKE "%follow%" AND mc.note IS NULL AND mi.info IN ("Sweden","Germany","Swedish","German") AND t.production_year=1998 AND lt.id=ml.link_type_id AND ml.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_type_id=ct.id AND mc.company_id=cn.id AND mi.movie_id=t.id AND t.id=cc.movie_id AND cct1.id=cc.subject_id AND cct2.id=cc.status_id AND ml.movie_id=mk.movie_id AND ml.movie_id=mc.movie_id AND mk.movie_id=mc.movie_id AND ml.movie_id=mi.movie_id AND mk.movie_id=mi.movie_id AND mc.movie_id=mi.movie_id AND ml.movie_id=cc.movie_id AND mk.movie_id=cc.movie_id AND mc.movie_id=cc.movie_id AND mi.movie_id=cc.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| 27c.sql |         100 | 2687.8ms ± 3%         | 2339.2ms ± 4%           | 97.0%         | #98(88.2%),#99(87.0%),#100(87.8%)                                                                                                                                                                                                                                                                                                                                                                                                                                   | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(cn.name) AS producing_company,MIN(lt.link) AS link_type,MIN(t.title) AS complete_western_sequel FROM (((((((((((complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN comp_cast_type AS cct2) JOIN company_name AS cn) JOIN company_type AS ct) JOIN keyword AS k) JOIN link_type AS lt) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t WHERE cct1.kind="cast" AND cct2.kind LIKE "complete%" AND cn.country_code!="[pl]" AND (cn.name LIKE "%Film%" OR cn.name LIKE "%Warner%") AND ct.kind="production companies" AND k.keyword="sequel" AND lt.link LIKE "%follow%" AND mc.note IS NULL AND mi.info IN ("Sweden","Norway","Germany","Denmark","Swedish","Denish","Norwegian","German","English") AND t.production_year BETWEEN 1950 AND 2010 AND lt.id=ml.link_type_id AND ml.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_type_id=ct.id AND mc.company_id=cn.id AND mi.movie_id=t.id AND t.id=cc.movie_id AND cct1.id=cc.subject_id AND cct2.id=cc.status_id AND ml.movie_id=mk.movie_id AND ml.movie_id=mc.movie_id AND mk.movie_id=mc.movie_id AND ml.movie_id=mi.movie_id AND mk.movie_id=mi.movie_id AND mc.movie_id=mi.movie_id AND ml.movie_id=cc.movie_id AND mk.movie_id=cc.movie_id AND mc.movie_id=cc.movie_id AND mi.movie_id=cc.movie_id                                                                                                                                                                                                                                                                                                                                                                            |
| 29a.sql |         100 | 217.0ms ±35%          | 217.0ms ±35%            | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(chn.name) AS voiced_char,MIN(n.name) AS voicing_actress,MIN(t.title) AS voiced_animation FROM ((((((((((((((((aka_name AS an) JOIN complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN comp_cast_type AS cct2) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN info_type AS it) JOIN info_type AS it3) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN name AS n) JOIN person_info AS pi) JOIN role_type AS rt) JOIN title AS t WHERE cct1.kind="cast" AND cct2.kind="complete+verified" AND chn.name="Queen" AND ci.note IN ("(voice)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND it.info="release dates" AND it3.info="trivia" AND k.keyword="computer-animation" AND mi.info IS NOT NULL AND (mi.info LIKE "Japan:%200%" OR mi.info LIKE "USA:%200%") AND n.gender="f" AND n.name LIKE "%An%" AND rt.role="actress" AND t.title="Shrek 2" AND t.production_year BETWEEN 2000 AND 2010 AND t.id=mi.movie_id AND t.id=mc.movie_id AND t.id=ci.movie_id AND t.id=mk.movie_id AND t.id=cc.movie_id AND mc.movie_id=ci.movie_id AND mc.movie_id=mi.movie_id AND mc.movie_id=mk.movie_id AND mc.movie_id=cc.movie_id AND mi.movie_id=ci.movie_id AND mi.movie_id=mk.movie_id AND mi.movie_id=cc.movie_id AND ci.movie_id=mk.movie_id AND ci.movie_id=cc.movie_id AND mk.movie_id=cc.movie_id AND cn.id=mc.company_id AND it.id=mi.info_type_id AND n.id=ci.person_id AND rt.id=ci.role_id AND n.id=an.person_id AND ci.person_id=an.person_id AND chn.id=ci.person_role_id AND n.id=pi.person_id AND ci.person_id=pi.person_id AND it3.id=pi.info_type_id AND k.id=mk.keyword_id AND cct1.id=cc.subject_id AND cct2.id=cc.status_id |
| 29b.sql |         100 | 32.5ms ±33%           | 32.5ms ±33%             | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(chn.name) AS voiced_char,MIN(n.name) AS voicing_actress,MIN(t.title) AS voiced_animation FROM ((((((((((((((((aka_name AS an) JOIN complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN comp_cast_type AS cct2) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN info_type AS it) JOIN info_type AS it3) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN name AS n) JOIN person_info AS pi) JOIN role_type AS rt) JOIN title AS t WHERE cct1.kind="cast" AND cct2.kind="complete+verified" AND chn.name="Queen" AND ci.note IN ("(voice)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND it.info="release dates" AND it3.info="height" AND k.keyword="computer-animation" AND mi.info LIKE "USA:%200%" AND n.gender="f" AND n.name LIKE "%An%" AND rt.role="actress" AND t.title="Shrek 2" AND t.production_year BETWEEN 2000 AND 2005 AND t.id=mi.movie_id AND t.id=mc.movie_id AND t.id=ci.movie_id AND t.id=mk.movie_id AND t.id=cc.movie_id AND mc.movie_id=ci.movie_id AND mc.movie_id=mi.movie_id AND mc.movie_id=mk.movie_id AND mc.movie_id=cc.movie_id AND mi.movie_id=ci.movie_id AND mi.movie_id=mk.movie_id AND mi.movie_id=cc.movie_id AND ci.movie_id=mk.movie_id AND ci.movie_id=cc.movie_id AND mk.movie_id=cc.movie_id AND cn.id=mc.company_id AND it.id=mi.info_type_id AND n.id=ci.person_id AND rt.id=ci.role_id AND n.id=an.person_id AND ci.person_id=an.person_id AND chn.id=ci.person_role_id AND n.id=pi.person_id AND ci.person_id=pi.person_id AND it3.id=pi.info_type_id AND k.id=mk.keyword_id AND cct1.id=cc.subject_id AND cct2.id=cc.status_id                                                         |
| 29c.sql |         100 | 7863.2ms ± 2%         | 1792.0ms ± 7%           | 59.0%         | #16(55.5%),#17(52.4%),#18(53.7%),#19(53.0%),#20(51.9%),#33(58.4%),#34(59.3%),#35(59.3%),#36(57.4%),#37(56.0%),#38(56.7%),#57(70.1%),#58(70.4%),#59(68.0%),#60(68.0%),#61(67.7%),#73(44.4%),#74(48.7%),#78(33.3%),#79(24.6%),#80(24.6%),#81(24.6%),#82(23.7%),#83(23.6%),#84(23.8%),#85(23.4%),#86(23.8%),#87(25.2%),#88(24.1%),#89(23.6%),#90(23.9%),#91(23.2%),#92(22.8%),#93(23.7%),#94(22.9%),#95(23.7%),#96(23.7%),#97(22.8%),#98(22.8%),#99(32.2%),#100(42.7%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(chn.name) AS voiced_char,MIN(n.name) AS voicing_actress,MIN(t.title) AS voiced_animation FROM ((((((((((((((((aka_name AS an) JOIN complete_cast AS cc) JOIN comp_cast_type AS cct1) JOIN comp_cast_type AS cct2) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN info_type AS it) JOIN info_type AS it3) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN name AS n) JOIN person_info AS pi) JOIN role_type AS rt) JOIN title AS t WHERE cct1.kind="cast" AND cct2.kind="complete+verified" AND ci.note IN ("(voice)","(voice: Japanese version)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND it.info="release dates" AND it3.info="trivia" AND k.keyword="computer-animation" AND mi.info IS NOT NULL AND (mi.info LIKE "Japan:%200%" OR mi.info LIKE "USA:%200%") AND n.gender="f" AND n.name LIKE "%An%" AND rt.role="actress" AND t.production_year BETWEEN 2000 AND 2010 AND t.id=mi.movie_id AND t.id=mc.movie_id AND t.id=ci.movie_id AND t.id=mk.movie_id AND t.id=cc.movie_id AND mc.movie_id=ci.movie_id AND mc.movie_id=mi.movie_id AND mc.movie_id=mk.movie_id AND mc.movie_id=cc.movie_id AND mi.movie_id=ci.movie_id AND mi.movie_id=mk.movie_id AND mi.movie_id=cc.movie_id AND ci.movie_id=mk.movie_id AND ci.movie_id=cc.movie_id AND mk.movie_id=cc.movie_id AND cn.id=mc.company_id AND it.id=mi.info_type_id AND n.id=ci.person_id AND rt.id=ci.role_id AND n.id=an.person_id AND ci.person_id=an.person_id AND chn.id=ci.person_role_id AND n.id=pi.person_id AND ci.person_id=pi.person_id AND it3.id=pi.info_type_id AND k.id=mk.keyword_id AND cct1.id=cc.subject_id AND cct2.id=cc.status_id                |
| 2a.sql  |          33 | 925.5ms ±13%          | 925.5ms ±13%            | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS movie_title FROM ((((company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN title AS t WHERE cn.country_code="[de]" AND k.keyword="character-name-in-title" AND cn.id=mc.company_id AND mc.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 2b.sql  |          33 | 793.0ms ± 8%          | 793.0ms ± 8%            | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS movie_title FROM ((((company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN title AS t WHERE cn.country_code="[nl]" AND k.keyword="character-name-in-title" AND cn.id=mc.company_id AND mc.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 2c.sql  |          33 | 6.5ms ±33%            | 6.5ms ±33%              | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS movie_title FROM ((((company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN title AS t WHERE cn.country_code="[sm]" AND k.keyword="character-name-in-title" AND cn.id=mc.company_id AND mc.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 2d.sql  |          33 | 917.2ms ±13%          | 917.2ms ±13%            | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS movie_title FROM ((((company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN title AS t WHERE cn.country_code="[us]" AND k.keyword="character-name-in-title" AND cn.id=mc.company_id AND mc.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
+---------+-------------+------------------------+--------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

+---------+-------------+------------------------+--------------------------+---------------+----------------------------------------------------------------------------------------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID      | #PLAN SPACE | DEFAULT EXECUTION TIME | BEST PLAN EXECUTION TIME | EFFECTIVENESS | BETTER OPTIMAL PLANS                                                                   | ESTROW Q-ERROR                                   | QUERY                                                                                                                                                                                                                                                                                                                                                                                                                   |
+---------+-------------+------------------------+--------------------------+---------------+----------------------------------------------------------------------------------------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 32a.sql |          31 | 2.0ms ± 0%            | 2.0ms ± 0%              | 100.0%        |                                                                                        | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(lt.link) AS link_type,MIN(t1.title) AS first_movie,MIN(t2.title) AS second_movie FROM (((((keyword AS k) JOIN link_type AS lt) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t1) JOIN title AS t2 WHERE k.keyword="10,000-mile-club" AND mk.keyword_id=k.id AND t1.id=mk.movie_id AND ml.movie_id=t1.id AND ml.linked_movie_id=t2.id AND lt.id=ml.link_type_id AND mk.movie_id=t1.id        |
| 32b.sql |          38 | 15754.2ms ± 5%        | 15754.2ms ± 5%          | 100.0%        |                                                                                        | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(lt.link) AS link_type,MIN(t1.title) AS first_movie,MIN(t2.title) AS second_movie FROM (((((keyword AS k) JOIN link_type AS lt) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t1) JOIN title AS t2 WHERE k.keyword="character-name-in-title" AND mk.keyword_id=k.id AND t1.id=mk.movie_id AND ml.movie_id=t1.id AND ml.linked_movie_id=t2.id AND lt.id=ml.link_type_id AND mk.movie_id=t1.id |
| 3a.sql  |          28 | 926.2ms ± 6%          | 926.2ms ± 6%            | 100.0%        |                                                                                        | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS movie_title FROM (((keyword AS k) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE k.keyword LIKE "%sequel%" AND mi.info IN ("Sweden","Norway","Germany","Denmark","Swedish","Denish","Norwegian","German") AND t.production_year>2005 AND t.id=mi.movie_id AND t.id=mk.movie_id AND mk.movie_id=mi.movie_id AND k.id=mk.keyword_id                                        |
| 3b.sql  |          28 | 66.2ms ±18%           | 66.2ms ±18%             | 100.0%        |                                                                                        | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS movie_title FROM (((keyword AS k) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE k.keyword LIKE "%sequel%" AND mi.info IN ("Bulgaria") AND t.production_year>2010 AND t.id=mi.movie_id AND t.id=mk.movie_id AND mk.movie_id=mi.movie_id AND k.id=mk.keyword_id                                                                                                           |
| 3c.sql  |          27 | 2133.2ms ± 4%         | 428.2ms ± 3%            | 70.4%         | #9(20.3%),#10(20.1%),#11(20.3%),#15(63.3%),#16(42.6%),#17(41.7%),#18(43.2%),#21(52.7%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS movie_title FROM (((keyword AS k) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE k.keyword LIKE "%sequel%" AND mi.info IN ("Sweden","Norway","Germany","Denmark","Swedish","Denish","Norwegian","German","USA","American") AND t.production_year>1990 AND t.id=mi.movie_id AND t.id=mk.movie_id AND mk.movie_id=mi.movie_id AND k.id=mk.keyword_id                       |
+---------+-------------+------------------------+--------------------------+---------------+----------------------------------------------------------------------------------------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

+---------+-------------+------------------------+--------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID      | #PLAN SPACE | DEFAULT EXECUTION TIME | BEST PLAN EXECUTION TIME | EFFECTIVENESS | BETTER OPTIMAL PLANS                                                                                                                                                                                                                                                                                                                                                                  | ESTROW Q-ERROR                                   | QUERY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+---------+-------------+------------------------+--------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10a.sql |          53 | 5182.2ms ± 4%         | 4549.2ms ± 6%           | 94.3%         | #22(89.7%),#23(87.8%),#26(88.9%)                                                                                                                                                                                                                                                                                                                                                      | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(chn.name) AS uncredited_voiced_character,MIN(t.title) AS russian_movie FROM ((((((char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN company_type AS ct) JOIN movie_companies AS mc) JOIN role_type AS rt) JOIN title AS t WHERE ci.note LIKE "%(voice)%" AND ci.note LIKE "%(uncredited)%" AND cn.country_code="[ru]" AND rt.role="actor" AND t.production_year>2005 AND t.id=mc.movie_id AND t.id=ci.movie_id AND ci.movie_id=mc.movie_id AND chn.id=ci.person_role_id AND rt.id=ci.role_id AND cn.id=mc.company_id AND ct.id=mc.company_type_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 11a.sql |          65 | 2467.2ms ± 8%         | 2467.2ms ± 8%           | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(cn.name) AS from_company,MIN(lt.link) AS movie_link_type,MIN(t.title) AS non_polish_sequel_movie FROM (((((((company_name AS cn) JOIN company_type AS ct) JOIN keyword AS k) JOIN link_type AS lt) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t WHERE cn.country_code!="[pl]" AND (cn.name LIKE "%Film%" OR cn.name LIKE "%Warner%") AND ct.kind="production companies" AND k.keyword="sequel" AND lt.link LIKE "%follow%" AND mc.note IS NULL AND t.production_year BETWEEN 1950 AND 2000 AND lt.id=ml.link_type_id AND ml.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_type_id=ct.id AND mc.company_id=cn.id AND ml.movie_id=mk.movie_id AND ml.movie_id=mc.movie_id AND mk.movie_id=mc.movie_id                                                                                                                                                                                                                                                                                |
| 11b.sql |          73 | 1478.2ms ±23%         | 1478.2ms ±23%           | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(cn.name) AS from_company,MIN(lt.link) AS movie_link_type,MIN(t.title) AS sequel_movie FROM (((((((company_name AS cn) JOIN company_type AS ct) JOIN keyword AS k) JOIN link_type AS lt) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t WHERE cn.country_code!="[pl]" AND (cn.name LIKE "%Film%" OR cn.name LIKE "%Warner%") AND ct.kind="production companies" AND k.keyword="sequel" AND lt.link LIKE "%follows%" AND mc.note IS NULL AND t.production_year=1998 AND t.title LIKE "%Money%" AND lt.id=ml.link_type_id AND ml.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_type_id=ct.id AND mc.company_id=cn.id AND ml.movie_id=mk.movie_id AND ml.movie_id=mc.movie_id AND mk.movie_id=mc.movie_id                                                                                                                                                                                                                                                                                |
| 11c.sql |          62 | 21241.2ms ± 7%        | 21241.2ms ± 7%          | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(cn.name) AS from_company,MIN(mc.note) AS production_note,MIN(t.title) AS movie_based_on_book FROM (((((((company_name AS cn) JOIN company_type AS ct) JOIN keyword AS k) JOIN link_type AS lt) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN movie_link AS ml) JOIN title AS t WHERE cn.country_code!="[pl]" AND (cn.name LIKE "20th Century Fox%" OR cn.name LIKE "Twentieth Century Fox%") AND ct.kind!="production companies" AND ct.kind IS NOT NULL AND k.keyword IN ("sequel","revenge","based-on-novel") AND mc.note IS NOT NULL AND t.production_year>1950 AND lt.id=ml.link_type_id AND ml.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_type_id=ct.id AND mc.company_id=cn.id AND ml.movie_id=mk.movie_id AND ml.movie_id=mc.movie_id AND mk.movie_id=mc.movie_id                                                                                                                                                                                                                                           |
| 15a.sql |          87 | 4204.5ms ±165%        | 4204.5ms ±165%          | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(mi.info) AS release_date,MIN(t.title) AS internet_movie FROM ((((((((aka_title AS at) JOIN company_name AS cn) JOIN company_type AS ct) JOIN info_type AS it1) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE cn.country_code="[us]" AND it1.info="release dates" AND mc.note LIKE "%(200%)%" AND mc.note LIKE "%(worldwide)%" AND mi.note LIKE "%internet%" AND mi.info LIKE "USA:% 200%" AND t.production_year>2000 AND t.id=at.movie_id AND t.id=mi.movie_id AND t.id=mk.movie_id AND t.id=mc.movie_id AND mk.movie_id=mi.movie_id AND mk.movie_id=mc.movie_id AND mk.movie_id=at.movie_id AND mi.movie_id=mc.movie_id AND mi.movie_id=at.movie_id AND mc.movie_id=at.movie_id AND k.id=mk.keyword_id AND it1.id=mi.info_type_id AND cn.id=mc.company_id AND ct.id=mc.company_type_id                                                                                                                                                                                                                    |
| 15b.sql |          85 | 85.0ms ±30%           | 85.0ms ±30%             | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(mi.info) AS release_date,MIN(t.title) AS youtube_movie FROM ((((((((aka_title AS at) JOIN company_name AS cn) JOIN company_type AS ct) JOIN info_type AS it1) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE cn.country_code="[us]" AND cn.name="YouTube" AND it1.info="release dates" AND mc.note LIKE "%(200%)%" AND mc.note LIKE "%(worldwide)%" AND mi.note LIKE "%internet%" AND mi.info LIKE "USA:% 200%" AND t.production_year BETWEEN 2005 AND 2010 AND t.id=at.movie_id AND t.id=mi.movie_id AND t.id=mk.movie_id AND t.id=mc.movie_id AND mk.movie_id=mi.movie_id AND mk.movie_id=mc.movie_id AND mk.movie_id=at.movie_id AND mi.movie_id=mc.movie_id AND mi.movie_id=at.movie_id AND mc.movie_id=at.movie_id AND k.id=mk.keyword_id AND it1.id=mi.info_type_id AND cn.id=mc.company_id AND ct.id=mc.company_type_id                                                                                                                                                                              |
| 15c.sql |          87 | 3181.5ms ± 2%         | 1711.5ms ± 2%           | 60.9%         | #40(54.6%),#41(53.9%),#42(53.8%),#43(60.0%),#44(66.5%),#45(77.4%),#46(76.7%),#47(75.5%),#48(75.9%),#49(77.0%),#50(76.6%),#51(76.2%),#52(74.9%),#53(75.6%),#54(75.9%),#55(75.7%),#56(81.9%),#58(83.6%),#59(76.5%),#60(76.5%),#61(81.4%),#62(83.7%),#63(84.6%),#64(83.8%),#65(84.6%),#66(85.3%),#67(83.9%),#68(84.1%),#69(83.6%),#70(84.3%),#71(85.2%),#72(84.3%),#73(84.7%),#74(83.7%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(mi.info) AS release_date,MIN(t.title) AS modern_american_internet_movie FROM ((((((((aka_title AS at) JOIN company_name AS cn) JOIN company_type AS ct) JOIN info_type AS it1) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE cn.country_code="[us]" AND it1.info="release dates" AND mi.note LIKE "%internet%" AND mi.info IS NOT NULL AND (mi.info LIKE "USA:% 199%" OR mi.info LIKE "USA:% 200%") AND t.production_year>1990 AND t.id=at.movie_id AND t.id=mi.movie_id AND t.id=mk.movie_id AND t.id=mc.movie_id AND mk.movie_id=mi.movie_id AND mk.movie_id=mc.movie_id AND mk.movie_id=at.movie_id AND mi.movie_id=mc.movie_id AND mi.movie_id=at.movie_id AND mc.movie_id=at.movie_id AND k.id=mk.keyword_id AND it1.id=mi.info_type_id AND cn.id=mc.company_id AND ct.id=mc.company_type_id                                                                                                                                                                                                          |
| 15d.sql |          85 | 2600.2ms ± 3%         | 2110.0ms ± 3%           | 87.1%         | #51(81.1%),#52(82.8%),#53(81.4%),#57(82.8%),#58(82.1%),#63(87.6%),#66(86.8%),#67(88.1%),#68(85.7%),#69(85.9%),#72(86.3%)                                                                                                                                                                                                                                                              | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(at.title) AS aka_title,MIN(t.title) AS internet_movie_title FROM ((((((((aka_title AS at) JOIN company_name AS cn) JOIN company_type AS ct) JOIN info_type AS it1) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN movie_keyword AS mk) JOIN title AS t WHERE cn.country_code="[us]" AND it1.info="release dates" AND mi.note LIKE "%internet%" AND t.production_year>1990 AND t.id=at.movie_id AND t.id=mi.movie_id AND t.id=mk.movie_id AND t.id=mc.movie_id AND mk.movie_id=mi.movie_id AND mk.movie_id=mc.movie_id AND mk.movie_id=at.movie_id AND mi.movie_id=mc.movie_id AND mi.movie_id=at.movie_id AND mc.movie_id=at.movie_id AND k.id=mk.keyword_id AND it1.id=mi.info_type_id AND cn.id=mc.company_id AND ct.id=mc.company_type_id                                                                                                                                                                                                                                                                                                           |
| 16a.sql |          67 | 349.8ms ±159%         | 349.8ms ±159%           | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an.name) AS cool_actor_pseudonym,MIN(t.title) AS series_named_after_char FROM (((((((aka_name AS an) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE cn.country_code="[us]" AND k.keyword="character-name-in-title" AND t.episode_nr>=50 AND t.episode_nr<100 AND an.person_id=n.id AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND an.person_id=ci.person_id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                            |
| 16b.sql |          66 | 7869.5ms ± 5%         | 4409.8ms ± 5%           | 81.8%         | #23(87.5%),#24(85.1%),#25(85.8%),#26(84.1%),#31(86.7%),#32(83.9%),#33(83.0%),#35(87.4%),#37(82.8%),#48(76.4%),#51(56.0%),#52(69.6%)                                                                                                                                                                                                                                                   | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an.name) AS cool_actor_pseudonym,MIN(t.title) AS series_named_after_char FROM (((((((aka_name AS an) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE cn.country_code="[us]" AND k.keyword="character-name-in-title" AND an.person_id=n.id AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND an.person_id=ci.person_id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| 16c.sql |          67 | 1039.8ms ± 6%         | 1039.8ms ± 6%           | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an.name) AS cool_actor_pseudonym,MIN(t.title) AS series_named_after_char FROM (((((((aka_name AS an) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE cn.country_code="[us]" AND k.keyword="character-name-in-title" AND t.episode_nr<100 AND an.person_id=n.id AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND an.person_id=ci.person_id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| 16d.sql |          67 | 858.5ms ± 4%          | 858.5ms ± 4%            | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an.name) AS cool_actor_pseudonym,MIN(t.title) AS series_named_after_char FROM (((((((aka_name AS an) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE cn.country_code="[us]" AND k.keyword="character-name-in-title" AND t.episode_nr>=5 AND t.episode_nr<100 AND an.person_id=n.id AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND an.person_id=ci.person_id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 17a.sql |          63 | 3673.0ms ± 5%         | 2703.2ms ± 3%           | 76.2%         | #23(81.3%),#24(79.1%),#25(78.4%),#26(77.7%),#31(86.3%),#32(74.6%),#33(74.8%),#44(81.3%),#45(82.6%),#46(73.6%),#51(81.4%),#52(84.3%),#55(80.0%),#61(87.8%),#62(89.9%)                                                                                                                                                                                                                  | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS member_in_charnamed_american_movie,MIN(n.name) AS a1 FROM ((((((cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE cn.country_code="[us]" AND k.keyword="character-name-in-title" AND n.name LIKE "B%" AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 17b.sql |          68 | 4042.8ms ± 8%         | 4042.8ms ± 8%           | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS member_in_charnamed_movie,MIN(n.name) AS a1 FROM ((((((cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword="character-name-in-title" AND n.name LIKE "Z%" AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 17c.sql |          68 | 4002.0ms ± 4%         | 4002.0ms ± 4%           | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS member_in_charnamed_movie,MIN(n.name) AS a1 FROM ((((((cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword="character-name-in-title" AND n.name LIKE "X%" AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 17d.sql |          67 | 5679.2ms ± 5%         | 4079.8ms ± 8%           | 85.1%         | #24(88.7%),#25(88.2%),#31(89.9%),#32(84.0%),#33(84.8%),#44(89.8%),#46(86.8%),#60(88.8%),#65(75.5%),#66(71.8%)                                                                                                                                                                                                                                                                         | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS member_in_charnamed_movie FROM ((((((cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword="character-name-in-title" AND n.name LIKE "%Bert%" AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| 17e.sql |          62 | 3731.2ms ± 7%         | 2920.5ms ± 4%           | 80.6%         | #23(86.7%),#24(83.0%),#25(81.4%),#26(82.2%),#32(79.9%),#33(78.5%),#44(83.7%),#45(86.2%),#46(78.3%),#51(84.6%),#52(87.3%),#55(82.6%)                                                                                                                                                                                                                                                   | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS member_in_charnamed_movie FROM ((((((cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE cn.country_code="[us]" AND k.keyword="character-name-in-title" AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 17f.sql |          67 | 5949.2ms ± 5%         | 4352.8ms ± 9%           | 79.1%         | #23(88.3%),#24(86.3%),#25(85.9%),#26(88.7%),#31(87.6%),#32(82.4%),#33(82.1%),#44(88.1%),#46(83.7%),#59(89.7%),#60(89.0%),#63(89.1%),#65(73.2%),#66(74.1%)                                                                                                                                                                                                                             | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS member_in_charnamed_movie FROM ((((((cast_info AS ci) JOIN company_name AS cn) JOIN keyword AS k) JOIN movie_companies AS mc) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword="character-name-in-title" AND n.name LIKE "%B%" AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mk.movie_id AND mk.keyword_id=k.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.movie_id=mc.movie_id AND ci.movie_id=mk.movie_id AND mc.movie_id=mk.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| 19a.sql |          83 | 7315.0ms ± 8%         | 5936.2ms ± 1%           | 94.0%         | #29(82.7%),#30(82.3%),#31(81.2%),#32(83.5%),#33(83.0%)                                                                                                                                                                                                                                                                                                                                | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS voicing_actress,MIN(t.title) AS voiced_movie FROM (((((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN info_type AS it) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note IN ("(voice)","(voice: Japanese version)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND it.info="release dates" AND mc.note IS NOT NULL AND (mc.note LIKE "%(USA)%" OR mc.note LIKE "%(worldwide)%") AND mi.info IS NOT NULL AND (mi.info LIKE "Japan:%200%" OR mi.info LIKE "USA:%200%") AND n.gender="f" AND n.name LIKE "%Ang%" AND rt.role="actress" AND t.production_year BETWEEN 2005 AND 2009 AND t.id=mi.movie_id AND t.id=mc.movie_id AND t.id=ci.movie_id AND mc.movie_id=ci.movie_id AND mc.movie_id=mi.movie_id AND mi.movie_id=ci.movie_id AND cn.id=mc.company_id AND it.id=mi.info_type_id AND n.id=ci.person_id AND rt.id=ci.role_id AND n.id=an.person_id AND ci.person_id=an.person_id AND chn.id=ci.person_role_id |
| 19b.sql |          89 | 6522.0ms ± 5%         | 1901.5ms ± 3%           | 79.8%         | #32(29.6%),#33(29.5%),#34(29.2%),#35(32.3%),#36(37.4%),#37(42.9%),#38(42.6%),#39(42.4%),#40(43.0%),#41(42.0%),#42(42.7%),#43(43.1%),#44(42.2%),#45(42.1%),#46(41.5%),#47(42.7%),#48(42.6%),#49(84.5%)                                                                                                                                                                                 | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS voicing_actress,MIN(t.title) AS kung_fu_panda FROM (((((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN info_type AS it) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note="(voice)" AND cn.country_code="[us]" AND it.info="release dates" AND mc.note LIKE "%(200%)%" AND (mc.note LIKE "%(USA)%" OR mc.note LIKE "%(worldwide)%") AND mi.info IS NOT NULL AND (mi.info LIKE "Japan:%2007%" OR mi.info LIKE "USA:%2008%") AND n.gender="f" AND n.name LIKE "%Angel%" AND rt.role="actress" AND t.production_year BETWEEN 2007 AND 2008 AND t.title LIKE "%Kung%Fu%Panda%" AND t.id=mi.movie_id AND t.id=mc.movie_id AND t.id=ci.movie_id AND mc.movie_id=ci.movie_id AND mc.movie_id=mi.movie_id AND mi.movie_id=ci.movie_id AND cn.id=mc.company_id AND it.id=mi.info_type_id AND n.id=ci.person_id AND rt.id=ci.role_id AND n.id=an.person_id AND ci.person_id=an.person_id AND chn.id=ci.person_role_id                                        |
| 19c.sql |          84 | 7108.8ms ± 4%         | 6266.2ms ± 3%           | 82.1%         | #42(89.9%),#43(89.7%),#44(89.6%),#45(89.5%),#46(88.9%),#47(89.6%),#48(89.8%),#49(88.8%),#53(88.6%),#54(88.8%),#55(88.1%),#56(89.6%),#57(89.9%),#58(89.4%),#62(89.8%)                                                                                                                                                                                                                  | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS voicing_actress,MIN(t.title) AS jap_engl_voiced_movie FROM (((((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN info_type AS it) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note IN ("(voice)","(voice: Japanese version)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND it.info="release dates" AND mi.info IS NOT NULL AND (mi.info LIKE "Japan:%200%" OR mi.info LIKE "USA:%200%") AND n.gender="f" AND n.name LIKE "%An%" AND rt.role="actress" AND t.production_year>2000 AND t.id=mi.movie_id AND t.id=mc.movie_id AND t.id=ci.movie_id AND mc.movie_id=ci.movie_id AND mc.movie_id=mi.movie_id AND mi.movie_id=ci.movie_id AND cn.id=mc.company_id AND it.id=mi.info_type_id AND n.id=ci.person_id AND rt.id=ci.role_id AND n.id=an.person_id AND ci.person_id=an.person_id AND chn.id=ci.person_role_id                                                                                               |
| 19d.sql |          83 | 13492.5ms ± 3%        | 13492.5ms ± 3%          | 100.0%        |                                                                                                                                                                                                                                                                                                                                                                                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS voicing_actress,MIN(t.title) AS jap_engl_voiced_movie FROM (((((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN info_type AS it) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note IN ("(voice)","(voice: Japanese version)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND it.info="release dates" AND n.gender="f" AND rt.role="actress" AND t.production_year>2000 AND t.id=mi.movie_id AND t.id=mc.movie_id AND t.id=ci.movie_id AND mc.movie_id=ci.movie_id AND mc.movie_id=mi.movie_id AND mi.movie_id=ci.movie_id AND cn.id=mc.company_id AND it.id=mi.info_type_id AND n.id=ci.person_id AND rt.id=ci.role_id AND n.id=an.person_id AND ci.person_id=an.person_id AND chn.id=ci.person_role_id                                                                                                                                                                                                           |
+---------+-------------+------------------------+--------------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

+--------+-------------+------------------------+--------------------------+---------------+---------------------------------------------------------------------------------------------------------------+--------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID     | #PLAN SPACE | DEFAULT EXECUTION TIME | BEST PLAN EXECUTION TIME | EFFECTIVENESS | BETTER OPTIMAL PLANS                                                                                          | ESTROW Q-ERROR                                   | QUERY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+--------+-------------+------------------------+--------------------------+---------------+---------------------------------------------------------------------------------------------------------------+--------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5a.sql |          36 | 996.2ms ± 8%          | 996.2ms ± 8%            | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS typical_european_movie FROM ((((company_type AS ct) JOIN info_type AS it) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN title AS t WHERE ct.kind="production companies" AND mc.note LIKE "%(theatrical)%" AND mc.note LIKE "%(France)%" AND mi.info IN ("Sweden","Norway","Germany","Denmark","Swedish","Denish","Norwegian","German") AND t.production_year>2005 AND t.id=mi.movie_id AND t.id=mc.movie_id AND mc.movie_id=mi.movie_id AND ct.id=mc.company_type_id AND it.id=mi.info_type_id                                                                                                                                                                                                                                                                                                                                                                            |
| 5b.sql |          36 | 1064.2ms ±12%         | 1064.2ms ±12%           | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS american_vhs_movie FROM ((((company_type AS ct) JOIN info_type AS it) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN title AS t WHERE ct.kind="production companies" AND mc.note LIKE "%(VHS)%" AND mc.note LIKE "%(USA)%" AND mc.note LIKE "%(1994)%" AND mi.info IN ("USA","America") AND t.production_year>2010 AND t.id=mi.movie_id AND t.id=mc.movie_id AND mc.movie_id=mi.movie_id AND ct.id=mc.company_type_id AND it.id=mi.info_type_id                                                                                                                                                                                                                                                                                                                                                                                                                            |
| 5c.sql |          36 | 1396.0ms ± 4%         | 935.0ms ± 3%            | 77.8%         | #13(68.3%),#14(67.0%),#15(67.6%),#18(76.7%),#19(78.4%),#20(78.2%),#22(77.5%),#23(77.3%)                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(t.title) AS american_movie FROM ((((company_type AS ct) JOIN info_type AS it) JOIN movie_companies AS mc) JOIN movie_info AS mi) JOIN title AS t WHERE ct.kind="production companies" AND mc.note NOT LIKE "%(TV)%" AND mc.note LIKE "%(USA)%" AND mi.info IN ("Sweden","Norway","Germany","Denmark","Swedish","Denish","Norwegian","German","USA","American") AND t.production_year>1990 AND t.id=mi.movie_id AND t.id=mc.movie_id AND mc.movie_id=mi.movie_id AND ct.id=mc.company_type_id AND it.id=mi.info_type_id                                                                                                                                                                                                                                                                                                                                                                          |
| 6a.sql |          33 | 48.0ms ±72%           | 48.0ms ±72%             | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(k.keyword) AS movie_keyword,MIN(n.name) AS actor_name,MIN(t.title) AS marvel_movie FROM ((((cast_info AS ci) JOIN keyword AS k) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword="marvel-cinematic-universe" AND n.name LIKE "%Downey%Robert%" AND t.production_year>2010 AND k.id=mk.keyword_id AND t.id=mk.movie_id AND t.id=ci.movie_id AND ci.movie_id=mk.movie_id AND n.id=ci.person_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 6b.sql |          35 | 732.8ms ± 9%          | 732.8ms ± 9%            | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(k.keyword) AS movie_keyword,MIN(n.name) AS actor_name,MIN(t.title) AS hero_movie FROM ((((cast_info AS ci) JOIN keyword AS k) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword IN ("superhero","sequel","second-part","marvel-comics","based-on-comic","tv-special","fight","violence") AND n.name LIKE "%Downey%Robert%" AND t.production_year>2014 AND k.id=mk.keyword_id AND t.id=mk.movie_id AND t.id=ci.movie_id AND ci.movie_id=mk.movie_id AND n.id=ci.person_id                                                                                                                                                                                                                                                                                                                                                                                                |
| 6c.sql |          33 | 25.2ms ±36%           | 25.2ms ±36%             | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(k.keyword) AS movie_keyword,MIN(n.name) AS actor_name,MIN(t.title) AS marvel_movie FROM ((((cast_info AS ci) JOIN keyword AS k) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword="marvel-cinematic-universe" AND n.name LIKE "%Downey%Robert%" AND t.production_year>2014 AND k.id=mk.keyword_id AND t.id=mk.movie_id AND t.id=ci.movie_id AND ci.movie_id=mk.movie_id AND n.id=ci.person_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 6d.sql |          35 | 2790.8ms ± 5%         | 2178.0ms ± 8%           | 77.1%         | #10(84.6%),#11(80.8%),#12(83.8%),#17(88.3%),#18(80.1%),#19(80.9%),#22(88.2%),#24(78.0%)                       | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(k.keyword) AS movie_keyword,MIN(n.name) AS actor_name,MIN(t.title) AS hero_movie FROM ((((cast_info AS ci) JOIN keyword AS k) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword IN ("superhero","sequel","second-part","marvel-comics","based-on-comic","tv-special","fight","violence") AND n.name LIKE "%Downey%Robert%" AND t.production_year>2000 AND k.id=mk.keyword_id AND t.id=mk.movie_id AND t.id=ci.movie_id AND ci.movie_id=mk.movie_id AND n.id=ci.person_id                                                                                                                                                                                                                                                                                                                                                                                                |
| 6e.sql |          33 | 38.5ms ± 3%           | 38.5ms ± 3%             | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(k.keyword) AS movie_keyword,MIN(n.name) AS actor_name,MIN(t.title) AS marvel_movie FROM ((((cast_info AS ci) JOIN keyword AS k) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword="marvel-cinematic-universe" AND n.name LIKE "%Downey%Robert%" AND t.production_year>2000 AND k.id=mk.keyword_id AND t.id=mk.movie_id AND t.id=ci.movie_id AND ci.movie_id=mk.movie_id AND n.id=ci.person_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 6f.sql |          35 | 2781.5ms ± 2%         | 2261.2ms ± 6%           | 85.7%         | #10(87.4%),#11(86.1%),#18(84.4%),#19(84.4%),#24(81.3%)                                                        | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(k.keyword) AS movie_keyword,MIN(n.name) AS actor_name,MIN(t.title) AS hero_movie FROM ((((cast_info AS ci) JOIN keyword AS k) JOIN movie_keyword AS mk) JOIN name AS n) JOIN title AS t WHERE k.keyword IN ("superhero","sequel","second-part","marvel-comics","based-on-comic","tv-special","fight","violence") AND t.production_year>2000 AND k.id=mk.keyword_id AND t.id=mk.movie_id AND t.id=ci.movie_id AND ci.movie_id=mk.movie_id AND n.id=ci.person_id                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 7a.sql |          59 | 546.5ms ±48%          | 546.5ms ±48%            | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS of_person,MIN(t.title) AS biography_movie FROM (((((((aka_name AS an) JOIN cast_info AS ci) JOIN info_type AS it) JOIN link_type AS lt) JOIN movie_link AS ml) JOIN name AS n) JOIN person_info AS pi) JOIN 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                                                                                                     |
| 7b.sql |          67 | 435.2ms ±10%          | 435.2ms ±10%            | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS of_person,MIN(t.title) AS biography_movie FROM (((((((aka_name AS an) JOIN cast_info AS ci) JOIN info_type AS it) JOIN link_type AS lt) JOIN movie_link AS ml) JOIN name AS n) JOIN person_info AS pi) JOIN 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                                                                                                                                                        |
| 7c.sql |          59 | 8470.0ms ±14%         | 8470.0ms ±14%           | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(n.name) AS cast_member_name,MIN(pi.info) AS cast_member_info FROM (((((((aka_name AS an) JOIN cast_info AS ci) JOIN info_type AS it) JOIN link_type AS lt) JOIN movie_link AS ml) JOIN name AS n) JOIN person_info AS pi) JOIN title AS t WHERE an.name IS NOT NULL AND (an.name LIKE "%a%" OR an.name LIKE "A%") AND it.info="mini biography" AND lt.link IN ("references","referenced in","features","featured in") AND n.name_pcode_cf BETWEEN "A" AND "F" AND (n.gender="m" OR (n.gender="f" AND n.name LIKE "A%")) AND pi.note IS NOT NULL AND t.production_year BETWEEN 1980 AND 2010 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 |
| 8a.sql |          60 | 4397.5ms ±13%         | 3666.2ms ± 2%           | 95.0%         | #27(83.7%),#28(84.5%),#29(83.4%)                                                                              | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an1.name) AS actress_pseudonym,MIN(t.title) AS japanese_movie_dubbed FROM ((((((aka_name AS an1) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN movie_companies AS mc) JOIN name AS n1) JOIN role_type AS rt) JOIN title AS t WHERE ci.note="(voice: English version)" AND cn.country_code="[jp]" AND mc.note LIKE "%(Japan)%" AND mc.note NOT LIKE "%(USA)%" AND n1.name LIKE "%Yo%" AND n1.name NOT LIKE "%Yu%" AND rt.role="actress" AND an1.person_id=n1.id AND n1.id=ci.person_id AND ci.movie_id=t.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.role_id=rt.id AND an1.person_id=ci.person_id AND ci.movie_id=mc.movie_id                                                                                                                                                                                                                                                   |
| 8b.sql |          61 | 4405.8ms ± 5%         | 3436.0ms ± 4%           | 93.4%         | #25(78.2%),#26(79.9%),#27(78.0%),#28(89.1%)                                                                   | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an.name) AS acress_pseudonym,MIN(t.title) AS japanese_anime_movie FROM ((((((aka_name AS an) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN movie_companies AS mc) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note="(voice: English version)" AND cn.country_code="[jp]" AND mc.note LIKE "%(Japan)%" AND mc.note NOT LIKE "%(USA)%" AND (mc.note LIKE "%(2006)%" OR mc.note LIKE "%(2007)%") AND n.name LIKE "%Yo%" AND n.name NOT LIKE "%Yu%" AND rt.role="actress" AND t.production_year BETWEEN 2006 AND 2007 AND (t.title LIKE "One Piece%" OR t.title LIKE "Dragon Ball Z%") AND an.person_id=n.id AND n.id=ci.person_id AND ci.movie_id=t.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.role_id=rt.id AND an.person_id=ci.person_id AND ci.movie_id=mc.movie_id                                                                                        |
| 8c.sql |          63 | 7323.8ms ± 3%         | 7323.8ms ± 3%           | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(a1.name) AS writer_pseudo_name,MIN(t.title) AS movie_title FROM ((((((aka_name AS a1) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN movie_companies AS mc) JOIN name AS n1) JOIN role_type AS rt) JOIN title AS t WHERE cn.country_code="[us]" AND rt.role="writer" AND a1.person_id=n1.id AND n1.id=ci.person_id AND ci.movie_id=t.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.role_id=rt.id AND a1.person_id=ci.person_id AND ci.movie_id=mc.movie_id                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 8d.sql |          63 | 1695.5ms ± 4%         | 1695.5ms ± 4%           | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an1.name) AS costume_designer_pseudo,MIN(t.title) AS movie_with_costumes FROM ((((((aka_name AS an1) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN movie_companies AS mc) JOIN name AS n1) JOIN role_type AS rt) JOIN title AS t WHERE cn.country_code="[us]" AND rt.role="costume designer" AND an1.person_id=n1.id AND n1.id=ci.person_id AND ci.movie_id=t.id AND t.id=mc.movie_id AND mc.company_id=cn.id AND ci.role_id=rt.id AND an1.person_id=ci.person_id AND ci.movie_id=mc.movie_id                                                                                                                                                                                                                                                                                                                                                                                             |
| 9a.sql |          72 | 5011.2ms ± 5%         | 3794.0ms ± 2%           | 86.1%         | #30(76.8%),#31(76.7%),#32(75.7%),#33(84.1%),#38(89.9%),#39(88.1%),#40(89.7%),#46(88.4%),#50(89.7%),#54(89.9%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an.name) AS alternative_name,MIN(chn.name) AS character_name,MIN(t.title) AS movie FROM (((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN movie_companies AS mc) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note IN ("(voice)","(voice: Japanese version)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND mc.note IS NOT NULL AND (mc.note LIKE "%(USA)%" OR mc.note LIKE "%(worldwide)%") AND n.gender="f" AND n.name LIKE "%Ang%" AND rt.role="actress" AND t.production_year BETWEEN 2005 AND 2015 AND ci.movie_id=t.id AND t.id=mc.movie_id AND ci.movie_id=mc.movie_id AND mc.company_id=cn.id AND ci.role_id=rt.id AND n.id=ci.person_id AND chn.id=ci.person_role_id AND an.person_id=n.id AND an.person_id=ci.person_id                                                          |
| 9b.sql |          75 | 4628.2ms ± 4%         | 3813.5ms ± 6%           | 96.0%         | #34(82.4%),#35(84.0%),#36(82.6%)                                                                              | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an.name) AS alternative_name,MIN(chn.name) AS voiced_character,MIN(n.name) AS voicing_actress,MIN(t.title) AS american_movie FROM (((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN movie_companies AS mc) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note="(voice)" AND cn.country_code="[us]" AND mc.note LIKE "%(200%)%" AND (mc.note LIKE "%(USA)%" OR mc.note LIKE "%(worldwide)%") AND n.gender="f" AND n.name LIKE "%Angel%" AND rt.role="actress" AND t.production_year BETWEEN 2007 AND 2010 AND ci.movie_id=t.id AND t.id=mc.movie_id AND ci.movie_id=mc.movie_id AND mc.company_id=cn.id AND ci.role_id=rt.id AND n.id=ci.person_id AND chn.id=ci.person_role_id AND an.person_id=n.id AND an.person_id=ci.person_id                                                                                             |
| 9c.sql |          68 | 4877.0ms ± 6%         | 3984.5ms ± 2%           | 95.6%         | #28(82.4%),#29(83.4%),#30(81.7%)                                                                              | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an.name) AS alternative_name,MIN(chn.name) AS voiced_character_name,MIN(n.name) AS voicing_actress,MIN(t.title) AS american_movie FROM (((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN movie_companies AS mc) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note IN ("(voice)","(voice: Japanese version)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND n.gender="f" AND n.name LIKE "%An%" AND rt.role="actress" AND ci.movie_id=t.id AND t.id=mc.movie_id AND ci.movie_id=mc.movie_id AND mc.company_id=cn.id AND ci.role_id=rt.id AND n.id=ci.person_id AND chn.id=ci.person_role_id AND an.person_id=n.id AND an.person_id=ci.person_id                                                                                                                                             |
| 9d.sql |          68 | 6426.2ms ± 3%         | 6426.2ms ± 3%           | 100.0%        |                                                                                                               | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT MIN(an.name) AS alternative_name,MIN(chn.name) AS voiced_char_name,MIN(n.name) AS voicing_actress,MIN(t.title) AS american_movie FROM (((((((aka_name AS an) JOIN char_name AS chn) JOIN cast_info AS ci) JOIN company_name AS cn) JOIN movie_companies AS mc) JOIN name AS n) JOIN role_type AS rt) JOIN title AS t WHERE ci.note IN ("(voice)","(voice: Japanese version)","(voice) (uncredited)","(voice: English version)") AND cn.country_code="[us]" AND n.gender="f" AND rt.role="actress" AND ci.movie_id=t.id AND t.id=mc.movie_id AND ci.movie_id=mc.movie_id AND mc.company_id=cn.id AND ci.role_id=rt.id AND n.id=ci.person_id AND chn.id=ci.person_role_id AND an.person_id=n.id AND an.person_id=ci.person_id                                                                                                                                                                         |
+--------+-------------+------------------------+--------------------------+---------------+---------------------------------------------------------------------------------------------------------------+--------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

How to read the report:

  • ID: query id
  • #PLAN SPACE: the plan space size of a query
  • DEFAULT EXECUTION TIME: the execution time of default plan, giving in the format of "Mean ±Diff", "Mean" is the mean value of round rounds, and "Diff" is the lower/upper bound of the mean value
  • BEST PLAN EXECUTION TIME: the execution time of the best plan
  • EFFECTIVENESS: the percent of the execution time of the default plan better than others on plan space
  • BETTER OPTIMAL PLANS: gives the better plan, each item is giving in the format of "nth_plan id(<execution time> / <default execution time>)"
  • QUERY: the query
@mahjonp mahjonp added the sig/planner SIG: Planner label Jul 16, 2020
@mahjonp
Copy link
Contributor Author

mahjonp commented Jul 21, 2020 •
edited
Loading

Index Selection

Reproduction step

  1. download indexes files: dyn.zip
  2. add index: ./bin/horo -d "root:@tcp(localhost:4000)/imdb?charset=utf8" index add
  3. bench: ./bin/horo -r 4 -d "root:@tcp(localhost:4000)/imdb?charset=utf8" bench

log.txt

+--------+-------------+------------------------+--------------------------+---------------+-------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID     | #PLAN SPACE | DEFAULT EXECUTION TIME | BEST PLAN EXECUTION TIME | EFFECTIVENESS | BETTER OPTIMAL PLANS          | ESTROW Q-ERROR                                   | QUERY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+--------+-------------+------------------------+--------------------------+---------------+-------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1.sql  |           2 | 623.0ms ±25%          | 401.8ms ±30%            | 50.0%         | #1(64.5%)                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM (aka_title) JOIN movie_link WHERE ((aka_title.id<=>242091 OR aka_title.id>242091) AND (aka_title.movie_id<=>3865448 OR aka_title.movie_id>3865448) OR (aka_title.title<=>"Golden jade slave" OR aka_title.title<"Golden jade slave") OR (aka_title.kind_id<=>1 OR aka_title.kind_id>1) OR (aka_title.production_year<=>1962 OR aka_title.production_year>1962) OR (aka_title.phonetic_code<=>"G4352" OR aka_title.phonetic_code<"G4352") AND (aka_title.episode_of_id<=>NULL OR aka_title.episode_of_id<NULL) OR (aka_title.md5sum<=>"0422d64139af699bceac3265e154fe04" OR aka_title.md5sum>"0422d64139af699bceac3265e154fe04")) AND ((movie_link.id<=>1996389 OR movie_link.id>1996389) AND (movie_link.movie_id<=>4352804 OR movie_link.movie_id>4352804) AND (movie_link.linked_movie_id<=>4352801 OR movie_link.linked_movie_id<4352801) AND (movie_link.link_type_id<=>1 OR movie_link.link_type_id>1)) LIMIT 100                                                                                                                                                                                                                                                                                                                                             |
| 2.sql  |           8 | 1823.5ms ±13%         | 1585.2ms ±17%           | 87.5%         | #8(86.9%)                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM ((aka_title) JOIN movie_link) JOIN role_type WHERE ((aka_title.id<=>376255 OR aka_title.id>376255) AND (aka_title.movie_id<=>4214852 OR aka_title.movie_id<4214852) OR (aka_title.title<=>"Five Bottles of Vodka" OR aka_title.title<"Five Bottles of Vodka") OR (aka_title.kind_id<=>1 OR aka_title.kind_id<1) AND (aka_title.production_year<=>2001 OR aka_title.production_year<2001) OR (aka_title.phonetic_code<=>"F1342" OR aka_title.phonetic_code>"F1342") AND (aka_title.episode_of_id<=>NULL OR aka_title.episode_of_id>NULL) AND (aka_title.md5sum<=>"8967ac41f4e49966612fe9a32fea51bc" OR aka_title.md5sum>"8967ac41f4e49966612fe9a32fea51bc")) AND ((movie_link.id<=>553749 OR movie_link.id>553749) OR (movie_link.movie_id<=>2796348 OR movie_link.movie_id>2796348) OR (movie_link.linked_movie_id<=>3801095 OR movie_link.linked_movie_id>3801095) OR (movie_link.link_type_id<=>5 OR movie_link.link_type_id<5)) AND ((role_type.id<=>4 OR role_type.id<4) AND (role_type.role<=>"writer" OR role_type.role<"writer")) LIMIT 100                                                                                                                                                                                                                 |
| 3.sql  |           6 | 5879.8ms ±51%         | 2026.8ms ±15%           | 50.0%         | #4(43.0%),#5(34.5%),#6(37.7%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM (movie_keyword) JOIN name WHERE ((movie_keyword.id<=>2737256 OR movie_keyword.id>2737256) AND (movie_keyword.movie_id<=>3516666 OR movie_keyword.movie_id>3516666) OR (movie_keyword.keyword_id<=>48502 OR movie_keyword.keyword_id<48502)) AND ((name.id<=>3485090 OR name.id>3485090) AND (name.name<=>"López, Gaby" OR name.name<"López, Gaby") OR (name.imdb_id<=>NULL OR name.imdb_id>NULL) AND (name.gender<=>"f" OR name.gender<"f") AND (name.name_pcode_cf<=>"L121" OR name.name_pcode_cf>"L121") AND (name.name_pcode_nf<=>"G1412" OR name.name_pcode_nf>"G1412") AND (name.surname_pcode<=>"L12" OR name.surname_pcode<"L12") OR (name.md5sum<=>"14a2e9f03adf9be89fb406fb9e0b4748" OR name.md5sum<"14a2e9f03adf9be89fb406fb9e0b4748")) LIMIT 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+--------+-------------+------------------------+--------------------------+---------------+-------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

log.txt

+--------+-------------+------------------------+--------------------------+---------------+-------------------------------------------------------------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID     | #PLAN SPACE | DEFAULT EXECUTION TIME | BEST PLAN EXECUTION TIME | EFFECTIVENESS | BETTER OPTIMAL PLANS                                        | ESTROW Q-ERROR                                   | QUERY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+--------+-------------+------------------------+--------------------------+---------------+-------------------------------------------------------------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 13.sql |           2 | 3690.8ms ±71%         | 1983.8ms ±21%           | 50.0%         | #2(53.7%)                                                   | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM (movie_companies) JOIN person_info WHERE ((movie_companies.id<=>3223225 OR movie_companies.id<3223225) OR (movie_companies.movie_id<=>2400151 OR movie_companies.movie_id>2400151) OR (movie_companies.company_id<=>13496 OR movie_companies.company_id>13496) AND (movie_companies.company_type_id<=>2 OR movie_companies.company_type_id>2)) AND ((person_info.id<=>3582946 OR person_info.id<3582946) AND (person_info.person_id<=>2299555 OR person_info.person_id<2299555) AND (person_info.info_type_id<=>17 OR person_info.info_type_id>17)) LIMIT 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| 37.sql |           8 | 44037.2ms ±14%        | 18121.5ms ±14%          | 25.0%         | #1(45.2%),#2(41.2%),#3(43.4%),#4(43.4%),#5(42.7%),#6(43.0%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM ((cast_info) JOIN char_name) JOIN link_type WHERE ((cast_info.id<=>143490 OR cast_info.id<143490) OR (cast_info.person_id<=>20921 OR cast_info.person_id<20921) OR (cast_info.movie_id<=>3636833 OR cast_info.movie_id>3636833) AND (cast_info.person_role_id<=>NULL OR cast_info.person_role_id>NULL) AND (cast_info.nr_order<=>8 OR cast_info.nr_order<8) OR (cast_info.role_id<=>1 OR cast_info.role_id<1)) AND ((char_name.id<=>3117118 OR char_name.id>3117118) OR (char_name.name<=>"La mère toilettes aéroport" OR char_name.name>"La mère toilettes aéroport") OR (char_name.imdb_index<=>NULL OR char_name.imdb_index>NULL) AND (char_name.imdb_id<=>NULL OR char_name.imdb_id>NULL) AND (char_name.name_pcode_nf<=>"L5634" OR char_name.name_pcode_nf>"L5634") AND (char_name.surname_pcode<=>"A6163" OR char_name.surname_pcode<"A6163") OR (char_name.md5sum<=>"9ce53f6be0e22e7ae1d79d738f6d468f" OR char_name.md5sum>"9ce53f6be0e22e7ae1d79d738f6d468f")) AND ((link_type.id<=>16 OR link_type.id<16) OR (link_type.link<=>"edited from" OR link_type.link>"edited from")) LIMIT 100                                                                                                                                                                 |
| 8.sql  |           8 | 390.2ms ±11%          | 212.8ms ± 8%            | 25.0%         | #1(57.9%),#2(59.3%),#3(57.1%),#4(54.5%),#5(55.7%),#6(55.7%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM ((keyword) JOIN company_name) JOIN company_type WHERE ((keyword.id<=>53697 OR keyword.id<53697) OR (keyword.keyword<=>"shadow-monster" OR keyword.keyword<"shadow-monster") AND (keyword.phonetic_code<=>"S3523" OR keyword.phonetic_code>"S3523")) AND ((company_name.id<=>188721 OR company_name.id>188721) AND (company_name.name<=>"Cruel Stories Inc." OR company_name.name<"Cruel Stories Inc.") AND (company_name.country_code<=>NULL OR company_name.country_code<NULL) AND (company_name.imdb_id<=>NULL OR company_name.imdb_id>NULL) AND (company_name.name_pcode_nf<=>"C6423" OR company_name.name_pcode_nf<"C6423") OR (company_name.name_pcode_sf<=>NULL OR company_name.name_pcode_sf>NULL) AND (company_name.md5sum<=>"478e25ef9abacb4804a3f9a912854c37" OR company_name.md5sum>"478e25ef9abacb4804a3f9a912854c37")) AND ((company_type.id<=>1 OR company_type.id<1) OR (company_type.kind<=>"distributors" OR company_type.kind>"distributors")) LIMIT 100                                                                                                                                                                                                                                                                                            |
+--------+-------------+------------------------+--------------------------+---------------+-------------------------------------------------------------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

@qw4990
Copy link
Contributor

qw4990 commented Jul 21, 2020

Index Selection

Reproduction step

  1. download indexes files: dyn.zip
  2. add index: ./bin/horo -d "root:@tcp(localhost:4000)/imdb?charset=utf8" index add
  3. bench: ./bin/horo -r 4 -d "root:@tcp(localhost:4000)/imdb?charset=utf8" bench

log.txt

+--------+-------------+------------------------+--------------------------+---------------+-------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID     | #PLAN SPACE | DEFAULT EXECUTION TIME | BEST PLAN EXECUTION TIME | EFFECTIVENESS | BETTER OPTIMAL PLANS          | ESTROW Q-ERROR                                   | QUERY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+--------+-------------+------------------------+--------------------------+---------------+-------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1.sql  |           2 | 623.0ms ±25%          | 401.8ms ±30%            | 50.0%         | #1(64.5%)                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM (aka_title) JOIN movie_link WHERE ((aka_title.id<=>242091 OR aka_title.id>242091) AND (aka_title.movie_id<=>3865448 OR aka_title.movie_id>3865448) OR (aka_title.title<=>"Golden jade slave" OR aka_title.title<"Golden jade slave") OR (aka_title.kind_id<=>1 OR aka_title.kind_id>1) OR (aka_title.production_year<=>1962 OR aka_title.production_year>1962) OR (aka_title.phonetic_code<=>"G4352" OR aka_title.phonetic_code<"G4352") AND (aka_title.episode_of_id<=>NULL OR aka_title.episode_of_id<NULL) OR (aka_title.md5sum<=>"0422d64139af699bceac3265e154fe04" OR aka_title.md5sum>"0422d64139af699bceac3265e154fe04")) AND ((movie_link.id<=>1996389 OR movie_link.id>1996389) AND (movie_link.movie_id<=>4352804 OR movie_link.movie_id>4352804) AND (movie_link.linked_movie_id<=>4352801 OR movie_link.linked_movie_id<4352801) AND (movie_link.link_type_id<=>1 OR movie_link.link_type_id>1)) LIMIT 100                                                                                                                                                                                                                                                                                                                                             |
| 2.sql  |           8 | 1823.5ms ±13%         | 1585.2ms ±17%           | 87.5%         | #8(86.9%)                     | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM ((aka_title) JOIN movie_link) JOIN role_type WHERE ((aka_title.id<=>376255 OR aka_title.id>376255) AND (aka_title.movie_id<=>4214852 OR aka_title.movie_id<4214852) OR (aka_title.title<=>"Five Bottles of Vodka" OR aka_title.title<"Five Bottles of Vodka") OR (aka_title.kind_id<=>1 OR aka_title.kind_id<1) AND (aka_title.production_year<=>2001 OR aka_title.production_year<2001) OR (aka_title.phonetic_code<=>"F1342" OR aka_title.phonetic_code>"F1342") AND (aka_title.episode_of_id<=>NULL OR aka_title.episode_of_id>NULL) AND (aka_title.md5sum<=>"8967ac41f4e49966612fe9a32fea51bc" OR aka_title.md5sum>"8967ac41f4e49966612fe9a32fea51bc")) AND ((movie_link.id<=>553749 OR movie_link.id>553749) OR (movie_link.movie_id<=>2796348 OR movie_link.movie_id>2796348) OR (movie_link.linked_movie_id<=>3801095 OR movie_link.linked_movie_id>3801095) OR (movie_link.link_type_id<=>5 OR movie_link.link_type_id<5)) AND ((role_type.id<=>4 OR role_type.id<4) AND (role_type.role<=>"writer" OR role_type.role<"writer")) LIMIT 100                                                                                                                                                                                                                 |
| 3.sql  |           6 | 5879.8ms ±51%         | 2026.8ms ±15%           | 50.0%         | #4(43.0%),#5(34.5%),#6(37.7%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM (movie_keyword) JOIN name WHERE ((movie_keyword.id<=>2737256 OR movie_keyword.id>2737256) AND (movie_keyword.movie_id<=>3516666 OR movie_keyword.movie_id>3516666) OR (movie_keyword.keyword_id<=>48502 OR movie_keyword.keyword_id<48502)) AND ((name.id<=>3485090 OR name.id>3485090) AND (name.name<=>"López, Gaby" OR name.name<"López, Gaby") OR (name.imdb_id<=>NULL OR name.imdb_id>NULL) AND (name.gender<=>"f" OR name.gender<"f") AND (name.name_pcode_cf<=>"L121" OR name.name_pcode_cf>"L121") AND (name.name_pcode_nf<=>"G1412" OR name.name_pcode_nf>"G1412") AND (name.surname_pcode<=>"L12" OR name.surname_pcode<"L12") OR (name.md5sum<=>"14a2e9f03adf9be89fb406fb9e0b4748" OR name.md5sum<"14a2e9f03adf9be89fb406fb9e0b4748")) LIMIT 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+--------+-------------+------------------------+--------------------------+---------------+-------------------------------+--------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

log.txt

+--------+-------------+------------------------+--------------------------+---------------+-------------------------------------------------------------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID     | #PLAN SPACE | DEFAULT EXECUTION TIME | BEST PLAN EXECUTION TIME | EFFECTIVENESS | BETTER OPTIMAL PLANS                                        | ESTROW Q-ERROR                                   | QUERY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+--------+-------------+------------------------+--------------------------+---------------+-------------------------------------------------------------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 13.sql |           2 | 3690.8ms ±71%         | 1983.8ms ±21%           | 50.0%         | #2(53.7%)                                                   | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM (movie_companies) JOIN person_info WHERE ((movie_companies.id<=>3223225 OR movie_companies.id<3223225) OR (movie_companies.movie_id<=>2400151 OR movie_companies.movie_id>2400151) OR (movie_companies.company_id<=>13496 OR movie_companies.company_id>13496) AND (movie_companies.company_type_id<=>2 OR movie_companies.company_type_id>2)) AND ((person_info.id<=>3582946 OR person_info.id<3582946) AND (person_info.person_id<=>2299555 OR person_info.person_id<2299555) AND (person_info.info_type_id<=>17 OR person_info.info_type_id>17)) LIMIT 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| 37.sql |           8 | 44037.2ms ±14%        | 18121.5ms ±14%          | 25.0%         | #1(45.2%),#2(41.2%),#3(43.4%),#4(43.4%),#5(42.7%),#6(43.0%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM ((cast_info) JOIN char_name) JOIN link_type WHERE ((cast_info.id<=>143490 OR cast_info.id<143490) OR (cast_info.person_id<=>20921 OR cast_info.person_id<20921) OR (cast_info.movie_id<=>3636833 OR cast_info.movie_id>3636833) AND (cast_info.person_role_id<=>NULL OR cast_info.person_role_id>NULL) AND (cast_info.nr_order<=>8 OR cast_info.nr_order<8) OR (cast_info.role_id<=>1 OR cast_info.role_id<1)) AND ((char_name.id<=>3117118 OR char_name.id>3117118) OR (char_name.name<=>"La mère toilettes aéroport" OR char_name.name>"La mère toilettes aéroport") OR (char_name.imdb_index<=>NULL OR char_name.imdb_index>NULL) AND (char_name.imdb_id<=>NULL OR char_name.imdb_id>NULL) AND (char_name.name_pcode_nf<=>"L5634" OR char_name.name_pcode_nf>"L5634") AND (char_name.surname_pcode<=>"A6163" OR char_name.surname_pcode<"A6163") OR (char_name.md5sum<=>"9ce53f6be0e22e7ae1d79d738f6d468f" OR char_name.md5sum>"9ce53f6be0e22e7ae1d79d738f6d468f")) AND ((link_type.id<=>16 OR link_type.id<16) OR (link_type.link<=>"edited from" OR link_type.link>"edited from")) LIMIT 100                                                                                                                                                                 |
| 8.sql  |           8 | 390.2ms ±11%          | 212.8ms ± 8%            | 25.0%         | #1(57.9%),#2(59.3%),#3(57.1%),#4(54.5%),#5(55.7%),#6(55.7%) | count:0, median:NaN, 90th:NaN, 95th:NaN, max:NaN | SELECT * FROM ((keyword) JOIN company_name) JOIN company_type WHERE ((keyword.id<=>53697 OR keyword.id<53697) OR (keyword.keyword<=>"shadow-monster" OR keyword.keyword<"shadow-monster") AND (keyword.phonetic_code<=>"S3523" OR keyword.phonetic_code>"S3523")) AND ((company_name.id<=>188721 OR company_name.id>188721) AND (company_name.name<=>"Cruel Stories Inc." OR company_name.name<"Cruel Stories Inc.") AND (company_name.country_code<=>NULL OR company_name.country_code<NULL) AND (company_name.imdb_id<=>NULL OR company_name.imdb_id>NULL) AND (company_name.name_pcode_nf<=>"C6423" OR company_name.name_pcode_nf<"C6423") OR (company_name.name_pcode_sf<=>NULL OR company_name.name_pcode_sf>NULL) AND (company_name.md5sum<=>"478e25ef9abacb4804a3f9a912854c37" OR company_name.md5sum>"478e25ef9abacb4804a3f9a912854c37")) AND ((company_type.id<=>1 OR company_type.id<1) OR (company_type.kind<=>"distributors" OR company_type.kind>"distributors")) LIMIT 100                                                                                                                                                                                                                                                                                            |
+--------+-------------+------------------------+--------------------------+---------------+-------------------------------------------------------------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Thanks~ I will analyze these slow SQLs soon.

@mahjonp mahjonp mentioned this issue Jul 31, 2020
Open
@qw4990
Copy link
Contributor

qw4990 commented Aug 6, 2020 •
edited
Loading

Here is the Chinese analysis document about index selection tests.

@mahjonp
Copy link
Contributor Author

mahjonp commented Aug 6, 2020 •
edited
Loading

I generate 50 SQLs on only one table each, to get rid of the interference of table join, this is the report, you need to apply the same add-indexes.sql on above.

+--------+-------------+------------------------+--------------------------+---------------+-----------------------------------------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID     | #PLAN SPACE | DEFAULT EXECUTION TIME | BEST PLAN EXECUTION TIME | EFFECTIVENESS | BETTER OPTIMAL PLANS                    | ESTROW Q-ERROR                                             | QUERY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+--------+-------------+------------------------+--------------------------+---------------+-----------------------------------------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 21.sql |           2 |  1: 5.6ms ±100%       | 2.0ms ± 0%              | 50.0%         | #1(35.7%)                               | count:1, median:1.7, 90th:1.7, 95th:1.7, max:1.7           | SELECT * FROM cast_info WHERE ((cast_info.id<=47868212) AND (cast_info.person_id<=2245656) AND (cast_info.movie_id<=3347883) OR (cast_info.person_role_id IS NULL) AND (cast_info.nr_order IS NULL) AND (cast_info.role_id<=5)) LIMIT 100                                                                                                                                                                                                                                                              |
| 3.sql  |           6 |  0: 423.4ms ±99%      | 153.4ms ±78%            | 66.7%         | #4(36.2%),#6(46.9%)                     | count:1, median:6.6, 90th:6.6, 95th:6.6, max:6.6           | SELECT * FROM aka_title WHERE ((aka_title.id>=329003) AND (aka_title.movie_id<=4074751) OR (aka_title.title<="Bloody Banjo: Extreme Edition") AND (aka_title.imdb_index IS NULL) AND (aka_title.kind_id>=1) OR (aka_title.production_year>=2017) AND (aka_title.phonetic_code<="B4315") AND (aka_title.episode_of_id IS NULL) AND (aka_title.season_nr IS NULL) AND (aka_title.episode_nr IS NULL) AND (aka_title.md5sum>="405b8b1a7b237a107610fddd4adcb4f3")) ORDER BY aka_title.imdb_index LIMIT 100 |
| 31.sql |          26 |  0: 2615.0ms ±21%     | 236.2ms ±97%            | 92.3%         | #1(11.8%),#14(9.0%)                     | count:12, median:100.0, 90th:457.9, 95th:493.1, max:493.1  | SELECT * FROM cast_info WHERE ((cast_info.id<=295701) AND (cast_info.person_id>=42240) AND (cast_info.movie_id<=3612014) AND (cast_info.person_role_id>=69608) AND (cast_info.nr_order IS NULL) AND (cast_info.role_id<=1)) LIMIT 100                                                                                                                                                                                                                                                                  |
| 34.sql |          22 |  0: 13.0ms ±111%      | 2.4ms ±50%              | 95.5%         | #1(18.5%)                               | count:11, median:10.7, 90th:37.8, 95th:37.8, max:37.8      | SELECT * FROM complete_cast WHERE ((complete_cast.id<=10610) AND (complete_cast.movie_id<=411932) AND (complete_cast.subject_id>=1) AND (complete_cast.status_id>=3)) LIMIT 100                                                                                                                                                                                                                                                                                                                        |
| 38.sql |           6 |  0: 745.2ms ±27%      | 226.0ms ±135%           | 66.7%         | #4(30.3%),#6(31.9%)                     | count:1, median:1.3, 90th:1.3, 95th:1.3, max:1.3           | SELECT * FROM aka_name WHERE ((aka_name.id>=1212531) OR (aka_name.person_id<=3622168) AND (aka_name.name<="Muñiz, Emme Maribel") AND (aka_name.imdb_index IS NULL) OR (aka_name.name_pcode_cf>="M2561") AND (aka_name.name_pcode_nf<="E5614") OR (aka_name.surname_pcode>="M2") AND (aka_name.md5sum>="c32f43ef8cc95c6180aef2e7799ec92a")) ORDER BY aka_name.name_pcode_nf LIMIT 100                                                                                                                   |
| 41.sql |           8 |  0: 59.8ms ±27%       | 35.4ms ±48%             | 50.0%         | #3(63.9%),#4(64.9%),#7(59.2%),#8(67.6%) | count:3, median:5.0, 90th:5.0, 95th:5.0, max:5.0           | SELECT * FROM keyword WHERE ((keyword.id>=164082) AND (keyword.keyword>="bleeding-from-the-mouth") AND (keyword.phonetic_code<="B4352")) ORDER BY keyword.keyword LIMIT 100                                                                                                                                                                                                                                                                                                                            |
| 45.sql |           2 |  1: 4.5ms ±275%       | 3.0ms ±33%              | 50.0%         | #1(66.7%)                               | count:1, median:2.2, 90th:2.2, 95th:2.2, max:2.2           | SELECT * FROM title WHERE ((title.id>=2669501) AND (title.title<="Volunteer") OR (title.imdb_index IS NULL) OR (title.kind_id>=7) OR (title.production_year<=1997) AND (title.imdb_id IS NULL) OR (title.phonetic_code>="V4536") AND (title.episode_of_id<=2669280) AND (title.season_nr>=3) OR (title.episode_nr<=11) AND (title.series_years IS NULL) AND (title.md5sum<="e0330d4fc80403189b9c99422162d375")) LIMIT 100                                                                              |
+--------+-------------+------------------------+--------------------------+---------------+-----------------------------------------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

@mahjonp
Copy link
Contributor Author

mahjonp commented Aug 13, 2020 •
edited
Loading

I design two control groups. The first group turns off auto analyze and feedback. The second group turns off auto analyze but turns on feedback.

Experiment steps:

  1. set performance.run-auto-analyze: false and create imdb_slice db using schema.sql
  2. import half data into the database: python3.6 load_half.py
  3. analyze all tables in imdb_slice
  4. run: python3.6 run.py

Seems there existed the effectiveness regression on some SQLs.

SELECT * FROM title WHERE (title.id IS NOT NULL AND title.title!="(#1.69)" AND title.imdb_index IS NULL AND title.kind_id<8 AND title.production_year!=1974 AND title.imdb_id IS NULL AND title.phonetic_code IS NULL AND title.episode_of_id>184590 AND title.season_nr IS NULL AND title.episode_nr IS NULL AND title.series_years IS NULL AND title.md5sum<="7cf95ddbd379fdb3e530e0721ff61494") LIMIT 100

image

scripts.zip
queries.zip

@qw4990 qw4990 mentioned this issue Aug 20, 2020
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@qw4990 qw4990

@winoros winoros

Labels
sig/planner SIG: Planner
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@mahjonp @qw4990 @winoros