Dynamic query optimization with pilot runs
In one embodiment, a computer-implemented method includes selecting one or more sub-expressions of a query during compile time. One or more pilot runs are performed by one or more computer processors. The one or more pilot runs include a pilot run associated with each of one or more of the selected sub-expressions, and each pilot run includes at least partial execution of the associated selected sub-expression. The pilot runs are performed during execution time. Statistics are collected on the one or more pilot runs during performance of the one or more pilot runs. The query is optimized based at least in part on the statistics collected during the one or more pilot runs, where the optimization includes basing cardinality and cost estimates on the statistics collected during the pilot runs.
1. A computer-implemented method, comprising:
selecting, during compile time of a query, one or more user-defined functions (UDFs) for which to perform one or more pilot runs;
performing, by one or more computer processors, during the execution of the query and after completion of the compile time of the query, the one or more pilot runs of the one or more UDFs, comprising a pilot run associated with each of the one or more UDFs, and wherein each pilot run comprises at least partial execution of the associated UDF selected during compile time of the query and before execution of the query;
collecting statistics on the one or more pilot runs of the one or more UDFs during performance of the one or more pilot runs of the one or more UDFs; and
optimizing the query, after execution of the query begins, wherein the optimizing comprises:
determining, during the execution of the query, an order of execution for predicates in the query based at least on part on the statistics on the one or more pilot runs of the one or more UDFs;
wherein the statistics comprise information about performance of the one or more UDFs, and wherein the optimizing further comprises basing cardinality and cost estimates on the statistics collected during the one or more pilot runs of the one or more UDFs.
2. The method of claim 1 , wherein performing the one or more pilot runs of the one or more UDFs comprises performing two or more pilot runs in parallel across two or more processors.
3. The method of claim 1 , further comprising storing, in a database metastore, the statistics collected during the one or more pilot runs of the one or more UDFs, wherein the statistics collected during a first pilot run associated with a first UDF are stored in the database metastore in association with a first expression signature identifying the first UDF.
4. The method of claim 1 , wherein the one or more UDFs comprise a first UDF, and wherein performing the one or more pilot runs of the one or more UDFs comprises:
searching a database metastore for previous pilot-run data related to the first UDF; and
performing a first pilot run associated with the first UDF if the previous pilot run data related to the first UDF is not found in the database metastore.
5. The method of claim 4 , wherein the one or more UDFs further comprise a second UDF, and wherein performing the one or more pilot runs of the one or more UDFs comprises:
searching the database metastore for previous pilot-run data related to the second UDF; and
opting not to perform a pilot run associated with the second UDF if the previous pilot run data related to the second UDF is found in the database metastore.
6. A system comprising:
a memory having computer readable instructions; and
one or more processors for executing the computer readable instructions, the computer readable instructions comprising:
selecting, during compile time of a query, one or more user-defined functions (UDFs) for which to perform one or more pilot runs; and
performing, during the execution of the query and after completion of the compile time of the query, the one or more pilot runs of the one or more UDFs, comprising a pilot run associated with each of the one or more UDFs, and wherein each pilot run comprises at least partial execution of the associated UDF selected during compile time of the query and before execution of the query;
collecting statistics on the one or more pilot runs of the one or more UDFs during performance of the one or more pilot runs of the one or more UDFs; and
optimizing the query, after execution of the query begins, wherein the optimizing comprises:
determining, during the execution of the query, an order of execution for predicates in the query based at least on part on the statistics on the one or more pilot runs of the one or more UDFs;
wherein the statistics comprise information about performance of the one or more UDFs, and wherein the optimizing further comprises basing cardinality and cost estimates on the statistics collected during the one or more pilot runs of the one or more UDFs.
7. The system of claim 6 , wherein performing the one or more pilot runs of the one or more UDFs comprises performing two or more pilot runs in parallel across two or more processors.
8. The system of claim 6 , the computer readable instructions further comprising storing, in a database metastore, the statistics collected during the one or more pilot runs of the one or more UDFs, wherein the statistics collected during a first pilot run associated with a first UDF are stored in the database metastore in association with a first expression signature identifying the first UDF.
9. The system of claim 6 , wherein the one or more UDFs comprise a first UDF, and wherein performing the one or more pilot runs of the one or more UDFs comprises:
searching a database metastore for previous pilot-run data related to the first UDF; and
performing a first pilot run associated with the first UDF if the previous pilot run data related to the first UDF is not found in the database metastore.
10. The system of claim 9 , wherein the one or more UDFs further comprise a second UDF, and wherein performing the one or more pilot runs of the one or more UDFs comprises:
searching the database metastore for previous pilot-run data related to the second UDF; and
opting not to perform a pilot run associated with the second UDF if the previous pilot run data related to the second UDF is found in the database metastore.
11. A computer program product for optimizing a query, the computer program product comprising a computer readable storage medium having program instructions embodied therewith, the program instructions executable by a processor to cause the processor to perform a method comprising:
selecting, during compile time of a query, one or more user-defined functions (UDFs) for which to perform one or more pilot runs;
performing, during the execution of the query and after completion of the compile time of the query, the one or more pilot runs of the one or more UDFs, comprising a pilot run associated with each of the one or more UDFs, and wherein each pilot run comprises at least partial execution of the associated UDF selected during compile time of the query and before execution of the query;
collecting statistics on the one or more pilot runs of the one or more UDFs during performance of the one or more pilot runs of the one or more UDFs; and
optimizing the query, after execution of the query begins, wherein the optimizing comprises:
determining, during the execution of the query, an order of execution for predicates in the query based at least on part on the statistics on the one or more pilot runs of the one or more UDFs;
wherein the statistics comprise information about performance of the one or more UDFs, and wherein the optimizing further comprises basing cardinality and cost estimates on the statistics collected during the one or more pilot runs of the one or more UDFs.
12. The computer program product of claim 11 , wherein performing the one or more pilot runs of the one or more UDFs comprises performing two or more pilot runs in parallel across two or more processors.
13. The computer program product of claim 11 , the method further comprising storing, in a database metastore, the statistics collected during the one or more pilot runs of the one or more UDFs, wherein the statistics collected during a first pilot run associated with a first UDF are stored in the database metastore in association with a first expression signature identifying the first UDF.
14. The computer program product of claim 11 , wherein the one or more UDFs comprise a first UDF, and wherein performing the one or more pilot runs of the one or more UDFs comprises:
searching a database metastore for previous pilot-run data related to the first UDF; and
performing a first pilot run associated with the first UDF if the previous pilot run data related to the first UDF is not found in the database metastore.
15. The computer program product of claim 14 , wherein the one or more UDFs further comprise a second UDF, and wherein performing the one or more pilot runs of the one or more UDFs comprises:
searching the database metastore for previous pilot-run data related to the second UDF; and
opting not to perform a pilot run associated with the second UDF if the previous pilot run data related to the second UDF is found in the database metastore.