N1QL which used primary index scan starts working after Re-Creating primary index

Couchbase Server
1

One of the N1Ql query was not returning the results. It was using primary index. Results did came when we used “USE KEY”. We dropped the primary index and recreated. After that query works fine. Please let us know if we are doing anything wrong.

Thanks
Ashish

2

USE KEYS will not use any index.
FYI
https://blog.couchbase.com/perform-various-n1ql-queries-without-indexes-couchbase-server/

3

Thanx ,

My question is actually that initially the N1Ql primary index did not work, but after re-creating the index the N1ql starts working.

Steps for the issue-
The same query was working earlier but after insertion of around 10000 records later on it stops working.
After re-creating the primary index it starts working again.

4

Could you please share below details?

  1. Version of Couchbase
  2. Specific query that was not working
  3. Any logs you may have
5

Thanx Jeelan ,

I have share the details as suggested by you.
Version: 4.5.1-2845 Community Edition (build-2845)

Some of the query-

  1. Select Count(1) from XYZ[bucket] item where item.documentType=‘OrderItem’
    and item.Id=‘a785b590-cdbe-4cc9-bac6-bf0d395f8aae’

  2. SELECT item.Id, errors.errorCode AS ErrorCode,Count(1) as ErrorCount
    FROM XYZ[bucket] item UNNEST errors where item.Id=‘7d760e00-4936-486a-a346-30448e5ff26a’
    GROUP BY item.Id,errors.errorCode order by item.Id

Logs File -attached CBLogs.zip (2.9 MB)

6

Hi @omi.ashish,

Can you please mention the bucket in which the primary index was created for which you observed the issue? Also, is this reproducible? If yes, share the steps for reproducing this issue.

Thanks,
Prathibha

7

Thank you for looking into this.

The buckets which we are using are “RZPTMSFE” and “UCServices”.

Any help will be greatly appreciated.

8

Hi @omi.ashish

The indexer log is for duration of 1 hour and it does not seem to have info about old primary index which had issues. I do not see obvious errors/issues in the indexer log. A full cbcollect is necessary to look at other log files related to bucket stats. Please share the full cbcollect for your cluster as well any steps to reproduce this issue.

9

Thanks Prathibha.

I don’t have the logs now. But can you give me some ideas why N1QL query stops working. We are not able to reproduce, it comes randomly. Is there anything to do with CPU and RAM utilization.

10

When you mention “N1Ql primary index did not work” , did you get partial results or did you get no result at all? Also, were the queries stale = ok queries?

11

Thanks Prathibha,

I again faced the same situation , attached log files
debug.zip (528.7 KB)
error.zip (13.4 KB)
indexer.zip (2.0 MB)
query.zip (2.7 MB)

12

In the attached logs, the all primary indexes of all buckets have received 0 scan requests. Please share the exact query in which you faced issue and also a full cbcollect of the logs which will more info like index definitions.

13

We don’t know the exact query. It comes randomly on our server. But why all N1ql queries start failing. Is there anything we can set at cb level like query timeout or something which can avoid this. Or is there any limitation with the indexes.

14

@omi.ashish, can you provide more details on what error do you see when “all N1ql queries start failing”?