Want faster queries? Use a database that uses CBO. List of OLAP databases that uses a cost based optimizer (CBO) or rule based optimizer for query execution.

How does a query engine work to give the fastest query performance

First, some background….

The main difference between cost-based optimizer (CBO) and rule-based optimizer (RBO) is that CBO uses statistics about the data to estimate the cost of different execution plans, while RBO uses a set of rules to determine the execution plan.

Cost-based optimizer (CBO) is the most common type of optimizer used in modern databases. It works by estimating the cost of each possible execution plan and choosing the plan with the lowest cost. The cost of an execution plan is estimated based on factors such as the number of rows that need to be scanned, the number of joins that need to be performed, and the size of the indexes that are used.

Rule-based optimizer (RBO) is a simpler type of optimizer that uses a set of rules to determine the execution plan. The rules are based on the structure of the query and the statistics about the data. For example, RBO might always choose to use an index if one is available.

CBO is generally considered to be more accurate than RBO.

Here is the list of OLAP databases that uses a cost based optimizer (CBO) or rule based optimizer for query execution

• StarRocks: (cbo) https://docs.starrocks.io/en-us/latest/introduction/StarRocks_intro
• ClickHouse: (rbo) https://www.alibabacloud.com/blog/clickhouse-source-code-introduction-the-story-of-sql-queries_597893#:~:text=ClickHouse%20uses%20a%20syntax%20analyzer%20featuring%20recursive%20descent%20analysis.&text=This%20is%20usually%20referred%20to,without%20changing%20the%20query%20semantics.
• Apache Druid: (cbo?) https://www.deep.bi/blog/druid-sql-queries-on-apache-druid
• SingleStore: (cbo) https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/explain/
• Trino: (cbo) https://trino.io/blog/2019/07/04/cbo-introduction.html
• TiDB: (cbo) https://docs.pingcap.com/tidb/stable/dev-guide-hybrid-oltp-and-olap-queries/

Below are some charts that show the difference between of databases with CBO and RBO (there are other factors but I’ll leave that to another post)

Graphic of JOIN performance using the TPC-DS test data between StarRocks and Trino (I would expect similar with AWS Athena and PrestoDB)

Graphic of SSB Flat Table Benching among SIMD database StarRocks, ClickHouse and Apache Druid. Note: ClickHouse and Druid partially support JOINS so we compared denormalized tables.

More info at https://github.com/alberttwong/databasecomparison