Skip to main content

Plan Cache

By the end of this page you will know which statements ScramDB plans and compiles once and runs many times, what plan_cache_mode chooses, what still happens on every execution, and how to see it on /metrics.

A statement with parameters is planned once, compiled once and run from its cached plan: a prepared statement your driver sends with Parse, Bind and Execute, a PREPAREd statement run by EXECUTE, a statement inside a PL/pgSQL function (its variables are its parameters) and a statement of a SQL function's body (its arguments are its parameters). The plan, called the generic plan, holds a slot for each parameter, and every execution binds its own values into the slots and runs the compiled plan. A statement without parameters is planned once and kept as it is.

Examples on this page use this table:

sql
CREATE TABLE orders (id BIGINT PRIMARY KEY, customer INT, total BIGINT);
CREATE INDEX orders_customer ON orders (customer);
INSERT INTO orders SELECT i, i % 100, i * 10 FROM generate_series(1, 10000) AS g(i);

Custom and generic plans​

plan_cache_mode is PostgreSQL's setting, a session setting any role may change:

ValueWhat a statement with parameters runs
auto (the default)Its first five executions are planned with their values written in (custom plans). Then the generic plan is planned once, and every later execution runs it, unless its estimated cost is above the average custom plan's, with the work of planning counted into each custom plan as PostgreSQL counts it.
force_generic_planThe generic plan, from the first execution.
force_custom_planA plan with the values written in, for every execution.
sql
SET plan_cache_mode = force_generic_plan;
PREPARE by_customer(int) AS SELECT count(*), sum(total) FROM orders WHERE customer = $1;
EXECUTE by_customer(7); -- plans the generic plan
EXECUTE by_customer(8); -- binds 8 into it; nothing is planned

The choice is made per statement and shared by every session that runs the same statement under the same settings, so the five custom plans of auto are counted once for all of them.

What happens on every execution​

A generic plan is compiled once. An execution binds its values into the plan's slots and runs the compiled plan: nothing is planned or compiled again, whatever the values. What depends on the values is read when the plan starts, as PostgreSQL's run-time keys and run-time pruning read it:

  • Indexes. The index is chosen once, and a read through it takes its key from the parameter when it starts: WHERE customer = $1 reads orders_customer for every value, and WHERE id IN ($1, $2, $3) or WHERE id >= $1 reads the primary key's index with each execution's values. EXPLAIN shows the key as $1.
  • Pages. A scan skips the pages a comparison with a parameter rules out (WHERE total > $1), by the execution's value, as it does for a value written in.
  • Subqueries that run once. An uncorrelated scalar subquery (WHERE id = (SELECT max(id) FROM orders WHERE customer = $1)) is part of the one plan and runs once per execution, before the rest, with the execution's values, from its own compiled form.
  • Values. Both the interpreter and compiled code read a parameter once per batch, never per row. A value the compiled code prepares before it runs (an IN list hashed, a LIKE pattern compiled, a numeric bound scaled to a column's) is prepared once per execution, and so is what a function reads from a parameter (round(n, $1)'s scale, a to_char template over a date, a number or an interval, an lpad width and fill), once per batch at most, so a plan reading a parameter keeps the same compiled shape a plan with the value written in has.
  • IN lists. A list of parameters past the length at which a list of literals becomes a set (WHERE id IN ($1, ..., $70)) is one set built once per execution from the bound values, as the literal list is; a shorter list over a column stays the comparisons, each a key of the column's index.
  • Row windows. LIMIT $1, OFFSET $1 and FETCH FIRST $1 ROWS of the statement's top query are read when the execution starts, a sort keeping only the rows the window asks for.
  • NULL. A parameter bound to NULL runs a form of the plan made for that pattern of NULLs, planned and compiled the first time the pattern is seen and kept beside the others.

A parameter is a value, never a column position: GROUP BY $1 and ORDER BY $1 group and sort by the value, as PostgreSQL does, whichever plan runs.

A parameter has its type wherever it stands, as in PostgreSQL: a parameter declared int8 is a bigint column in SELECT $1, a declared float8 bound 1e20 prints 1e+20, and a bound NULL is a NULL of the parameter's type. The custom plans, the generic plan and the result columns the driver's Describe announces agree on every execution.

