Runtime Environment: Why the App’s Plan Differs from psql #

The plan you read in an interactive psql EXPLAIN is frequently not the plan your production application executes for the same SQL. This is one of the most disorienting gaps in query tuning: you optimize a statement to run in two milliseconds at the prompt, deploy nothing, and the application still times out. The query text is byte-for-byte identical. What differs is the runtime environment wrapped around that text.

This section builds the mental model for that gap and then works through its three concrete mechanisms — connection pooling and the per-backend plan cache, prepared-statement plan pinning, and session parameter (GUC) drift — before laying out a diagnostic workflow you can apply the next time psql and production disagree.

A Plan Is a Function of Four Inputs #

Hold this model in your head: a PostgreSQL execution plan is a pure function of four inputs.

plan = f(SQL text, parameter values, session GUCs, statistics)

When you run EXPLAIN in psql, you control all four inputs directly and can see them. The trap is that the application runtime mutates three of the four — everything except the text — without telling you. A connection pooler changes which backend (and therefore which plan cache and which session GUCs) you land on. The prepared-statement machinery changes whether the parameter values are used to build the plan at all. And configuration drift changes the session GUCs from one connection to the next. The SQL text is the only input that survives the trip unchanged, which is exactly why comparing text alone is so misleading.

App clients multiplexed through a pooler onto per-backend plan caches and GUCs Many application clients on the left connect to a single connection pooler in the middle. The pooler multiplexes them onto three PostgreSQL backends on the right. Each backend contains its own plan cache and its own set of session GUCs, so which backend a transaction lands on determines which cached plan and which settings apply. App Clients client 1 client 2 client 3 client 4 … N clients Pooler (PgBouncer) multiplexes Backends backend A plan cache GUCs work_mem=4MB backend B plan cache GUCs work_mem=64MB backend C plan cache GUCs search_path=alt each transaction may land on a different backend → different cache + GUCs
Application clients are multiplexed by the pooler onto a small pool of backends. Each backend carries its own plan cache and session GUCs, so which backend a transaction is routed to silently determines which cached plan and which settings apply.

Core Mechanics 1 — Connection Pooling and the Per-Backend Plan Cache #

Everything PostgreSQL caches for plan reuse — server-side named prepared statements, the plan cache entries behind them, and the effect of any SET command — lives inside a single backend process. It is not shared across the server’s other backends and does not survive the backend being handed to a different client.

A connection pooler in transaction mode multiplexes many client connections over a small number of backends, returning a backend to the pool at the end of every transaction. Your client’s next transaction may run on an entirely different backend. So a server-side prepared statement you created (PREPARE, or the extended-query protocol your driver uses) exists on backend A, but your next call runs on backend B where that statement was never prepared — the driver either re-prepares it (extra planning) or the call fails with “prepared statement does not exist.”

-- Run this over a transaction-mode pooler and it is frequently empty,
-- because the named statements were prepared on a different backend.
SELECT name, statement, generic_plans, custom_plans
FROM pg_prepared_statements;
-- (0 rows)  -- your app "prepared" 40 statements, but not on THIS backend

The consequence for plans is subtle. When plan reuse works, PostgreSQL plans a statement once and reuses that plan; when the pooler defeats reuse, every execution re-plans, inflating planning time and, more importantly, changing which plan runs from call to call depending on how many times the current backend happens to have seen the statement. The connection pooling and plan cache cluster works through PgBouncer’s session, transaction, and statement modes and how each interacts with the cache.

Core Mechanics 2 — Prepared-Statement Plan Pinning #

The second mechanism operates even on a single dedicated connection. When a statement is prepared and executed repeatedly, PostgreSQL does not commit to a generic plan immediately. For the first five executions it builds a custom plan each time — re-planning with the actual bind values, so selectivity reflects the real parameter. On the sixth and later executions it compares the average cost of those custom plans against the cost of a generic plan (planned once, ignoring the parameter values). If the generic plan is not estimated to be more expensive, PostgreSQL pins it and stops re-planning.

