Workload-driven recommendations for Columnstore and Rowstore indexes in relational databases

A system for tuning a database, the system comprising:

• one or more processors;
• and
• a non-transitory computer-readable medium coupled to the one or more processors having instructions stored thereon that, when executed by the one or more processors, cause the one or more processors to perform operations comprising:
• receiving an input workload, the workload including a plurality of queries;
• identifying, for each query in the workload, candidate indexes for the query, wherein the candidate indexes include rowstore indexes and columnstore indexes;
• for each candidate columnstore index of the candidate indexes:
• estimating a size of the candidate columnstore index, wherein the estimating of the size of the candidate columnstore index includes:
• collecting a sample of data from a table of the database to create statistics corresponding to the candidate columnstore index;
• estimating, for each column of a plurality of columns in the sample of data, a number of distinct values in the column;
• estimating, for each column of the plurality of columns in the sample of data, a number of runs in the column;
• and
• generating an estimated size of the candidate columnstore index based on the estimated numbers of distinct values in the columns and the estimated numbers of runs in the columns;
• creating a corresponding hypothetical index based on the estimated size of the candidate columnstore index, and
• generating a cost estimate for the candidate columnstore index based on the corresponding hypothetical index created for the candidate columnstore index;
• for each query in the workload, determining one or more of the candidate indexes to recommend for the query based on the generated cost estimates;
• and
• generating a tuning recommendation for performance of the workload based on the one or more candidate indexes determined for each query in the workload.

A tangible, non-transitory computer readable medium, or media, storing machine readable instructions that, when executed by one or more processors, cause the one or more processors to perform operations comprising:

• receiving an input workload, the workload including a plurality of queries;
• identifying, for each query in the workload, candidate indexes for the query, wherein the candidate indexes include rowstore indexes and columnstore indexes;
• for each candidate columnstore index of the candidate indexes:
• estimating a size of the candidate columnstore index, wherein the estimating of the size of the candidate columnstore index includes:
• collecting a sample of data from a table of the database to create statistics corresponding to the candidate columnstore index;
• estimating, for each column of a plurality of columns in the sample of data, a number of distinct values in the column;
• estimating, for each column of the plurality of columns in the sample of data, a number of runs in the column;
• and
• generating an estimated size of the candidate columnstore index based on the estimated numbers of distinct values in the columns and the estimated numbers of runs in the columns;
• creating a corresponding hypothetical index based on the estimated size of the candidate columnstore index, and
• generating a cost estimate for the candidate columnstore index based on the corresponding hypothetical index created for the candidate columnstore index;
• for each query in the workload, determining one or more of the candidate indexes to recommend for the query based on the generated cost estimates;
• and
• generating a tuning recommendation for performance of the workload based on the one or more candidate indexes determined for each query in the workload.