Iterative multi-attribute index selection for large database systems

Abstract

The inventors implemented and studied the access patterns of a large production enterprise system in a columnar in-memory database. In order to obtain an accurate cost estimate for the configuration, the inventors used the hypothesis function of modern query optimizers. However, hypothesis calls are the main bottleneck of most index selection methods. Therefore, the main constraint is to limit the number of hypothesis optimizer calls. And even if the method of the present invention does not limit the index candidate set, it will reduce the number of hypothesis calls because the number of possible (index) extensions in each iteration step is relatively small, which leads to a limited number of hypothesis calls.

Description

Technical Field

[0001] The present invention relates to a database system, and in particular to index selection of a database system. Background Art

[0002] Large database instances that handle highly concurrent workloads require secondary indexes to avoid sequential scans, save memory bandwidth, and thereby achieve acceptable latency and throughput.

[0003] Indexes trade increased performance for additional memory, which is often a scarce resource in database systems. This highlights the need for mechanisms that efficiently balance memory consumption with performance gains.

[0004] In the prior art, indexes are typically selected by database administrators (DBAs), who must rely on their intuition and index advisor tools, which typically rely on heuristics.

[0005] Index selection (also called index tuning) belongs to the category of NP-hard problems. Finding the best index selection from a large set of potential indexes is highly challenging because there are index elements that affect each other's effect and overall performance, which is called Index Interaction ("IIA" in this article).

[0006] In particular, large real-world problem instances exhibit properties that most existing selection methods cannot handle:

[0007] (i) There are thousands of attributes to consider,

[0008] (ii) workloads consisting of thousands of query templates,

[0009] (iii) memory budget constraints, and

[0010] (iv) Balancing reconfiguration costs and performance gains, including considerations regarding IIA.

[0011] A series of publications (e.g., [Falkowski 1992; Ip et al. 1983]) reduced the index selection problem (herein referred to as "ISP") to the knapsack problem (herein referred to as "KP") [Mathews 1896]. The two problems have similarities and are comparable to some extent. However, the IIA (see [Schnaitter et al. 2009]) leads to an oversimplification of the problem by reducing the ISP to the KP.

[0012] Valentin et al. proposed an index selection method for IBM DB2 [Valentin et al. 2000] that greedily selects indexes until a given budget is exceeded. The authors proposed evaluating indexes by the ratio of their run-time improvement to their size consumption. To account for index interactions and maintenance costs, the determined configuration is randomly shuffled several times to search for potentially better configurations.

[0013] Chaudhuri and Narasaya proposed an optimizer-based index selection tool for Microsoft SQL Server [Chaudhuri and Narasaya 1997, 1998]. They explained several techniques to reduce the complexity of ISP. For example, to avoid considering every admissible index, they determined a set of index candidates as input to the actual index selection process. These candidates were selected in the following way: only indexes that are the best index for at least a single query are considered index candidates, which may lead to waste of potential indexes. In addition, their solution (see [Chaudhuri and Narasaya 1997, 1998]) adopts a fixed number of indexes as a constraint and stopping criterion. However, since the size of the index varies depending on the underlying data, the index size can be considered a more reasonable constraint and a fixed memory budget can be considered.

[0014] A more recent version of the Index Advisor for Microsoft SQL Server also incorporates compressed indexes [Kimura et al. 2011]. Here, potential index candidates are first screened to be efficient (i.e., not dominated by others). Kimura et al. then propose a heuristic that, in contrast to the DB2 advisor, greedily selects the index with the largest expected run time improvement. Only after the budget is exceeded, when the advisor attempts to "recover" from the budget violation and iteratively replaces the index with a more space-efficient alternative, is the index cost considered.

[0015] The work of Idreos et al. [Idreos et al. 2011] focuses on index selection for column stores. They introduce adaptive indexing, where index tuning is not a separate task but part of query processing. Adaptive merging [Graefe and Kuno 2010] and database cracking [Idreos et al. 2007] are two techniques that either require a lot of processing to fully work or have a high initial cost for first-time queries compared to non-indexed scans.

[0016] The idea of ​​formulating ISP as an optimization problem is not new [Caprara et al., 1995; Dash et al., 2011; Papadomanolaki et al., 2007b]. These works consider binary integer problems and associated continuous relaxations. Since integer optimization problems are NP-complete [Garey and Johnson, 1979], heuristics are often used. Caprara et al. [Caprara et al., 1995] use a branch-and-bound algorithm to approximate the solution to their linear integer problem. Their model assumes that at most one index is used for each query, which is a strong simplification. The benefit of using one index for a query is not affected by the decisions of other indexes. Therefore, index interactions that lead to suboptimal index configurations are usually not considered.

[0017] Dash et al. [Dash et al., 2011] proposed a prior art solution for index selection together with CoPhy. The authors claim to efficiently solve larger problem instances using well-known techniques in linear optimization. Their technique also relies on what-if optimizer calls to feed the input for their linear programming optimization.

[0018] The object of the present invention is to provide an improved index selection device, an index selection method and an index selection computer program product. Summary of the invention

[0019] This object can be achieved by one of the following solutions.

[0020] A method for determining an index set I for the purpose of allowing queries to be run more efficiently on a database, the method being implemented in a computer system having a physical processing unit and a physical storage device, the computer storing a database having N attributes in the physical storage device, the method comprising the steps of:

[0021] (1) Set the working index set

[0022] (2) determining a single attribute index {i} (i=1, . . . , N) that minimizes the ratio between the associated cost reduction and the associated additional memory consumption;

[0023] (3) adding the determined single attribute index to the working index set I;

[0024] (4) For each basic index element {i} (i=1, ..., N) and for each index k∈I,

