Question: Plans and the planner
Why is my query fast in psql but slow in the app?
Answered in the first paragraph. Last updated .
Most often because the two are not running the same plan. Your application sends the statement with parameters, and after it has been executed a few times the server may choose a plan built without looking at the values. Typing the query yourself with the values written in never does that, because there are no parameters to ignore. The second possibility is that the plan is identical and the time is being spent somewhere you are not measuring.
The prepared statement path
A parameterised statement is planned in one of two ways: with the actual values, which costs planning effort each time, or once without them in a form reusable for any values. The server tries the first several times, compares, and may settle on the second. Generic and custom plans covers the switch and the setting that forces either one, which is the right diagnostic here: force the reusable form in your own session and the fast query becomes the slow one in front of you.
It goes wrong when the values are not interchangeable. A status column that is overwhelmingly one value, a tenant identifier where one tenant is a thousand times larger than the rest, or a date range that is usually narrow and occasionally the whole table: in each case the plan that suits the average is badly wrong for some inputs, and the client never sees which one it got.
When the plan really is the same
Then the difference is not in the database, and the measurement has to move. Three candidates cover most of it. The client fetches every row where you paged through the first screen, so the cost is transmission and materialisation rather than execution. The statement runs inside a transaction that is also doing other work, and the request timing includes the rest. Or the query is one of several the application issues per request, and the slow one is not the one you copied.
Server-side timing settles it, but read it carefully: a statement called inside a function is accounted separately from the statement the client sent, so totals can double-count if you mix the two. That distinction is top-level versus nested statements, and the column that marks it arrived in PostgreSQL 14.
If the query used to be fast from the application too, the plan changed for a reason and the reason is usually data rather than code, which is why a query suddenly gets slow. Query plan regression covers catching the change rather than discovering it.