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

            Sitaram.Vemulapalli Sitaram Vemulapalli created issue -

            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.
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Field Original Value New Value
            Summary LIKE with query parameters very slow with inline LIKE with query parameters slow vs inline

            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
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Assignee Kamini Jagtiani [ kamini.jagtiani ] Sitaram Vemulapalli [ sitaram.vemulapalli ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Summary LIKE with query parameters slow vs inline LIKE with query parameters 4x slow vs inline
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Link This issue is triggered by CBSE-8676 [ CBSE-8676 ]
            anusha.mathur Anusha Mathur made changes -
            Link This issue blocks CBSE-8676 [ CBSE-8676 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Link This issue blocks CBSE-8676 [ CBSE-8676 ]
            wayne Wayne Siu made changes -
            Labels 6.6.1-candidate
            wayne Wayne Siu made changes -
            Link This issue blocks MB-40528 [ MB-40528 ]
            wayne Wayne Siu made changes -
            Labels 6.6.1-candidate approved-for-6.6.1
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Assignee Sitaram Vemulapalli [ sitaram.vemulapalli ] Mihir Kamdar [ mihir.kamdar ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Resolved [ 5 ]

            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
            anusha.mathur Anusha Mathur made changes -
            Link This issue blocks CBSE-8749 [ CBSE-8749 ]
            wayne Wayne Siu made changes -
            Link This issue is cloned by MB-41231 [ MB-41231 ]
            wayne Wayne Siu made changes -
            Link This issue backports to MB-41231 [ MB-41231 ]
            wayne Wayne Siu made changes -
            Link This issue is cloned by MB-41231 [ MB-41231 ]
            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
            ajay.bhullar Ajay Bhullar made changes -
            Status Resolved [ 5 ] Closed [ 6 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Link This issue causes MB-44354 [ MB-44354 ]
            lynn.straus Lynn Straus made changes -
            Fix Version/s 7.0.0 [ 17233 ]
            lynn.straus Lynn Straus made changes -
            Fix Version/s Cheshire-Cat [ 15915 ]

            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