[0025] (a) If Then a new index set is created by adding {i} as a new single attribute index to the working index set I or

[0026] (b) A new index k is created by appending {i} to the end of the index k in the working index set I.

[0027] cited collection

[0028] (5) From the new index set created in step (4) Select the new collection The new collection having an optimal ratio between associated cost reduction and associated additional memory consumption;

[0029] (6) Setting the working index set as well as

[0030] (7) Repeat steps (4) to (6) until the predetermined condition is met.

[0031] A computer system is configured to execute the method for determining the index set I described above.

[0032] A computer program product comprising instructions, when a computer executes the program, the instructions cause the computer to execute the steps of the aforementioned method.

[0033] The claimed invention takes into account the inner workings of a database computer system and provides further technical effects.

[0034] The inventors have implemented and studied the access patterns of a large production enterprise system in a columnar in-memory database. To obtain an accurate cost estimate for the configuration, the inventors used the what-if functionality of modern query optimizers, similar to the inventive method [Chaudhuri and Narasaya, 1997; Dash et al., 2011; Papadomanolaki et al., 2007b]. However, hypothesis calls are the main bottleneck of most index selection methods (see [Papadomanolaki et al., 2007a]). Therefore, the main constraint is to limit the number of hypothesis optimizer calls. And even if the inventive method does not restrict the set of index candidates, the number of hypothesis calls is reduced because the number of possible (index) extensions in each iteration step is relatively small, which leads to a limited number of hypothesis calls.

[0035] The inventors have evaluated the concepts of the present invention in a real-world enterprise system. The inventors extracted the largest 500 tables (in terms of memory consumption) from the database system, including the queries that access these tables. These 500 tables consisted of 4,204 related attributes. The number of rows in these tables ranged from 350,000 to 1.5 billion rows. 55,758 different queries were executed (Q = 2271 query templates), and more than 50 million query executions occurred during the recorded period. The workload can be characterized as primarily transactional with mostly point-access queries, but also containing a small number of analytical queries (more information on comparable systems can be found in [Boissier et al., 2016]).

[0036] Figure 1 The cost (computational memory traffic) of processing the above workload is shown while varying our index selection strategy (denoted by (H6)) and the memory budget of CoPhy's selection method [Dash et al., 2011]. In addition, the size of CoPhy's candidate set is varied. Using all possible candidates (9912 candidates in this example), the running time of our method totals about half a second, while CoPhy requires several minutes. With a limited candidate set, our method significantly outperforms CoPhy's LP-based method in terms of solution quality. If CoPhy's candidate set is unrestricted, its performance is comparable to that of the present method. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Figure 1 An ERP system example is shown: a comparison of the scan performance and the associated memory budget (expressed as the share ω of the memory used to store all single-attribute indexes) for the strategy (H6) of the present invention and the concept of CoPhy with different index candidate sets under the conditions of N=4204 attributes, Q=2271 queries.

[0038] Figure 2 A diagram of the inventive method for index selection for TPC-C is shown: construction steps, suitability of indexes for different queries, and consideration of related index enhancements.

[0039] Figure 3 It shows that for the strategy (H6) of the present invention and when |I|=500 and all candidates are I max , N=500, Q=1000, comparison of the scanning performance and the associated relative memory budget A(w), w∈[0,0.4] combinations of CoPhy concepts with different index candidate sets using (H1-M), (H2-M), and (H3-M) in Example 1.

[0040] Figure 4 The concept of CoPhy with different index candidate sets for the strategy (H6) of the present invention and using (H1-M) and |I|=100, 1000, |I max | candidates, w∈[0,0.4], N=500, Q=1000, comparison of the scanning performance and the associated relative memory budget (see A(w)) under the conditions of Example 1.

[0041] Figure 5 An example of an ERP system is shown: for the strategy (H6) of the present invention and the concept of CoPhy with different index candidates using (H1-M) and at |I|=100, 1000, |I max |, N=4204, Q=2271, combined comparison of scanning performance and associated relative memory budget (see A(w), w∈[0,0.1]).

[0042] Figure 6 End-to-end evaluations are shown for the proposed strategy (H6) versus the frequency-based heuristic (H1), without the skyline method and using all 2937 candidates (H4), using the skyline method (H4), using all candidates (H5), and CoPhy with the candidate heuristic (H1-M) at 10% of the indexed candidates, and CoPhy using all candidates (best), for w∈[0,1], N=100, Q=100, |I max = 2937, the combination of workload runtime and associated relative memory budget (see A(w)). H2 and H3, as well as candidate set reduction via H2-M and H3-M, are not presented due to their poor performance. DETAILED DESCRIPTION

[0043] Below, embodiments of the present invention are described and compared with other approaches.

[0044] A. Problem and Model Description

[0045] We consider a system with N attributes. The problem is to choose a secondary index for a workload consisting of Q queries so as to maximize the overall performance, for example by minimizing execution time, I / O traffic, or amount of storage transferred. Each of the Q queries is characterized by the following set of attributes:

[0046]

[0047] This set of attributes is accessed during query evaluation. The symbol table is given below:

[0048] Symbol Table

[0049]

[0050]

[0051] An index k with K attributes (multiple attributes) is represented as an ordered set of attributes

[0052] k={i1,...,i K}, where i u ∈{1,...,N}, u=1,...,K

[0053] Furthermore, to describe index candidates, we use the index set denoted by I

[0054]

[0055] We represent index selection by the following subset:

[0056]

[0057] Without loss of generality, we use the binary variable x k to indicate whether the candidate index k∈I is part of the selection I*, that is

[0058]

