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

LIKE with query parameters 4x slow vs inline

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • Cheshire-Cat, 6.0.3, 6.5.1
    • 6.6.1, 7.0.0
    • query
    • Untriaged
    • 1
    • Unknown

    Description

      create index tx1 on `travel-sample` (schedule);
      select count(1) from `travel-sample` t where ANY v IN t.schedule SATISFIES v.flight LIKE "A%" END AND t.schedule IS NOT NULL;
       
      {
          "requestID": "2edb8ee1-ab92-4901-afd4-f6441773721f",
          "signature": {
              "$1": "number"
          },
          "results": [
          {
              "$1": 5348
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "794.997532ms",
              "executionTime": "794.945549ms",
              "resultCount": 1,
              "resultSize": 26
          }
      }
       
       prepare p1 from select count(1) from `travel-sample` t where ANY v IN t.schedule SATISFIES v.flight LIKE $1 END AND t.schedule IS NOT NULL;
      \set -args ["A%"];
       
      execute p1;
       
      {
          "requestID": "e980f556-9c46-4408-ae17-d402e2a06ea4",
          "signature": {
              "$1": "number"
          },
          "results": [
          {
              "$1": 5348
          }
          ],
          "status": "success",
          "metrics": {
              "elapsedTime": "3.749420749s",
              "executionTime": "3.749330119s",
              "resultCount": 1,
              "resultSize": 26
          }
      }
      
      

      Same Plan only filter is difference 800ms vs 3.7secs

      Attachments

        Issue Links

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

          Activity

            We might be doing regular expression prefix evaluation every document when query parameter is there vs one time for constant . investigate and optimize.

            Sitaram.Vemulapalli Sitaram Vemulapalli added a comment - We might be doing regular expression prefix evaluation every document when query parameter is there vs one time for constant . investigate and optimize.

            Build couchbase-server-7.0.0-2704 contains query commit 888b97a with commit message:
            MB-40648. cache compiled like when it is static

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.0-2704 contains query commit 888b97a with commit message: MB-40648 . cache compiled like when it is static

            Build couchbase-server-6.6.1-9002 contains query commit e96315c with commit message:
            MB-40648. cache compiled like when it is static

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.6.1-9002 contains query commit e96315c with commit message: MB-40648 . cache compiled like when it is static
            ajay.bhullar Ajay Bhullar added a comment -

            verified in 6.6.1-9089

            both queries run in same amount of time

            ajay.bhullar Ajay Bhullar added a comment - verified in 6.6.1-9089 both queries run in same amount of time
            ajay.bhullar Ajay Bhullar added a comment -

            verified in 6.6.1-9089 and 7.0.0-3225, queries both take the same amount of time

            ajay.bhullar Ajay Bhullar added a comment - verified in 6.6.1-9089 and 7.0.0-3225, queries both take the same amount of time

            People

              mihir.kamdar Mihir Kamdar (Inactive)
              Sitaram.Vemulapalli Sitaram Vemulapalli
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty