> i think you need to provide more details for a good reply. what changed between the time index was used and when it wasn’t? I also had to “convince” postgresql to use my index but that lead to a much better design
I disagree: given that nothing changed, I don't think any details need to be provided.
The question is NOT "Is postgresql's choice better than mine?" The question is "A certain design was working and suddenly broke because one day the query planner decided to start choosing a different (and unusable) plan - is this ever acceptable?" and the answer is obviously No, regardless of the details.
I guarantee you that something changed. Maybe the row count passed a certain threshold. Maybe you upgraded the database version.
If you don't want the query planner to pull arbitrary execution behaviour out of its ass, why are you using an SQL database in the first place? The whole point of SQL is that you declare your queries and leave it up to the planner to decide, and for that to be at all workable the planner needs to be free to decide arbitrarily based on its own heuristics, which will sometimes be wrong.
Thing is, MySQL, with judicious use of STRAIGHT_JOIN, won't do the same thing. And generally MySQL is much more predictable because it's much less sophisticated: it only has a couple of join strategies (pre 8.0, only nested loop join) and quite limited query rewriting, so you can - with practice - expect a query plan as you write the SQL. And in practice, there's usually only two or three really big tables involved in performance-sensitive queries, tables which you need to pay attention that you don't end up doing scans on. The rest of the tables you can leave up to the planner.
> And in practice, there's usually only two or three really big tables involved in performance-sensitive queries, tables which you need to pay attention that you don't end up doing scans on.
The problem is that those few big tables are often critical to most of the queries and so each query has to carefully use the right hints or query order if the planner isn't doing much.
It almost makes me wonder if indexes themselves should get hints or at least priorities to help the planner order operations.
I honestly think that for live operations an SQL database is more trouble than it's worth - sooner or later you need more control than it gives you, so you're better off using a datastore that gives you lower-level access to construct and use your own indices explicitly. SQL makes sense for reporting-type use cases where you don't know exactly what queries and aggregations you'll be doing ahead of time (but have a rough idea of which columns you might need to index on), but that's all.
The problem is something changed at a random time in a production db on the weekend in the middle of the night, what changed, is that logged somewhere?
Other databases show that you can have the planner decide if you don't specify but with some simple hints you can override because I as the developer am in charge not the planner.
> I disagree: given that nothing changed, I don't think any details need to be provided.
You sound like a typical enterprise customer. "The whole system stopped working!!!!" "What did you change?" "Nothing!!!" "Are you sure?" "Yes!!!" .. searching around, looking into logs, and so on .. "Could it be that someone did x? The logs say x has happened and had to be done manually." "Oh yes. x was done by me."
I disagree: given that nothing changed, I don't think any details need to be provided.
The question is NOT "Is postgresql's choice better than mine?" The question is "A certain design was working and suddenly broke because one day the query planner decided to start choosing a different (and unusable) plan - is this ever acceptable?" and the answer is obviously No, regardless of the details.