Update Optimizer documentation

[alamb]

Is your feature request related to a problem or challenge?

@devesh-2002 moved the optimizer documentation in #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:

datafusion/datafusion/core/src/lib.rs

Lines 647 to 651 in 5da7ab3

	#[cfg(doctest)]
	doc_comment::doctest!(
	"../../../docs/source/library-user-guide/using-the-sql-api.md",
	library_user_guide_sql_api
	);

Test like

cargo test --doc

Describe alternatives you've considered

No response

Additional context

No response

[edmondop]

take