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

[BP MB-40648 to 6.0.x] - LIKE with query parameters 4x slow vs inline

    XMLWordPrintable

Details

    • 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

            Build couchbase-server-6.0.5-3301 contains query commit da0372a with commit message:
            MB-41231. cache compiled like when it is static

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.0.5-3301 contains query commit da0372a with commit message: MB-41231 . cache compiled like when it is static

            Ran repro on build version:6.0.5-3301-enterprise ... performance for direct query vs prep was similar on my VM.

            cbq> 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": "1a2bc2c9-a3ae-4955-a822-ba76608ee1d0",
                "signature": {
                    "$1": "number"
                },
                "results": [
                    {
                        "$1": 5348
                    }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "2.697070114s",
                    "executionTime": "2.696632417s",
                    "resultCount": 1,
                    "resultSize": 34
                }
            }
            cbq> execute p1;
            {
                "requestID": "8bd8610a-b485-46bb-a5e3-ee98fe94baf0",
                "signature": {
                    "$1": "number"
                },
                "results": [
                    {
                        "$1": 5348
                    }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "2.710518475s",
                    "executionTime": "2.710174423s",
                    "resultCount": 1,
                    "resultSize": 34
                }
            }
            

            pierre.regazzoni Pierre Regazzoni added a comment - Ran repro on build version:6.0.5-3301-enterprise ... performance for direct query vs prep was similar on my VM. cbq> 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": "1a2bc2c9-a3ae-4955-a822-ba76608ee1d0", "signature": { "$1": "number" }, "results": [ { "$1": 5348 } ], "status": "success", "metrics": { "elapsedTime": "2.697070114s", "executionTime": "2.696632417s", "resultCount": 1, "resultSize": 34 } } cbq> execute p1; { "requestID": "8bd8610a-b485-46bb-a5e3-ee98fe94baf0", "signature": { "$1": "number" }, "results": [ { "$1": 5348 } ], "status": "success", "metrics": { "elapsedTime": "2.710518475s", "executionTime": "2.710174423s", "resultCount": 1, "resultSize": 34 } }

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty