There is no optimizer that is capable of getting the correct result 100% of the time. Even Oracle with decades of costly engineering behind it requires query tuning (either via explicit hints on the client side sql or via server side plan tuning) at any reasonably large scale.

I like Postgresql and use it when I can and I respect the Postgreql core dev team's adherence to principles but in this particular case I believe they are just inventing arguments to justify their unreasonable stance against allowing query hinting.

So "most of the time" means "100% of the time" where you live?

I mean, I think this is just one of those things where it only matters rarely, but when it does it matters a lot.

Maybe postgres isn't the right solution in those cases. If you need tight control over how your queries are executing you may not want an rdbms.

I don't think choices of things like this ever really come down to one thing like that. Not using an RDBMS might mean having to build a whole bunch of features you wouldn't otherwise have to.

That's possible I guess, but at least some other relational dbs do allow that control, so it's not like it's impossible.

The biggest reason - which doesn't seem to be mentioned - is consistency and stability.

I don't like being woken up to debug why the query planner has decided to do something new. Yes, there's always a "good reason" but does it need to communicate that by taking down production at 3am?

Even if it's a better plan, I don't need my anomaly detection alerts going off because something is now running in half the time...

This is why I prefer to page on features working within SLA instead of lower level things like CPU usage or query time.

Having a system send warnings about CPU or query time are great when I am awake. They are hints to a problem, but not always a problem themselves.

> Failure to actually improve query performance: most of the time, the optimizer is actually right.

my favorite response to someone complaining about a query doing a sequential scan is to tell them to `set enable_seqscan = false;` and rerun the query. usually, Postgres is right. it's rare that it is wrong, but it's nice to have a workaround for those few times that it's wrong - at least on fairly static data.

A common flaw to “just do x and rerun it” is that you are not running both in the same conditions: the data is in RAM second time around. I've seen people think that a change (an index hint, switching between a CTE and a sub-query, …) has made a massive difference because the last couple of times the query ran it timed out or took ages, when in fact the next run works and works faster not because of the change but because the previous run or two have primed the page cache with all the data that is needed, cutting out all the IO.

Test from both warm and cold states. Warm because most queries you are optimising are those that run often so usually do have some or all of the required data in RAM, and cold because sometimes they will run cold in production (and some queries just touch so much data, and/or cause spooling to disk of intermediate steps, so that IO will always be involved).

Ignore anyone who says always test warm because that is the most common state by far: if your query is reading far more than it really needs to when IO is involved, it will be using much more CPU than it needs to at other times and will harm concurrency potential (directly by eating CPU when other things could use it instead, or through holding more read locks than needed and holding them longer than necessary).

Planner is usually correct given the information it has, the table statistics are usually off and are causing problems.

You can't check complex cases with that.

complex cases aren't usually thrown over a wall with "why is this doing a sequential scan?" typically, in the cases someone is asking me that specific question, it's a very simple query.

complex cases, otoh, usually aren't approached with that much naivety.

> people who use hints seldom report the query problem to the project

This statement is shockingly user-hostile and paints the users overall as selfish and uninterested in the improvement of the postgresql project. Certainly some users will use their hint and go home, but you can't know that this would result in a net decrease in performance reports. Maybe with hints different users would be empowered to submit more reports like "The query planner creates a slow plan for query X, I know because when I use hint Z it's faster". Not implementing hints just to spite selfish users while ignoring the potential additional reports submitted by different caring users is shortsighted and antisocial.

Maybe there are a bunch of good reasons why hints should not be implemented, but this is not one of them.

My past commentary: https://news.ycombinator.com/item?id=29987139