
Example:
User JIM likes to run CROSS JOINS on tables with a lot of records which requires a significant amount of RAM. Let’s stop him from doing that!
dbadmin=> CREATE RESOURCE POOL all_users MAXMEMORYSIZE '2G' MAXQUERYMEMORYSIZE '400000K';
CREATE RESOURCE POOL
dbadmin=> ALTER USER jim RESOURCE POOL all_users;
ALTER USER
dbadmin=> \c - jim
You are now connected as user "jim".
dbadmin=> SELECT * FROM big_fact2 a CROSS JOIN big_fact2 b CROSS JOIN big_fact2 c;
ERROR 8722: The minimal memory required by the query [497460 KB] exceeds the query cap size [400000 KB]
HINT: Increase MAXQUERYMEMORYSIZE parameter of the 'all_users' resource pool
Note that he’s still able to run queries that use less memory than the value of MAXQUERYMEMORYSIZE.
dbadmin=> SELECT COUNT(*) FROM big_fact2;
COUNT
-----------
200000000
(1 row)
dbadmin=> SELECT COUNT(*) FROM big_fact2 WHERE c1 = 'AAAAC';
COUNT
-------
15
(1 row)
Helpful Link:https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/Built-inPoolConfiguration.htm
Have fun!