Query plan adaptation using query plan fragments
Techniques are disclosed relating to determining query plans for execution by database systems. In various embodiments, a query optimizer determines a first query plan to implement a query requesting data from a database system. The determining includes selecting one of a plurality of query plans evaluated based on a cost analysis and caching plan fragments of the unselected query plans. The database system can then determine a second query plan for the query by replacing a plan fragment in the first query plan with one of the cached plan fragments of the unselected query plans.
1 . A non-transitory computer readable medium having program instructions stored thereon that are capable of causing a computing system to implement operations comprising:
determining, by a query optimizer of a database system, a first query plan to implement a query requesting data from the database system, wherein the determining includes:
selecting, for execution by an execution engine of the database system, one of a plurality of query plans evaluated based on a cost analysis; and
caching plan fragments of unselected query plans, wherein the unselected query plans are ones of the plurality of query plans that are not selected for execution, and wherein the caching includes storing the cached plan fragments in an ordering such that the stored cached plan fragments are arranged based on their respective costs as determined by the cost analysis, wherein the storing includes storing ones of the cached plan fragments in a linked list arranged such that a lowest-cost plan fragment is positioned at a head of the linked list; and
determining, by the query optimizer, a second query plan for the query by replacing a plan fragment in the first query plan with one of the cached plan fragments of the unselected query plans, wherein the cached plan fragment used in the replacing is selected based on the arranged ordering.
2 . The computer readable medium of claim 1 , wherein the replacing alters the ordering in which the cached plan fragments are stored.
3 . The computer readable medium of claim 1 , wherein the operations further comprise:
collecting, by an execution engine of the database system, one or more performance metrics from an execution of the first query plan; and
determining the second query plan in response to the one or more performance metrics satisfying one or more criteria.
4 . The computer readable medium of claim 3 , wherein the execution engine evaluates the one or more performance metrics and replaces the plan fragment in the first query plan with the one cached plan fragment.
5 . The computer readable medium of claim 1 , wherein the operations further comprise:
receiving, via a user interface, a request to modify the first query plan, wherein determining the second query plan is performed in response to the request.
6 . The computer readable medium of claim 1 , wherein the operations further comprise:
receiving, with the query, user information identifying a source of the query; and
in response to the user information identifying the source as a particular set of users, causing execution of the second query plan to service the query.
7 . The computer readable medium of claim 1 , wherein the operations further comprise:
storing the second query plan as a pointer array including a plurality of pointers to plan fragments stored in a cache.
8 . The computer readable medium of claim 7 , wherein a given one of the pointers points to a linked list that includes a selected plan fragment for an action and one or more alternative plan fragments for the action; and
wherein the replacing includes altering an ordering of plan fragments in the linked list.
9 . The computer readable medium of claim 1 , wherein the operations further comprise:
using one or more of the cached plan fragments to determine a third query plan for another query.
10 . A method, comprising:
selecting, by a database system, one of a plurality of query plans to execute a query requesting data from the database system;
storing, by the database system, plan fragments of unselected ones of the query plans in a cache, wherein the unselected query plans are ones of the plurality of query plans that are not selected for execution, wherein the stored plan fragments are linked in an ordering determined based on costs of the stored plan fragments, wherein the storing includes storing ones of the plan fragments in a linked list arranged such that a lowest-cost plan fragment is positioned at a head of the linked list;
modifying, by the database system and based on an execution of the selected query plan, the selected query plan by replacing a plan fragment in the selected query plan with one of the plan fragments of the unselected query plans stored in the cache, wherein the plan fragment of the unselected query plan is selected based on the linked ordering; and
executing, by the database system, the selected query plan with the replaced plan fragment.
11 . The method of claim 10 , further comprising:
preserving, by the database system, cost information determined from the selecting of the plurality of query plans, wherein the replacing is based on the cost information.
12 . The method of claim 10 , wherein the selecting is performed by a query optimizer of the database system; and
wherein the modifying is performed by an execution engine of the database system.
13 . The method of claim 10 , further comprising:
storing, by the database system, the selected query plan as a pointer array including a plurality of pointers to plan fragments stored in the cache, wherein the replacing includes modifying the pointer array.
14 . The method of claim 10 , further comprising:
determining, by the database system, two query plans for two different queries, wherein the two query plans share one or more plan fragments in the cache.
15 . A computing system, comprising:
one or more processors; and
memory having program instructions stored thereon that are executable by the one or more processors to cause the computing system to implement a database system performing operations including:
receiving a query requesting data from the database system;
determining a first query plan to implement the query, wherein the determining includes:
selecting, for execution by the database system, one of a plurality of query plans evaluated based on a cost analysis; and
caching plan fragments of unselected query plans, wherein the unselected query plans are ones of the plurality of query plans that are not selected for execution, wherein the caching includes storing cached plan fragments in an ordering corresponding to the costs of using the cached plan fragments, wherein the storing includes storing ones of the cached plan fragments in a linked list arranged such that a first plan fragment at a head of the linked list has a lower cost than a second plan fragment at a tail of the linked list; and
in response to receiving the query again, determining a second query plan for the query by replacing a plan fragment in the first query plan with one of the cached plan fragments of the unselected query plans, wherein the cached plan fragment used in the replacing is selected based on the stored ordering.
16 . The computing system of claim 15 ,
wherein the replacing alters the ordering of plan fragments in the linked list.
17 . The computing system of claim 15 , wherein the operations further include:
an execution engine of the database system tracking performance metrics from execution of the first query plan; and
the execution engine determining the second query plan based on the performance metrics.
18 . The computing system of claim 17 , wherein the performance metrics are tracked for a given user; and
wherein the second query plan is determined for the given user.
19 . The computing system of claim 15 , wherein the operations further include:
storing the first and second query plans as pointer arrays identifying plan fragments in a cache.