What stays planned per execution​

  • Every parameter type is a slot. A parameter takes its type without the type's modifier, as in PostgreSQL: a varchar(10) parameter is text, a numeric(10,2) one keeps its value's own scale, a character(5) one is the unconstrained bpchar, and a parameter of a domain holds the domain's base type, the domain's constraints applied where the statement casts to the domain. A parameter with no type at all (one the statement gives no type) keeps its value in the plan.
  • A parameter the statement needs as a number while it is planned below its top query (LIMIT $1 of a subquery, a window frame's offset, TABLESAMPLE), or a top query whose select list draws a value per row it returns (nextval, a volatile function) under LIMIT $1, makes the statement keep its custom plans.
  • An UPDATE or DELETE of one table (no FROM or USING, no subquery) runs its generic plan like a SELECT: one plan, the one it finds its rows with, judges its privileges and row policies by and computes its new values from, each execution's values read from its slots. INSERT, MERGE and an UPDATE or DELETE with FROM or USING are planned with their values written in; a SELECT they read from is planned once. A prepared read of one row by its primary key runs as a cached point operation and is never planned again.
  • A statement whose joins read an input that is computed first and kept for the join (a grouped subquery joined to a table, for example) computes that input on each execution, from its compiled form; the joined part is compiled once for each way the input turned out (kept as a table, or a single row read into the plan).

EXECUTE's arguments​

EXECUTE reads its arguments as PostgreSQL does: each is an expression of no table, evaluated once before the statement runs and converted to the type PREPARE declared for it as an INSERT converts a value for a column.

sql
PREPARE spread(float8) AS SELECT count(DISTINCT x) FROM (SELECT $1 AS x FROM generate_series(1, 50)) s;
EXECUTE spread(random()); -- 1: one value, read by all 50 rows

The number of arguments must be the statement's (42601). A subquery (0A000), an aggregate (42803), a window function (42P20) or a set-returning function (0A000) in an argument is refused, a column name is 42703, and an argument no assignment converts to the declared type is 42804 (parameter $1 of type boolean cannot be coerced to the expected type integer). A quoted literal is read by the declared type's input.

EXPLAIN ANALYZE EXECUTE​

EXPLAIN ANALYZE EXECUTE name(args) binds the arguments once, shows the plan the execution runs and runs it once, a write included, with the rows it returned:

sql
PREPARE one(bigint) AS SELECT total FROM orders WHERE id = $1;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) EXECUTE one(42);

When a plan is planned again​

  • DDL. A change to a table, index, type, function or schema a plan reads drops it, and the next execution plans again (auto counts its five custom plans again).
  • Privileges. A plan keeps the answer of its privilege check; a GRANT or REVOKE makes the next execution check again, so a revoked role is refused at its next execution.
  • Statistics. A new ANALYZE drops the cached plans.
  • Memory. Plans are kept within plan_cache_memory (Configuration); a plan dropped for room is planned again when it is next run.

Watching it​

scramdb_plans_total{kind} counts every planning: generic (a generic plan), custom (a statement with parameters planned with its values) and plain (a statement without parameters). A single-table UPDATE or DELETE, and an UPDATE ... FROM or DELETE ... USING whose FROM or USING items are tables or views, counts like a SELECT: its rows to change are found by the plan it was bound to, and its FROM or USING join runs as part of that plan, on one server and on a cluster alike; any other write planned with its values counts one planning per execution (custom when the client bound its values as parameters, plain otherwise). On a steady workload of prepared reads, generic and custom stay flat after the first executions, while scramdb_plan_cache_hits_total grows with every execution (Observability).

bash
curl -s http://127.0.0.1:9090/metrics | grep scramdb_plans_total

Differences from PostgreSQL​

  • PostgreSQL keeps a generic plan per prepared statement in each session; ScramDB keeps one per statement shape for every session, so a statement planned in one session runs from its cached plan in the next.
  • On a cluster, each node plans the part of a statement it runs once per shape and binds every execution's values into it, as the node a statement enters does. During a rolling upgrade, a node still on the older version plans each part it runs with its values written in.
  • EXPLAIN of a statement with parameters shows the plan that runs: a generic plan with $1 where a key or a value goes, a custom plan with the values written in.