[0059] Query q j , j = 1, ..., the cost of Q is determined by the value f j (I * ) indicates that it depends on the choice I * Usually f j (I * ) is determined by assuming that the optimizer calls the function f j Assign costs to indexed collections (including one-dimensional collections). Note that the query q j It can be of various types, such as select, join, insert, update, etc., or a combination of the above types. The total workload cost F is composed of all queries q j The query cost f j With b j , j=1,..., Q is defined as the sum of the number of occurrences of the query cost represented by Q.

[0060]

[0061] Furthermore, we assume that the memory consumption of the selected index is not allowed to exceed a certain budget A. The storage space required for a (multi-attribute) index k, k∈I* is denoted by p k It is represented by and can be defined arbitrarily. The total storage space used by I* is

[0062]

[0063] In addition, we allow for reconfiguration costs. We use Indicates changing the existing index selection I to a new selection (That is, create a new index And delete unnecessary indexes ) has a cost (arbitrarily defined). Finally, (given an arbitrary but fixed current index choice )The multi-attribute index selection problem can usually be defined as

[0064]

[0065] Note that in problems (3) to (4), the variables Both and the index candidate set I are crucial and must be optimized.

[0066] B. LP Method of CoPhy

[0067] In this subsection, we consider an integer linear programming approach to the index selection problem. The approach is similar to the concept of CoPhy [Dash et al., "CoPhy: A scalable, portable, and interactive index advisor for large workloads", PVLDB, vol.4, no.6, pp.362-372, 2011.]. For simplicity, without loss of generality, we do not consider the cost of updates and reconfigurations. CoPhy's model assumes that, for each query, at most one index is applied (per table), and that the scan costs for different tables are sum-separable. Furthermore, it is assumed that the scan cost of a query when using a particular index is not affected by the presence of other indexes. Thus, if index k is applied to query g j , then CoPhy uses the scan cost f j (k), k∈I∪0,

[0068] Among them, 0 describes no j Option to apply indexing. In addition, we use

[0069]

[0070] Denotes the number of columns in I that apply to query q j , j = 1, ..., the set of index candidates for Q. Without loss of generality, we assume that the query operates on only one table. For a given candidate set I, the essence of CoPhy's index selection LP method can be written as:

[0071]

[0072] The constraint family (6) ensures that at most one index k is used for query q j Constraint (7) is used to express which indices k are used. Finally, constraint (8) ensures that the memory budget A is not exceeded. CoPhy takes IIA into account because LP allows all index combinations I* in I.

[0073] The complexity of the problems described by (5) to (8) is characterized in particular by the number of variables and constraints.

[0074] Note that using

[0075]

[0076] Replacing I allows the number of variables and constraints (from approximately Q*I, see variable z, respectively) to be jk and constraint (7)) reduces to

[0077] and in

[0078]

[0079] is the average number of relevant index candidates per query.

[0080] If only the first attribute at index k represented by l(k) is q j The index k is applicable to query q only if part of the conditions j , then the relevant set I j It can be defined as, j = 1, ..., Q,

[0081]

[0082] In this case, the number of variables and constraints can be approximated as follows. On average, we have |I| = N indices that start with a particular attribute i, i = 1, ..., N. In addition,

[0083]

[0084] represents the average number of attributes that appear in the query. Therefore, the average number of candidates that are suitable for the query is

[0085]

[0086] Note that the number of eligible indexes can be even larger, since I often contains indexes starting with attributes that appear disproportionately in workload queries. Finally, the number of variables and constraints is approximately

[0087]

[0088] Therefore, as expected, the problem complexity increases significantly with the number of queries Q and the number of candidate indices |I|. Due to the linear programming formulation, see (5) to (8), all cost coefficients f j (k), assuming that the number of optimizer calls can also be obtained by For estimation, see (9). Note that the hypothetical cost estimates can be efficiently and accurately derived, for example via INUM [S. Papadomanolakis, D. Dash, and A. Ailamaki, "Efficient use of the query optimizer for automated database design," in Proc. VLDB, 2007, pp. 1093-1104].

[0089] C. Preferred index selection method according to the present invention

[0090] Below, we define our multi-attribute index selection method.Many index selection concepts are characterized by a two-step approach: (i) defining an appropriately sized set of index candidates, and (ii) deriving an optimal selection for a fixed memory budget (using heuristics or linear programming techniques).

[0091] Instead of these two steps, the present invention proposes a constructive method for computing index selections based on a recursive solution with an increasing budget. The present invention proposes a workload-driven index selection method that relies on an iterative mechanism and effectively considers IIA. The key idea is to prune index candidates as late as possible and build index selections in an iterative manner. The ability to recurse allows (i) handling very large index combinations and (ii) incorporating index interactions in each construction step.

[0092] In addition, at each step, a new index is selected or an existing index is expanded to consistently maximize the ratio of additional performance to additional memory. This technique has advantages over the random shuffling or replacement heuristics used by existing methods. The method of the present invention also allows the reconfiguration (index creation and deletion) costs to be included.

[0093] Therefore, one aspect of the present invention relates to a method for determining an index set I for the purpose of allowing queries to a database to be run more efficiently, for example with shorter response times and / or fewer computing resources, the method being implemented in a computer system having a physical processing unit and a physical storage device, wherein the computer stores a database having N attributes in the physical storage device.

[0094] According to an embodiment of the present invention, the method comprises the following steps:

[0095] (1) Set the working index set

[0096] (2) determining a single attribute index {i} (i=1, . . . , N) that minimizes the ratio between the associated cost reduction and the associated additional memory consumption;

[0097] (3) Add the determined single attribute index to the working index set I;

[0098] (4) For each basic index element {i} (i=1, ..., N) and for each index k∈I,

[0099] (a) If Create a new index set by adding {i} as a new single-attribute index to the working index set I as well as

