Improving query with multiple OR clauses

Is It supposed to improve N1QL performance as well? I was thinking SQL++ is faster and N1QL will give same performance for both versions of couchbase.

And if these two are doing things differently, then shouldn’t it be reflected in the explain plan as well? The exact same explain plan for both confused me a bit.

If the two installations have exact same plan (and exact version of the Couchbase server), the only difference is hardware, type of the index, etc. Check for which type of index you have on both.

@keshav_m , the machine 1 has Couchbase 5.0.1-5003-enterprise and Machine 2 has couchbase 5.0.0-837-enterprise (Analytics DP4). Both have Standard Global Secondary Indexes and hardware configuration for both machines is same.

5.0.0-837-enterprise (Analytics DP4)
Is this Analytical service.

I am using N1QL query service on this one as well. Not Analytics query service

Enable profile (https://blog.couchbase.com/optimize-n1ql-performance-using-request-profiling/) and check which operator taking more time on both the machines

Machine 1:

   "phaseCounts": {
        "indexScan": 38492,
        "sort": 36853
    },
    "phaseOperators": {
        "authorize": 1,
        "indexScan": 1,
        "sort": 2
    },
    "phaseTimes": {
        "authorize": "37.051862ms",
        "indexScan": "8.407853868s",
        "instantiate": "69.229µs",
        "parse": "3.21491ms",
        "plan": "50.447944ms",
        "run": "9.795408322s",
        "sort": "233.726781ms"
    }

Machine 2:

  "phaseCounts": {
        "indexScan": 56191,
        "sort": 53728
    },
    "phaseOperators": {
        "authorize": 1,
        "indexScan": 1,
        "sort": 2
    },
   "phaseTimes": {
            "authorize": "631.49µs",
            "indexScan": "1.269954302s",
            "instantiate": "62.986µs",
            "parse": "3.239395ms",
            "plan": "1.373459ms",
            "run": "1.397644393s",
            "sort": "94.06877ms"
        }

My bad. Machine 1 (5.0.1) is using Legacy Global Secondary Index. (Didn’t even know this type of index existed!). How is this different than Standard Global Secondary Indexes?

Legacy Global Secondary Index is old forestdb, due to upgrade from old server
Standard Global Secondary Indexes is plasma

So I guess this shouldn’t be causing the issue as well.

That is the reason of performance differance, forestdb is very slow, plasma is much faster https://blog.couchbase.com/plasma-storage-engine-gsi/ cc @deepkaran.salooja .

https://blog.couchbase.com/plasma-index-performance/

This is unrelated. Did you tried this index.
Also add escape charter for _ in like   meta().id NOT LIKE "\\_sync%" 

 

CREATE INDEX IX_reporting_Person ON reporting((distinct (array n for n in [lower(reservationNumber), lower(firstName), lower(lastName)] end)),verificationStatus,lastPendingDocumentDate,startDate,isInternational,isOcrDirty,endDate,firstName,middleName,lastName,personId,securityPhotoMediaItemId,cardNumber,isReviewLater,verifiedBy) WHERE (type = "Person") and meta().id NOT LIKE "_sync%"

the escape character should be in main query or in index query?

normally it doesn’t matter. LIKE on index key then in main query, you see difference in spans.

One more thing… How can I disable profile through cbq?

Got it from the link you shared. Thanks a lot for your help. :smile:

@vsr1, Can you suggest the best way to change the indexes from forestdb to plasma in existing installation? Also do let me know what particular step I need to take while upgrading old version of couchbase so that Legacy Global Secondary index is not selected.

@Krishan, Could you please confirm if you are using Couchbase Enterprise Edition? Plasma is available only in EE and Standard GSI indexes in existing installation(s) will be automatically upgraded from forestdb to plasma when upgrading to Couchbase server EE 5.0.x.

@jeelan.poola, The version after upgrade is 5.0.1-5003-enterprise.

I have created a separate thread for this issue. Could you please respond on that?
here’s the link: