Couchbase数组索引未被查询使用问题排查

Let's break down exactly why this is happening, and how to fix it:

1. The scheme-a_credits_index Doesn't Match Your Query's Filter Criteria #

Look closely at the WHERE clause in your index definition:

CREATE INDEX `scheme-a_credits_index` ON `default`( ... ) 
WHERE ((`type` = "scheme") and (`schemeId` = "scheme-a")) 
WITH { "num_replica":1 }

Your query explicitly filters for p.type = "account", but the index is only built for documents where type = "scheme". These two values are mutually exclusive—so the query optimizer immediately discards this index as irrelevant.

2. The Index Key Structure Doesn't Align With Your Query's Needs #

Even if you fixed the type mismatch, the index's key uses a distinct array of funders for Credit events:

(distinct (array (`e`.`funder`) for `e` in `events` when ((`e`.`type`) = "Credit") end))

Your query uses an ANY predicate to check for the presence of a specific funder (e.funder = "funder-a") and then extracts the value of matching Credit events. The distinct array in the index doesn't help the optimizer efficiently evaluate this predicate or retrieve the needed value fields.

Why scheme-a_customers_index Was Chosen #

This index's WHERE clause perfectly matches your query's core filters:

WHERE ((`schemeId` = "scheme-a") and (`type` = "account"))

It also includes customerId—which your query selects directly. The optimizer knows it can quickly scan this index to get all relevant documents, then post-filter the events array in each document to meet the remaining criteria.

Fix: Create a Relevant Index for Your Query #

To get the optimizer to use an index tailored to your Credit event query, adjust the index definition to match your document type and query needs. Here are two solid options:

Option 1: Index for Funder Presence and Credit Values #

This index targets the exact ANY predicate in your query and includes the necessary fields to avoid document lookups:

CREATE INDEX `scheme-a_account_credits_index` ON `default`(
  `type`, 
  `schemeId`, 
  `customerId`,
  ARRAY e.funder FOR e IN events WHEN e.type = "Credit" END,
  ARRAY e.value FOR e IN events WHEN e.type = "Credit" END
) 
WHERE ((`type` = "account") AND (`schemeId` = "scheme-a")) 
WITH { "num_replica":1 }

Option 2: Index with Embedded Filter for Credit Events #

If you frequently query for specific funders, you can embed the Credit/funder condition directly in the index's WHERE clause to further narrow down the indexed documents:

CREATE INDEX `scheme-a_account_funder_credits_index` ON `default`(
  `type`, 
  `schemeId`, 
  `customerId`,
  ARRAY e.value FOR e IN events WHEN e.type = "Credit" AND e.funder LIKE "funder%" END
) 
WHERE ((`type` = "account") AND (`schemeId` = "scheme-a") AND ANY e IN events SATISFIES e.type = "Credit" AND e.funder LIKE "funder%" END)
WITH { "num_replica":1 }

After creating either of these indexes, your query should use the new index instead of scheme-a_customers_index.

内容的提问来源于stack exchange，提问作者Sreerag