[0100] (b) Create a new index set by appending {i} to the end of index k in the working index set I

[0101]

[0102] (5) The new index set created from step (4) The new set with the best ratio between the associated cost reduction and the associated additional memory consumption is selected from as well as

[0103] (6) Set the working index set

[0104] (7) Repeat steps (4) to (6) until the predetermined condition is met.

[0105] This method recursively adds attributes to a set of indexes. Attributes may extend the current set of indexes I as new single-attribute indexes, or may be appended to the "end" of an existing index in I (see variations). The decision of which attribute to add and in what manner is determined by comparing the additional performance (e.g., F(I)+R(I)) with the associated additional utilized memory (see P(I), see (2)). Figure 2 The process is illustrated for TPC-C Example 1. In short, the method seeks to approximate the efficient frontier (for both performance and size) associated with the multi-attribute index selection problem.

[0106] According to a preferred embodiment of the present invention, the predetermined condition is that the memory consumption exceeds the memory budget, or a predefined maximum number of construction steps is reached, or no further improvement can be achieved.

[0107] According to a preferred embodiment of the present invention, the associated cost includes the workload cost and preferably also includes the function Limit the current index set The reconfiguration cost of changing to working index set I.

[0108] According to a preferred embodiment of the present invention, the workload cost is defined by a function F(I), where the current index set The reconfiguration cost of changing to a working index set I is given by the function is defined, and the memory consumption is defined by the function P(I).

[0109] According to one embodiment of the present invention, the database currently does not use an index set.

[0110] According to a preferred embodiment of the present invention, the database currently uses the current index set

[0111] According to a preferred embodiment of the present invention, the ratio according to step (2) is based on determined, and wherein the ratio according to step (5) is based on Sure.

[0112] According to a preferred embodiment of the present invention, step (5) involves calculating the relevant costs, and the step of calculating the relevant costs involves caching intermediate results for the purpose of using these intermediate results in subsequent runs of step (5). This is a time-saving optimization. If the basic enhancement i=1,...,N is considered for the index k∈I, it is not necessary to completely recalculate the cost using the hypothetical optimizer It is sufficient to recalculate the cost only for queries that may be affected by the potential addition, see step (4a or 4b). The cost of most queries remains unchanged.

[0113] According to a preferred embodiment of the present invention, the method is applicable independently of the specific values ​​of F, R, P and f. The method can be generally applied and is independent of a specific cost model or optimizer. The only requirement is that the query cost needs to be quantified and the relative performance improvement of the index attachments must be comparable, see steps (4) to (5). Note that the set of index candidates is not pre-restricted. No maximum number of attributes is used in multi-attribute indexes. The method provides a series of construction steps that recursively and therefore effectively determine the growing index set I and its associated memory consumption P(I). Recursive selection allows consideration of the cannibalization effect between different indexes when the value of a candidate index is measured by the additional performance improvement of other candidate indexes in the presence of an earlier selected index (see IIA).

[0114] The present invention also has further preferred and alternative embodiments:

[0115] 1) For example, the method can be sped up by considering only the n best single-attribute indices in the construction step.

[0116] 2) Indexes built in earlier iteration steps may no longer be used because new indexes (requiring a higher memory budget) allow more efficient scans. In this case, the method can be optimized by excluding unnecessary indexes from the current index set.

[0117] 3) To be able to identify different indexes with the same dominant properties, the estimated impact of missed (eg, suboptimal) opportunities to expand the index can be stored and potentially used in a later step. Note that the performance impact may have to be re-estimated.

[0118] 4) Under the assumption that the call cost is low or there is a sufficiently accurate cost model, the method can be generalized to consider not only a single attribute but also certain attribute pairs to build or expand the index.

[0119] Another aspect of the present invention relates to a computer system, which is configured to execute the method for determining the index set I described in relation to the above embodiments.

[0120] Another aspect of the present invention relates to a computer program product comprising instructions, which, when executed by a computer, cause the computer to execute the steps of the method described with respect to the above embodiments.

[0121] D. Discussion of different selection methods

[0122] The index selection problem cannot be solved completely due to the huge number of combinations and the limited number of hypothesis calls that can be processed to estimate each cost improvement due to a particular index. Therefore, the set of index candidates to be considered must be restricted. Since a limited set of candidates can easily reduce the solution quality, the set of index candidates must be either (i) very large or (ii) well defined. However, (i) makes it difficult to find the best choice, and (ii) is not easy to solve because it is similar to the index selection problem itself.

[0123] Existing methods work in the following way: First, index candidates (a large set of index candidates) are selected based on simple metrics (e.g., access frequency, selectivity, or size), and these index candidates are evaluated individually for all relevant queries using hypothetical optimizer calls. Second, for a given memory budget, the final index selection is chosen according to a specific mechanism. In [S. Chaudhuri and VR Narasayya, “An efficient cost-driven index selection tool for Microsoft SQL Server,” in Proc. VLDB, 1997, pp. 146–155], the final index is greedily selected based on the performance of individual measurements of the index. However, this process is proportional, and IIA is not explicitly considered. The selected indexes are likely to have their effects cannibalize each other.

[0124] CoPhy's linear programming approach allows identifying the best index selection while also taking IIA into account. However, due to the complexity of the LP, the applicability of this approach is limited to small sets of index candidates. In [G. Valentin, M. Zuliani, DC Zilio, GML Ohman, and A. Skelley, "DB2 Advisor: An optimizer smart enough to recommend its own indexes," in Proc. ICDE, 2000, pp. 101–110], an index is first greedily selected (starting solution) based on its individually measured performance / size ratio. In order to address IIA and to improve performance, index elements are randomly replaced. Therefore, this method can be applied to large candidate sets. However, since the starting solution is usually far from the optimal solution and the shuffling is not targeted, it may take a long time to obtain the optimization result, especially for large candidate sets. At first, the method of the present invention looks similar to the greedy method, such as [G. Valentin, M. Zuliani, DC Zilio, GML Ohman, and A. Skelley, "DB2 Advisor: An optimizer smart enough to recommend its own indexes," in Proc. ICDE, 2000, pp. 101-110]. However, the method of the present inventors has important differences. We apply the greedy selection in a recursive subsequent manner, thereby taking into account the existence of IIA, i.e., other (earlier selected) indexes.

