[MB-66050] CBO may choose less optimal index with high memory residence ratio

Product: Couchbase Server
Component: Query-Service
Issue Link: MB-66050
Affects Version: 7.6.x
Fix Versions: 7.6.8, 8.0.0

Summary

A low resident ratio for indexes may result in suboptimal index selection when the Cost-Based-Optimizer is enabled. If there are two eligible indexes for a query, CBO may select the less performant index if it has a significantly higher resident ratio.

Symptoms 

• Unexpected (suboptimal) index selected for a query.
• Lower than expected query performance for a specific statement.
• Higher than expected CPU/memory utilization for the Query Service.

Triggers

• Multiple indexes are eligible for a query statement.
• Indexes have low/significantly different resident ratios (eg, <5% vs >90%).

Verification

• Identify the (unexpected/observed) index used for query execution.
  • The index used for indexScan can be seen by reviewing the query’s plan.
• Identify the expected index for query execution.
  • E.g., by comparing previous executions from before the issue was present, or executions on an identical parallel cluster if present.
• Review the resident ratio for the expected and observed indexes.
• Look for evidence of a significant difference in resident ratios where the expected index (not used) has a significantly lower resident ratio compared to the observed index used (eg, <5% vs >90%).

Workarounds

• Facilitate an increase in the resident ratio for affected indexes. As the RR cannot be changed directly, consider increasing the Index Service quota, removing unused indexes, or removing indexed data.
• Modify the query statement to include a USE INDEX clause with the expected index.
• If multiple equivalent indexes are present, remove indexes that are not needed (e.g., remove Primary Indexes).
• Disable the Cost-Based Optimizer at the request level, or cluster level if the issue is seen for multiple query statements.