Optimizing selection of materialized views for automatic creation or refresh

1 . A system, comprising:

at least one processor; and

a memory, storing program instructions that when executed by the at least one processor, cause the at least one processor to implement a database system, configured to:

identify a plurality of materialized views for creation or refresh;

determine a subset of the plurality of materialized views for creation or refresh according to an optimization technique that selects the subset of the plurality of materialized views according to a maximized performance benefit for performing subsequent queries by maintaining the subset of the plurality of materialized views, wherein the maximized performance benefit for performing subsequent queries is determined based on a workload of the database system caused by a set of previously received queries that can be performed using one or more of the plurality of materialized views and a benefit function used to evaluate the set of previously received queries with respect to the plurality of materialized views, wherein at least a first materialized view of the subset of the plurality of materialized views does exist in the database system, and wherein at least a second materialized view of the subset of the plurality of materialized views does not yet exist in the database system;

queue the subset of the plurality of materialized views to create or refresh according to respective performance benefit, the queue including refresh to perform on the first materialized view of the subset of the plurality of materialized views that does exist and creation to perform on the second materialized view of the subset of the plurality materialized views that does not yet exist; and

individually pull, from the queue, a number of materialized views up to a managed number of execution slots available to perform respective operations for the number of materialized views in parallel, including pulling the first materialized view of the subset of the plurality of materialized views that does exist to refresh and the second materialized view of the subset of the plurality materialized views that does not yet exist to create.

2 . The system of claim 1 , wherein to identify the plurality of materialized views for creation or refresh, the database system is configured to:

collect the set of previously received queries; and

generate a candidate set of materialized views from the set of previously received queries.

3 . The system of claim 2 , wherein to identify the plurality of materialized views for creation or refresh, the database system is further configured to merge two or more of the materialized views of the candidate set of materialized views into a merged materialized view that is included in the plurality of materialized views.

4 . The system of claim 1 , wherein the database system is a database service implemented as part of a provider network and wherein the identification of the plurality of materialized views, the determining of the subset of the materialized views, the obtaining of individual ones of the subset of the materialized views, and the causing the creation or refresh of the obtained individual ones are performed after receiving a request via an interface of the database service to enable automatic materialized view management.

5 . A method, comprising:

identifying, by a database system, a plurality of materialized views for creation or refresh;

determining, by the database system, a subset of the plurality of materialized views for creation or refresh according to an optimization technique that selects the subset of the plurality of materialized views according to a maximized performance benefit for performing subsequent queries by maintaining the subset of the plurality of materialized views, wherein the maximized performance benefit for performing subsequent queries is determined based on a workload of the database system caused by a set of previously received queries that can be performed using one or more of the plurality of materialized views and a benefit function used to evaluate the set of previously received queries with respect to the plurality of materialized views, wherein at least a first materialized view of the subset of the plurality of materialized views does exist in the database system, and wherein at least a second materialized view of the subset of the plurality of materialized views does not yet exist in the database system;

individually selecting, by the database system, a number of materialized views from the subset of the plurality of materialized views up to a managed number of execution slots available to perform respective operations for the number of materialized views in parallel according to a performance benefit-based order of the subset of the plurality of materialized views, including selecting the first materialized view of the subset of the plurality of materialized views that does exist and the second materialized view of the subset of the plurality materialized views that does not yet exist; and

performing, by the database system, the of selected number of materialized views from the subset of the plurality of materialized views.

6 . The method of claim 5 , wherein identifying the plurality of materialized views for creation or refresh comprises:

collecting the set of previously received queries; and

generating a candidate set of materialized views from the set of previously received queries.

7 . The method of claim 6 , wherein identifying the plurality of materialized views for creation or refresh further comprises merging two or more of the materialized views of the candidate set of materialized views into a merged materialized view that is included in the plurality of materialized views.