[0125] By comparing the cost estimates, it is possible to determine for which queries a potential new index will be applied (when added to the current set of indexes), see (1), and therefore to determine how much the current overall performance will be improved. In each iterative step of the method of the present invention, the index is selected so that the ratio of "additional performance" to "additional required memory" is always optimized. Therefore, in contrast to [G. Valentin, M. Zuliani, DC Zilio, GML Ohman, and A. Skelley, "DB2 Advisor: An optimizer smart enough to recommend its own indexes," in Proc. ICDE, 2000, pp. 101–110], we address IIA in a targeted manner.

[0126] The combination and variety of indexes that can be composed by the method of the present invention is not pre-restricted (as is the case with starting with a certain set of index candidates). However, the number of necessary hypothesis calls is relatively small because we also prune the index candidates: due to the use of recursive incremental index expansion / modification, at each step only a small subset of potential new indexes is considered and very few hypothesis calls are performed.

[0127] Most importantly, the "direction" of considering and selecting indexes follows a "ratio of additional performance to additional memory" criterion that reflects IIA. In summary, the inventive method (i) efficiently incorporates IIA, (ii) performs a small number of hypothesis calls, and (iii) quickly identifies index choices for a given budget without using a solver (even for large problems).

[0128] E. Exemplary Implementation of the Invention and Comparison with Alternative Methods

[0129] In the following, an example index selection heuristic and three heuristic rules and two index selection methods similar to the concepts used in [G. Valentin, M. Zuliani, D.C. Zilio, G.M. Ohman, and A. Skelley, “DB2 Advisor: An optimizer smart enough to recommend its own indexes,” in Proc. ICDE, 2000, pp. 101–110] and [S. Chaudhuri and VR Narasayya, “An efficient cost-driven index selection tool for Microsoft SQL Server,” in Proc. VLDB, 1997, pp. 146–155] are described:

[0130] Definition 1: The method of the present invention and the concept of the prior art:

[0131] (H1): = As long as the given memory budget A is not exceeded, select the index with the most commonly used attribute in the given index candidate set I, where the most commonly used attribute is the number of times it appears in the query

[0132]

[0133] To measure.

[0134] (H2): = As long as the given memory budget A is not exceeded, the index with the lowest selectivity in the given index candidate set I is selected, and the selectivity is

[0135] s i :=1 / d i ,i=1,...,N,

[0136] (d i is the number of distinct values).

[0137] (H3): = As long as it does not exceed A, select the index with the smallest ratio of selectivity to number of occurrences in the given index candidate set I, that is, s i =g i ,i=1,…,N。

[0138] (H4): = Select the index with the best performance from a given set of index candidates I as long as it does not exceed A; exclude candidates that are inefficient (in terms of performance and size) for at least one query (skyline candidates), see [S. Chaudhuri and V. R. Narasayya, "An efficient cost-driven index selection tool for Microsoft SQL Server," in Proc. VLDB, 1997, pp. 146-155].

[0139] (H5): = As long as it does not exceed A, select the index with the best performance-size ratio in the given index candidate set I, see the starting solution of [G.Valentin, M.Zuliani, DCZilio, GMLohman, and A.Skelley, "DB2 Advisor: An optimizer smart enough to recommend its own indexes," in Proc.ICDE, 2000, pp.101-110].

[0140] (H6): = As long as A is not exceeded, a series of construction steps are applied, see the method according to the invention.

[0141] Reproducible assessment

[0142] In this section, we compare the running time (Section 1) and quality (Section 2) of our index selection method (see Section C) with CoPhy's solution (see Section B) using a reproducible and scalable benchmark scenario.

[0143] In Section 3, all presented index selection concepts (including (H4) and (H5), see Definition 1) are compared for real workloads as well as end-to-end evaluation.

[0144] Example 1. (A reproducible and scalable example is illustrated) To compare the invented approach with CoPhy's solution, we consider the following setup.

[0145] (i) CoPhy applies at most one index per query. To obtain comparable results, we express the query cost in a simplified “only one index” setting, i.e., given the choice I * , the cost of query j is determined by:

[0146]

[0147] (ii) To illustrate the applicability of the proposed method in a reproducible setting, we target f according to the exemplary cost model described in Section B of the Appendix. j (k), k∈I * ∪0 fills in the hypothetical optimizer call.

[0148] (iii) In addition, we consider a (randomized) synthetic workload setting with T = 10 tables. We consider different problem sizes, where Q t = 50, ..., 5000 queries, and N t =50 attributes per table, t=1,...,T.

[0149] (iv) We compare the inventors' heuristic method (H6) (see the present invention and Definition 1) with CoPhy's method using the following three heuristic methods for limiting the scalable index candidate set I:

[0150] (H1-M) For each m=1,...,4, select m attributes {i1,...,i m} appears most frequently in the queries of the entire workload u =1,...,N,u=1,...,m, h index candidates (arranged in descending order).

[0151] (H2-M) For each m=1,...,4, select m attributes {i1,...,i m} has the smallest combination selectivity h index candidates (arranged in ascending order).

