[jeltz]

I feel that is the least fair of the complaints (I agree with several of them and have some of my own too). Not because query hints are not disreable but because who is going to pay for maintaing them? It is not really dogma (I, with dome help, managed to convince them to merge one very specific query hint: MATERIALIZED for CTEs) but that they do not want to expose more of the innards of the query planner than necessary to not slow down planner development. Planner development is hard enough as is, with query hints it is going to become at least twice as hard due to even more fear of breaking applications.

[barrkel]

STRAIGHT_JOIN is probably my favourite feature of MySQL in terms of planner hints; but there's actually a deeper inconsistency behind the philosophy.

Usually, when you get a bad query plan, it's because the join order isn't right. Outside the start table and hash joins, indexes need to match up with both predicates and the join keys. Get the wrong join order and then your indexes aren't used.

Since you need to specify which indexes to build and maintain, and such indexes are generally predicated on the query plan, why not ensure that the query is using the expected indexes?

If one really wants to go down the route of no optimizer hints, then the planner should start making decisions about what indexes to build and update. Go all in.

[SigmundA]

Right db developers decide what indexes to add but aren't allowed decide if and how they are used.

Join order / type and which indexes to use would go a long way, thats pretty much all I need to do on MSSQL server if the planner is not cooperating.

[magicalhippo]

> Join order

Had to fight this a few times, planner thought it was smart to scan an index for a few million rows, then throw almost all of them away in a join further up, ending up with a few hundred rows.

Caused the query to take almost a minute. Once the join order was inverted (think I ended up with nesting queries) the thing took a second or two.

[fuy]

I wouldn't paint all hints with the same brush - it's not like the very fact of having a hint exposes query planner internals for arbitrary usage. Some hints may be more useful than others, and some may be less complicated to maintain - why not try to investigate if there's an intersection of these two sets that would be a valuable addition to Postgres?