Have query optimizers hit the wall?

Abstract

The query optimization phase within a database management system (DBMS) ostensibly finds the fastest query execution plan from a potentially large set of enumerated plans, all of which correctly compute the specified query. Occasionally the cost-based optimizer selects a slower plan, for a variety of reasons. We introduce the notion of empirical suboptimality of a query plan chosen by the DBMS, indicated by the existence of a query plan that performs more efficiently than the chosen plan, for the same query. From an engineering perspective, it is of critical importance to understand the prevalence of suboptimality and its causal factors. We examined the plans for thousands of queries run on four DBMSes, resulting in over a million query executions. We previously observed that the construct of empirical suboptimality prevalence positively correlated with the number of operators in the DBMS. An implication is that as operators are added to a DBMS, the prevalence of slower queries will grow. Through a novel experiment that examines the plans on the query/cardinality combinations, we present evidence for a previously unknown upper bound on the number of operators a DBMS may be able to support before performance suffers. We show that this upper bound may have already been reached.

Appendix A: Details on the experiments

Table 1 in Sect. 3.3 lists the run statistics of the four experiments used in this paper. In this appendix, we provide more detailed information on the experiments.

We first discuss the features shared between the first four data sets: “A” through “D.” As introduced in Sect. 2, the queries referenced tables ft_HT1, ft_HT2, ft_HT3, and ft_HT4. All four tables contain four columns, each of type integer. The specific values of the rows for all but the first column depend on the Presence of skewed data.

To generate the values for different values of skew, we start with a distribution without a tail: the uniform distribution: the values from 1 to 2 million (2M). We consider this to be a skew of 0 (no skew). At the other end of the spectrum is one in which all the values are identical, or a skew of 1.0.

We define skew as “the reciprocal of the number of distinct values,” so $0 < { skew} \le 1$. For 2M distinct values, skew would be $\frac{1}{2M}= 0.000005$, which is practically 0. For exactly one distinct value, skew would be 1.0. For two distinct values, skew would be 0.5. For ten distinct values, the skew would be 0.1.

We can generate the table of 2M rows by generating values sequentially from 1 to the number of distinct values. This creates a “span of values.” We repeat this for the second span if necessary, and on and on, until we have 2M values in all.

When varying the cardinality, we remove 10K values from the variable table and then copy those tuples to a new table to ensure that every page is as full as possible (that is, 100% load factor). This gives us a table of 1.99M tuples. (We then get a query plan for this table.) We repeat this removal process until the final cardinality reaches 10K.

The way we effect the 10K removal is as follows. The key idea is to remove individual spans until we’ve deleted 10K values. Since we don’t touch the remaining spans, the umber of distinct values does not change, and so the skew remains constant.

We use two values of skew: $\frac{1}{2M}$ (termed tiny) and $\frac{1}{10K}$ (termed small). For the former, we generate a single span of 2M values. To shorten the table, it makes no sense to remove this single span in its entirety. But we can remove 10K values from this single span. Note that this changes the skew to 1/1.99M, then eventually to 1/10K, which is still very close to zero (skew has changed from .0000005 to .0001). For the latter, we generate 2000 spans each of 10K tuples, and drop a span to reduce to 1.99M tuples, repeating.

This algorithm is used in the second to fourth columns, which for any row will have identical values. The first column holds a unique integer starting from 1 and going to 60K or 2M, for use in an optional primary key.

There was one version of the last three tables, for use with one DBMS , with cardinality 60K, and one version for the rest of the DBMSes, with cardinality 2M.

We generate 200 versions of ft_HT1, termed the variable table. For that DBMS , these version contain 300, 600, 900, 1200, $\ldots$, 59,700, and 60,000 rows; for the other DBMSes, these versions contain 10K, 20K, 30K, $\ldots$, 1990K, and 2M rows, as introduced in Sect. 2.

Data Set C is the simplest to describe: it specifies no primary key, has no duplicate rows, and has no skew (of course, for any of the four tables). Data Set A differs from Data Set C only in that there is skew. As explained above, we use two values of skew, tiny and small.

Data Set B is similar to Data Set A, adding the specification of the first column as the primary key. And Data Set D is similar to Data Set B, adding the specification that the other three columns should each be associated with a secondary index, only for each (one) column. We see the confirmatory experiment examined a much larger variation in data sets than the exploratory experiment.

Lastly, Data Set E comes from the Join Order Benchmark [18]. Data Set E specifies primary key, has no duplicate rows, has some skew, and adds a secondary index. We choose the title table in JOB as the variable table akin to that of Data Set A. That is because title is the only table that is referenced by all the 113 queries in JOB. We generate 100 versions of title; these versions contain 25283, $\ldots$, 2528312 rows.

The following sets of queries were used in the four experiments.

QSa::

A query set consisting of 100 queries generated over the four tables (on Data Set A)

QSb::

A query set consisting of 900 queries additionally generated over the four tables (on Data Set A)

QSc::

A query set consisting of the 390 queries drawn from QSa–QSb (on Data Set B)

QSd::

A query set consisting of 110 new queries (on Data Set B)

QSe::

A query set consisting of 100 queries without aggregates (on Data Set C)

QSf::

A subquery query set consisting of 100 queries, each with a subquery (on Data Sets A, B, and D)

QSg::

A subquery query set consisting of 100 queries, each with a subquery (on Data Set D)

QSh::

A query set consisting of 100 queries drawn from QSc (on Data Set D)

QSi::

A query set consisting of 30 queries without aggregates drawn from QSa (on Data Set A)

QSj::

A query set consisting of 60 queries with aggregates drawn from QSa (on Data Set A)

QSk::

A query set consisting of 30 queries drawn from QSa (on Data Set A)

QSl::

A query set consisting of 30 queries drawn from QSf (on Data Set A)

QSm::

A query set consisting of 30 queries drawn from QSc (on Data Set B)

QSn::

A query set consisting of 60 queries (on Data Set D)

QSo::

A query set consisting of 113 queries (on Data Set E) from the Join Order Benchmark [18].

Experiment 1 (termed Exploratory) used QSa and the first 100 queries from QSb, plus the first 100 (primary key) queries from QSc, for the four DBMSes, for a total of 1200 query instances.

Experiment 2 (Confirmatory) used QSb except the first 100 queries used in Experiment 1, along with QSc except the first 100 queries included in Experiment 1, QSd for primary key (for two runs, or 220 queries), QSe for no data skew, QSf for primary key and subquery, QSf for subquery, QSf and QSg and for primary key and secondary index and subquery, QSh for primary key and secondary index, all across the four DBMSes, for a total of 7640 query instances.

Experiment 3 (Confirmatory with Warm Cache) used QSi (for no primary key, no aggregates, no secondary index, some data skew, and no subquery), QSj for aggregates, QSk for no data skew, QSl for subquery, QSm for primary key, and QSn for primary key and secondary index, all across the four DBMSes, for a total of 960 query instances.

Experiment 4 (JOB) used QSo, for the four DBMSes, for a total of 452 query instances.