database - Couchbase 5.1 N1QL OR 运算符不使用索引

Question

我有一个奇怪的情况，我的 couchbase 服务器中有一个索引是用命令构建的:

CREATE INDEX `idx_metadata` ON `dev`(`email`,`nickname`,`channelUuid`) WHERE (`type` = "user_metadata") WITH { "defer_build":true }`

如果我运行查询:

SELECT channelUuid FROM `dev` WHERE type="user_metadata" AND email="foo" AND nickname="bar"

然后它使用索引并按预期工作。

如果我只是像这样将 AND 更改为 OR:

SELECT channelUuid FROM `dev` WHERE type="user_metadata" AND (email="foo" OR nickname="bar")

然后它使用主索引，而不是专用索引。

这是怎么回事？

更新:

这里要求解释 AND 查询

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan2",
        "index": "idx_metadata",
        "index_id": "d6e2fb94ae221335",
        "index_projection": {
          "primary_key": true
        },
        "keyspace": "dev",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"test1@my-email.com\"",
                "inclusion": 3,
                "low": "\"test1@my-email.com\""
              },
              {
                "high": "\"Badger\"",
                "inclusion": 3,
                "low": "\"Badger\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Fetch",
        "keyspace": "dev",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "(((`dev`.`type`) = \"user_metadata\") and (((`dev`.`email`) = \"test1@my-email.com\") and ((`dev`.`nickname`) = \"Badger\")))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "self",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT * FROM `dev` WHERE type=\"user_metadata\" AND (email=\"test1@my-email.com\" AND nickname=\"Badger\");"
}

对于 OR 查询:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "PrimaryScan",
        "index": "#primary",
        "keyspace": "dev",
        "namespace": "default",
        "using": "gsi"
      },
      {
        "#operator": "Fetch",
        "keyspace": "dev",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "(((`dev`.`type`) = \"user_metadata\") and (((`dev`.`email`) = \"test1@my-email.com\") or ((`dev`.`nickname`) = \"Badger\")))"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "self",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT * FROM `dev` WHERE type=\"user_metadata\" AND (email=\"test1@my-email.com\" OR nickname=\"Badger\");"
}

Answer 1

第二个 OR 没有前导索引键有谓词。

You can create another index and it uses both indexes
CREATE INDEX `idx_metadata2` ON `dev`(`nickname`, `email`,`channelUuid`) WHERE (`type` = "user_metadata") WITH { "defer_build":true }`

或

SELECT channelUuid 
FROM `dev` 
WHERE type="user_metadata" AND email IS NOT NULL AND (email="foo" OR nickname="bar")

解释:

"k1" {"a":5}
"k2" {"b":10}
"k3" {"a":20, "b":10}
CREATE INDEX ix1 ON default(a,b);
When leading index key value is MISSING that document will not be indexed. i.e. ix1 will have only entries for "k1", "k3"

SELECT * FROM default WHERE a = 5 OR b = 10;

Above query can't use the index ix1 because due to OR clause. AND should be able to use ix1.
predicate a = 5 can use index ix1 and produce right results.
predicate b = 10  will result "k2", "k3" But index doesn't have "k2", can't use the index ix1

Bottom line to use index each OR clause predicate must have leading key as predicate.

If you create the following index.

CREATE INDEX ix2 ON default(b,a);

 a = 5 uses ix1 and b = 10 uses ix2 and does UNION.