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

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

          Activity

            Sitaram.Vemulapalli Sitaram Vemulapalli created issue -
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Field Original Value New Value
            Link This issue relates to DOC-4472 [ DOC-4472 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Link This issue blocks CBSE-5441 [ CBSE-5441 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Affects Version/s 5.5.2 [ 15412 ]
            Affects Version/s 6.0.0 [ 15048 ]
            Affects Version/s Mad-Hatter [ 15037 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Fix Version/s 5.5.3 [ 15520 ]
            Fix Version/s 6.0.1 [ 15522 ]
            Fix Version/s Mad-Hatter [ 15037 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Assignee Bingjie Miao [ bingjie.miao ] Keshav Murthy [ keshav ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Summary CLONE [MB-30344}- Allow index span on non-leading index keys for unnest scan CLONE [MB-30344]- Allow index span on non-leading index keys for unnest scan
            wayne Wayne Siu made changes -
            Fix Version/s 5.5.3 [ 15520 ]
            wayne Wayne Siu made changes -
            Affects Version/s 5.5.3 [ 15520 ]
            wayne Wayne Siu made changes -
            Link This issue blocks MB-31466 [ MB-31466 ]
            Sitaram.Vemulapalli Sitaram Vemulapalli made changes -
            Assignee Keshav Murthy [ keshav ] Bingjie Miao [ bingjie.miao ]

            Build couchbase-server-6.0.1-2011 contains query commit 71e79cf with commit message:
            MB-32366 Allow index span on non-leading index keys for UNNEST scan

            build-team Couchbase Build Team added a comment - Build couchbase-server-6.0.1-2011 contains query commit 71e79cf with commit message: MB-32366 Allow index span on non-leading index keys for UNNEST scan
            bingjie.miao Bingjie Miao made changes -
            Assignee Bingjie Miao [ bingjie.miao ] Ajay Bhullar [ ajay.bhullar ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Resolved [ 5 ]
            bingjie.miao Bingjie Miao made changes -
            Actual End 2018-12-19 16:47 (issue has been resolved)

            B

            ajay.bhullar Ajay Bhullar added a comment - B
            ajay.bhullar Ajay Bhullar made changes -
            Status Resolved [ 5 ] Closed [ 6 ]

            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