Query replaces correct results with 'empty object's when using index

SQL++
,
1

I have a query that takes 8-9 seconds to run and it uses the primary index on the bucket. I would really like to improve the speed of this query. I added this index

CREATE INDEX bonsecours_arr_clientids_index ON deco_db_bonsecours(type,client_ids,(distinct (array v for v in client_ids end)))

This is the query (it’s long)

SELECT META(aE).id as id FROM deco_db_common aE USE INDEX(common_arr_clientids_index) WHERE (aE.type=‘screeningSummary’) OR (aE.type=‘roleDefinition’) OR (aE.type=‘totalAssets’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘statusCode’) OR (aE.type=‘familySizeAssetLimits’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘referral’) OR (aE.type=‘program’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘documentConditions’) OR (aE.type=‘eligibilityObject’) OR (aE.type=‘tempReferral’) OR (aE.type=‘asset’) OR (aE.type=‘icd_code’) OR (aE.type=‘task’) OR (aE.type=‘question’) OR (aE.type=‘income’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘screening’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘client’) OR (aE.type=‘incomeDisregards’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘tempPerson’) OR (aE.type=‘applicationTemplate’) OR (aE.type=‘person’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘assetDisregards’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘chain’) OR (aE.type=‘permissionSet’) OR (aE.type=‘householdMember’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘wizard’) OR (aE.type=‘baseline’) OR (aE.type=‘familySize’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END)) OR (aE.type=‘person’) OR (aE.type=‘xmlUploadHistory’) OR (aE.type=‘familySizeAssetLimits’) OR (aE.type=‘assetDisregards’) OR (aE.type=‘householdMember’) OR (aE.type=‘debug’) OR (aE.type=‘list’) OR (aE.type=‘familySizeIncomeLimits’) OR (aE.type=‘familySize’) OR (aE.type=‘totalIncome’) OR (aE.type=‘incomeDisregards’) OR (aE.type=‘totalAssets’) OR (aE.type=‘webSession’) OR (aE.type=‘workflowDefinition’) OR (aE.type=‘program’) OR (aE.type=‘userCredentials’) OR (aE.type=‘user’) OR (aE.type=‘permissionSetDocumentTypes’) OR (aE.type=‘case’) OR (aE.type=‘income’) OR (aE.type=‘roleAssignment’) OR (aE.type=‘screening’)

Basically, this query is going through the bucket and if a document is one of those type it should return the ID of that document. Also, in some cases it should only return the ID if the client_ids array attribute is length 0 OR if the specified ID is in the client_ids array.

To explain further…

–> OR (aE.type=‘familySize’ AND (ARRAY_LENGTH(aE.client_ids) = 0 OR ANY v IN aE.client_ids SATISFIES v = ‘d12ade30-4f59-11e6-96a9-d91ed1ec0260’ END))

^^ this should return the ID of all documents of type ‘familySize’ if client_ids is an empty array or if the specified ID is in that array.

–> OR (aE.type=‘totalIncome’)

^^ this returns the ID of all documents of type ‘totalIncome’ regardless of what the client_ids array contains.

Now, when that query uses that index, the result count is the same (~12,000 documents) but most of the "ID"s just say “empty object” and I have no idea why. When I don’t specify any index it will use the primary index and it’s super slow.

Anyone know what’s going on? Am I making the index incorrectly? Is the query syntax incorrect? I’m pretty new to couchbase so any help would be really useful!

Thanks!

2

Your CREATE INDEX statement does not match your USE INDEX clause.

Remove all the ORs.

Use

ANY v IN client_ids SATISFIES v IN [ … ] END;

Use COUCHBASE 4.5.1 or later.

3

Well I need the OR’s because I need to get a lot of different document types. I need the ORs and the OR/ANDs. I figured out a solution that gives me all ~12,000 IDs in ~600-700 miliseconds. I put all the ORs in one SELECT and UNION it to another SELECT that has all the OR/ANDs. Each SELECT uses it’s own INDEX. This way i’m able to use an INDEX for both types.

1 Like
4

Ok. Use EXPLAIN to verify that you are getting the most effective index usage.

5

Thanks for the help!

1 Like