2 research outputs found
How Good Are Query Optimizers, Really?
Finding a good join order is crucial for query performance. In this paper, we introduce the Join Order Benchmark (JOB) and experimentally revisi
Finding Performance Issues in Database Engines via Cardinality Estimation Testing
Database Management Systems (DBMSs) process a given query by creating an
execution plan, which is subsequently executed, to compute the query's result.
Deriving an efficient query plan is challenging, and both academia and industry
have invested decades into researching query optimization. Despite this, DBMSs
are prone to performance issues, where a DBMS produces an inefficient query
plan that might lead to the slow execution of a query. Finding such issues is a
longstanding problem and inherently difficult, because no ground truth
information on an expected execution time exists. In this work, we propose
Cardinality Estimation Restriction Testing (CERT), a novel technique that
detects performance issues through the lens of cardinality estimation. Given a
query on a database, CERT derives a more restrictive query (e.g., by replacing
a LEFT JOIN with an INNER JOIN), whose estimated number of rows should not
exceed the number of estimated rows for the original query. CERT tests
cardinality estimators specifically, because they were shown to be the most
important component for query optimization; thus, we expect that finding and
fixing such issues might result in the highest performance gains. In addition,
we found that some other kinds of query optimization issues are exposed by the
unexpected cardinality estimation, which can also be detected by CERT. CERT is
a black-box technique that does not require access to the source code; DBMSs
expose query plans via the EXPLAIN statement. CERT eschews executing queries,
which is costly and prone to performance fluctuations. We evaluated CERT on
three widely used and mature DBMSs, MySQL, TiDB, and CockroachDB. CERT found 13
unique issues, of which 2 issues were fixed and 9 confirmed by the developers.
We expect that this new angle on finding performance bugs will help DBMS
developers in improving DMBSs' performance