[junippor]

> 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.

[lmm]

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.

[barrkel]

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.

[SigmundA]

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.

[sgift]

> 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."

But, obviously, nothing has changed.

[junippor]

You sound like you have to deal with idiots all the time and resent that.

You also sound like you don't know much about PostgreSQL if you can't immediately see what happened.