CBO rule control

Rewrite rules overview

Before generating an execution plan, the optimizer performs a series of equivalent transformations on the query to reduce scan volume and intermediate result sizes. The rewrite rules cover three areas: predicate optimization (pushing filter conditions below operators to execute them earlier), projection optimization (removing redundant projection operators), and join optimization (reordering joins or rewriting join types). All these rules are enabled by default.

Rule names are internal Nereids identifiers. SET_VAR hints strictly validate rule names — an invalid name causes the SQL to fail. The SET statement does not validate; an invalid name is silently stored but has no effect.

Disable rewrite rules

Two mechanisms are available, suited to different scenarios. The following commonly used rules can be disabled:

Category	Rule name	Effect
Predicate optimization	PUSH_DOWN_FILTER_THROUGH_JOIN	Pushes filter conditions below join operators to reduce the data entering the join
Predicate optimization	PUSH_DOWN_PREDICATE_THROUGH_AGGREGATION	Pushes filter conditions below aggregation operators
Projection optimization	ELIMINATE_UNNECESSARY_PROJECT	Removes redundant projection operators from the plan
Join optimization	REORDER_JOIN	Reorders multi-table joins based on cost estimates
Join optimization	ELIMINATE_OUTER_JOIN	Converts an outer join to an inner join when the outer side's NULLs are filtered out

Session variable: applies to all queries in the connection

Use SET disable_nereids_rules to disable rules for every subsequent query in the current connection. This is useful when comparing execution plans across multiple queries:

-- Disable join reordering
SET disable_nereids_rules = 'REORDER_JOIN';

-- Disable multiple rules (comma-separated)
SET disable_nereids_rules = 'REORDER_JOIN,PUSH_DOWN_FILTER_THROUGH_JOIN';

-- Re-enable all rules
SET disable_nereids_rules = '';

SQL hint: applies to a single statement only

Use a /*+ SET_VAR() */ comment hint to disable rules for a single SQL statement without affecting other queries in the session. This is the preferred approach for production SQL:

SELECT /*+ SET_VAR(disable_nereids_rules='REORDER_JOIN') */
    o.order_id, u.username, o.amount
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';

The hint only takes effect for this statement. Other queries in the same session are unaffected.

Enable CBO rewrite rules

Some CBO rules are disabled by default because they only benefit specific query patterns. When your query matches one of these patterns, use the USE_CBO_RULE hint to enable the rule and let the optimizer pre-aggregate or deduplicate data before a join.

Rule name	Effect	When to use
PUSH_DOWN_AGG_THROUGH_JOIN	Pre-aggregates data on both sides of the join before executing the join	Both sides are large tables and aggregation significantly reduces the row count
PUSH_DOWN_AGG_THROUGH_JOIN_ONE_SIDE	Pre-aggregates data on one side of the join only	One side is a large fact table that shrinks significantly after aggregation, while the other is a small dimension table
PUSH_DOWN_DISTINCT_THROUGH_JOIN	Deduplicates data before the join, then joins the deduplicated result	The query applies DISTINCT after a join, and deduplication before the join substantially reduces the number of rows entering the join

The syntax is similar to SET_VAR — add the hint after SELECT:

-- Enable aggregation pushdown so the optimizer pre-aggregates before the join
SELECT /*+ USE_CBO_RULE(PUSH_DOWN_AGG_THROUGH_JOIN) */
    o.user_id, SUM(oi.quantity * oi.price) AS total_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.user_id;

-- Enable multiple rules at once
SELECT /*+ USE_CBO_RULE(PUSH_DOWN_AGG_THROUGH_JOIN, PUSH_DOWN_AGG_THROUGH_JOIN_ONE_SIDE) */
    ...;

USE_CBO_RULE applies to the current statement only. It is the opposite of disable_nereids_rules: one enables rules that are off by default, while the other turns off rules that are on by default.

Typical scenarios

Join reordering degrades performance

