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.

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 singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_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 |
+-------------------------------+