PG does support hints actually.. and I used them to solve the last performance
problem I had, rather than waiting n years for the query planner to be
improved. The problem in question (from an automated query planning point of
view) is the lack of multi-column statistics, leading to the wrong index being
used.
The only thing is, the hints are expressed in an obscure, ad-hoc and
implementation dependant language.
For example, the "Don't use index X" hint (the one I used) can be accessed by
replacing your index with an index on values derived from the actual index,
instead of the values themselves. Then that index is not available during
normal query planning.
Another example is the "Maybe use index on X and also sort by X" hint, which
you access by adding "ORDER BY X" to your query. That would have solved my
problem for a simple select, but it didn't help for an update.
Then there's the "Don't use seq scan" hint, which is expressed as "set
enable_seqscan=off". That can help when it mistakenly chooses seq scan.
And there are many more such hints, which are regularly used by PG users to
work around erroneous query plans.
While writing this email, I had an idea for a FAQ, which would tell PG users
how to access this informal hint language:
Q: The query planner keeps choosing the wrong index. How do I force it to use
the correct index?
A: Have you analyzed your tables, increased statistics, etc etc etc? If that
doesn't help, you can change the index to use a value derived from the actual
row values. Then the index will not be available unless you explicitly use the
derived values in your conditions.
With such a FAQ, us people who use PG in the real world can have our queries
running reliably and efficiently, while work to improve the query planner continues.
