The bottom line here is if your site suddenly goes down because PostgreSQL changed its query plan in the middle of the night, the answer PostgreSQL has for you is "sorry, you're stupid and now you are fucked." Until you can decipher what exactly is happening and make an appropriate fix, which can take days or weeks, your site is effectively dead.
Nobody disagrees with the noble goal of building the best query planner, or thinks that index hints are anything less than a hack for dire siutations. Where we disagree is if it's OK for the sites we're responsible for to go down at the whim of the query planner. PostgreSQL's decision to not provide hints tells me they care more about the purity of their query execution engine than the applications that rely upon it.
A couple people have mentioned the query plan changing in the middle of the night. Is the scenario a single query getting executed two different ways by the same running system? Or is it from upgrading to a new version of PostgreSQL and hitting different behavior? If it's the first scenario, would using prepared statements help somewhat with avoiding unexpected changes?
// Edit: Sorry for the naive question. I see that other people are saying that is the first one.
Nobody disagrees with the noble goal of building the best query planner, or thinks that index hints are anything less than a hack for dire siutations. Where we disagree is if it's OK for the sites we're responsible for to go down at the whim of the query planner. PostgreSQL's decision to not provide hints tells me they care more about the purity of their query execution engine than the applications that rely upon it.