ESTIMATED_QUERY_LEAF_MEMORY

Returns an estimate of the amount of memory, in megabytes, required to run a query. The estimate is the total amount of memory across all leaf nodes.

This function is intended to be used inside a user-defined scalar function (UDF) that selects a resource pool.

Syntax

ESTIMATED_QUERY_LEAF_MEMORY()

Return Type

Integer

Remarks

• For ESTIMATED_QUERY_LEAF_MEMORY() to provide an accurate estimate, statistics must first be collected on the query that this function operates on. 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 -1 if MemSQL cannot estimate the amount of memory required to run the query because statistics on the query have not been collected.

Examples

Example 1: Using ESTIMATED_QUERY_LEAF_MEMORY() Inside a Resource Pool Selector Function

The topic Setting Resource Limits provides an example of using ESTIMATED_QUERY_LEAF_MEMORY() inside of a user-defined scalar function (UDF). The UDF selects a resource pool dynamically.

Example 2: Using ESTIMATED_QUERY_LEAF_MEMORY() Outside a Resource Pool Selector Function

While ESTIMATED_QUERY_LEAF_MEMORY() is intended to be used inside a resource pool selector function, you can use it outside the function for testing. The following example estimates the amount of memory needed to run a query.

First define a procedure that populates a table with many rows.

DROP DATABASE IF EXISTS memsql_docs_example;
CREATE DATABASE memsql_docs_example;
USE memsql_docs_example;

CREATE TABLE t(num INT);

DELIMITER //
CREATE PROCEDURE populate_table() AS
DECLARE
	counter INT = 0;
BEGIN
  WHILE counter < 500000 LOOP
    INSERT INTO t(num) VALUES (counter);
    counter = counter + 1;
  END LOOP;
END
//
DELIMITER ;

Call the procedure to populate the table. The procedure may take a few minutes to run.

CALL populate_table();

Next, do an initial run of the query SELECT COUNT(DISTINCT num) FROM t, followed by ANALYZE MEMORY to collect statistics on the query. Prior to running the query, transform it into a query that uses a common table expression, via the WITH clause; this allows ESTIMATED_QUERY_LEAF_MEMORY() to be included as a column in the query. The transformed query uses the same amount of memory as the original one.

The initial run of the query returns -1 because statistics have not yet been collected.

WITH cte AS (SELECT COUNT(DISTINCT num) FROM t)
	SELECT ESTIMATED_QUERY_LEAF_MEMORY() FROM cte;
****
+-------------------------------+
| ESTIMATED_QUERY_LEAF_MEMORY() |
+-------------------------------+
|                            -1 |
+-------------------------------+

Next, collect statistics on the query:

ANALYZE MEMORY;

Finally, re-run the previous query to get the estimate:

WITH cte AS (SELECT COUNT(DISTINCT num) FROM t)
	SELECT ESTIMATED_QUERY_LEAF_MEMORY() FROM cte;
****
+-------------------------------+
| ESTIMATED_QUERY_LEAF_MEMORY() |
+-------------------------------+
|                             4 |
+-------------------------------+

Was this Article Helpful?

Thanks For Your Feedback!