That switch is invisible from the SQL text and is the classic reason a query “suddenly” gets slow after warming up. A minimal look at a generic plan bound to a placeholder:

PREPARE order_lookup (int) AS
  SELECT order_id, total_amount FROM orders WHERE status_id = $1;

-- After 5+ executions PostgreSQL may pin the generic plan:
EXPLAIN EXECUTE order_lookup(3);
Index Scan using orders_status_id_idx on orders
    (cost=0.43..842.10 rows=5000 width=20)
  Index Cond: (status_id = $1)
  -- $1, not the literal 3 → this is a GENERIC plan
  -- rows=5000 is the AVERAGE selectivity across all status_id values,
  -- not the selectivity of status_id = 3 specifically

The Index Cond: (status_id = $1) — a placeholder rather than a literal — is the tell that a generic plan is in force. The rows=5000 estimate is derived from average selectivity, so if status_id = 3 is a high-frequency value covering a third of the table, the generic plan badly under-counts and the chosen index scan does far more work than planned. The plan_cache_mode GUC (auto, force_custom_plan, force_generic_plan) is the lever that controls this behavior; the prepared-statement plan pinning cluster covers the lifecycle and when to override it. This is fundamentally a cost estimation problem — the generic plan trades per-parameter accuracy for one-time planning cost.

Core Mechanics 3 — Session Parameter (GUC) Drift #

The third mechanism is the quietest. The same SQL, planned with the same parameter values, produces different plans on two connections because their session GUCs differ. The most common culprits:

Drift arises because settings can be applied from many layers: ALTER ROLE ... SET, ALTER DATABASE ... SET, connection-string options, application-issued SET, and the pooler’s server_reset_query. The session parameter drift cluster shows how to audit the effective value and its source on every connection.

-- What is actually in effect on THIS backend, and where did it come from?
SELECT name, setting, source, context
FROM pg_settings
WHERE name IN ('work_mem','search_path','random_page_cost',
               'statement_timeout','jit','effective_cache_size');

source tells you whether a value came from the config file, a per-database default, a per-role default, the client connection, or a runtime SET — which is exactly the audit trail you need when two connections disagree.

Diagnostic Workflow #

When psql and production disagree, work through these steps in order. Each isolates one of the four plan inputs.

  1. Reproduce with the app’s exact parameters and GUCs.

    Do not test with convenient literals. Capture the real bind values and the real session settings the application uses, then reproduce them explicitly:

    BEGIN;
SET LOCAL work_mem = '4MB';          -- match the app's effective value
SET LOCAL search_path = 'app, public';
PREPARE p (int) AS SELECT ... WHERE status_id = $1;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE p(3);
ROLLBACK;

  2. Inspect the live cache and effective settings.

    On the actual backend the app is using, read pg_prepared_statements (are statements cached? are generic_plans climbing?) and current_setting() for each suspect GUC:

    SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;
SELECT current_setting('work_mem'), current_setting('search_path');

  3. Capture the real executed plan with auto_explain.

    The only reliable way to see the plan the server ran — including a generic plan bound to $1 — is to log it server-side:

    LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;   -- log every statement (test only)
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;
-- run the app workload; read the plans out of the server log

  4. Compare and isolate the divergent input.

    Diff the captured plan against the psql plan. If the node shapes match but actual time differs, suspect a GUC (usually work_mem). If the shapes differ, suspect a generic-vs-custom plan switch or a different search_path resolving a different index.

  5. Pin the divergent input and re-verify on a fresh pooled connection.

    Fix the input at the right layer — ALTER ROLE/ALTER DATABASE for GUCs, plan_cache_mode for plan pinning — then re-run a canary query on a newly checked-out pooled connection to confirm the fix survives the pooler.

Common Pitfalls #

