Enable cost-based optimizer YSQL
Enable the YugabyteDB cost-based optimizer (CBO) to create optimal execution plans for queries. CBO provides significant performance improvements both in single-primary and distributed PostgreSQL workloads, and reduces or eliminates the need to use hints or modify queries to optimize query execution.
Use of CBO is recommended for all YSQL deployments.
You configure CBO using the yb_enable_cbo configuration parameter. The yb_enable_cbo
parameter also provides a heuristic-based optimizer mode that operates independently of table analysis. This allows you to continue using the system without unexpected plan changes during the transition to cost-based optimization, and to selectively enable this mode for specific connections if needed.
yb_enable_cbo
replaces the yb_enable_optimizer_statistics and yb_enable_base_scans_cost_model parameters, which will be deprecated and removed in a future release.
New deployments
For new YSQL deployments, or when migrating from another system, to enable CBO, add the parameter to ysql_pg_conf_csv as follows:
--ysql_pg_conf_csv=yb_enable_cbo=on
Alternatively, set the following YB-TServer flag:
--ysql_yb_enable_cbo=on
Existing deployments
When upgrading a deployment to a version of YugabyteDB that supports the yb_enable_cbo
parameter (v2025.1 and later), the parameter is set according to the current yb_enable_optimizer_statistics
and yb_enable_base_scans_cost_model
settings, as shown in the following table.
Current Settings | yb_enable_cbo | Use of Statistics |
---|---|---|
yb_enable_statistics: N/A yb_enable_base_scans_cost_model: on |
on | Yes |
yb_enable_statistics: off yb_enable_base_scans_cost_model: off |
legacy_mode | Partial |
yb_enable_statistics: on yb_enable_base_scans_cost_model: off |
legacy_stats_mode | Yes |
N/A | off | No (new behavior) |
legacy_mode
uses only reltuples for index scan nodes, and both reltuples and the column statistics for other operations such as joins, GROUP BY, and more. (legacy_mode
is currently the default for new deployments.)
You should migrate existing deployments from using legacy_mode
or legacy_stats_mode
to either on
(recommended) or, if you do not want to use CBO, off
.
Turn on CBO safely
To ensure that running applications don't use unsuitable plans, use the following instructions to migrate an existing deployment to use CBO.
Tables are not analyzed
If your tables are not analyzed, do the following:
-
Set
yb_enable_cbo
tooff
.This setting ensures that the heuristic-based optimizer mode does not use the statistics inconsistently while tables are being analyzed. It's important to ensure that each client connection reflects the updated setting after the parameter change.
For example, restart the servers with the parameter specified via the
ysql_pg_conf_csv
TServer flag, or via theysql_yb_enable_cbo
flag. -
Remove any existing flag setting (
yb_enable_optimizer_statistics
,yb_enable_base_scans_cost_model
) fromysql_pg_conf_csv
and elsewhere (ALTER DATABASE, ALTER ROLE, hints, SET command issued in the application connection, and so on). -
Analyze the tables. Refer to ANALYZE.
-
Set
yb_enable_cbo
toon
. -
Re-establish any existing application connections (if specifying the parameter via ALTER DATABASE command, for example) or perform a rolling restart of the servers if you are setting the TServer flag.
Tables are already analyzed
If your tables are analyzed, do the following:
-
Set
yb_enable_cbo
toon
. -
Re-establish any existing application connections (if specifying the parameter via ALTER DATABASE command, for example) or perform a rolling restart of the servers if you are setting the TServer flag.
ANALYZE and Auto Analyze service
Use the YugabyteDB Auto Analyze service EA to automate the execution of ANALYZE commands for any table where rows have changed more than a configurable threshold. This ensures table statistics are always up-to-date.
Even with the Auto Analyze service, for the CBO to create optimal execution plans, you should still run ANALYZE manually on user tables after data load, as well as in other circumstances.
For more information, refer to Auto Analyze.