Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-51453

[BP to 7.0.4] - IN/NOT IN filters not using Hash for evaluation

    XMLWordPrintable

Details

    • Untriaged
    • 1
    • Unknown

    Description

      WITH invals AS (ARRAY_RANGE (1,20)) SELECT d FROM [1] AS d  WHERE 30  IN invals;
      

      Attachments

        Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            Build couchbase-server-7.0.4-7238 contains query commit a24e366 with commit message:
            MB-51453 Additional changes for IN-list handling

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.4-7238 contains query commit a24e366 with commit message: MB-51453 Additional changes for IN-list handling

            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", 

            pierre.regazzoni Pierre Regazzoni added a comment - 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" ,

            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?

            kamini.jagtiani Kamini Jagtiani (Inactive) added a comment - 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?

            Verified on 7.1.0-7238

            pierre.regazzoni Pierre Regazzoni added a comment - Verified on 7.1.0-7238

            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.

            kamini.jagtiani Kamini Jagtiani (Inactive) added a comment - 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.

            People

              pierre.regazzoni Pierre Regazzoni
              Sitaram.Vemulapalli Sitaram Vemulapalli
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty