FULL AI ANALYSIS

Partitioned-table queries run fast in psql but 8–12× slower when routed through PgBouncer transaction pooling

Generated Oct 15, 2026, 9:17:34 AM

Analysis summary

Transaction pooling in PgBouncer breaks session affinity: server-side prepared statements and session-local planner state cannot be relied on because client connections are multiplexed across server connections. This often causes the server to use generic plans (plan cached without specific parameter values) or choose conservative plans because parameter types or values are not visible at prepare time. The behavior matches: session pooling => generic/worse plans; session mode => good plans. Secondary contributors include differences in search_path/role or SET configuration propagated per-session but lost under pooling.

Possible causes

PgBouncer transaction pooling prevents server-side prepared statements and per-session planner state, producing generic plans that are suboptimal for partition pruning (planner lacks parameter value information) — primary cause

90%

Session-local configuration (search_path, application_name, role, enable_partition_pruning, or planner-related GUCs) differs between direct psql sessions and server connections used by PgBouncer, causing different plans

40%

Client uses prepared statements with ambiguous parameter data types; type resolution differs when prepared via pooled connection versus direct session, resulting in bad plan selection

30%

Planner statistics for newer partitions are stale; direct psql tests hit recently cached plans or different partition set due to session-local settings causing different planning decisions

25%

Suggested fixes

Diagnostic step 1

Confirm that PgBouncer is intentionally in transaction pooling mode. If so, consider switching to session pooling for application pools that rely on server-side prepared statements or session-local configuration, and measure impact.

Diagnostic step 2

If transaction pooling must be kept, disable server-side prepared statements in the client driver (for psycopg2 set server_side_prepared_statements=False) so parameter values are visible at run-time and the planner can pick a custom plan.

Diagnostic step 3

Collect EXPLAIN (ANALYZE, VERBOSE) of the same query from both direct psql and pooled runs; compare planner-related GUCs (show all) to identify differences in settings like search_path, default_transaction_isolation, enable_partition_pruning, etc.

Diagnostic step 4

Test query performance after adding explicit type casts to parameters in the application SQL to eliminate ambiguity that can trigger generic plans.

Diagnostic step 5

Consider moving critical queries to prepared statements at application layer with plan caching that is safe under pooling (e.g., using session pooling) or rewrite queries to be more stable across parameter values (use explicit partition constraint conditions). If these steps do not resolve the issue, escalate to DBA to evaluate PgBouncer topology changes or to add a dedicated session-pool for partitions-heavy workloads.

Contact • Help • API Docs • Careers • Terms • Privacy • Cookies • Ads