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

Environment

PostgreSQL 14.5 primary on Debian 11 (dedicated server), partitioned table (range partitions by date, ~120 partitions), application servers connect via PgBouncer v1.17.0 in transaction pooling mode; app uses psycopg2 with prepared statements via server PREPARE.

Symptoms

Complex SELECT queries that run in ~120–200 ms when executed directly in psql execute in ~1.0–2.5 s when issued by the application through PgBouncer. Problem only appears under load in production; single-user test from psql always fast. EXPLAIN shows different plans between direct and pooled executions.

Observations

Reproduced by logging into the same DB with psql on the app host: direct execution fast. 2) When connecting via PgBouncer in session pooling mode, performance matches direct psql. 3) EXPLAIN (ANALYZE, BUFFERS) for the slow run shows a generic plan using bitmap heap scans across many partitions; direct psql uses index-only scans on recent partitions. 4) Setting 'server_prepared_statements' = off in the client removes some variation but not all. 5) No significant CPU or I/O differences observed on DB server during tests; planner estimates differ dramatically between paths.

Already attempted

Switched PgBouncer to session pooling temporarily — queries returned to normal. Disabled client-side prepared statements and replaced with simple statements; performance improved partially. Gathered pg_stat_statements and compared plans. Confirmed application uses connection pooling with prepared statements that are bound at client side.

Question

Why do the same SQL statements choose a generic/inefficient plan when executed through PgBouncer transaction pooling, but optimal plans when run directly or via session pooling? What changes should I make to keep PgBouncer in front of the app without regressing query performance?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 3h ago• Trouble ID

Most likely 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%

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.

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.