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.
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.