Prepared-statement parameters

An application query often says WHERE tenant_id = $1. The value is known: the application sends it. Postgres still plans that prepared statement differently from a statement with the value pasted in.

${ORDERS} in a schema is a row count ExoBench substitutes before the database exists. $1 is not. ExoBench does not inline $1, and it does not write a PREPARE for you.

Measure a parameter as two arguments.

preBenchmarkSql runs once:

PREPARE exobench_stmt(int) AS
SELECT c.tier, count(*) AS n, sum(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.tenant_id = $1
GROUP BY c.tier

The query is what repetitions re-run. It is one statement, and it has no $1 left in it:

EXPLAIN (ANALYZE, BUFFERS) EXECUTE exobench_stmt(1)

The value goes in EXECUTE. Postgres can still estimate it as unknown from the sixth execution of that same prepared statement: a generic plan whose selectivity is 1 / count(DISTINCT tenant_id), instead of the custom plan for the value you passed.

Replacing $1 with 1 in the SELECT will not show that plan change. Ten runs of tenant_id = 1 are a different query and stay on one plan.

Do not put PREPARE in the query. With more than one repetition the query must be a single EXPLAIN (ANALYZE, BUFFERS). A PREPARE on every run is a new prepared statement, so a plan change that depends on executing the same prepared statement more than once never shows up. A $1 left in the query fails in Postgres with bind message supplies 0 parameters, but prepared statement "" requires 1.

The statement name is yours. exobench_stmt is fine. $1 stays inside the PREPARE body. $$ and $tag$ are Postgres dollar-quoting. Leave them alone.