So, it is 2017 and Postgres still doesn't support query hints like other relational databases? Yep, it's true, and probably will be forever since "'Oracle-style' optimizer hints" is listed in the "Features We Do Not Want" section of the Postgres todo list. A wiki page outlines the reasons for this.
While this seems perfectly logical to people who have used Postgres for years, it strikes new users as rigid and extreme. There are several reasons for this divergence.
First, what new users don't realize is that there are ways to control the optimizer, just not with inline query hints. The most useful ways are by changing planner constants and other planner options, which allow you to tailor the optimizer to your specific hardware and query types. This is a more holistic approach to optimizer tuning because, if set properly, they improve all queries, compared to "use this index"-style hints which must be added to every query.
There are also several crude enable settings that can be changed at the session level to prevent the optimizer from considering certain executor capabilities, like join and scan types. Unfortunately, these effect the entire query, i.e. you can't turn off sequential scan for just one table. It also doesn't allow the specification of which index to use. These are really designed for debugging.
In the same vein, an area that often causes suboptimal plans are queries that reference several columns from the same table where column values are correlated, e.g. a US state column with the value 'NY' is more likely to have a city value of 'New York City' than a state value of 'AZ' would. While inline query hints might be able to fix this case for constants, it can't help when variables are passed into the query, like with parameterized queries. A patch being considered for Postgres 10, multivariate statistics, would benefit all queries by collecting statistics on column combinations in addition to the single column statistics already collected. This, again, is an example of a holistic approach to improving the optimizer.
There are two additional ways to control single queries that are often used in production. Common table expressions allow a query to be broken up into stages which are executed sequentially because each with clause acts as an optimization fence. Secondly, the use of offset 0 prevents subqueries from being moved into the outer query.
I expect future improvements to address other optimizer shortcomings. One area where inline query hints would really help is fixing queries in an emergency, when you don't have time to research the cause. Postgres currently doesn't have an easy solution for that.
Bruce Momjian is a Senior Database Architect at EnterpriseDB.
This post originally appeared on Bruce's personal blog.
For information on adopting Postgres, contact us or send an email to sales@edbpostgres.com.