8 . The method of claim 5 , wherein selecting the individual ones of the subset of the plurality of materialized views to create or refresh according to the performance benefit-based order of the subset of the plurality of materialized views comprises increasing the number of available execution slots for refreshing or creating materialized views.

9 . The method of claim 5 , wherein the subset of the plurality of materialized views includes at least one existing automatically created materialized view to be refreshed and at least one materialized view to be automatically created.

10 . The method of claim 5 , wherein determining the subset of the plurality of materialized views for creation or refresh according to the optimization technique comprises predicting respective refresh times for one or more existing materialized views.

11 . The method of claim 5 , further comprising removing one or more of the plurality of materialized views that were automatically created after determining that the one or more materialized views satisfies one or more removal conditions.

12 . The method of claim 11 , wherein the one or more removal conditions include a time since a last refresh exceeding a time threshold.

13 . The method of claim 5 , further comprising:

receiving, at the database system, a query;

identifying, by the database system, one of the selected materialized views to use to perform the query; and

rewriting, by the database system, the query to use the one materialized view before performing the query.

14 . One or more non-transitory, computer-readable storage media, storing program instructions that when executed on or across one or more computing devices cause the one or more computing devices to implement:

identifying, by a database system, a plurality of materialized views for creation or refresh;

determining, by the database system, a subset of the plurality of materialized views for creation or refresh according to an optimization technique that selects the subset of the plurality of materialized views according to a maximized performance benefit for performing subsequent queries by maintaining the subset of the plurality of materialized views, wherein the maximized performance benefit for performing subsequent queries is determined based on a workload of the database system caused by a set of previously received queries that can be performed using one or more of the plurality of materialized views and a benefit function used to evaluate the set of previously received queries with respect to the plurality of materialized views, wherein at least a first materialized view of the subset of the plurality of materialized views does exist in the database system, and wherein at least a second materialized view of the subset of the plurality of materialized views does not yet exist in the database system;

individually obtaining, by the database system, a number of materialized views from the subset of the plurality of materialized views up to a managed number of execution slots available to perform respective operations for the number of materialized views in parallel from a task queue that orders the subset of the plurality of materialized views according to respective performance benefit, including refresh of the first materialized view of the subset of the plurality of materialized views that does exist and the second materialized view of the subset of the plurality materialized views that does not yet exist; and

causing, by the database system, the performance of the respective operations of the selected number of materialized views from the subset of the plurality of materialized views.

15 . The one or more non-transitory, computer-readable storage media of claim 14 , wherein, in identifying the plurality of materialized views for creation or refresh, the program instructions cause the one or more computing devices to implement:

collecting the set of previously received queries; and

generating a candidate set of materialized views from the set of previously received queries.

16 . The one or more non-transitory, computer-readable storage media of claim 15 , wherein, in identifying the plurality of materialized views for creation or refresh the program instructions further cause the one or more computing devices to implement merging two or more of the materialized views of the candidate set of materialized views into a merged materialized view that is included in the plurality of materialized views.

17 . The one or more non-transitory, computer-readable storage media of claim 14 , wherein the subset of the plurality of materialized views includes at least one manually created materialized view.

18 . The one or more non-transitory, computer-readable storage media of claim 14 , storing further instructions that when executed on or across the one or more computing devices cause the one or more computing devices to implement removing one or more of the plurality of materialized views that were automatically created after determining that the one or more materialized views satisfies one or more removal conditions.

19 . The one or more non-transitory, computer-readable storage media of claim 18 , wherein the one or more removal conditions include a time since a last query exceeding a time threshold.

20 . The one or more non-transitory, computer-readable storage media of claim 14 , wherein the database system is a database service implemented as part of a provider network and wherein the identification of the plurality of materialized views, the determining of the subset of the materialized views, the obtaining of individual ones of the subset of the materialized views, and the causing the creation or refresh of the obtained individual ones are performed after receiving a request via an interface of the database service to enable automatic materialized view management.