Summary:
The current default cost model, which uses heuristic-based estimates, occasionally produces problematic execution plans when tables are analyzed. This poses a risk for users running `ANALYZE` in preparation for transitioning to the new CBO (Cost-Based Optimizer). The new `yb_enable_cbo` GUC parameter introduces a behavior similar to the non-CBO mode but ignores the statistics, preventing such issues and also allows users to disable statistics for problematic queries only.
#### `yb_enable_cbo` Values:
* **legacy_mode** (default): Behaves the same as the current default.
* **off**: (New behavior) Similar to the current default but completely ignores statistics.
* **on**: Functions like `yb_enable_base_scans_cost_model=on`.
* **legacy_stats_mode**: Functions like `yb_enable_optimizer_statistics=on`.
#### Previous/New Optimizer Setting and Behavior Details:
##### A. Default, Unanalyzed → `yb_enable_cbo=off` (regardless of analyze)
* Uses the PG cost model along with YB-tuned heuristics-based default estimates.
##### B. New CBO Enabled (`yb_enable_base_scans_cost_model=on`) → `yb_enable_cbo=on`
* Uses YB-specific cost functions for YB-specific nodes, relying on full statistics (or PG defaults where stats are unavailable).
###### Less Common Patterns:
##### C. Default + `pg_class.reltuples` Manual Patch → `yb_enable_cbo=legacy_mode`
* A workaround to influence join optimization choices before YB relations supported `ANALYZE`.
* Introduced in commit [48f54177fe05ffaeb35ef5665de3948b40d76ac4](https://github.com/yugabyte/yugabyte-db/issues/7097).
##### D. `yb_enable_optimizer_statistics=on`, Analyzed → `yb_enable_cbo=legacy_stats_mode`
* A workaround to influence index selection and join optimization before a proper cost model for YB nodes was introduced (new CBO).
* Introduced in commit [837bc5eb4a185f844b554987ffa010b3be772c7b](https://github.com/yugabyte/yugabyte-db/issues/10840).
###### Unrecommended Patterns:
##### E. Default, Analyzed → `yb_enable_cbo=legacy_mode`
* Uses the PG cost model combined with a mixture of real stats and YB-tuned heuristics-based internal default estimates. `reltuples` is used for base table accesses, and the column stats are also used for others such as joins, group by, etc.
* Behavior is unpredictable due to inconsistent blending of heuristics, PG defaults, and real stats.
##### F. `yb_enable_optimizer_statistics=on`, Unanalyzed → `yb_enable_cbo=legacy_stats_mode`
* Uses the PG cost model with PG default estimates.
Jira: DB-16284
Test Plan: ./yb_build.sh release --java-test 'org.yb.pgsql.TestPgRegressPlanner'
Reviewers: mihnea, william.mckenna, gkukreja
Reviewed By: gkukreja
Subscribers: yql
Differential Revision: https://phorge.dev.yugabyte.com/D43008
