Update Optimizer documentation

### Is your feature request related to a problem or challenge?

@devesh-2002 moved the optimizer documentation in https://github.com/apache/datafusion/pull/11563 to the doc site (thank you!)

However, the content is somewhat out of date

### Describe the solution you'd like

It would be great if someone could:
1. Update the documentation with any changes
2. Add it so it runs as part of the doc tests

Doc tests are run like this:

https://github.com/apache/datafusion/blob/5da7ab300215c44ca5dc16771091890de22af99b/datafusion/core/src/lib.rs#L647-L651

Test like

`cargo test --doc`

### Describe alternatives you've considered

_No response_

### Additional context

_No response_

Review the optimizer documentation moved in pull request #11563, then compare it with current optimizer behavior to identify outdated content. Read the doc-test setup in datafusion/core/src/lib.rs around lines 647-651, update the documentation, and run `cargo test --doc` to verify it is included.

Written by the indexing model from the issue text.