CBA Index not being used by simple query

Analytics
1

I have a simple query below that will not use the index (below) when I added the nested property visitCharges.chargeCode. If create an index on just tenantName it works. What am I missing here?

query:
SELECT *
FROM ChargeCat.charges_postDate c
WHERE c.tenantName = client1’
limit 1

index:
use ChargeCat;
CREATE INDEX idx_charges ON charges_postDate
(tenantName: string, visitCharges.chargeCode: string)

2

Hi @drGarbinsky1,
Starting from CB Server 6.5.1, using an index when only a prefix of its keys appear in an Analytics query was disabled due to the issue MB-38292. All keys in the index must now appear in query for it to be used.

3

SELECT c.tenantName
FROM ChargeCat.charges_postDate c
WHERE c.tenantName = ‘prov_eastwa’
limit 1

this query produces the same result. a DataScan
I suspect the index is unhealthy as an identical query / index pattern in a different data set in the same 'vers works fine

how does one look for index issues?

4

@drGarbinsky1,
The second query you posted is also expected not to use the index as the field (visitCharges.chargeCode) doesn’t appear in the query.

As I mentioned, if you would like the index (idx_charges) to be used, you need to specify both fields that appear in the index in the query. For example:

SELECT *
FROM ChargeCat.charges_postDate c
WHERE c.tenantName = 'client1'
AND c.visitCharges.chargeCode = 'some_code'
LIMIT 1

The Analytics engine decides if an index can be used during query compilation/optimization time. It uses the information in the system catalog to identify the indexes available, and there is no such thing as unhealthy index during that stage of the query evaluation. If you believe that using the pattern you provided makes an index used, please share with us the DDLs for the dataset/indexes and the query used and we will investigate it.