Couchbase 4.5 - Index is not covered when array is used in where clause

Question

I have a Couchbase(4.5) bucket my-data. A minimal overview of the bucket is as follows.

Document structure

{
    _class: “com.dom.Activity”,
    depId: 984,
    dayIds: [17896, 17897, 17898],
    startTime: 10,
    endTime: 20
}

Index

I also have an index in the bucket as follows.

CREATE INDEX idx_dep_day ON my-data(depId, distinct array i for i in dayIds end, , meta().id) WHERE _class = “com.dom.Activity” and startTime is not null and endTime is not null;

I need to fetch some document ids and I hope to use the index given above for the purpose. Also, I want the result to be covered by the index.

The problem is that the query is not covered when I use the dayIds field in the where clause.

The following are the queries and their explanations I tried

Query-1 (with the dayIds array in where clause)

select meta(b).id from my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984 and any i in dayIds satisfies i = 17896 end and startTime is not null and meta().id > ‘Activity-a65e7e616f21e4c6d7b7bccbfd154da1’ and endTime is not null limit 80000

Explain-1

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "index": "idx_dep_day",
                "index_id": "53398c61c49ae09a",
                "keyspace": "my-data",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "984",
                        "17896"
                      ],
                      "Inclusion": 2,
                      "Low": [
                        "984",
                        "17896",
                        "\"Activity-a65e7e616f21e4c6d7b7bccbfd154da1\""
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            },
            {
              "#operator": "Fetch",
              "as": "b",
              "keyspace": "my-data",
              "namespace": "default"
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Filter",
                    "condition": "(((((((`b`.`_class`) = \"com.dom.Activity\") and ((`b`.`depId`) = 984)) and any `i` in (`b`.`dayIds`) satisfies (`i` = 17896) end) and ((`b`.`startTime`) is not null)) and (\"Activity-a65e7e616f21e4c6d7b7bccbfd154da1\" < (meta(`b`).`id`))) and ((`b`.`endTime`) is not null))"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "(meta(`b`).`id`)"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Limit",
          "expr": "80000"
        }
      ]
    },
    "text": "select meta(b).id from `my-data` b use index (`idx_dep_day`)where `_class`= 'com.dom.Activity' and depId = 984 and any i in dayIds satisfies i = 17896 end and startTime is not null and \nmeta().id > 'Activity-a65e7e616f21e4c6d7b7bccbfd154da1' and endTime is not null limit 80000"
  }
]

But when I remove the array from the where clause

Query -2(without dayIds array in where clause)

select meta(b).id from my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984 and startTime is not null and meta().id > ‘Activity-a65e7e616f21e4c6d7b7bccbfd154da1’ and endTime is not null limit 80000

Explain-2

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "covers": [
                  "cover ((`b`.`depId`))",
                  "cover ((distinct (array `i` for `i` in (`b`.`dayIds`) end)))",
                  "cover ((meta(`b`).`id`))",
                  "cover ((meta(`b`).`id`))"
                ],
                "filter_covers": {
                  "cover (((`b`.`endTime`) is not null))": true,
                  "cover (((`b`.`startTime`) is not null))": true,
                  "cover ((`b`.`_class`))": "com.dom.Activity"
                },
                "index": "idx_dep_day",
                "index_id": "53398c61c49ae09a",
                "keyspace": "core-data-20190221",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "successor(984)"
                      ],
                      "Inclusion": 1,
                      "Low": [
                        "984"
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Filter",
                    "condition": "(((((cover ((`b`.`_class`)) = \"com.dom.Activity\") and (cover ((`b`.`depId`)) = 984)) and cover (((`b`.`startTime`) is not null))) and (\"Activity-a65e7e616f21e4c6d7b7bccbfd154da1\" < cover ((meta(`b`).`id`)))) and cover (((`b`.`endTime`) is not null)))"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "cover (meta(`b`).`id`))"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject"
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Limit",
          "expr": "80000"
        }
      ]
    },
    "text": "select meta(`b`).`id` from \n`my-data` b use index (`idx_dep_day`)where \n`_class`= 'com.dom.Activity' and depId = 984 and startTime is not null and meta().id > 'Activity-a65e7e616f21e4c6d7b7bccbfd154da1' and endTime is not null limit 80000"
  }
]

Why can’t I get the index covering when I use the dayIds array in the where clause?

Answer 1

Finally, I could solve the issue. Turns out that we need to add the array as a scalar in the index for the covering to work.

CREATE INDEX idx_dep_day ON my-data(depId, distinct array i for i in dayIds end, meta().id, dayIds) WHERE _class = “com.dom.Activity” and startTime is not null and endTime is not null;

Now it works fine and the following is the result

Query

explain select meta(b).id from my-data b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984 and any i in dayIds satisfies i = 17896 end and startTime is not null and meta().id > ‘Activity-2’ and endTime is not null limit 80000

Output

[  
   {  
      "plan":{  
         "#operator":"Sequence",
         "~children":[  
            {  
               "#operator":"Sequence",
               "~children":[  
                  {  
                     "#operator":"DistinctScan",
                     "scan":{  
                        "#operator":"IndexScan",
                        "covers":[  
                           "cover ((b.depId))",
                           "cover ((distinct (array i for i in (b.dayIds) end)))",
                           "cover ((meta(b).id))",
                           "cover ((b.dayIds))",
                           "cover ((meta(b).id))"
                        ],
                        "filter_covers":{  
                           "cover (((b.endTime) is not null))":true,
                           "cover (((b.startTime) is not null))":true,
                           "cover ((b._class))":"com.dom.Activity"
                        },
                        "index":"idx_dep_day",
                        "index_id":"cb0adb18bf0f081f",
                        "keyspace":"test",
                        "namespace":"default",
                        "spans":[  
                           {  
                              "Range":{  
                                 "High":[  
                                    "984",
                                    "17896"
                                 ],
                                 "Inclusion":2,
                                 "Low":[  
                                    "984",
                                    "17896",
                                    "\"Activity-2\""
                                 ]
                              }
                           }
                        ],
                        "using":"gsi"
                     }
                  },
                  {  
                     "#operator":"Parallel",
                     "~child":{  
                        "#operator":"Sequence",
                        "~children":[  
                           {  
                              "#operator":"Filter",
                              "condition":"((((((cover ((b._class)) = \"com.dom.Activity\") and (cover ((b.depId)) = 984)) and any i in cover ((b.dayIds)) satisfies (i = 17896) end) and cover (((b.startTime) is not null))) and (\"Activity-2\" < cover ((meta(b).id)))) and cover (((b.endTime) is not null)))"
                           },
                           {  
                              "#operator":"InitialProject",
                              "result_terms":[  
                                 {  
                                    "expr":"cover ((meta(b).id))"
                                 }
                              ]
                           },
                           {  
                              "#operator":"FinalProject"
                           }
                        ]
                     }
                  }
               ]
            },
            {  
               "#operator":"Limit",
               "expr":"80000"
            }
         ]
      },
      "text":"select meta(b).id from\ntest b use index (idx_dep_day)where _class= ‘com.dom.Activity’ and depId = 984\nand any i in dayIds satisfies i = 17896 end and startTime is not null and\nmeta().id > ‘Activity-2’ and endTime is not null limit 80000"
   }
]