Stats based query planning is really useful for ad-hoc queries where you don't care exactly how fast it runs, you just want it to be as fast as possible with as little thought put into the query as possible. Given that originally SQL was meant to be used by humans, its dependence on planners makes sense.
For queries issued by machines where predictability is the most important thing, it probably makes sense to hard-code plans or at least pin a plan so it can't change out from underneath you at midnight in production. I'm not sure about Postgres, but you can do this on Oracle DB. There's probably a better way to express schemas in which both indexing and plans can be constructed ahead of time in many cases where the developer already knows the likely distribution of data, and not having performance cliffs is more important than the convenience of the planner. Example:
@Entity
class SomeTable {
@Mostly("SUCCESSFUL", "FAILED") SomeEnum row;
}
and then the DB mapper - not necessarily an ORM - could provide the query hints needed to force the DB onto a reasonable plan for the annotated distribution.
Not possible. It’s explicitly a non-goal, sub-optimal plans are considered a bug to be fixed, you can’t even force the use of an index.
To their credit, the Postgres query planner is amazing and does generally work very well that you don’t need forcing indexes or plans. But that’s little comfort when it does not and you have a production incident on your hands
Edit: I think RDS Aurora Postgres does let you have managed query plans
It's not really true. There are many session-scoped flags you can set which influence planner behavior, and sometimes toggling them for a specific query is what gets you through. More often, judicious use of CTEs as an optimization barrier (materialized CTE in moderrn versions) is useful to force execution order.
I like that Postgres has a lot of tools at its disposal for executing queries. But you can't rely on it for data even with modest scale. It's rare that it does a good job with interesting queries.
I think we’re saying the same thing: you can’t force PG to a plan or an index, you can only change your query in hope it gets the hint.
Even session scoped flags are a really coarse tool, don’t guarantee you’ll get the plan you want and it might unexpectedly impact other queries in the session.
Materialised CTE are one of the only tools that give real control, but an optimisation barrier is often the opposite of what you want
We don’t entirely disagree tbh. But having to work with MySQL now, I find I’m more surprised by how bad it is than I am by Postgres being too smart for its own good. I dont love everything about Postgres at all, but I always end up thinking it’s the least bad option
For queries issued by machines where predictability is the most important thing, it probably makes sense to hard-code plans or at least pin a plan so it can't change out from underneath you at midnight in production. I'm not sure about Postgres, but you can do this on Oracle DB. There's probably a better way to express schemas in which both indexing and plans can be constructed ahead of time in many cases where the developer already knows the likely distribution of data, and not having performance cliffs is more important than the convenience of the planner. Example:
@Entity class SomeTable { @Mostly("SUCCESSFUL", "FAILED") SomeEnum row; }
and then the DB mapper - not necessarily an ORM - could provide the query hints needed to force the DB onto a reasonable plan for the annotated distribution.