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

Text Search (Token) N1QL query doesn't use tokens field in Index

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 5.5.0
    • 5.5.0
    • query
    • Cluster: iris
      OS: CentOS 7
      CPU: Data: E5-2630 v2 (24 vCPU), Query & Index: E5-2680 v3 (48 vCPU) Memory: Data & Query: 64GB, Index: 512GB
      Disk: Samsung Pro 850

    Description

      Since 5.5.0-1705 the Token-based query doesn't utilize the Index field with tokenized values, resulting full scan and major performance degradation.

      Index def: 
      CREATE INDEX tokens_idx ON `bucket-1`(address.zip, DISTINCT ARRAY s FOR s IN TOKENS(address.street) END);
      Query: 
      SELECT first_name, last_name, address FROM `bucket-1` WHERE address.zip = $1 AND ANY s IN TOKENS(address.street) SATISFIES s = $2 END
      Query plan expected (on builds prior 5.5.0-1705):

      ...

      "spans": [
      {
      "exact": true,
      "range": [

      { "high": "\"71053\"", "inclusion": 3, "low": "\"71053\"" }

      ,

      { "high": "\"446010\"", "inclusion": 3, "low": "\"446010\"" }

      ]
      }
      ],

      ...

      Query plan since 5.5.0-1705:
      ...
      "spans": [
      {
      "exact": true,
      "range": [

      { "high": "\"71053\"", "inclusion": 3, "low": "\"71053\"" }

      ]
      }
      ],
      ...
      The second field (tokens) is missing from the plan

       

       

      Changes in 5.5.0-1705:

      goproj/src/github.com/couchbase/query changed from 22bec496c5f6b03ac6cdcd3f2274f50e43038096 to master [+] 6296b0d MB-26740 Allow index keys or variables inside index keys to use same identifier as keyspace alias

      https://github.com/couchbase/query/commit/6296b0dee66cb2f9a7ee7133061b03d6f9b199a5

       

       

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MB-26740 Select query throwing the error "Duplicate variable v already in scope", since an index contains the variable v

          • Major - Major loss of function.
          • Closed
          is triggering

          Bug - A problem which impairs or prevents the functions of the product. MB-28533 "No index available.." error when querying XATTR array index

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          For Gerrit Dashboard: MB-27706
          # Subject Branch Project Status CR V
          88387,3 MB_27706 Token index fields may not be formalized properly when query executed multiple times master query Status: MERGED +2 +1
          90612,2 MB-28533 Properly formalize keyspace reference inside Meta() functions master query Status: MERGED +2 +1

          Activity

            People

              oleksandr.gyryk Alex Gyryk (Inactive)
              oleksandr.gyryk Alex Gyryk (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty