What is Optimizer / Query optimizer?

A key subsystem in relational database to determine the efficient way to execute a given query in a timely fashion. It transforms an input SQL query into an executable query plan. Most mainstream databases use the cost-based optimizer (CBO) which determines the best query by calculating the cost of many factors such I/O speed, number of rows to be accessed etc. Because the cost is an estimate, and also the plan needs to be generated in a timely fashion, thus the optimizer can not permutate all possible plans and pick the optimal plan.