How DISCARD ALL and Session Reset Affect Plan Reuse #

When a query that used to execute in a fraction of a millisecond suddenly shows inflated planning time on a pooled connection, the cause is often not the query at all — it is the server_reset_query that PgBouncer runs between checkouts. In transaction and statement pooling, PgBouncer returns a backend to the pool after every transaction and, by default, wipes it clean with DISCARD ALL before handing it to the next client. That reset is what keeps tenants isolated, but it is also what guarantees that no cached plan survives long enough to be reused.

What server_reset_query Actually Runs #

PgBouncer’s server_reset_query fires when a server connection is returned to the pool. The standard value is:

; pgbouncer.ini
[pgbouncer]
pool_mode = transaction
server_reset_query = DISCARD ALL

DISCARD ALL is not a single narrow operation — it is a superset command that PostgreSQL expands into five separate resets. Understanding the members of that set is the whole game, because each one clears a different piece of cached state:

Because DEALLOCATE ALL and DISCARD PLANS both run, the plan cache is empty by the time the next client checks the connection out. That is the mechanism behind the first-query-is-slow phenomenon: the backend is structurally forced to re-plan.

DISCARD ALL between checkouts wipes all cached plan state A horizontal timeline of one pooled backend. Client A prepares and executes a statement, building a plan cache. On checkin, PgBouncer runs server_reset_query DISCARD ALL, which clears prepared statements, plan caches, temp tables, and GUCs. Client B then checks out a cold backend and must re-plan on its first statement. one pooled backend, over time → Client A checkout PREPARE + EXECUTE plan cache built SET search_path, work_mem checkin → DISCARD ALL DEALLOCATE ALL DISCARD PLANS / TEMP RESET ALL Client B checkout cold backend first statement must re-plan state that does NOT survive the reset prepared statements cached plans temp tables GUCs / search_path Result: every checkout starts with an empty plan cache → first statement pays full planning time Isolation is the benefit; re-planning is the cost.

This behavior is tightly coupled to how PgBouncer transaction mode handles prepared statements: the reset is one half of why server-side prepared statements are fragile under transaction pooling. The reset drops them, and the pooler can route the next EXECUTE to a backend that never had them.

Annotated Evidence: The Re-Planning Tax #

To measure the cost, enable planning-time tracking and read it from pg_stat_statements. This surfaces how much time the planner burns before execution even begins.

-- Requires: shared_preload_libraries = 'pg_stat_statements'
SET pg_stat_statements.track_planning = on;   -- record planning time separately

SELECT calls,
       plans,                                  -- how many times the statement was PLANNED
       round(mean_plan_time::numeric, 3) AS mean_plan_ms,
       round(mean_exec_time::numeric, 3) AS mean_exec_ms
FROM pg_stat_statements
WHERE query LIKE 'SELECT % FROM invoices WHERE account_id = $1%';

On a pool that resets every backend, plans climbs almost in lockstep with calls, because nearly every call is the first statement on a freshly reset connection:

 calls  | plans  | mean_plan_ms | mean_exec_ms
--------+--------+--------------+--------------
 480000 | 472310 |       0.842  |       0.091
--        ↑ plans ≈ calls: almost every execution re-planned
--                          ↑ planning dominates: 0.842 ms plan vs 0.091 ms exec

The planner is spending nearly ten times as long choosing the plan as the executor spends running it. The plan itself is trivial — a single index scan:

EXPLAIN
SELECT invoice_id, amount_due
FROM invoices
WHERE account_id = 4471;
Index Scan using idx_invoices_account on invoices
  (cost=0.43..8.45 rows=6 width=20)          -- unitless planner cost estimate
  Index Cond: (account_id = 4471)
-- Trivial plan, yet it is rebuilt on every checkout because DISCARD PLANS wiped the cache.

The plan is cheap to execute and cheap to build in isolation — but rebuilding it hundreds of thousands of times, once per checkout, turns planning into the dominant cost. This is the same class of problem covered under cost estimation models: the planner does real work every time it re-derives even a simple estimate.

Before / After: Preserving Plan Reuse #

The lever is whether cached plans survive across the transactions a single client runs. The comparison below holds the query constant and changes only the reset policy.

-- BEFORE: server_reset_query = DISCARD ALL, transaction pooling
--   plan cache wiped every checkout
 calls  | plans  | mean_plan_ms | mean_exec_ms
 480000 | 472310 |       0.842  |       0.091
--   planning ≈ 90% of total statement time

-- AFTER: session pooling (or DISCARD PLANS narrowed reset, single-tenant)
--   plan cache survives the client's session; prepared plan reused
 calls  | plans  | mean_plan_ms | mean_exec_ms
 480000 |    118 |       0.061  |       0.089
