CREATE STORED QUERY¶
Registers a named query inside a schema template so its plan is generated ahead of time (“warmed”), letting matching queries issued at runtime reuse the cached plan instead of being planned from scratch. A stored query is not invoked by name, it exists only to pre-populate the plan cache.
The plan cache is local to each engine instance and starts empty. When a fresh instance is constructed it opens the catalog once and, for every schema template present that declares stored queries, plans each stored query offline and stores the resulting plan in that instance’s cache. Templates created after the instance is up are warmed by the next fresh instance.
A runtime query hits a warmed plan when its canonical (literal-stripped) form, the temporary functions in scope, and its plan constraints all match — so bound values may differ from those the stored query was written with.
Syntax¶
CREATE STORED QUERY query_name
[ ( parameter_name data_type [ NOT NULL ], ... )
PREPARE FOR
( parameter_name { IS [ NOT ] NULL | = { TRUE | FALSE } }, ... ),
...
]
[ DECLARE
FUNCTION function_name ( [IN] parameter_name data_type [DEFAULT default_value], ... )
AS ( query );
...
]
AS query
Parameters¶
query_nameThe name of the stored query, unique within the schema template. The name identifies the stored query in metadata; it is not used to invoke the query.
- parameter list
Optional. Declares typed named parameters, which the body refers to by name. See Parameter list below.
PREPARE FORblockLists the combinations of parameter states that each get their own warmed plan. Every nullable parameter must be pinned in every case; one declared
NOT NULLmay be left out. See Prepared cases below.DECLAREblockOptional. Declares one or more transaction-local functions that the stored query body may call, using the same syntax as CREATE TEMPORARY FUNCTION. Multiple functions are separated by semicolons.
queryThe body of the stored query — any SELECT statement (including CTEs, recursive CTEs, and joins). The body may contain concrete literals.
Parameter list¶
A stored query may declare parameters, which is how it stands in for a runtime query that binds values rather than writing them as literals:
CREATE STORED QUERY by_col1(param_a BIGINT)
PREPARE FOR (param_a IS NOT NULL)
AS SELECT * FROM t1 WHERE col1 = param_a
In the body a parameter is written as a bare identifier, with no ? — unlike the runtime statement it stands for, where the same reference is ?param_a. It becomes exactly that internally, so the stored form above is equivalent to SELECT * FROM t1 WHERE col1 = ?PARAM_A. A parameter may also be referred to inside a declared function’s body.
The name does not take part in matching. The plan-cache key writes every parameter as a bare ?, and a value is bound by its position in the query text. So a runtime query that writes ?x, or a plain ?, at the same place reuses the plan just as well as one that writes ?PARAM_A.
A parameter is nullable by default, as a column is. Write NOT NULL to declare that it never receives a null.
A parameter may be of any type a prepared statement parameter can be. A type declared by the schema template — a struct or an enum — needs the TYPE keyword, unlike a column definition, which takes the bare name:
CREATE TYPE AS STRUCT stats_t (start_date BIGINT, hometown STRING)
CREATE TABLE reviewer (id BIGINT, stats stats_t, PRIMARY KEY(id)) -- column: bare name
CREATE STORED QUERY by_stats(p TYPE stats_t) -- parameter: TYPE keyword
PREPARE FOR (p IS NOT NULL)
AS SELECT id FROM reviewer WHERE stats = p
Such a parameter is not warmed yet: see What is not warmed.
Naming¶
A parameter name is an ordinary identifier. Quoted, it keeps its spelling exactly. Unquoted, it is normalized according to the connection option CASE_SENSITIVE_IDENTIFIERS. Inside the stored query the same rule applies to the parameter list, the prepared cases and every reference in the body, so they must agree with each other. An application that sends the matching query at runtime does not have to use the same name: matching is by position, not by name.
A quoted name must still be valid after a ?, since every reference in the body is rewritten to ?name: it must start with a letter and continue with letters, digits, _ or /, which is what ?name accepts. "my param" and "a-b" are rejected, because a reference to them in the body would become ?my param, which does not parse, and ?a - b, which parses as something else.
A parameter name must not collide with a parameter of a declared function, since inside that function’s body the two references would be indistinguishable.
Prepared cases¶
Most plans depend only on the type of a value, so one plan serves every value of that type. A few depend on the value itself: a NULL, or a boolean TRUE or FALSE, lets the planner fold predicates away, drop index probes and delete whole branches. PREPARE FOR lists the combinations worth their own plan, and each is warmed separately.
Four states can be written for a parameter:
written |
warmed for |
|---|---|
|
any non-null value of |
|
a null binding only, with the predicate folded away at plan time |
|
a |
|
a |
= TRUE and = FALSE require the parameter to be declared BOOLEAN. IS NULL cannot be written for a parameter declared NOT NULL, since that would contradict the declaration.
Every parameter declared nullable — the default — must be pinned in every case:
CREATE STORED QUERY by_region("regionId" BIGINT, show_archived BOOLEAN)
PREPARE FOR
("regionId" IS NULL, show_archived = FALSE),
("regionId" IS NOT NULL, show_archived = FALSE)
AS SELECT * FROM t1 WHERE region_id = "regionId" AND archived = show_archived
Two plans are warmed here. The first has "regionId" bound to null while planning, so the planner folds that predicate; the second leaves it without a value and only knows it is not null, so the plan keeps the predicate and can use an index on region_id.
Because every parameter ends up with a definite state, no two cases can overlap, and there is no rule about which one wins. A binding no case matches — show_archived bound to true above — is not an error: the runtime misses the cache, plans the query with the values in hand and returns correct rows. Only the warm-up is missed. Add a case for a combination that turns out to be hot.
Leaving a nullable parameter unpinned is rejected. Its plan would have to be built with no value and a nullable type, and no single plan is correct for both a null and a non-null binding.
A parameter declared NOT NULL may be left out, since IS NOT NULL is the only state its declaration allows and writing it says nothing new. It is recorded as if written, so what is warmed does not depend on whether the author spelled it out:
CREATE STORED QUERY by_region("regionId" BIGINT NOT NULL, owner_a INTEGER)
PREPARE FOR (owner_a IS NOT NULL)
AS SELECT * FROM t1 WHERE region_id = "regionId" AND owner = owner_a
What is not warmed¶
A failure to warm is never an error at CREATE time and never affects a runtime query: the reason is logged, the counter OFFLINE_STORED_QUERIES_QUERIES_FAILED is bumped, and that stored query is skipped. Queries in the same schema template are unaffected — each is warmed on its own.
A query over a filtered (sparse) index is not warmed. The planner needs the concrete value to prove that the index predicate covers the range, and a parameter pinned IS NOT NULL supplies a type only.
A parameter declared with a schema template type — a struct, an enum, or an array of either — is not warmed when a case leaves it without a value, i.e. pins it IS NOT NULL. Warm-up has to build the type from the declaration, and a name declared by the template cannot be resolved from persisted metadata yet. Everything else resolves, including VECTOR(128, FLOAT), UUID and arrays of primitives.
This is not a runtime limitation. A client binds a struct with setObject and the type comes from the value itself, so such a query plans and runs normally — only cold. Pinning the same parameter IS NULL is warmed too, since that case binds a real value and needs no type.
Warm-up has no record store, so it plans as if every index were readable. A store with an index that is disabled or still being built plans with fewer indexes, so its queries do not find the warmed plan and are planned when they run.
Examples¶
Declare a stored query as part of a schema template:
CREATE SCHEMA TEMPLATE example_template
CREATE TABLE t1(id BIGINT, col1 BIGINT, PRIMARY KEY(id))
CREATE INDEX i1 AS SELECT col1 FROM t1
CREATE STORED QUERY by_col1 AS SELECT * FROM t1 WHERE col1 = 10
by_col1 declares no parameters, so it needs no PREPARE FOR. It warms a plan for col1 = 10. Because literal values are stripped during planning, any runtime query of the same shape reuses it regardless of the constant:
SELECT * FROM t1 WHERE col1 = 42 -- reuses the warmed plan
A DECLARE block declares transaction-local functions the body can call. A declared function is the warm-up counterpart of a runtime CREATE TEMPORARY FUNCTION — same function, supplied while warming instead of in a live session:
CREATE STORED QUERY by_helper
DECLARE
FUNCTION recent(IN threshold BIGINT) AS (SELECT * FROM t1 WHERE col1 > threshold)
AS
SELECT * FROM recent(10)
Temporary functions in scope are part of the plan-cache key, so a runtime query reuses this warmed plan only if it has declared the same temporary function. The runtime session installs an equivalent CREATE TEMPORARY FUNCTION and issues the same query:
CREATE TEMPORARY FUNCTION recent(IN threshold BIGINT) ON COMMIT DROP FUNCTION
AS SELECT * FROM t1 WHERE col1 > threshold;
SELECT * FROM recent(20) -- reuses the warmed plan
The function definition must match the one declared in the stored query; the invocation’s literal is stripped, so any argument value reuses the plan.
A parameter list and a DECLARE block combine, and a parameter may be captured inside a function’s body. PREPARE FOR comes before DECLARE:
CREATE STORED QUERY by_fn(param_a BIGINT, param_b BIGINT)
PREPARE FOR (param_a IS NOT NULL, param_b IS NOT NULL)
DECLARE
FUNCTION f1(IN p BIGINT) AS (SELECT * FROM t1 WHERE col1 = p AND col2 = param_a)
AS
SELECT id FROM f1(param_b)
Here param_a is captured by f1’s body while param_b is passed as its argument. The runtime counterpart installs the same temporary function and issues the same query, binding a value at each of the two places, under any names. The declared function is planned together with the body, once per case, since a captured parameter changes the function’s plan too.
Because names do not matter, each declared function can have its own parameter, pinned by its own case, even when the application uses one prepared parameter name in all of them. Here every function filters on a flag that selects a filtered index, so each needs a real value at warm-up:
CREATE STORED QUERY by_flags(flag1 BOOLEAN, flag2 BOOLEAN)
PREPARE FOR (flag1 = TRUE, flag2 = TRUE), (flag1 = TRUE, flag2 = FALSE),
(flag1 = FALSE, flag2 = TRUE), (flag1 = FALSE, flag2 = FALSE)
DECLARE
FUNCTION t1_by_flag() AS (SELECT * FROM t1 WHERE flag = flag1);
FUNCTION t2_by_flag() AS (SELECT * FROM t2 WHERE flag = flag2)
AS
SELECT A.id FROM t1_by_flag() AS A, t2_by_flag() AS B WHERE A.id = B.id
At runtime, the application creates the same two temporary functions. Each CREATE TEMPORARY FUNCTION is its own prepared statement with a named parameter ?flag, and each statement binds its own value: true for the first function and false for the second. The query then reuses the plan warmed for the case (flag1 = TRUE, flag2 = FALSE):
CREATE TEMPORARY FUNCTION t1_by_flag() ON COMMIT DROP FUNCTION
AS SELECT * FROM t1 WHERE flag = ?flag; -- ?flag bound to true
CREATE TEMPORARY FUNCTION t2_by_flag() ON COMMIT DROP FUNCTION
AS SELECT * FROM t2 WHERE flag = ?flag; -- ?flag bound to false
SELECT A.id FROM t1_by_flag() AS A, t2_by_flag() AS B WHERE A.id = B.id
One shared parameter cannot do this: a case pins it once, so both functions would receive the same value.
See Also¶
CREATE SCHEMA TEMPLATE - The template that stored queries are declared in
CREATE TEMPORARY FUNCTION - Same function syntax as the
DECLAREblock