iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A PostgreSQL query can be fast in psql and slow in an application because the two clients may send different parameters, use different protocols, or run under different session conditions. One possibility is prepared-statement plan caching: PostgreSQL may reuse a generic plan that is less efficient for a particular parameter value than a custom plan would be. That is a hypothesis to test, not a diagnosis; the driver, pooler, SQL, and execution plans determine what is happening in a specific application.
Why can the same query be fast in psql but slow in an application?
The query text alone does not guarantee identical execution. The application may bind parameters separately, use different parameter types or values, reuse prepared statements on a connection, or run with different session settings. A query run interactively in psql may follow a different path.
PostgreSQL’s simple query protocol sends a textual query for parsing and execution. The extended query protocol separates parsing, binding parameter values, and execution, and can retain prepared statements within a session. A client library chooses how and when to use these facilities. Check the actual driver and connection-pool behavior rather than assuming every application uses the same path. See the PostgreSQL protocol overview.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWhat are custom and generic plans?
Custom plans use the current values
A custom plan is made for an execution using that execution’s parameter values. This can help when values have sharply different selectivity—for example, when one value matches a small number of rows but another matches many. PostgreSQL can use value-specific information to choose a scan or join strategy.
#1 Best Overall
Generic plans are reused
A generic plan does not depend on the supplied parameter values, so it can be reused and reduce planning work. The trade-off is that it may be a poor fit when different values call for different plans.
With plan_cache_mode = auto, PostgreSQL’s documented heuristic makes custom plans for the first five executions, calculates their average estimated cost, then creates a generic plan and compares its estimated cost with that average. It uses the generic plan when the estimated cost does not make repeated replanning preferable. This is a cost heuristic, not a guarantee that the generic plan will be fast for every real execution. The behavior is documented in the PostgreSQL 18 PREPARE documentation.
How do I check which plan the application is using?
- Make the comparison equivalent. Test the same SQL against the same database with matching parameter values and types, and account for relevant session settings. Include both common and uncommon values if their selectivity may differ.
- Inspect the application’s database session. For an SQL-level prepared statement, run
EXPLAIN EXECUTE statement_name(actual_values)in the same session. A generic plan displays parameter symbols such as$1; a custom plan displays the supplied values. The session’spg_prepared_statementsview lists prepared statements available there. See the PREPARE documentation. - Compare plan details, not just elapsed time. Look at plan shape and estimated versus actual row counts, as well as planning and execution time.
EXPLAIN ANALYZEexecutes the query and reports actual runtime information, but its profiling adds overhead—especially relevant for short-running queries. Check the PostgreSQL 17 EXPLAIN documentation and verify syntax for your server release. - Capture evidence when the slowdown is intermittent. PostgreSQL’s
auto_explainmodule can log plans for slow statements. It does nothing by default: configure a duration threshold, and account for the module’s overhead. Details are in the auto_explain documentation. - Review aggregate planning and execution statistics.
pg_stat_statementstracks planning and execution statistics. It must be loaded throughshared_preload_librariesbecause it requires shared memory. See the pg_stat_statements documentation.
For a generic-plan view without actual parameter values, EXPLAIN (GENERIC_PLAN) accepts placeholders. It cannot be combined with ANALYZE; consult the EXPLAIN documentation for the deployed release.
How should I test whether plan caching is the cause?
Only after capturing the application’s behavior, test PostgreSQL’s plan-mode overrides as a controlled diagnostic. Set plan_cache_mode to force_custom_plan or force_generic_plan for a comparison, then measure representative parameter values and the combined planning-plus-execution cost. A custom plan may improve execution but add planning work; a generic plan may save planning time but perform worse for some values. Neither mode is universally preferable.
Rank #3
Use the measured plans and timings to determine whether caching explains the slowdown. If forcing a mode does not account for the difference, continue investigating the actual inputs and session path rather than treating plan caching as established.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Why do application sessions and poolers matter?
SQL-level prepared statements belong to a database session; one connection does not automatically share its statement with another simultaneous client. An application pooler’s connection reuse therefore affects which prepared statements are present in a session and which plan you observe.
PostgreSQL may also re-analyze and re-plan before execution after relevant DDL changes or planner-statistics updates. A change in search_path can trigger reparsing as well. These behaviors are described in the PREPARE documentation and the protocol overview. Because drivers and poolers differ, inspect the configuration and session that actually execute the slow query.
Free tools Windows power users keep installed
One-click scans. No signup required.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
