Hacker News Reader

Comment by zmmmmm | Hacker News Reader

> that page has a quite balanced view of pros and cons of existing hint systems

I found the cons section somewhat disappointing given how much respect I have for Postgresql maintainers in general.

Most of the "problems" are essentially manifestations of dysfunction within users or PostgreSQL development itself. People won't report optimizer bugs if they can fix them themselves, etc. (far more likely they won't report the bugs if they have no way to prove their alternative query plan actually performs better, eg: by adding a hint).

Many are just assertions which I highly doubt would hold up if validated ("most of the time the optimizer is actually right", "hints in queries require massive refactoring" ...).

View on HN

> (far more likely they won't report the bugs if they have no way to prove their alternative query plan actually performs better, eg: by adding a hint).

They don't need to run an alternative plan, they just need to report that the main plan did far more work than needed.

In the real world, when users have a problem in their mission-critical system, they ask for a solution, not just give up on the mission.

> Many are just assertions which I highly doubt would hold up if validated ("most of the time the optimizer is actually right", "hints in queries require massive refactoring" ...).

Their 20 years of experience beats your idle speculation.

> Their 20 years of experience beats your idle speculation.

Perhaps.

But it comes across to me on that page as arrogance and dismissiveness of user needs. Hence why I find it disappointing, even if it is mainly in how it is expressed.

> they just need to report that the main plan did far more work than needed.

And how do they know that the chosen plan is not already optimal? How many reports do you think you'd get if every time someone encountered a "slow" query they reported it? And what fraction of those would be finally found to be caused by a bad optimizer as opposed to the user's fault like a missing index, outdated statistics, ... 1%? 0.1%? See stack overflow for a sample of that ratio. Be careful what you wish for.

If hints existed performance reports could be "default plan of x is slow, I know because when I use hints w,z then it's fast".

What should they do in the meantime while they're waiting for the pg project to acknowledge the problem, someone to propose a solution, and someone to implement a fix, just sit on their hands and twiddle their thumbs while their database server spews smoke? Hints are an escape hatch that empowers users to take over a when the optimizer veers off course. Not having any way to control the plan is like a tesla autopilot that doesn't allow the driver to take control over the car in an emergency.

Also, distrusting users to report performance issues is a weird attitude and gives a bad taste. Maybe it's true, or maybe you need to make it easier.

Long-time developers of Postgres have spent a long time developing Postgres -- not necessarily running a deployment of it nor writing and running queries on growing datasets.

It's almost self-evident that this is the case, because if they had, they'd have implemented some more fine-grained way of overriding the query optimizer when it inevitably fails at times.

Reply on news.ycombinator.com