Exploring the New YugabyteDB Cost Based Optimizer

YugabyteDB has introduced a new Cost Based Optimizer (CBO) to achieve performance parity with PostgreSQL while leveraging the advantages of distributed systems. The CBO determines optimal execution plans for SQL queries by modeling the complexities of YugabyteDB's distributed storage architecture, including LSM index lookups, data replication, and network throughput. Unlike the heuristic-based CBO previously used, the new CBO accurately accounts for unique factors like cluster topology and advanced optimizations such as Batched Nested Loop Joins and Filter Pushdowns. Testing with the TAQO framework has demonstrated that the new CBO significantly improves query performance across various workloads, showing efficacy rates comparable to or exceeding those of PostgreSQL. These improvements are highlighted in benchmarks like TPC-H and the Join Order Benchmark, with network costs accurately modeled to ensure optimal execution plans. The introduction of the new CBO marks a substantial enhancement in query optimization for YugabyteDB, promising further developments and refinements in the future.