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

CLONE [MB-30344]- Allow index span on non-leading index keys for unnest scan

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 6.0.0, 5.5.2, 5.5.3
    • 6.0.1
    • query
    • None

    Description

      Currently UNNEST scan is considered when no other index scan is feasible. UNNEST scan only uses an index if an appropriate array index key is the leading key of an index definition, and it currently only uses predicates on the array index key (leading key). This enhancement allows UNNEST scan to generate index spans on non-leading keys (non-array-index keys) if appropriate predicates exist on those keys.

      For example:
       

      CREATE INDEX ix30 ON default(ALL ARRAY (ALL ARRAY [op.name, op.val, x] FOR op IN OBJECT_PAIRS(x.properties) END) FOR x IN OBJECT_VALUES(entity.entityVersions) END, accountId, entity.entityId );
       
      SELECT RAW fr
      FROM default AS d UNNEST OBJECT_VALUES(d.entity.entityVersions) AS x
           UNNEST OBJECT_PAIRS(x.properties) AS op
      LET fr = [op.name, op.val, x][2]
      WHERE d.accountId = 2 AND d.entity.entityId = 30002
        AND [op.name,op.val,x] >= ["type2","bla"]
        AND [op.name,op.val,x] < ["type2",SUCCESSOR("bla")];

       
      Here we have accountId and entity.entityId as additional index keys after the array index key, and the query has predicates on these predicates. If UNNEST scan is used, we should be able to generate index spans on non-leading these index keys.

      This allows the following:

      • Reduce false positives during index scan
      • Allows other query pushdowns
      • Currently no way we can create partition index on individual elements of ARRAY. Only option is include other scalars and create partition index on those. As this enhancement allows those spans push to indexer, indexer can do partition elimination (without spans, it has to scatter gather). This can result in low latencies and cluster scalability.

      Attachments

        Issue Links

          For Gerrit Dashboard: MB-32366
          # Subject Branch Project Status CR V

          Activity

            People

              ajay.bhullar Ajay Bhullar
              Sitaram.Vemulapalli Sitaram Vemulapalli
              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