JDBC prepared statements on application server suddenly select a generic plan causing a 15× slowdown for partitioned table queries

Environment

Java Spring Boot application (OpenJDK 11, PostgreSQL JDBC driver 42.x) connecting via a connection pool (HikariCP) to PostgreSQL 13 on Debian. The app issues parameterized queries against a large date-partitioned table using PreparedStatement.setTimestamp(). Production query latency jumped from ~200 ms to multiple seconds for queries that filter on the partition key.

Symptoms

Only requests issued via application (prepared statements through JDBC) are slow; identical SQL copy-pasted and executed in psql (with explicit literal values) returns in ~200 ms. EXPLAIN (ANALYZE, BUFFERS) shows that prepared statement executions use a generic plan with sequential scan across partitions instead of index-constraint exclusion plan. Query times worsen during peak load and remain fast when run in psql.

Observations

pg_stat_activity shows large number of active prepared statements. Prepared statement PLANS in pg_prepared_statements (when present) show generic_plan=true. Server logs show 'plan is generic' messages when log_min_duration_sampled enabled. Application connection pool uses transaction-level pooling and may reuse connections; app uses PreparedStatement for many slightly different parameter types (sometimes setObject). No recent schema changes; stats were updated recently via autovacuum. Reproduced with a local JDBC minimal test harness showing same generic plan behavior when using setTimestamp vs hard-coded literal.

Already attempted

Disabled HikariCP statement caching and forced the application to use simple query mode by setting 'prepareThreshold=0' in the JDBC URL; prepared queries returned to fast plans. Increased default_statistics_target and ran ANALYZE on partitions — partial improvement for some queries. Restarted database to clear plan caches and observed no lasting improvement until prepareThreshold was zeroed. Upgraded JDBC driver in a staging environment but issue persisted until disabling server-side prepared statements.

Question

What is the safest way to configure JDBC/connection pool or PostgreSQL so that parameterized queries against partitioned tables use custom/bound plans (or re-plan per parameter) without disabling all prepared statements? Are there server-side settings or driver parameters to prefer custom plans for partition pruning use-cases, and what are the trade-offs?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

JDBC driver / connection pool switching to server-side prepared statements so the server produces generic plans that prevent effective partition pruning. 95%

Large number of partitions increases cost of a generic plan; planner defaults to sequential scan over partitions when parameter values are unknown. 90%

Parameter binding with varying data types (setObject vs strongly-typed setters) causing planner to generalize and choose a generic plan. 55%

Summary

This is the known interaction where server-side prepared statements can produce a generic plan if the planner cannot rely on parameter values at plan time — leading to partition pruning not firing and index scans being skipped. The JDBC driver and connection pool by default use server-side prepared statements after a threshold; setting driver parameters that force simple query mode or a zero prepare threshold causes the planner to see constants and produce custom plans. Recent growth in partition count and parameterized usage amplifies the effect. Disabling server-side prepares fixes the symptom but removes benefits of prepared statement reuse.

Suggested Fixes

Diagnostic step 1

In a staging environment, set the JDBC URL parameter prepareThreshold=0 (or equivalent for your driver) to force simple query mode and validate latency improvement; this is reversible by changing the JDBC config and restarting app pool.

Diagnostic step 2

If you want server-side prepares mostly enabled, lower the driver's server-side prepare threshold (e.g., increase prepare threshold to a higher number) and selectively use prepared statements only for queries that benefit from them.

Diagnostic step 3

Audit application code to use strongly-typed setter methods (setTimestamp, setInt) instead of setObject to reduce type ambiguity at the server planner.