System and method for optimizing large database management systems with multiple optimizers
A large highly parallel database management system includes thousands of nodes storing huge volume of data. The database management system includes multiple query optimizers for determining low cost execution plans for queries. The database management system is adapted to receive a data query. An execution plan generator component of the database management system generates an initial execution plan for the query. The initial execution plan is fed as input to more than one query optimizers. Each optimizer starts with the initial execution plan, generates alternative execution plans, and determines a satisfactory execution plan that incurs the lowest cost. The database management system compares the selected execution plans by the optimizers and selects one with the lowest cost. The multiple query optimizers run in parallel.
1. A database management system comprising:
a processing circuit that operates in conjunction with a memory to create an initial execution plan of a query;
a first query optimizer configured to be executed by the processing circuit by utilizing the initial execution plan as input to generate a first satisfactory execution plan for the query; and
a second query optimizer that is distinct from the first query optimizer, configured to be executed by the processing circuit concurrently with execution of the first query optimizer by utilizing the initial execution plan as input to generate a second satisfactory execution plan for the query, wherein the processing circuit is configured to select a lowest cost execution plan from the first satisfactory execution plan and the second satisfactory execution plan;
wherein the first query optimizer is further configured to be executed by the processing circuit utilizing the second satisfactory execution plan as input to generate a third satisfactory execution plan for the query, wherein the second query optimizer is further configured to be executed by the processing circuit utilizing the second satisfactory execution plan as input to generate a fourth satisfactory execution plan for the query, and wherein the processing circuit is configured to select the lowest cost execution plan from the first satisfactory execution plan, the second satisfactory execution plan, the third satisfactory execution plan, the fourth satisfactory execution plan.
2. The database management system of claim 1 wherein the first satisfactory execution plan is different from the second satisfactory execution plan.
3. The database management system of claim 1 wherein the first satisfactory execution plan is same as the second satisfactory execution plan.
4. The database management system of claim 1 , wherein the processing circuit executes the query in accordance with the lowest cost execution plan.
5. The database management system of claim 1 , wherein the first query optimizer is configured to determine a first execution cost corresponding to the first satisfactory execution plan, wherein the second query optimizer is configured to determine a second execution cost corresponding to the first satisfactory execution plan, and wherein the first execution cost is compared to the second execution cost to select the lowest cost execution plan.
6. The database management system of claim 5 , wherein the first query optimizer is configured to determine the first execution cost corresponding to the first satisfactory execution plan based on at least one of: a throughput cost between a plurality of nodes in a database system associated with the database management system, or a storage drive access speed of each of the plurality of nodes in the database system.
7. The database management system of claim 6 , wherein the second query optimizer is configured to determine the second execution cost corresponding to the first satisfactory execution plan based on at least one of: the throughput cost between a plurality of nodes in a database system associated with the database management system, or the storage drive access speed of each of the plurality of nodes in the database system.
8. A method for execution by a database management system, comprising:
receiving a query;
generating, via at least one processing circuit, an initial query execution plan for the query;
generating, via the at least one processing circuit, a first satisfactory execution plan for the query via execution of a first query optimizer utilizing the initial query execution plan as input;
generating, via the at least one processing circuit, a second satisfactory execution plan for the query via execution of a second query optimizer utilizing the initial query execution plan as input, wherein the second query optimizer is distinct from the first query optimizer;
generating, via the at least one processing circuit, a third satisfactory execution plan for the query via another execution of the first query optimizer utilizing the second satisfactory execution plan as input; and
generating, via the at least one processing circuit, a fourth satisfactory execution plan for the query via another execution of the second query optimizer utilizing the first satisfactory execution plan as input; and
selecting, via the at least one processing circuit, a lowest cost execution plan from the first satisfactory execution plan, the second satisfactory execution plan, the third satisfactory execution plan, and the fourth satisfactory execution plan.
9. The method of claim 8 , further comprising executing the query in accordance with the lowest cost execution plan.
10. The method of claim 8 , further comprising:
determining a first execution cost corresponding to the first satisfactory execution plan;
determining a second execution cost corresponding to the second satisfactory execution plan; and
comparing the first execution cost to the second execution cost to select the lowest cost execution plan from the first satisfactory execution plan and the second satisfactory execution plan.
11. The method of claim 10 , wherein first execution cost is determined based on at least one of: a throughput cost between a plurality of nodes in a database system associated with the database management system, or a storage drive access speed of each of the plurality of nodes in the database system.
12. The method of claim 11 , wherein the second execution cost is determined based on at least one of: the throughput cost between a plurality of nodes in a database system associated with the database management system, or the storage drive access speed of each of the plurality of nodes in the database system.
13. A database management system comprising:
a processing circuit that operates in conjunction with a memory to perform operations that include:
receiving a query;
generating an initial query execution plan for the query;
generating a first satisfactory execution plan for the query via execution of a first query optimizer utilizing the initial query execution plan as input;
generating a second satisfactory execution plan for the query via execution of a second query optimizer utilizing the initial query execution plan as input, wherein the second query optimizer is distinct from the first query optimizer;
generating a third satisfactory execution plan for the query via another execution of the first query optimizer utilizing the second satisfactory execution plan as input; and
generating a fourth satisfactory execution plan for the query via another execution of the second query optimizer utilizing the first satisfactory execution plan as input; and
selecting a lowest cost execution plan from the first satisfactory execution plan, the second satisfactory execution plan, the third satisfactory execution plan, and the fourth satisfactory execution plan.
14. The database management system of claim 13 wherein the first satisfactory execution plan is different from the second satisfactory execution plan.
15. The database management system of claim 13 wherein the first satisfactory execution plan is same as the second satisfactory execution plan.
16. The database management system of claim 13 , wherein the processing circuit executes the query in accordance with the lowest cost execution plan.
17. The database management system of claim 13 , wherein the first query optimizer is configured to determine a first execution cost corresponding to the first satisfactory execution plan, wherein the second query optimizer is configured to determine a second execution cost corresponding to the first satisfactory execution plan, and wherein the first execution cost is compared to the second execution cost to select the lowest cost execution plan.
18. The database management system of claim 17 , wherein the first query optimizer is configured to determine the first execution cost corresponding to the first satisfactory execution plan based on at least one of: a throughput cost between a plurality of nodes in a database system associated with the database management system, or a storage drive access speed of each of the plurality of nodes in the database system.
19. The database management system of claim 18 , wherein the second query optimizer is configured to determine the second execution cost corresponding to the first satisfactory execution plan based on the throughput cost between a plurality of nodes in a database system associated with the database management system.
20. The database management system of claim 18 , wherein the second query optimizer is configured to determine the second execution cost corresponding to the first satisfactory execution plan based on the storage drive access speed of each of the plurality of nodes in the database system.