[0152] (H3-M) For each m=1, ..., 4, select h index candidates in ascending order with the best ratio of combined selectivity (see H2-M) to number of occurrences (see H1-M) on tuples of m attributes.

[0153] For M index candidates, for each m=1, ..., 4, let h:=M / 4.

[0154] 1. Applicability, Scalability, and Uptime

[0155] We use Example 1 to compare the applicability of the inventive method to CoPhy's solver-based approach.

[0156] Table 1 shows the running time (excluding hypothesis calls) of our strategy (H6) (implemented using a single-threaded C++ program) and the running time (excluding hypothesis calls) of Cophy's method (CPLEX 12.7.0.0, mipap=0.05, 4 threads, via the NEOS solver) for different sizes of index candidate sets for different problem sizes (characterized by the number of queries and attributes), see Example 1 ( 3 DNF: did not finish within eight hours).

[0157] Table I

[0158] Run time comparison: In IC max potential indexes, T = 10 tables, ∑ t N t =500 attributes, budget w = 0.2, for different problem sizes, our strategy (H6) vs. Cophy’s approach (with 5% best-case difference and |I| index candidates); not considering the time of hypothesis calls; Example 1

[0159]

[0160] The number of selected indices is approximately |I * |≈w·∑ t N t = 100. The budget A(w) is defined by the total memory share w required for all single attribute indexes, 0≤w≤1

[0161] A(w): =w·∑k∈{{1}，...，{N}} p k (10)

[0162] The running time of (H6) was collected again on a consumer notebook with an Intel Core i5 and 16GB of main memory. The inventors observed that even for practically large problem instances, the computation took only a few seconds and thus allowed essentially immediate response times. In order to further speed up the running time of the inventors' strategy, the method of the present invention can be parallelized.

[0163] The inventors observed that the computation time of CoPhy increases significantly with the number of queries Q and the number of index candidates. The generation of the candidate set and the call to the hypothesis optimizer are not included. Note that the basic LP model with Q = 500 queries and |I| = 1000 candidates (see (5) to (8)) typically has more than 100,000 variables and 100,000 constraints.

[0164] Furthermore, the number of index combinations grows exponentially with the number of candidates (see

[15] ). Even though solvers use pruning, they cannot circumvent this problem. Therefore, for large problems, solver-based methods have limited applicability and therefore have to reduce the number of candidates to a small subset of potential indexes. Therefore, even if the final index selection is optimal (for the subset of index candidates), the overall quality of the solution may be far from optimal.

[0165] Comparing the running times in Table |, the inventors observed that their method is still applicable when the problem is large. The complexity of the inventors' method is different: due to the recursive nature of their method, only a small number of possibilities must be evaluated in each construction step. Since the current index selection remains, the number of possibilities is not multiplied in each step - they are just added, which greatly reduces the complexity.

[0166] Furthermore, the inventors' recursive method does not require a particularly large number of cost estimates, e.g., hypothetical optimizer calls. Although in principle a large number of index candidates can be constructed, not all index candidates are evaluated using hypothetical optimizer calls. Most (e.g., typically more than 50%) of the optimizer calls occur in the first construction step, where all queries q must be evaluated. j , j = 1, ..., Q determines the scanning cost of all potential single attribute indexes k = {i} i = 1, ..., N, that is, the inventors roughly perform In each subsequent build step of the method of the present invention, only those queries that would fully use the new potential (incrementally expanded) multi-attribute index need to be evaluated - the costs of the other queries do not change and have been previously determined. Therefore, in each build step, only a small number of what-if calls need to be performed. Note, again, that these numbers are just cumulative across all build steps.

[0167] Given that in the method of the present invention, it is assumed that the number of calls As the number of construction steps required to reach the target memory budget slowly increases, the number of calls ( See (9)) increases approximately linearly in the number of index candidates |I. Therefore, compared with CoPhy, the method of the present invention requires fewer cost estimates, especially for the case where the number of candidates |I| is large compared to the number of columns N.

[0168] Furthermore, if multiple indices per query are allowed, the complexity of the index selection problem increases further, since the query cost cannot be derived separately for different indices, see Example 1(i). Instead, the query cost is context-based, i.e., the query cost may depend on the entire selection I*. Therefore, Cophy's framework will have to be extended so that the cost parameter f j (I) can be used for all subsets I * In addition, LP (5) to (8) are likely to become nonlinear and thus may hardly apply anymore.

[0169] The inventive method is applicable in settings with multiple indexes per query. In each step, a hypothesis call is made to evaluate potential expansions, taking into account the current state, i.e., the current selection I. The only difference from the "one index only setting" is that some of the hypothesis calls performed in earlier steps must be refreshed, since the underlying current state (and, in turn, the query cost) may have changed.

[0170] 2. Performance comparison with C0Phy

[0171] Next, we compare the performance of CoPhy’s approach with our strategy. To investigate to what extent the performance of CoPhy depends on the size and quality of the index candidate set, we consider Example 1 for different parameters h (number of candidate indices).

[0172] Figure 3The performance and memory combinations for our selection method and CoPhy's selection method are shown when different heuristics are used to limit the index candidate set. The inventors observed that the results of CoPhy are affected by the classification of the candidate set. Depending on the target memory budget, the heuristics can be implemented differently. However, the inventor's strategy provides excellent results for any budget.

[0173] Figure 4 The corresponding results are depicted when CoPhy's selection method uses candidate sets of different sizes according to (H1-M). As expected, the size of the candidate set significantly affects the results of CoPhy. The smaller the candidate set, the higher the probability of missing important indexes. The larger the candidate set, the higher the solution time becomes.

