> Now why can't compilers do this sort of thing automatically?

Because they are not query compilers, ie: They don't know the data.

For example a query compiler could swap index to full scan because it "see" (by runtime statistics) the data not benefit for it.

In the other hand, an optimization here can pessimism there. So optimizers in general should be very conservative because butterfly effects!