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