What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
A PostgreSQL query can be fast in psql and slow in an application because the two clients may not be executing it with the same parameters, session settings, or prepared-statement behavior. One possibility is plan caching: PostgreSQL may reuse a generic plan that is less suited to a particular parameter value than a custom plan would be. That is a hypothesis to test—not a diagnosis based on the timing difference alone.
Why is a PostgreSQL query fast in psql but slow in my application?
The application may take a different path to PostgreSQL than an interactive psql command. The SQL text, parameter values and types, session settings, connection reuse, and driver or pooler behavior can all affect what PostgreSQL plans and executes. The PostgreSQL protocol overview explains that the simple query protocol sends a textual query for parsing and execution, while the extended query protocol separates parsing, binding parameters, and execution.
As an Amazon Associate I earn from qualifying purchases.
Prepared-statement plan caching is one possible explanation. A prepared statement can use a custom plan that accounts for the current parameter values, or a generic plan that is reused independently of those values. When parameter values vary greatly in how many rows they match, a reusable plan may perform poorly for some executions. The actual cause in a particular application can only be established by comparing its inputs and plans.
How custom and generic plans differ
| Plan type | How PostgreSQL plans | Trade-off |
|---|---|---|
| Custom | Generates a plan for an execution using the parameter values supplied for that execution. | Can select a strategy suited to those values, but requires planning for executions. |
| Generic | Uses a plan independent of the parameter values, reusable across executions. | Saves repeated planning work, but may be inefficient when values strongly affect selectivity and the best scan or join strategy. |
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 if its estimated cost is not sufficiently higher to make repeated custom planning preferable. The five executions are a documented part of this decision rule, not a guarantee that the generic plan will be fast for every real execution. See PREPARE.
#1 Best Overall
How to check whether the app is using a generic plan
Run the check in the same database session used by the application, where possible. SQL-level prepared statements are session-scoped; a statement prepared on one connection is not automatically available to another. The protocol overview also describes named prepared statements as lasting until their session ends or they are explicitly closed. Pooling and connection reuse can therefore affect which statement and plan you observe.
- Match the inputs. Compare the application’s SQL with the query you run in
psql, using the same database, representative parameter values and types, and relevant session settings. Include both common and uncommon values if their selectivity differs. - Inspect the prepared statement. If you can access the application’s session and know the SQL-level statement name, run
EXPLAIN EXECUTE statement_name(actual_values). A generic plan displays parameter symbols such as$1; a custom plan displays the values supplied for that execution. You can also inspectpg_prepared_statements, which lists prepared statements available in that session. See PREPARE. - Compare plan details and timings. Look at the plan shape and estimated versus actual row counts, as well as planning time and execution time.
EXPLAIN ANALYZEruns the query and reports actual runtime information, but its profiling adds overhead; take care when comparing short-running queries. See EXPLAIN.
To view a generic plan with placeholders without actual values, use EXPLAIN (GENERIC_PLAN). PostgreSQL documents that this option cannot be combined with ANALYZE; see EXPLAIN.
How to capture evidence when the slowdown is intermittent
Log plans for slow statements
PostgreSQL’s auto_explain module can log plans for statements that cross a configured duration threshold. It does nothing by default, so it must be configured; account for the module’s overhead when enabling it. See the auto_explain documentation.
Free tools Windows power users keep installed
One-click scans. No signup required.
Compare planning and execution statistics
pg_stat_statements tracks planning and execution statistics. It must be loaded through shared_preload_libraries because it requires shared memory. See the pg_stat_statements documentation.
Rank #3
When to test a plan-mode override
Only after collecting evidence, compare PostgreSQL’s plan modes as a controlled diagnostic. The plan_cache_mode setting supports force_custom_plan and force_generic_plan. Test with representative parameter values and compare the combined planning-plus-execution cost: forcing either mode is not a universal fix. Details are in PREPARE.
Why the plan can change between observations
PostgreSQL may re-analyze and re-plan a prepared statement before execution after relevant DDL changes or planner-statistics updates. A change to search_path can also trigger reparsing. Because prepared statements are scoped to sessions, the connection selected by an application pooler and its session settings matter when reproducing a result. See PREPARE and the protocol overview.
The documented plan-selection behavior here is from PostgreSQL 18’s PREPARE documentation; the cited EXPLAIN documentation is for PostgreSQL 17. Check the documentation for the release running on your server before relying on version-specific syntax or behavior.
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.