1. psql shows a custom plan while the app runs a generic plan. Diagnostic: psql EXPLAIN with a literal shows an accurate rows= and a fast index scan, but the app is slow. The app’s driver uses server-side prepared statements and has passed the five-execution threshold. Fix: reproduce with PREPARE/EXECUTE past five runs, or set plan_cache_mode = force_custom_plan for the affected statement and re-measure.

2. The pooler resets the backend and loses prepared statements. Diagnostic: intermittent “prepared statement does not exist” errors, or planning time that never amortizes. Fix: in PgBouncer transaction mode, either use PgBouncer 1.21+ protocol-level prepared-statement support, disable server-side prepared statements in the driver, or move to session pooling for the affected pool.

3. Per-role work_mem differs from the value you tested. Diagnostic: a sort or hash spills in production but not in psql. Fix: check pg_settings.source and current_setting('work_mem') on the app’s connection; the app role may have an ALTER ROLE ... SET work_mem you never saw. See sort and hash node analysis for reading the spill signals.

4. search_path changes which index is used. Diagnostic: the same query uses a different index (or a seq scan) on two connections. Fix: qualify table names with their schema, or pin search_path at the role level; an unqualified name resolved against a different schema hits a different physical table and its indexes.

5. statement_timeout aborts the query mid-plan. Diagnostic: the app reports “canceling statement due to statement timeout” while psql completes. Fix: the slow generic plan simply runs past the app’s statement_timeout; fix the plan (custom plan or better index) rather than raising the timeout, and confirm with identifying plan bottlenecks.

6. jit adds latency to short queries. Diagnostic: EXPLAIN ANALYZE shows a JIT section with non-trivial Generation and Inlining time on a query that returns few rows. Fix: JIT only pays off on long CPU-bound queries; raise jit_above_cost or set jit = off for OLTP workloads dominated by short statements.

Frequently Asked Questions #

Why does my query run fast in psql but slow in the application? #

psql sends the query with literal values and your interactive session GUCs, so the planner builds a custom plan tailored to those literals. The application usually sends the query as a server-side prepared statement with bind parameters, and after five executions PostgreSQL may switch to a cached generic plan built from average selectivity. Different session GUCs on the pooled connection — work_mem, search_path — compound the difference, so the two environments can legitimately produce different plans for identical text.

How do I see the plan PostgreSQL actually executed for an app query? #

Enable the auto_explain module with auto_explain.log_min_duration set low enough to capture the query, and turn auto_explain.log_analyze on to record actual times. The plan is written to the server log with the real bind parameters and the plan type that ran. This is the only reliable way to see a generic plan bound to $1 placeholders, because the application never surfaces the plan the way an interactive EXPLAIN does.

Does a connection pooler change my execution plans? #

Indirectly, yes. A pooler in transaction mode hands each transaction a different backend, so server-side prepared statements, the per-backend plan cache, and any SET commands do not persist across your client’s transactions. This resets plan caching and can silently discard session GUCs you set earlier, producing different plans than a dedicated connection would. The SQL text is unchanged, but three of the four plan inputs are affected by which backend you land on.

What makes a generic plan risky for skewed data? #

A generic plan is built once without knowing the parameter value, so it uses average column selectivity from pg_statistic. When a parameter later matches a high-frequency most-common value, the real row count is far larger than the average the plan assumed, so an index scan chosen for the average case scans far more rows than estimated and can be dramatically slower. This is why parameter skew and generic plans interact so badly, and why plan_cache_mode exists.

How do I stop work_mem from differing between connections? #

Pin it deterministically at the role or database level with ALTER ROLE app_user SET work_mem = '64MB' or ALTER DATABASE appdb SET work_mem = '64MB', rather than issuing SET from application code where it may leak or be reset by the pooler. Audit the effective value per connection with current_setting('work_mem') and check pg_settings.source to confirm where the value came from, so no connection quietly falls back to a different default.

Back to home