Octane says absolutely nothing about energy content. The octane rating only tells you how compressible the gas is before it spontaneously combusts.
Engines are designed for a specific octane rating. If you use too low of an octane rating for your engine, the gasoline may combust before the spark ignites it. This is very much not good for your engine, and you can actually ruin your engine because of it. Most cars these days have a knock sensor which can detect when this is happening and it will adjust the timing of your engine to prevent damage, though your gas mileage will go to hell in a handbasket.
Generally speaking, if you use a higher octane than what your engine specifies all you are doing is wasting money. You are paying for gasoline that won’t spontaneously combust at pressures far higher than what are produced inside your engine.
In terms of actual energy content, higher octane gas can often surprisingly be lower in energy content. Want a higher octane gas? Add alcohol to it. Alcohol has only about 60 percent of the energy of gasoline, but it has a much higher octane rating.
Engines vary. The engine in my Cadillac can tolerate gasoline that is 89 octane or higher. Put in 87 and it will run very poorly. Put in 89 and it is happy and runs well. Put in 91 or 93 and it gets slightly better mileage, but not enough to justify the extra cost. Our Toyota Camry though doesn’t behave the same way. Put in 87 and it runs like crap (that much is the same - the car is designed for 89 or better). Put in 89 and you get good mileage. Put in 91 or 93 though and the mileage actually starts to drop. It doesn’t go down as much as if you put 87 in, but it definitely goes down instead of up.
The oil companies don’t help matters either. They advertise higher octane as “supreme” as if it is somehow better gasoline. It isn’t. It may be better or it may be worse, and the octane rating alone won’t tell you which it is. Often, the higher octane gasolines burn slower, which may make them run very poorly in your particular car.
The long and short of it is that you should use exactly the octane rating specified in your car’s users manual. If it gives a single number (like 89) then use it. If it’s like my Cadillac and it says something like 89 or better, then you can experiment and see what your cost per mile is with the options that your car allows.