--   plans collapses toward the number of distinct backends, not calls
--   mean_plan_ms drops ~14x because the generic plan is reused

The plans counter is the tell. When it tracks calls, the plan cache is being destroyed between uses. When it collapses to a small number, plans are being reused across many executions.

Step-by-Step Diagnostic Workflow #

Step 1 — Confirm the pool mode and reset query #

; pgbouncer.ini — inspect these two lines first
pool_mode = transaction
server_reset_query = DISCARD ALL

In transaction or statement mode with DISCARD ALL, expect zero cross-checkout plan reuse. In session mode the reset still runs at checkin, but a client holds the backend for its whole session, so plans survive within that session.

Step 2 — Capture planning-time evidence #

SET pg_stat_statements.track_planning = on;
SELECT queryid, calls, plans,
       round(mean_plan_time::numeric,3) AS mean_plan_ms
FROM pg_stat_statements
ORDER BY plans DESC
LIMIT 10;                        -- statements re-planned most often float to the top

Step 3 — Enumerate what the reset clears #

Run the reset manually on a session and watch state disappear:

PREPARE acct_lookup AS SELECT * FROM accounts WHERE account_id = $1;
CREATE TEMP TABLE temp_invoice_batch (invoice_id int);
SET work_mem = '64MB';

DISCARD ALL;                     -- the exact command PgBouncer runs at checkin

SELECT count(*) FROM pg_prepared_statements;   -- → 0, DEALLOCATE ALL ran
SELECT current_setting('work_mem');            -- → back to default, RESET ALL ran
-- temp_invoice_batch is gone: DISCARD TEMP dropped it

Step 4 — Quantify the re-planning tax #

Compare planning time on a cold checkout against a warm reuse. The difference between mean_plan_ms when plans ≈ calls and when plans is small is the tax you are paying per checkout.

Step 5 — Choose a policy #

; Single-tenant pool only — keeps prepared statements, drops stale plans
server_reset_query = DISCARD PLANS
server_reset_query_always = 0

Step 6 — Re-measure #

Re-run the Step 2 query. plans should fall well below calls and mean_plan_ms should drop. Confirm no session state leaks by checking current_setting('search_path') and pg_prepared_statements from a freshly checked-out connection.

Common Pitfalls #

Disabling the reset leaks GUCs and search_path between tenants. If you set server_reset_query = (empty) to preserve plans in a multi-tenant pool, the next client inherits the previous client’s search_path, work_mem, role, and statement_timeout. A tenant that set search_path = tenant_42 can silently read another tenant’s tables. This is the most dangerous optimization on this page — never disable the reset on a shared pool. Related drift in session settings is covered under detecting work_mem drift across sessions.

Temp table bloat when DISCARD TEMP is skipped. Narrowing the reset to DISCARD PLANS keeps prepared statements but stops dropping temp tables. A workload that creates temp_invoice_batch on each transaction will accumulate temp relations on the backend until it errors on a name collision or exhausts temp space.

Prepared-statement staleness referencing dropped objects. If you keep prepared statements across checkouts but a migration drops or alters accounts, the cached plan references an object that no longer matches. The next EXECUTE fails with a cache-lookup or “cached plan must not change result type” error until the statement is re-prepared. DISCARD PLANS between checkouts prevents this by forcing a re-plan.

Assuming session pooling removes the reset. server_reset_query still runs at checkin in session mode — it simply matters less because the client held the backend for its whole session. If you rely on temp tables surviving across transactions, confirm the reset is not silently dropping them at every transaction boundary via server_reset_query_always.

Frequently Asked Questions #

Does DISCARD ALL clear the plan cache as well as prepared statements? Yes. DISCARD ALL is a superset that runs DEALLOCATE ALL, DISCARD PLANS, DISCARD TEMP, DISCARD SEQUENCES, and RESET ALL in one statement. DEALLOCATE ALL drops the prepared statements and DISCARD PLANS drops the cached generic plans behind them, so nothing that was planned on the previous checkout survives — the next first execution of any statement re-plans from scratch.

Can I narrow server_reset_query to DISCARD PLANS to keep some caching? You can, but it trades safety for reuse. DISCARD PLANS alone keeps prepared statements while dropping stale generic plans, and in session pooling you can drop the reset entirely. In transaction pooling, any narrowing leaves GUCs, search_path, and temp tables from the previous client attached to the backend, which is a cross-tenant leakage risk. Only narrow the reset when every client shares the same role, search_path, and security context.

Why does the first query on a pooled connection always seem slower? Because DISCARD ALL ran between the previous checkin and your checkout, the backend has an empty plan cache. The first execution pays full planning time before it can execute, which pg_stat_statements records as elevated mean_plan_time. Subsequent executions within the same checkout can reuse the plan, but the next checkout starts cold again.

Related