search

pingcap / tidb

TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
https://pingcap.com
Apache License 2.0
36.89k stars 5.81k forks source link

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

Open mahjonp opened 4 years ago

mahjonp commented 4 years ago

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:

mahjonp commented 4 years ago

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 commented 4 years ago

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.

qw4990 commented 4 years ago

Here is the Chinese analysis document about index selection tests.

mahjonp commented 4 years ago

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 commented 4 years ago

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