[MB-70519] CBO may overestimate selectivity for a LIKE predicate when query parameter is present

Product: Couchbase Server
Component: Query-Service
Issue Link: MB-70519
Affects Versions: 7.6, 8.0
Fix Versions: 7.6.10, 8.0.1

Summary

When the Cost-Based Optimizer (CBO) is in use and optimizer statistics have been collected via UPDATE STATISTICS, a LIKE predicate that references a named or positional query parameter (for example meta().id LIKE $param) is evaluated using a default selectivity value rather than the actual parameter value. This causes the CBO to overestimate the number of matching documents and select a suboptimal index, resulting in significantly slower query execution.

The same query using a literal value in the LIKE predicate (e.g. meta().id LIKE "prefix%") correctly calculates selectivity and selects the appropriate index. This makes the issue difficult to diagnose because the query may appear to work correctly in testing where literals are used.

Symptoms

Please note that not all of the following symptoms are required to deem this issue as present.

• Queries containing a LIKE $parameter predicate run significantly slower than expected (e.g. several seconds instead of milliseconds) when CBO is enabled.
• The same query executed with a literal string value in the LIKE predicate (e.g. meta().id LIKE "prefix%") runs as expected.
• Running EXPLAIN on the affected query shows a suboptimal or unexpected index being used.
• Query performance degrades or varies unexpectedly after running UPDATE STATISTICS on the affected keyspace.

Triggers

• The Cost-Based Optimizer (CBO) is active, which requires optimizer statistics to have been collected via UPDATE STATISTICS on the affected bucket/collection.
• The query includes a LIKE predicate where the pattern is supplied as a named parameter (e.g. $param) or a positional parameter rather than a literal value.
• A more selective index exists in the keyspace that would be chosen if the correct selectivity were calculated for the LIKE predicate.

Verification

Run EXPLAIN on the affected query with the named parameters supplied (as the application would execute them) and check which index is selected:

EXPLAIN SELECT *
FROM <keyspace>
WHERE <field> IN [<value_1>, <value_2>]
  AND <other_condition>
  AND meta().id LIKE $param;

Confirm that:

1. The EXPLAIN output contains an optimizer_estimates section. This confirms CBO is active. If the section is absent, UPDATE STATISTICS has not been run on this keyspace and a different root cause should be investigated.
2. The index selected under CBO (with the parameter) differs from the index selected when the same predicate uses a literal string value.

To compare, run EXPLAIN again with the LIKE parameter replaced by a representative literal:

EXPLAIN SELECT *
FROM <keyspace>
WHERE <field> IN [<value_1>, <value_2>]
  AND <other_condition>
  AND meta().id LIKE "prefix%";

If the two plans select different indexes, this confirms the issue. The plan using the literal value should show a lower cost and cardinality in the optimizer_estimates for the index scan step, and should select a more targeted index (e.g. one covering meta().id) rather than a broader array or multi-key index.

Workarounds

• Upgrade to a version of Couchbase Server containing the fix: 7.6.10, 7.6.11, or 8.0.1 and above.
• Use a USE INDEX hint to instruct the Query Service to use the correct index directly, bypassing the CBO's index selection:

SELECT *
FROM <keyspace> USE INDEX (<correct_index_name> USING GSI)
WHERE ...
AND meta().id LIKE $param;

• Replace the named or positional parameter in the LIKE predicate with an inline literal value. Note: this requires a code change and may not be practical if the pattern value is dynamic at runtime.