3 ms·

It's so odd because Postgres generally does everything else so well and is generally very customizable and modular otherwise. Yet the maintainers totally scoff

It's so odd because Postgres generally does everything else so well and is generally very customizable and modular otherwise. Yet the maintainers totally scoff at the idea of query hints even in the face of so many examples of planning failures. Their arguments are asinine too (from https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion): * Poor application code maintainability: hints in queries require massive refactoring. * Interference with upgrades: today's helpful hints become anti-performance after an upgrade. * Encouraging bad DBA habits slap a hint on instead of figuring out the real issue. * Does not scale with data size: the hint that's right when a table is small is likely to be wrong when it gets larger. * Failure to actually improve query performance: most of the time, the optimizer is actually right. * Interfering with improving the query planner: people who use hints seldom report the query problem to the project. Literally all of these are expressions of condescension and distrust of their own users. Anyone who would use hinting would be using it to solve a real issue caused by the shortcomings of the planner -- that much should be self-evident.

rrrrrrrrrrrryan 5y ago
Is it possible to at least lock-in a query plan with Postgres? I understand the philosophy behind wanting to keep the queries themselves as declarative as possible, but there should be some way to prevent a really important query from randomly going rogue and using a stupid index, just because something completely unrelated changed somewhere else in the database.
jandrewrogers 5y ago
Ironically, the underlying cause of the query planner failures is a defect in the statistics collection architecture -- it doesn't scale to large databases in some cases. An argument of "does not scale with data size" applies to the query planner.

rrrrrrrrrrrryan 5y ago

Is it possible to at least lock-in a query plan with Postgres? I understand the philosophy behind wanting to keep the queries themselves as declarative as possible, but there should be some way to prevent a really important query from randomly going rogue and using a stupid index, just because something completely unrelated changed somewhere else in the database.

jandrewrogers 5y ago

Ironically, the underlying cause of the query planner failures is a defect in the statistics collection architecture -- it doesn't scale to large databases in some cases. An argument of "does not scale with data size" applies to the query planner.