Couchbase Build Team added a comment - 05/Apr/22 10:38 PM Build couchbase-server-7.0.4-7238 contains query commit a24e366 with commit message: MB-51453 Additional changes for IN-list handling

Pierre Regazzoni added a comment - 06/Apr/22 8:14 AM On 7.0.4-7238, looks more as expected: [root @node1 ~]# for _ in $(seq 10 ); do cbs -s 'WITH invals AS (ARRAY_RANGE(1,8192)) SELECT t.type FROM `travel-sample` t WHERE t.type NOT IN invals LIMIT 100' | grep executionTime; done         "executionTime" : "31.379187ms" ,         "executionTime" : "44.561879ms" ,         "executionTime" : "22.732918ms" ,         "executionTime" : "27.121233ms" ,         "executionTime" : "30.320179ms" ,         "executionTime" : "21.810443ms" ,         "executionTime" : "37.187003ms" ,         "executionTime" : "23.141434ms" ,         "executionTime" : "28.632828ms" ,         "executionTime" : "35.207512ms" ,

Kamini Jagtiani (Inactive) added a comment - 06/Apr/22 9:43 AM It had worked for me because it was tried with primary index only. Bingjie Miao just confirmed that  " It depends on whether covering index is being used or not ... if the IN list is in the Filter operator then it works, if it is in the Expression Scan or in IndexScan3 then it won't work if you only have primary index then there is no covering and a Filter operator will be there "   Pierre Regazzoni   you probably had other indexes?

Pierre Regazzoni added a comment - 06/Apr/22 10:11 AM Verified on 7.1.0-7238

Kamini Jagtiani (Inactive) added a comment - 06/Apr/22 10:54 AM Yes, if you look at MB-50911 , it says: Sitaram Vemulapalli added a comment -  10 /Feb/ 22 6 : 20 PM Repro: Use primary index so that it will not use IndexScan on type.   check latency before and after fix and (ignore first attempt due to cold run)   WITH invals AS (ARRAY_RANGE( 1 , 8192 )) SELECT t.type FROM `travel-sample` t WHERE t.type NOT IN invals LIMIT 100 ; So I had deleted all existing indexes leaving primary index (def_primary) before I ran the query.   Alternatively, WITH invals AS (ARRAY_RANGE(1,8192)) SELECT t.type FROM `travel-sample` t use index(def_primary) WHERE t.type NOT IN invals  LIMIT 100;   had also worked.