OptimizerHintsDiscussion
From PostgreSQL wiki
Jump to navigationJump to searchOptimizer and Query Hints Discussion
Many people over the years have requested that the PostgreSQL project implement "optimizer hints" or "query hints" as they are implemented in other RDBMSes such as Oracle and MySQL. The official current stance from the community is this:
We are not interested in implementing hints in the exact ways they are commonly implemented on other databases. Proposals based on "because they've got them" will not be welcomed. If you have an idea that avoids the problems that have been observed with other hint systems, that could lead to valuable discussion.
Problems with existing Hint systems
- 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.
Where Existing Hint Systems Benefit
- "One-shot" issues, such as annual or one-time reports, for which maintainability is not a concern
- Ability to "test" various execution paths in detail and see how the optimizer is working (or not)
- Optimizer failure
- Implementation failure (known issues)
- Theoretical failure (estimation limits, n^2 correlation problem)
Available mechanisms for query troubleshooting
There are several existing controls with the database that have an impact similar to what people expect hinting to accomplish. See Hinting at PostgreSQL for one list.