[0174] Cophy's solution is optimal in the case where the maximum number of potential indices (see Imax) is used as the exhaustive candidate set. The fact that our strategy (H6) performs close to optimally verifies the high quality of our solution. Recall that in terms of computation time, ours clearly outperforms solver-based approaches in the case of large candidate sets required to obtain high-quality solutions.

[0175] Finally, the inventors' solution can be used to replace and improve traditional index selection methods by supplementing the index candidate set. If the indices selected by the inventors' method are used as additional index candidates, the problem complexity of the solver-based method is hardly increased, but the quality of the solution is only improved.

[0176] 3. Enterprise workloads and end-to-end assessment

[0177] In Section 3.a, we present benchmark results based on data and workloads from production enterprise systems of Fortune Global 500 companies. In Section 3.b, we demonstrate that the quality of our solution does not depend on the exemplary cost model used in Sections A to D. We evaluate our approach for all proposed concepts including (H4) and (H5) (see Definition 1) by measuring actual runtime costs with a commercial DBMS.

[0178] a. Application to enterprise workloads

[0179] The inventors evaluated their strategy against a real-world enterprise system. They extracted the largest 500 tables (in terms of memory consumption) from the database system, including the queries that accessed these tables. These 500 tables consisted of 4,204 related attributes. The tables ranged from about 350,000 to about 1.5 billion rows. 55,758 different queries were executed (with Q=2,271 query templates), and more than 50 million query executions occurred during the recorded period. The workload can be characterized as primarily transactional with most point-access queries, but also containing a few analytical queries.

[0180] Figure 5 The cost (computational memory traffic) of processing the aforementioned workload for different memory budgets is shown for our index selection strategy (H6) and CoPhy. In addition, the size of CoPhy's candidate set is varied using (H1-M). The measurements again confirm the results from Sections E.1 and E.2. In terms of the quality of the solution, our method significantly outperforms CoPhy's LP-based approach with a limited candidate set. The runtime of our method totals about half a second, while CoPhy with all 9912 candidates takes several minutes.

[0181] The nature of real-world workloads can explain the poor performance of the heuristics. The interactions between the above-mentioned indexes play an important role. Some attributes are often accessed together. Therefore, an index on one attribute may degrade the performance of a potential index on another attribute. Rule-based heuristics, such as (H1) to (H3), cannot take these effects into account.

[0182] b. Evaluation using a commercial DBMS

[0183] In this section, the applicability of the inventors' solution to a real-world database system is demonstrated in practice. Furthermore, it is shown that the inventive approach is independent of exact cost modeling or assumed optimization implementation.

[0184] The following performance evaluation was performed using a commercial columnar main memory database system. Although the use of cost estimates (based on hypothetical optimizers) is necessary to achieve solutions to large problem instances, it has also been proven that they are often inaccurate. Therefore, the inventors ran all evaluations without relying on estimates based on assumptions or other optimizers but using the above-mentioned database system to execute all queries in succession. Then, the query cost for a given index configuration was determined using the actual reported execution time. In order to determine the impact of index candidates on query performance, the inventors also created all index candidates in succession and executed all queries for each candidate. These measured runtimes (rather than hypothetical estimates) are then used to feed the cost parameters of the model. Although this approach guarantees accurate costs, it is accompanied by high evaluation times due to multiple evaluations of each query for each index candidate. Therefore, the inventors use scalable workloads such as in Example 1 to replace real-world ERP workloads to implement these experiments.

[0185] The measurements were performed on a machine with 512GB main memory and 64 cores at a clock speed of 2.3GHz using the latest version of the DBMS. The inventors repeated the measurements at least 100 times for each query per index configuration to reduce the inaccuracy of the measurements. The workload was created by the workload generator introduced above that simulates the characteristics of real-life workloads.

[0186] Based on the measured costs, the inventors use CoPhy, heuristics and its recursive strategy to calculate the index configuration.

[0187] Figure 6 The performance of various index selection strategies is shown. Up to about 30% of the budget, our solution is on par with CoPhy's best solution with all (2937) candidates. The performance of our solution is always within 3% of the best solution.

[0188] The results of the skyline methods ((H1) and (H4) [Kimura et al. 2011]) with and without dominated candidates based on an exhaustive candidate set are far from optimal. The heuristic (H5) with a complete set of indexed candidates also provides good results. However, if the candidate set is too small (see Figure 4 ) or of lower quality (for example, when using H2-M or H3-M to limit the candidate set, see Figure 3 ), then the result of (H5) is worse. Note that the results of CoPhy provide an upper bound on the performance when using a reduced candidate set, because for a given candidate set, CoPhy computes the best selection. In this case, the results of CoPhy based on 10% of the candidate selections (according to heuristic H1-M) show a significant performance degradation compared to CoPhy with all indexed candidates, see Figure 6 .

[0189] The results can be summarized as follows: First, the selection of candidate sets can significantly affect the overall performance. Second, for a fixed candidate set, the final selection mechanism seriously affects the overall performance. Third, the proposed method does not rely on the appropriate selection of candidates and provides near-optimal results.

[0190] Although the workload size of the inventor's example is relatively small, the problem complexity of CoPhy's LP is already extremely high. Both the number of constraints and variables increases linearly with the size of the index candidate set. The complexity of the problem (i.e., the solution time) usually increases superlinearly with the number of variables and the number of constraints. The exhaustive set of 2937 index candidates results in approximately 20,000 variables and constraints. Depending on the solver used, the specified optimal gap, the available hardware, etc., the solution time for these problems may have taken several minutes (see Table I). In order to reduce the number of candidates, the permutations of multi-attribute index candidates can be replaced by their approximately best representatives. However, in general, the results will be negatively affected by removing other permutations.

