ESTIMATED_
On this page
Returns an estimate of the elapsed time, in seconds, required to run a query.
The elapsed time includes the execution time for the query as well as the time required by any blocking operations that delay the query from executing.
This function is intended to be used inside a user-defined scalar function (UDF) that selects a resource pool.
Note
At this time, ESTIMATED_ is an experimental function.
Syntax
ESTIMATED_QUERY_RUNTIME()
Return Type
Double
Remarks
-
For
ESTIMATED_to provide an accurate estimate, statistics must first be collected on the query that this function operates on.QUERY_ RUNTIME After an initial run of the query, statistics are automatically collected at ten minute intervals. You can collect statistics immediately by running ANALYZE MEMORY. -
Returns
-1if SingleStore cannot estimate the time required to run the query, because statistics on the query have not been collected.
Examples
Example 1: Using ESTIMATED_ Inside a Resource Pool Selector Function
Setting Resource Limits provides an example of using ESTIMATED_ inside of a user-defined scalar function (UDF).
Example 2: Using ESTIMATED_ Outside a Resource Pool Selector Function
While ESTIMATED_ is intended to be used inside a resource pool selector function, you can use it outside the function for testing.
First, do an initial run of the three queries, followed by ANALYZE MEMORY to collect statistics on the queries.-1 because statistics have not yet been collected.
SELECT SLEEP(0.5), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(0.5) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
| 0 | -1 |
+------------+---------------------------+SELECT SLEEP(1.5), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(1.5) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
| 0 | -1 |
+------------+---------------------------+SELECT SLEEP(3.0), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(3.0) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
| 0 | -1 |
+------------+---------------------------+Next, collect statistics on the queries:
ANALYZE MEMORY;
Re-run the previous query that sleeps for 0.
SELECT SLEEP(0.5), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(0.5) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
| 0 | 0.507 |
+------------+---------------------------+Re-run the previous query that takes 1.
SELECT SLEEP(1.5), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(1.5) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
| 0 | 1.508 |
+------------+---------------------------+Re-run the previous query that takes 3.
SELECT SLEEP(3.0), ESTIMATED_QUERY_RUNTIME();
+------------+---------------------------+
| SLEEP(3.0) | ESTIMATED_QUERY_RUNTIME() |
+------------+---------------------------+
| 0 | 3.009 |
+------------+---------------------------+Last modified: August 21, 2024