The problem of a query optimizer doesn't go away by not having one.

They were invented for a reason: data changes. Data volume changes and data distribution changes. If your algorithms don't change to suit the inputs, performance will still fall off a cliff. That's a fundamental problem and not unique to SQL.

And this really only matters for somewhat "interesting" queries anyway. It's not like the database will all of a sudden change an "UPDATE foo SET x = x + 1 WHERE id = 13" into a 14-table join.

In practice, you have many queries adapting nicely to changing data, and then a couple where they get some estimates wrong and drive you crazy. But just because you are able to fix the plans in some of those instances doesn't make it a valid comparison. You have to compare it against hand crafting all of the plans (or writing equivalent algorithms) and the results of doing that. I suspect that in most cases, more plans will drive you crazy if you choose the plans rather than letting an optimizer do it.

Remember, the data is going to change once you get in production.

Of course. Sorry, I didn't mean to imply CBOs are bad or unnecessary. They are fundamental and necessary. Tuning queries is of course something that needs to be reviewed as data changes - pruning unnecessary indexes is often as importent as adding new ones!

Hopefully 95% or more of queries don't need to be nudged :-)

Lovely conceptual models always founder on the shoals of terrible implementation.

Not necessarily. It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions. But the relational model is still beautiful, I find its limitations are minor in the grand scheme of things.

"It's almost impossible for a CBO to get the best query plan for every query under every load under all data distributions."

It's also impossible for a human to do it. At least the optimizer will be awake at 3 in the morning and able to respond to data changes; the human will be asleep.

I'm not disagreeing :-)

Although the poor DBA on pager may not agree with you.

That really depends on the database. With MySQL? Yes, you have to apply hundreds of tricks. With Oracle? Chances are, that the CBO knows much better than you how to transform your SQL.

Tell that to all those people still adding rule hints to their queries :-)

Good point. It's been some time though, since I've last been using anything below Oracle 12c ;-)