ESTIMATED_QUERY_LEAF_MEMORY

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. You must have SUPER privileges to run the ANALYZE MEMORY command or an error will be generated. This command also runs in the background every 10 minutes, so you can wait 10 minutes and then proceed to the next step:

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 |
+-------------------------------+