[0191] Furthermore, performance-based approaches such as (H4) and (H5) also suffer if the problem instances are large. Since in their approaches, cost predictions must be available for all index candidates, the number of required hypothetical optimizer invocations may be (too) large. As a result, a reduced candidate set must be used, which requires appropriate candidate heuristics and often leads to reduced overall performance, see Figure 6 .

[0192] Finally, our results verify that the results of our solution also apply to the end-to-end case: (i) our method outperforms simple rule-based selection strategies, (ii) our method outperforms CoPhy with a restricted candidate set, and (iii) we verify that our results are close to optimal in the case of tractable selection problems. Thus, we show that our solution can find near-optimal index configurations without relying on assumed estimates or specified cost models. Furthermore, our solution is scalable because the costs of the assumed optimizer (see Section E.1) are determined less frequently and no solver is required.

[0193] 4. Discussion of Evaluation Results

[0194] The inventors compared their solution with the conceptual heuristics used by [Valentin et al. 2000] (see (H5)), [Kimura et al. 2011] (see (H4)), and the CoPhy approach. Note that for [Valentin et al. 2000], their starting solution (H5) provides a lower bound, while CoPhy produces an upper bound on performance.

[0195] For reproducibility, in Section E.1 (Scalability) and Section E.2 (Performance), the inventors used an exemplary illustrative cost model, see Example 1. For the end-to-end evaluation in Section E.3.b, instead of a cost model, the inventors used cost predictions based on a hypothetical optimizer that includes the complex effects of modern execution engines.

[0196] The results show that our method scales and quickly provides near-optimal index selection. If the candidate set is small compared to all potential candidate sets, our method outperforms (H4) and (H5) as well as the results of CoPhy. Therefore, our method is a promising alternative to existing tools, especially for large problems.

[0197] The scalability (i.e., fast running time) of our method can be explained as follows: (i) during the recursive method, the inventors only consider a limited subset of index candidates, and (ii) they use a constructive deterministic mechanism with a relatively small number of steps instead of a randomized mechanism.

[0198] The performance of the inventors' approach can also be illustrated. Their approach exploits structures and properties that are typical for the performance of real-world workloads and indexes: First, index A can be applied to more queries than the (extended) index AB and requires less memory (property 1). Second, similar indexes AB and AC can often erode each other, i.e., when similar indexes are selected together, they may only increase the overall workload performance by a small amount compared to the case where only the (best) one of the similar indexes is selected (property 2). Third, it can be assumed that taking any index out of the Pareto efficient selection significantly affects performance. Therefore, it follows from property 2 that the selection with several similar indexes is inefficient, and including an index must significantly improve performance (property 3). Fourth, the Pareto efficient frontier of "performance" and "required memory" is convex in shape, see Figures 3 to 6 .

[0199] The method of the present invention exploits these properties: if index AB is beneficial, then usually index A is also beneficial, see property 1, and is therefore identified (and extended) by the inventors' method. Their method does not build similar indexes, see property 2. The inventors' recursion only achieves index selection / extension with a significant additional performance-to-size ratio, while taking into account (the current state of) IIA, see property 3. As the number of steps of the method of the present invention increases, the additional performance-to-size ratio achieved generally decreases (diminishing returns), see properties 1 and 4. Therefore, their method is very unlikely to miss important improvements in future steps, and their method is similar to the effective frontier of performance and required memory.

[0200] The inventors' recursive approach may have limitations when properties 1 to 4 are not satisfied. For example, their approach may miss beneficial indices in cases where they need to append prior expensive but not directly beneficial indices. In this case, the inventors refer to the discussion of potential extensions of the inventive approach. To study the optimal gap for special cases, the inventors recommend using CoPhy's solution as a reference. Due to the lack of functional structure based on the cost of the assumed optimizer, it will be challenging to derive performance guarantees.

Claims

1. A method for determining a set of indexes I for the purpose of allowing queries to be run more efficiently on a database, the method being implemented in a computer system having a physical processing unit and a physical storage device, the computer storing a database having N attributes in the physical storage device, the method comprising the following steps: (1) Set the working index set (2) determining a single attribute index {i} (i=1, . . . , N) that minimizes the ratio between the associated cost reduction and the associated additional memory consumption; (3) adding the determined single attribute index to the working index set I; (4) For each basic index element {i} (i=1, ..., N) and for each index k∈I, (a) If Then a new index set is created by adding {i} as a new single attribute index to the working index set I or (b) Create a new index set by appending {i} to the end of index k in the working index set I (5) From the new index set created in step (4) Select the new collection The new collection having an optimal ratio between associated cost reduction and associated additional memory consumption; (6) Setting the working index set as well as (7) Repeat steps (4) to (6) until the predetermined condition is met.

2. The method according to claim 1, wherein: The predetermined condition is that the memory consumption exceeds the memory budget, or a predefined maximum number of construction steps is reached, or no further improvement can be achieved.

3. The method according to claim 1 or 2, wherein: The associated costs include workload costs.

4. The method according to claim 3, wherein the associated cost further comprises a function Limit the current index set The reconfiguration cost of changing to working index set I.

5. The method according to claim 4, wherein: The workload cost is defined by the function F(I), where the current index set The reconfiguration cost of changing the working index set I is given by the function is defined, and the memory consumption is defined by the function P(I).

6. The method according to claim 1 or 2, wherein: The database is not currently used Index set.

7. The method according to claim 1 or 2, wherein: The database currently uses the current set of indexes 8. The method according to claim 5, wherein: The ratio according to step (2) is based on determined, and wherein the ratio according to step (5) is based on Sure.

9. A computer system configured to execute the method for determining an index set I according to one of claims 1 to 8.

10. A computer program product comprising instructions causing a computer to perform the steps of the method according to one of claims 1 to 8 when the computer executes the program.

Images