When statistics are inaccurate, the optimizer may choose a join order worse than the one you wrote. Disable REORDER_JOIN to force the optimizer to follow the FROM/JOIN order in your SQL:

-- Disable join reordering; joins execute in the written order:
-- sales_detail JOIN product_category, then JOIN warehouse
SELECT /*+ SET_VAR(disable_nereids_rules='REORDER_JOIN') */
    a.*, b.category_name, c.warehouse_name
FROM sales_detail a
JOIN product_category b ON a.category_id = b.id
JOIN warehouse c ON a.warehouse_id = c.id
WHERE a.sale_date = '2024-06-01';

Predicate pushdown increases scan range

In aggregate queries, pushing a HAVING or outer WHERE condition below the aggregation node can sometimes increase the scan range (for example, by breaking prefix index matching). Disable PUSH_DOWN_PREDICATE_THROUGH_AGGREGATION in this case:

-- Prevent total_amount > 100000 from being pushed below the aggregation
SELECT /*+ SET_VAR(disable_nereids_rules='PUSH_DOWN_PREDICATE_THROUGH_AGGREGATION') */
    region, total_amount
FROM (
    SELECT region, SUM(amount) AS total_amount
    FROM orders
    GROUP BY region
) t
WHERE total_amount > 100000;

Outer join elimination changes query semantics

When the optimizer detects that the NULL side of an outer join is filtered out downstream, it rewrites the LEFT/RIGHT JOIN as an INNER JOIN. If the outer layer actually relies on NULL values (such as an IS NULL check to find users without orders), this rewrite produces incorrect results. Disable ELIMINATE_OUTER_JOIN to preserve the original semantics:

-- Keep the LEFT JOIN semantics; do not convert to INNER JOIN
SELECT /*+ SET_VAR(disable_nereids_rules='ELIMINATE_OUTER_JOIN') */
    u.username, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

Large-table join with aggregation: enable pre-aggregation pushdown

A common analytics pattern joins multiple large tables and then aggregates the results (GROUP BY + SUM/COUNT). By default, the optimizer completes the full join first, which can produce a very large intermediate result set. Enabling PUSH_DOWN_AGG_THROUGH_JOIN instructs the optimizer to pre-aggregate each side before joining, significantly reducing the data volume entering the join:

-- order_items (tens of millions) JOIN orders (millions), aggregate by user
-- With aggregation pushdown: each table pre-aggregates by user_id before the join
SELECT /*+ USE_CBO_RULE(PUSH_DOWN_AGG_THROUGH_JOIN) */
    o.user_id, SUM(oi.quantity * oi.price) AS total_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-06-30'
GROUP BY o.user_id;

Use EXPLAIN to verify that the aggregation operator appears below the join operator in the execution plan, confirming that the rule is in effect.

Identifying the problematic rule

If you suspect a rewrite rule is slowing down a query, follow this process to isolate the cause:

-- 1. View the default execution plan
EXPLAIN SELECT o.order_id, u.username
FROM orders o JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2024-01-01';

-- 2. Disable the suspect rule and compare the plan
SET disable_nereids_rules = 'REORDER_JOIN';
EXPLAIN SELECT o.order_id, u.username
FROM orders o JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2024-01-01';

-- 3. Compare actual execution times
SET disable_nereids_rules = '';
SELECT ...; -- Record the execution time

SET disable_nereids_rules = 'REORDER_JOIN';
SELECT ...; -- Record the execution time

Once you confirm which rule is responsible, embed a SET_VAR hint in that specific SQL rather than disabling the rule at the session level.

Best practices

Run ANALYZE TABLE first to refresh statistics. Most rewrite issues stem from stale statistics — once refreshed, the rules typically make the right call.
Disabling a rule is a workaround, not a fix. Database upgrades may resolve the underlying issue. Periodically re-test whether the disabled rule is still needed.
In production SQL, prefer SET_VAR hints over session variables to limit the scope to a single statement.
Rule names are internal identifiers that may change between major releases. Re-verify rule names in existing hints after database upgrades.