3 ms·
Use named prepared statements (the ones where you have to manually DEALLOCATE to free up resources on the server). It is to my understanding that a named prepar
by SPBS 5y ago
Use named prepared statements (the ones where you have to manually DEALLOCATE to free up resources on the server). It is to my understanding that a named prepared statement runs five times using both a custom query plan and a generic query plan, and from the sixth execution onwards it picks the faster of the two and sticks to it for the rest of the prepared statement's lifetime (the query planner never gets called again, for better or worse).
[Thread: SELECT slows down on sixth execution](https://postgrespro.com/list/thread-id/2070689 https://postgrespro.com/list/thread-id/2070689)
If I'm wrong on this, I would desperately like someone more informed on this to correct me because it seems to me that you can force postgres to stick to a query plan, thus obviating the need for query hints
- jurple 5y agoReading up on this (https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html), it seems that there are a number of exceptions where the statement will be re-planned: - DDL modifications to the used objects - updated planner statistics for the used objects - modification to search path I would expect a major Postgres version upgrade to hit these conditions, so the OP problem would still occur. Nevertheless, thanks for the pointer - I had mostly forgotten about this possibility.
- SPBS 5y agoI'd like to correct myself on this point, it seems that if Postgres picks custom_plan over generic_plan it will always re-plan (that is what custom_plan is, it takes the prepared statement parameters into account while generic_plan always uses the same plan regardless of the parameters). So it's a toss up whether Postgres will actually lock in the static query plan over the dynamic query plan.