OPTIMIZER_COSTS

MySQL

Example

SET optimizer_trace="enabled=on";
SELECT * FROM my_table;
SHOW WARNINGS;
SET optimizer_trace="enabled=off";

Output

+-----------+------+----------------------------------------------------+
| Level     | Code | Message                                            |
+-----------+------+----------------------------------------------------+
| Warning   | 1003 | /*+ OPTIMIZER_COSTS <cost_model>=1.23 /> SELECT... |
+-----------+------+----------------------------------------------------+

Explanation

In the given SQL code, the optimizer_trace variable is initially set to "enabled=on". After this point, any query run on the server will generate the optimizer cost detail. Once we run the SELECT * FROM my_table; statement, we can get the optimizer trace details with SHOW WARNINGS; as the trace details are treated as warnings. Turning off the feature by SET optimizer_trace="enabled=off" ensures that no further statements will generate these details. The output returns a warning message including the optimizer costs along with execution plan generated by the optimizer for the select query.

PostgreSQL

Example

SET LOCAL optimizer_cost = 'enable';

EXPLAIN (ANALYZE, COSTS)
SELECT * FROM Books WHERE author = 'John Doe';

Output

Seq Scan on books  (cost=0.00..35.50 rows=8 width=70) (actual time=0.012..0.012 rows=0 loops=1)
  Filter: ((author)::text = 'John Doe'::text)
  Rows Removed by Filter: 30

Explanation

The “optimizer_costs” configuration option can influence the query planner’s decisions in PostgreSQL. Setting optimizer_cost to ‘enable’ makes the query planner use cost-based decisions.

The EXPLAIN command is then used to output the plan that the PostgreSQL planner generates for the given query. This output includes the cost of each operation the planner considers.

In the described output, the “Seq Scan on books” represents a full table scan on the Books table. The costs (minimum and estimated maximum) are given, alongside the actual time taken, the amount of rows processed, and some loop info. The filter indicates the condition used to filter the data. The row removal description provides additional information about the rows eliminated by the filter.

Oracle

Example

ALTER SESSION SET OPTIMIZER_COSTS = IO_CPU_COST;
SELECT * FROM employees WHERE employee_id = 100;

Output

| employee_id | first_name | last_name  | email       | phone_number | hire_date  | job_id | salary | manager_id | department_id |
|-------------|------------|------------|-------------|--------------|------------|--------|--------|------------|---------------|
| 100         | Steven     | King       | SKING       | 515.123.4567 | 2003-06-17 | AD_PRES| 24000  | NULL       | 90            |

Explanation

The specified SQL code first adjusts the optimizer to use the IO/CPU cost model via OPTIMIZER_COSTS = IO_CPU_COST for the current session. Then, it retrieves all columns for the employee with employee_id of 100 from the employees table.