Comment by jval43
12 days ago
I've actually come around to the Postgres way of thinking. We shouldn't want or need plan hints usually.
Literally every slow Postgres statement I worked on in the last few years was due to lack of accurate statistics, missing indexes, or just badly designed queries. Every one was fixable at the source, by actually fixing the core issue.
This was in stark contrast to the myriads of Oracle queries I also debugged. The larger older ones had accumulated a "crust" of plan hints over the years. Most not so well thought out and not valid anymore. In fact, often just removing all hints made the query faster rather than slower on newer Oracle versions.
It's so tempting to just want to add a plan hint to "fix" the suboptimal query plan. However, the Postgres query planner often has an actual reason for why it does what it does and overall I've found the decisions to be very consistent.
>I've actually come around to the Postgres way of thinking. We shouldn't want or need plan hints usually.
They only come out at night, mostly.
PG is 40 years old and still has planner bugs being fixed up regularly, and having no control and waiting for a new version when a hint could fix the issue at runtime is an obvious problem that should have been addressed long ago.
It's great the devs want to make the planner perfect and strive for that, it is an unattainable goal worth pursuing IMO. Escape hatches are required hence the very popular pg_hint_plan extension.
But in the end after many years of dealing with these things I have come to the opposite conclusion, let the query language drive the plan more directly and senior devs can fix juniors devs mistakes in the apps source code and the plans will be committed in source control for all to see and reference going forward.
SQL comes from an idea of non technical people querying a system in ad-hoc ways, still useful, but if you are technically competent in data structures and programming and making an application that uses the db, the planner just gets in your way at least in my experience.