The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Prepare a typed query | PREPARE order_by_id (bigint) AS
SELECT id, status, total
FROM app.orders
WHERE id = $1; | View examples |
| Execute a statement | EXECUTE order_by_id(42001); | View examples |
| Prepare a write | PREPARE add_event (uuid, text) AS
INSERT INTO app.events(account_id, kind)
VALUES ($1, $2)
RETURNING id; | View examples |
| Fix parameter meaning | PREPARE events_since (timestamptz) AS
SELECT id
FROM app.events
WHERE occurred_at >= $1::timestamptz; | View examples |
| Inspect the chosen plan | EXPLAIN (ANALYZE, BUFFERS, SETTINGS) EXECUTE orders_for_tenant(42); | View examples |
| Test custom planning | SET LOCAL plan_cache_mode = 'force_custom_plan'; | View examples |
| Test generic planning | SET LOCAL plan_cache_mode = 'force_generic_plan'; | View examples |
| Restore automatic choice | SET plan_cache_mode = 'auto'; | View examples |
| List prepared statements | SELECT name,
parameter_types,
generic_plans,
custom_plans
FROM pg_prepared_statements
ORDER BY name; | View examples |
| Find generic-plan users | SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements
WHERE generic_plans > 0; | View examples |
| Discard cached plans | DISCARD PLANS; | View examples |
| Release one statement | DEALLOCATE order_by_id; | View examples |
| Release all statements | DEALLOCATE ALL; | View examples |
| Pin object resolution | PREPARE active_accounts AS
SELECT id
FROM app.accounts
WHERE disabled_at IS NULL; | View examples |
| Check server version | SHOW server_version; | View examples |
| Identify two-phase state | SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared; | View examples |
Prepared statements separate parsing from repeated execution and can reduce planning work, but they are session-local state rather than a universal speed switch. Parameter-sensitive data distributions, connection pooling, DDL, statistics changes, privileges, and failover all affect their behavior. Measure both planning and execution, use typed value parameters, and let the driver own protocol-level statements unless an operational reason calls for SQL PREPARE.
Step by step
Detailed examples
Prepare once per session and execute with typed values
PREPARE accepts SELECT, INSERT, UPDATE, DELETE, MERGE, or VALUES. PostgreSQL parses, analyzes, and rewrites at preparation, then plans when EXECUTE needs a plan. Names are unique within one database session and disappear when that session ends. Explicit parameter types reduce inference surprises and make operator and index selection easier to reason about.
PREPARE order_by_id (bigint) AS
SELECT id, status, total
FROM app.orders
WHERE id = $1;
EXECUTE order_by_id(42001);
EXECUTE order_by_id(42002);Bind data values, not SQL structure
Parameters stand in for values; they cannot replace table names, column names, keywords, or sort directions. Use driver bind APIs for untrusted values and a strict allowlist when identifiers must vary. Parameterization prevents values from becoming SQL syntax, but it does not grant privileges or bypass row-level security. Keep pooled sessions from crossing trust boundaries with stale role, search_path, or prepared-statement state.
PREPARE add_event (uuid, text) AS
INSERT INTO app.events(account_id, kind)
VALUES ($1, $2)
RETURNING id;
EXECUTE add_event('7cc8f02b-c7f4-47cc-9532-498908014e1b', 'invoice.sent');Note: For application code, use the driver's parameter API rather than constructing this EXECUTE call from untrusted text.
Diagnose generic plans against parameter skew
A custom plan uses current parameter values; a generic plan can be reused across calls. With plan_cache_mode=auto, PostgreSQL currently runs the first five parameterized executions with custom plans, then compares their average estimated cost with a generic plan. Highly skewed tenants or predicates can make one generic plan poor for outliers. Compare realistic values with EXPLAIN; EXPLAIN ANALYZE executes the statement and can write data for DML, so wrap tests safely or omit ANALYZE.
BEGIN;
SET LOCAL plan_cache_mode = 'force_custom_plan';
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
EXECUTE orders_for_tenant(42);
SET LOCAL plan_cache_mode = 'force_generic_plan';
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
EXECUTE orders_for_tenant(42);
ROLLBACK;Note: Run performance experiments on a safe workload replica or controlled environment; ANALYZE really executes the statement.
Inspect the cache from the owning connection
pg_prepared_statements exposes only prepared statements available in the current session, including whether SQL PREPARE or the frontend/backend protocol created them. generic_plans and custom_plans are cumulative choice counts, not timing data. Capture EXPLAIN results and workload latency separately, and remember that connecting through another pool backend gives a different view.
SELECT name, prepare_time, parameter_types, result_types, from_sql,
generic_plans, custom_plans
FROM pg_prepared_statements
ORDER BY prepare_time;Let PostgreSQL invalidate plans and retire unused state
PostgreSQL re-analyzes and replans after relevant object-definition changes, planner-statistics updates, or search_path changes. DISCARD PLANS releases cached plans but keeps the prepared statements; DEALLOCATE removes statements. DISCARD ALL is a broader session reset, includes DEALLOCATE ALL and advisory-lock cleanup, and cannot run inside a transaction block. Coordinate manual cleanup with the driver because it may cache server statement names too.
DISCARD PLANS; -- keep definitions; force later replanning
DEALLOCATE order_by_id; -- remove one SQL prepared statement
DEALLOCATE ALL; -- remove all SQL prepared statementsMake deployment boundaries predictable
Schema-qualify application objects and list result columns explicitly. Although search_path changes trigger reparsing, creating a same-named object earlier on an unchanged path does not itself invalidate an already resolved statement. DDL that changes a protocol-prepared query's result shape can also surprise clients that cached row metadata. Roll out compatible schemas before application code, drain or recycle long-lived connections when necessary, and test old and new clients together.
PREPARE active_accounts AS
SELECT a.id, a.display_name, a.created_at
FROM app.accounts AS a
WHERE a.disabled_at IS NULL
ORDER BY a.id;Treat prepared state as connection-local and disposable
Drivers commonly use the extended query protocol and may decide when to name and cache statements; SQL PREPARE is not a substitute for the driver's bind API. Transaction-pooling proxies can hand consecutive transactions to different server sessions, so named session state requires explicit pooler and driver support. A reconnect, server restart, or failover loses prepared statements; recreate them or let the driver do so. They are neither durable nor WAL-replicated. PREPARE TRANSACTION is separate two-phase-commit state and should not be confused with query preparation. Verify behavior against the deployed PostgreSQL and client versions.
SELECT name, prepare_time
FROM pg_prepared_statements
ORDER BY prepare_time;
SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared;Note: The first view is session-local query state; the second is durable two-phase transaction state visible cluster-wide. pg_prepared_statements.result_types requires PostgreSQL 16 or newer.
Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupPostgreSQL: PREPAREpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: EXECUTEpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: pg_prepared_statementspostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Query Planning Configurationpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Frontend/Backend Protocol Overviewpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: DISCARDpostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.