# US 2023/0067482 A1 · Estimated execution time for query execution > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 2023/0067482 A1 - Document ID: US20230067482A1 - Status: Published application - Kind: A1 - Application number: 18/051,185 - Filed: 2022-10-31 - Published: 2023-03-02 - Earliest claimed: 2021-01-29 - Assignees: Snowflake Inc. - Applicants: Snowflake Inc. - Inventors: Sangyong Hwang; Sourav Sikdar; Li Yan - Web page: https://patentgrep.com/patent/US20230067482A1 ## CPC - G06F 16/24542: {Plan optimisation} - G06F 11/3419: {by assessing time} - G06F 16/24549: {Run-time optimisation} - G06F 16/24565: {Triggers; Constraints} - G06F 2201/80: Database-specific techniques - G06F 2201/81: Threshold - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract The subject technology tracks a plurality of queries corresponding to a plurality of query plans based on join operations contained in each of the plurality of queries and a previous time of executing each query. The subject technology selects a first query plan among the plurality of query plans. The subject technology determines a value indicating an estimated improvement in execution time of the first query plan in comparison to a previous execution time of a previous query plan. The subject technology attempts to execute a first query using the first query plan. The subject technology determines that a second query plan selected among the plurality of query plans has a second estimated execution time that is less than an estimated execution time of the first query plan. The subject technology executes the first query corresponding to the first query plan at a subsequent time using the second query plan. ## Claims ### Claim 1 A system comprising: at least one hardware processor; and a memory storing instructions that cause the at least one hardware processor to perform operations comprising: tracking a plurality of queries corresponding to a plurality of query plans based on join operations contained in each of the plurality of queries and a previous time of executing each query from the plurality of query plans, the join operations contained in each of the plurality of queries resulting in a table size larger than a particular number of rows and the previous time of executing each query falling within a particular period of time; selecting a first query plan among the plurality of query plans; determining a value indicating an estimated improvement in execution time of the first query plan in comparison to a previous execution time of a previous query plan; in response to determining that the value is greater than a threshold value, attempting to execute a first query using the first query plan, the attempting comprising: determining that a second query plan selected among the plurality of query plans has a second estimated execution time that is less than an estimated execution time of the first query plan; and executing the first query corresponding to the first query plan at a subsequent time using the second query plan, the executing the second query plan facilitating a decrease in execution time of the first query compared to using the first query plan. ### Claim 2 (depends on 1) The system of claim 1, wherein determining that the second query plan has the second estimated execution time that is less than the estimated execution time of the first query plan comprises: determining a particular cost associated with the second query plan is less than half a cost of the first query plan. ### Claim 3 (depends on 1) The system of claim 1, wherein the operations further comprising: generating a first version number associated with the first query plan; and generating a second version number associated with the second query plan. ### Claim 4 (depends on 1) The system of claim 1, wherein the operations further comprise: during execution of the first query using the first query plan, determining whether a current execution time of the first query plan is reduced in comparison with the previous execution time of the previous query plan; and in response to determining that the current execution time is not reduced, cancelling the execution and re-executing the first query using the previous query plan. ### Claim 5 (depends on 4) The system of claim 4, wherein the operations further comprise: in response to the determining that the current execution time is not reduced, performing a rollback operation to switch back to the previous query plan for a subsequent execution of the first query, and the operations further comprise: increasing the threshold value in response to the performing the rollback operation. ### Claim 6 (depends on 1) The system of claim 1, wherein the determining the value comprises: determining a first set of constants in the first query plan; determining a second set of constants in the previous query plan; and determining the value based at least in part on differences between the first set of constants and the second set of constants decreasing an estimated execution time of the first query plan in comparison with the previous execution time of the previous query plan. ### Claim 7 (depends on 6) The system of claim 6, wherein the first query includes at least one constant value embedded in a first query string corresponding to the first query. ### Claim 8 (depends on 7) The system of claim 7, wherein the first set of constants includes the at least one constant value embedded in the first query string. ### Claim 9 (depends on 6) The system of claim 6, wherein the operations further comprise: in response to determining that the first set of constants decrease the estimated execution time of the first query plan, selecting the first query plan instead of the previous query plan for execution. ### Claim 10 (depends on 9) The system of claim 9, wherein the operations further comprise: detecting at least one new change in software for query plan selection, the software utilized at least in part for the selecting the first query plan; activating the at least one new change; and deactivating the at least one new change in response to determining that a current execution time of the first query plan is greater than the previous execution time of the previous query plan. ### Claim 11 A method comprising: tracking a plurality of queries corresponding to a plurality of query plans based on join operations contained in each of the plurality of queries and a previous time of executing each query from the plurality of query plans, the join operations contained in each of the plurality of queries resulting in a table size larger than a particular number of rows and the previous time of executing each query falling within a particular period of time; selecting a first query plan among the plurality of query plans; determining a value indicating an estimated improvement in execution time of the first query plan in comparison to a previous execution time of a previous query plan; in response to determining that the value is greater than a threshold value, attempting to execute a first query using the first query plan, the attempting comprising: determining that a second query plan selected among the plurality of query plans has a second estimated execution time that is less than an estimated execution time of the first query plan; and executing the first query corresponding to the first query plan at a subsequent time using the second query plan, the executing the second query plan facilitating a decrease in execution time of the first query compared to using the first query plan. ### Claim 12 (depends on 11) The method of claim 11, wherein determining that the second query plan has the second estimated execution time that is less than the estimated execution time of the first query plan comprises: determining a particular cost associated with the second query plan is less than half a cost of the first query plan. ### Claim 13 (depends on 11) The method of claim 11, further comprising: generating a first version number associated with the first query plan; and generating a second version number associated with the second query plan. ### Claim 14 (depends on 11) The method of claim 11, further comprising: during execution of the first query using the first query plan, determining whether a current execution time of the first query plan is reduced in comparison with the previous execution time of the previous query plan; and in response to determining that the current execution time is not reduced, cancelling the execution and re-executing the first query using the previous query plan. ### Claim 15 (depends on 14) The method of claim 14, further comprising: in response to the determining that the current execution time is not reduced, performing a rollback operation to switch back to the previous query plan for a subsequent execution of the first query, and further comprising: increasing the threshold value in response to the performing the rollback operation. ### Claim 16 (depends on 11) The method of claim 11, wherein the determining the value comprises: determining a first set of constants in the first query plan; determining a second set of constants in the previous query plan; and determining the value based at least in part on differences between the first set of constants and the second set of constants decreasing an estimated execution time of the first query plan in comparison with the previous execution time of the previous query plan. ### Claim 17 (depends on 16) The method of claim 16, wherein the first query includes at least one constant value embedded in a first query string corresponding to the first query. ### Claim 18 (depends on 17) The method of claim 17, wherein the first set of constants includes the at least one constant value embedded in the first query string. ### Claim 19 (depends on 16) The method of claim 16, further comprising: in response to determining that the first set of constants decrease the estimated execution time of the first query plan, selecting the first query plan instead of the previous query plan for execution. ### Claim 20 (depends on 19) The method of claim 19, further comprising: detecting at least one new change in software for query plan selection, the software utilized at least in part for the selecting the first query plan; activating the at least one new change; and deactivating the at least one new change in response to determining that a current execution time of the first query plan is greater than the previous execution time of the previous query plan. ### Claim 21 A non-transitory computer-storage medium comprising instructions that, when executed by one or more processors of a machine, configure the machine to perform operations comprising: tracking a plurality of queries corresponding to a plurality of query plans based on join operations contained in each of the plurality of queries and a previous time of executing each query from the plurality of query plans, the join operations contained in each of the plurality of queries resulting in a table size larger than a particular number of rows and the previous time of executing each query falling within a particular period of time; selecting a first query plan among the plurality of query plans; determining a value indicating an estimated improvement in execution time of the first query plan in comparison to a previous execution time of a previous query plan; in response to determining that the value is greater than a threshold value, attempting to execute a first query using the first query plan, the attempting comprising: determining that a second query plan selected among the plurality of query plans has a second estimated execution time that is less than an estimated execution time of the first query plan; and executing the first query corresponding to the first query plan at a subsequent time using the second query plan, the executing the second query plan facilitating a decrease in execution time of the first query compared to using the first query plan. ### Claim 22 (depends on 21) The non-transitory computer-storage medium of claim 21, wherein determining that the second query plan has the second estimated execution time that is less than the estimated execution time of the first query plan comprises: determining a particular cost associated with the second query plan is less than half a cost of the first query plan. ### Claim 23 (depends on 21) The non-transitory computer-storage medium of claim 21, wherein the operations further comprising: generating a first version number associated with the first query plan; and generating a second version number associated with the second query plan. ### Claim 24 (depends on 21) The non-transitory computer-storage medium of claim 21, wherein the operations further comprise: during execution of the first query using the first query plan, determining whether a current execution time of the first query plan is reduced in comparison with the previous execution time of the previous query plan; and in response to determining that the current execution time is not reduced, cancelling the execution and re-executing the first query using the previous query plan. ### Claim 25 (depends on 24) The non-transitory computer-storage medium of claim 24, wherein the operations further comprise: in response to the determining that the current execution time is not reduced, performing a rollback operation to switch back to the previous query plan for a subsequent execution of the first query, and the operations further comprise: increasing the threshold value in response to the performing the rollback operation. ### Claim 26 (depends on 21) The non-transitory computer-storage medium of claim 21, wherein the determining the value comprises: determining a first set of constants in the first query plan; determining a second set of constants in the previous query plan; and determining the value based at least in part on differences between the first set of constants and the second set of constants decreasing an estimated execution time of the first query plan in comparison with the previous execution time of the previous query plan. ### Claim 27 (depends on 26) The non-transitory computer-storage medium of claim 26, wherein the first query includes at least one constant value embedded in a first query string corresponding to the first query. ### Claim 28 (depends on 27) The non-transitory computer-storage medium of claim 27, wherein the first set of constants includes the at least one constant value embedded in the first query string. ### Claim 29 (depends on 26) The non-transitory computer-storage medium of claim 26, wherein the operations further comprise: in response to determining that the first set of constants decrease the estimated execution time of the first query plan, selecting the first query plan instead of the previous query plan for execution. ### Claim 30 (depends on 29) The non-transitory computer-storage medium of claim 29, wherein the operations further comprise: detecting at least one new change in software for query plan selection, the software utilized at least in part for the selecting the first query plan; activating the at least one new change; and deactivating the at least one new change in response to determining that a current execution time of the first query plan is greater than the previous execution time of the previous query plan.