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

[N1QL] unnest query with a join is now using primary index instead of covering index

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 7.1.0
    • 7.1.0
    • query
    • None
    • 7.1.0-1350
    • Untriaged
    • 1
    • Yes

    Description

      First off a build this query had the correct plan in was 7.0.0-1325

      1 bucket, 1 node kv index n1ql
      1 secondary index 1 primary index

      CREATE INDEX `idxVM2` ON `default`((all (array (`x`.`RAM`) for `x` within `VMs` end)),`VMs`)

      SELECT x
      FROM default emp1 USE INDEX(idxVM2)
      UNNEST emp1.VMs AS x
      JOIN default task ON KEYS META(`emp1`).id
      WHERE x.RAM > 1
      AND x.RAM < 5

      This query in 1350 returns this plan
      {'#operator': 'Sequence', '~children': [{'#operator': 'PrimaryScan3', 'as': 'emp1', 'index': '#primary', 'index_projection':

      {'primary_key': True}

      , 'keyspace': 'default', 'namespace': 'default', 'using': 'gsi'},

      {'#operator': 'Fetch', 'as': 'emp1', 'keyspace': 'default', 'namespace': 'default'}

      , {'#operator': 'Parallel', '~child': {'#operator': 'Sequence', '~children': [

      {'#operator': 'Filter', 'condition': 'is_array((`emp1`.`VMs`))'}

      ,

      {'#operator': 'Unnest', 'as': 'x', 'expr': '(`emp1`.`VMs`)', 'filter': '((1 < (`x`.`RAM`)) and ((`x`.`RAM`) < 5) and (`x` is not missing))'}

      ]}},

      {'#operator': 'Join', 'as': 'task', 'keyspace': 'default', 'namespace': 'default', 'on_keys': '(meta(`emp1`).`id`)'}

      , {'#operator': 'Parallel', '~child': {'#operator': 'Sequence', '~children': [{'#operator': 'InitialProject', 'result_terms': [

      {'expr': '`x`'}

      ]}]}}]}

      in 1325 however it was using the correct index and was covered properly
      {'#operator': 'Sequence', '~children': [{'#operator': 'DistinctScan', 'scan': {'#operator': 'IndexScan3', 'as': 'emp1', 'covers': ['cover ((all (array (`x`.`RAM`) for `x` within (`emp1`.`VMs`) end)))', 'cover ((`emp1`.`VMs`))', 'cover ((meta(`emp1`).`id`))'], 'filter': 'is_array(cover ((`emp1`.`VMs`)))', 'index': 'idxVM2', 'index_id': '74018268a4c7493b', 'index_projection':

      {'entry_keys': [1], 'primary_key': True}

      , 'keyspace': 'default', 'namespace': 'default', 'spans': [{'exact': True, 'range': [

      {'high': '5', 'inclusion': 0, 'index_key': '(all (array (`x`.`RAM`) for `x` within `VMs` end))', 'low': '1'}

      , {'high': '{}', 'inclusion': 1, 'index_key': '`VMs`', 'low': '[]'}]}], 'using': 'gsi'}}, {'#operator': 'Parallel', '~child': {'#operator': 'Sequence', '~children': [

      {'#operator': 'Unnest', 'as': 'x', 'expr': 'cover ((`emp1`.`VMs`))', 'filter': '((1 < (`x`.`RAM`)) and ((`x`.`RAM`) < 5) and (`x` is not missing))'}

      ]}},

      {'#operator': 'Join', 'as': 'task', 'keyspace': 'default', 'namespace': 'default', 'on_keys': 'cover ((meta(`emp1`).`id`))'}

      , {'#operator': 'Parallel', '~child': {'#operator': 'Sequence', '~children': [{'#operator': 'InitialProject', 'result_terms': [

      {'expr': '`x`'}

      ]}]}}]}

      Attachments

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

        Activity

          People

            ajay.bhullar Ajay Bhullar
            ajay.bhullar Ajay Bhullar
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty