@Rocky Do

WITH

CTE type queries act as optimization fences ? Is the planner able to see inside the CTE ?

In StarRocks,

WITH

CTE (Common Table Expression) queries do not act as optimization fences by default. The Cost-Based Optimizer (CBO) is designed to "see inside" the CTE to perform cross-boundary optimizations. Key Optimizer Behaviors * No Optimization Fence: Unlike some older database engines, StarRocks' planner can push predicates, limits, and projections into the CTE. It can also perform table pruning (removing tables from a join if their columns aren't used in the final output) even when those tables are defined inside a CTE. * Inlining vs. Materialization: * Inlining: By default, the planner often "inlines" CTEs, treating them similarly to subqueries. This allows the optimizer to merge the CTE logic into the main query plan for better optimization. * Reuse (Materialization): If a CTE is referenced multiple times, the planner can choose to "reuse" it (materialize the result) to avoid redundant scans. This is controlled by the

cbo_cte_reuse

variable. * Optimization Speed: For extremely large or complex CTEs, the planner might spend significant time optimizing the same tree repeatedly. To mitigate this, StarRocks introduced the variable

cbo_cte_force_reuse_node_count

(v3.5.3+). If a CTE's node count exceeds this threshold, the planner skips certain inlining/transforming steps to save time, which effectively treats it more like a "fence" for the sake of planning performance. Relevant Session Variables *

cbo_cte_reuse

(default

true

): Allows the optimizer to reuse the results of a CTE instead of re-executing the logic multiple times. *

cbo_cte_force_reuse_node_count

(default

2000

): Controls at what point the planner stops aggressive inlining to save on optimization overhead. * `enable_recursive_cte`: Required to be

true

to use

WITH RECURSIVE

features (v4.1+). If you want to verify how the planner is handling your specific CTE, you can run

EXPLAIN <query>

or

EXPLAIN LOGICAL <query>

to see if predicates are being pushed down or if tables within the CTE are being pruned. References • knowledge/usage/best-practice.md • using_starrocks/accelerate-cardinality-preserving-joins.md

@Rocky In this query,

conversations

is a parent object with many linked child

interactions

. We time filter on conversation start timestamp and both tables are

ORDER BY (account_key, start_time)

In practice, interactions start_time are >= conversation start_time and cannot extend more than 1 day after that start time so the JOIN has some sort of temporal correlation. What's the best way to deal with this beside adding extra time filters on the JOIN expression ? Does starrocks have some facilities to makes those time correlated join easier to express

WITH filtered_conversations AS (
    SELECT
        i.account_key,
        i.conversation_id,
        i.interaction_id,
        CASE WHEN i.user_key IN (
                1089594449226274682,8823295356010877563,7690799479114910338,4608229570202836354,146058441455511426,2961061096029806970,3949265863397662842,4494768766309593210,7963502551857470331,1511139510528124283,8793565661107407482,1931647732777526402,6011143735082455418,1163446446241111418,6593182309672124794,2725923937566452346
        ) THEN 1 ELSE 0 END as has_target_user
    FROM
        conversations c
        INNER JOIN interactions i ON
            i.account_key = c.account_key
            AND i.conversation_id = c.conversation_id
    WHERE
        c.account_key = 7507522985186226043
        AND c.start_time >= '2026-06-06 00:00:00'
        AND c.start_time <  '2026-06-09 00:00:00'
) SELECT * FROM user_filtered_conversation;

In StarRocks, there are several "facilities" and architectural features you can leverage to optimize these temporal joins. While StarRocks doesn't have a dedicated "interval join" operator (like those found in some stream processors), you can achieve equivalent performance using the following strategies: 1. Manually Add Redundant Constant Filters (Best for Pruning) The most effective way to optimize this is to provide the optimizer with a constant range for the

interactions

table. Because StarRocks performs Partition Pruning and Short Key Indexing at the scan level, adding a filter directly to the

interactions

scan is the only way to ensure it doesn't scan the entire table (or the entire account's data). Since you know

c.start_time

is between

'2026-06-06'

and

'2026-06-09'

, and interactions can't be more than 1 day later, you should add:

sql
AND i.start_time >= '2026-06-06'
AND i.start_time <  '2026-06-10' -- (Max conversation start + 1 day)

This allows the storage engine to skip irrelevant data segments before the join even begins. 2. Leverage Colocate Joins If your queries frequently join these two tables on

account_key

and

conversation_id

, you should use Colocation Groups. * How: Set the

PROPERTIES("colocate_with" = "group_name")

during table creation and ensure both tables use the same

DISTRIBUTED BY HASH(account_key, conversation_id)

. * Result: StarRocks will ensure that data for the same

conversation_id

resides on the same physical node. This transforms a expensive "Shuffle Join" into a "Local Join," which is significantly faster and uses far less memory. 3. ASOF JOIN (v4.0+) If your use case is to find the most recent interaction relative to a conversation (e.g., the last interaction before the conversation closed), StarRocks 4.0 introduced

ASOF JOIN

.

sql
SELECT ...
FROM conversations c
ASOF LEFT JOIN interactions i 
  ON c.account_key = i.account_key 
  AND c.conversation_id = i.conversation_id
  AND c.start_time >= i.start_time

This is specifically optimized for temporal "nearest match" scenarios. 4. Min/Max Runtime Filters StarRocks uses Global Runtime Filters. When you join two tables, the engine calculates the Min and Max values of the join keys from the left side and pushes them down as a predicate to the right side's scan. * If you include

i.start_time

in your join condition (e.g.,

i.start_time >= c.start_time

), StarRocks may generate a Min/Max Runtime Filter for the timestamp. * However, because this is a non-equi join condition, it is generally less efficient than the constant filters mentioned in point #1. 5. Short Key Index Optimization Since your tables are

ORDER BY (account_key, start_time)

, the

account_key

and the beginning of the

start_time

field form the Short Key Index. * Filtering on

account_key

and

start_time

(with constants) allows the engine to perform a range scan directly on the data segments. * If you omit the

start_time

filter on

interactions

, the engine must scan all interactions for that

account_key

, which could be millions of rows across years of data, even if the join eventually discards them. Recommended Pattern To make this "easier to express" while remaining performant, we recommend the following pattern:

sql
WITH filtered_conversations AS (
    SELECT ... FROM conversations 
    WHERE account_key = 7507522985186226043
    AND start_time >= '2026-06-06 00:00:00'
    AND start_time <  '2026-06-09 00:00:00'
)
SELECT * 
FROM filtered_conversations c
INNER JOIN interactions i ON i.account_key = c.account_key
    AND i.conversation_id = c.conversation_id
WHERE i.start_time >= '2026-06-06' -- Explicitly help the scanner
  AND i.start_time <  '2026-06-10'

Note: Even though the CTE is not an optimization fence, the planner often cannot "reverse engineer" the interaction time range from the conversation time range without you explicitly stating it. References

http://github.com/StarRocks/starrocks/pull/70802 will land in 4.2