Hi 👋 guys We'd like better insight into the

like_predicate_consolidate_min

parameter, at its default value of

2

it's turning some

LIKE

chains in our queries into regexes but it often leads to higher CPU usage. We are trying to understand if there is a pattern we should look out for in order to, for example, dynamically set the value with an inline hint; or any additional documentation at all really. @Rocky

The

like_predicate_consolidate_min

session variable was introduced in recent StarRocks versions (appearing in 3.4+ and 4.0.0) to optimize queries containing multiple

LIKE

predicates on the same column. What it does When a query contains multiple

OR

-connected

LIKE

predicates for a single column (a "LIKE chain"), the StarRocks optimizer can consolidate them into a single

REGEXP

predicate. The variable

like_predicate_consolidate_min

(default:

2

) defines the minimum number of

LIKE

predicates required to trigger this rewrite. Example: If

like_predicate_consolidate_min = 2

, the following:

sql
WHERE col LIKE 'abc%' OR col LIKE 'def%'

is rewritten to something equivalent to:

sql
WHERE col REGEXP '^(abc|def)'

Why it leads to higher CPU usage While consolidation reduces the number of predicates to evaluate, it introduces the following trade-offs: 1. Regex Engine Overhead: Evaluating a regular expression is generally more CPU-intensive than simple prefix (

LIKE 'abc%'

) or suffix (

LIKE '%abc'

) matching, which StarRocks handles with highly optimized string functions. 2. Index Pushdown Limitations: Simple

LIKE

predicates are often eligible for pushdown to Inverted Indexes or can utilize Bloom Filters and zone maps more effectively. Once rewritten to a complex

REGEXP

, the query may lose these low-level scan optimizations, forcing the CPU to evaluate the pattern for every row. 3. Short-Circuiting: Multiple

LIKE

predicates with

OR

can short-circuit quickly if the first condition is met. A complex regex engine may not always be as efficient for very simple "OR" sets of constants. How to manage it If you find that this optimization is detrimental to your performance, you can adjust it at the session level or per query. 1. Disable via Session Variable To prevent consolidation globally for your session, set the threshold to a very high number:

sql
SET like_predicate_consolidate_min = 100;

2. Use an Inline Hint You can dynamically control this for a specific query using the

SET_VAR

hint. This is useful for queries where you know the individual

LIKE

checks are faster or where you want to ensure index pushdown:

sql
SELECT /*+ SET_VAR(like_predicate_consolidate_min = 10) */ 
* FROM my_table 
WHERE col LIKE 'pattern1%' OR col LIKE 'pattern2%';

Summary Recommendation * Look out for: Queries with 2–5

LIKE

predicates on columns with Inverted Indexes. The rewrite to

REGEXP

likely prevents the index from being used effectively. * Best Practice: If your

LIKE

patterns are simple (e.g., constant prefixes or simple wildcards), it is often better to keep them as separate

LIKE

predicates to benefit from StarRocks' vectorized string matching and indexing. Set the parameter to a higher value (e.g.,

10

) if you prefer the manual

OR

chains. References • sql-reference/sql-functions/like-predicate-functions/like.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/sql-functions/aggregate-functions/min.md • table_design/indexes/inverted_index.md • sql-reference/sql-functions/math-functions/least.md • sql-reference/sql-functions/Window_function.md • sql-reference/sql-functions/like-predicate-functions/regexp_replace.md