[MB-61014] Complex WHERE clauses in index definitions can cause Query Planner to miss optimal index

Product: Couchbase Server
Component: Query-Service
Issue Link: MB-61014
Affects Versions: 7.1.6, 7.2, 7.6
Fix Versions: 7.2.5, 7.6.2

Symptoms 

Queries may experience suboptimal performance due to the query planner selecting a suboptimal index. If there are multiple covering indexes for a query statement, indexes containing complex WHERE clauses may be discounted, resulting in the selection of a simpler, less optimal index.

Triggers

• A query is executed that has multiple covering indexes.
• The most optimal index contains a complex WHERE clause.

For example, consider the following indexes where idx2 has a complex WHERE clause:

CREATE INDEX idx1 ON default(c5, c6, c7, c8, c9);
CREATE INDEX idx2 ON default(c5, c8, c10) WHERE c6 != 1 AND c7 NOT IN [1,2] AND c11 != 0;

The plan for the following query may incorrectly choose index idx1, even though idx2 is a covering index and better suited for the query:

EXPLAIN SELECT 1 FROM default WHERE c5 = 10 AND c8 >= 11 AND c8 <= 20 AND c6 != 1 AND c7 NOT IN [1,2] AND c11 != 0;

Verification

• Use the EXPLAIN statement to review the query execution plan and verify whether the selected index is optimal or suboptimal for the query.

Workarounds

The workarounds for this issue are only recommended in cases where this issue has been positively identified by Couchbase Technical Support.

Multiple workarounds exist, ordered from simplest to more complex:

• Use the USE INDEX clause to explicitly specify the index to use in your query.
• Drop indexes that are being incorrectly chosen by the query planner for affected queries. Note that deleting indexes may impact other queries relying on those indexes. We strongly recommend to test this particular workaround in a lower environment prior implementing it into production environments.
• Recreate the appropriate covering index with additional index keys to make it more comprehensive, reducing the likelihood that the query planner excludes it prematurely during query planning.