Reddit reviews Readings in Database Systems (The MIT Press)

This is spread throughout the world of computer science, but you can basically think of an optimizer as a dedicated search engine. Different types of access and activity are given default scores. Metadata is maintained by the database engine and that metadata is fed into a variety of algorithms in order to figure out what will be the shortest path to query results.

This is covered in a few papers listed in the CS286 syllabus and Readings in Database Systems.

The future execution time is just a guess and isn't represented as a time but as an arbitrary cost based on the bogus scores that are assigned to each operation.

Since relational databases are a mature technology, there is some well-established theory that is very useful to learn (in conjunction with the more practical, vendor-specific knowledge).

I'd suggest:

Database Management Systems - an undergrad-level textbook with a good balance of theory and practice, foundations and advanced material.

Readings in Database Systems - a compilation of influential academic papers from the database field.

Data Modeling Made Simple - a great little book that bridges the gap between databases and the real-world entities they store.