Nereids is the query optimizer in ApsaraDB for SelectDB. It replaces the legacy optimizer for complex analytical queries—multi-table joins and deeply nested subqueries—delivering comparable performance to a manually tuned legacy plan without requiring manual SQL rewrites.
How it works
Nereids presents RBO and CBO optimization points in the form of rules. Each rule provides a set of patterns to describe the shape of the query plan, which allows Nereids to match and rewrite complex query structures including multi-layer nested subqueries.
The CBO is developed based on the Cascades framework and uses various data statistics with a cost model across multiple dimensions, making it well-suited for multi-table join queries.
All optimization rules operate on the logical execution plan tree. Representing the query as a tree (rather than the flat structure used by the legacy optimizer) means subqueries are handled in a single unified pass, which eliminates logical errors that arose when multiple rules processed subqueries independently.
In a TPC-H SF100 benchmark using three BE nodes (each with 16 CPU cores and 64 GB of memory), Nereids—with statistics collected before query execution—performed much better than the legacy optimizer on original SQL, and achieved similar performance to the legacy optimizer on manually optimized SQL.
Prerequisites
Collect statistics on tables before running queries with Nereids. Run the ANALYZE statement on each table to collect statistics for CBO. For collection strategies and more information, see Statistics.
Nereids is enabled by default. No additional setup is required.
Enable or disable Nereids
Use session variables to control Nereids behavior:
| Variable | Description |
|---|---|
enable_nereids_planner | Enables Nereids for the current session. Enabled by default. |
enable_fallback_to_original_planner | Falls back to the legacy optimizer when an issue occurs in Nereids. |
To disable Nereids and force the legacy optimizer for a session:
SET enable_nereids_planner = false;To re-enable Nereids:
SET enable_nereids_planner = true;To enable automatic fallback to the legacy optimizer:
SET enable_fallback_to_original_planner = true;When enable_fallback_to_original_planner is enabled, Nereids automatically routes queries that encounter issues to the legacy optimizer. Disable the fallback only if you want explicit control over which optimizer runs each query.
Limitations
When enable_fallback_to_original_planner is enabled, queries that hit these limitations are automatically routed to the legacy optimizer.
Semi-structured data types
Queries that involve columns or function return values of type JSON, ARRAY, MAP, or STRUCT cannot be processed by Nereids.
Workaround: Keep enable_fallback_to_original_planner = true. The legacy optimizer handles these queries automatically.
DML statements
Nereids supports only INSERT INTO SELECT, UPDATE, and DELETE. Other DML patterns are not supported.
Workaround: Use the legacy optimizer by setting enable_fallback_to_original_planner = true.
Materialized views with filter conditions
Nereids does not rewrite queries to use materialized views that have filter conditions defined.
Workaround: Use materialized views without filter conditions, or query the base tables directly.
Function aliases
Function aliases are not recognized by Nereids.
Workaround: Replace function aliases with the full function expressions in the query.
User-defined functions
User-defined functions (UDFs) implemented in Java or loaded from Hadoop Distributed File System (HDFS) are not supported.
Workaround: Keep enable_fallback_to_original_planner = true. These queries fall back to the legacy optimizer automatically.
High-concurrency point queries
Nereids is optimized for analytical workloads. High-concurrency point queries should use the legacy optimizer.
Workaround: Set enable_nereids_planner = false for sessions that run high-concurrency point queries.
Known issues
Nereids cannot use partition cache to accelerate queries.
What's next
Statistics — Collect and manage table statistics for CBO.