--- title: "Enable cost-based optimizer" url: "https://docs.yugabyte.com/stable/best-practices-operations/ysql-yb-enable-cbo/" --- # Enable cost-based optimizer Create optimal execution plans for queries Enable the YugabyteDB [cost-based optimizer (CBO)](/stable/architecture/query-layer/planner-optimizer/ "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 [ysql\_yb\_enable\_cbo](/stable/reference/configuration/yb-tserver/#ysql-yb-enable-cbo "ysql_yb_enable_cbo") flag or its equivalent [yb\_enable\_cbo](/stable/reference/configuration/yb-tserver/#yb-enable-cbo "yb_enable_cbo") configuration parameter. The flag provides a heuristic-based optimizer mode (`yb_enable_cbo=off`) that doesn't rely on table statistics. This allows you to continue using the system without unexpected plan changes during the transition to cost-based optimization, and to selectively enable CBO for specific connections if needed. The `off` setting trades plan adaptability (provided by the CBO and using table statistics) for plan stability (heuristic-based). `yb_enable_cbo` replaces the [yb\_enable\_optimizer\_statistics](/stable/reference/configuration/yb-tserver/#yb-enable-optimizer-statistics "yb_enable_optimizer_statistics") and [yb\_enable\_base\_scans\_cost\_model](/stable/reference/configuration/yb-tserver/#yb-enable-base-scans-cost-model "yb_enable_base_scans_cost_model") parameters, which will be deprecated and removed in a future release. ## New deployments For new universes running v2025.2 or later, CBO is enabled by default when you deploy using yugabyted, YugabyteDB Anywhere, or YugabyteDB Aeon. For older YSQL deployments, or when deploying manually, to enable CBO, set the following YB-TServer flag: ```sh --ysql_yb_enable_cbo=on ``` Alternatively, you can add the `yb_enable_cbo` parameter to [ysql\_pg\_conf\_csv](/stable/reference/configuration/yb-tserver/#ysql-pg-conf-csv "ysql_pg_conf_csv") as follows: ```sh --ysql_pg_conf_csv=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`. Note that when upgrading a deployment to v2025.2 or later, if the universe has the cost-based optimizer enabled (`on`), YugabyteDB will enable the following features: - [Auto Analyze](/stable/additional-features/auto-analyze/ "Auto Analyze"): ysql\_enable\_auto\_analyze is set to true. - [YugabyteDB bitmap scan](/stable/reference/configuration/postgresql-compatibility/#yugabytedb-bitmap-scan "YugabyteDB bitmap scan"): yb\_enable\_bitmapscan is set to true. - [Parallel append](/stable/additional-features/parallel-query/ "Parallel append"): yb\_enable\_parallel\_append is set to true, yb\_parallel\_range\_rows is set to 10000. ### 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: 1. Set `yb_enable_cbo` to `off`. 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 the `ysql_yb_enable_cbo` flag. 2. Remove any existing flag setting (`yb_enable_optimizer_statistics`, `yb_enable_base_scans_cost_model`) from `ysql_pg_conf_csv` and elsewhere (ALTER DATABASE, ALTER ROLE, hints, SET command issued in the application connection, and so on). 3. Analyze the tables. Refer to [ANALYZE](/stable/api/ysql/the-sql-language/statements/cmd_analyze/ "ANALYZE"). 4. Set `yb_enable_cbo` to `on`. 5. Re-establish any existing application connections (if specifying the parameter via [ALTER DATABASE](/stable/api/ysql/the-sql-language/statements/ddl_alter_db/ "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: 1. Set `yb_enable_cbo` to `on`. 2. Re-establish any existing application connections (if specifying the parameter via [ALTER DATABASE](/stable/api/ysql/the-sql-language/statements/ddl_alter_db/ "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](/stable/additional-features/auto-analyze/ "YugabyteDB Auto Analyze service") 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](/stable/additional-features/auto-analyze/ "Auto Analyze").