Query plan showing intersect scan even after creating proper index. (CB- 4.6.4-4590-enterprise)

SQL++
,
1

Hi, the below query is showing intersect scan in explain plan (When providing index hint, query works fine).

The query is:

SELECT Count(*)
FROM reporting rgm 
WHERE rgm.type = "DocumentStatus" 
AND meta(rgm).id NOT LIKE "_sync%" 
AND ANY x IN [lower(rgm.fullName),lower(rgm.lastName)] SATISFIES x like "an%" END
AND rgm.verificationStatusCode in ["ICMCN","IC"] 
;

Index is:

CREATE INDEX IX_GlobalSearch_Name_DocumentStatus ON reporting((distinct (array x for x in [lower(fullName), lower(lastName)] end)),verificationStatusCode) WHERE ((type = "DocumentStatus") and (not ((meta().id) like "_sync%")))

Explain:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IntersectScan",
          "scans": [
            {
              "#operator": "DistinctScan",
              "scan": {
                "#operator": "IndexScan",
                "index": "IX_GlobalSearch_Name_DocumentStatus",
                "index_id": "6d65e13012f63d9b",
                "keyspace": "reporting",
                "namespace": "default",
                "spans": [
                  {
                    "Range": {
                      "High": [
                        "\"ao\"",
                        "successor(\"IC\")"
                      ],
                      "Inclusion": 1,
                      "Low": [
                        "\"an\"",
                        "\"IC\""
                      ]
                    }
                  },
                  {
                    "Range": {
                      "High": [
                        "\"ao\"",
                        "successor(\"ICMCN\")"
                      ],
                      "Inclusion": 1,
                      "Low": [
                        "\"an\"",
                        "\"ICMCN\""
                      ]
                    }
                  }
                ],
                "using": "gsi"
              }
            },
            {
              "#operator": "IndexScan",
              "index": "#Primary",
              "index_id": "30b96b69f69e281c",
              "keyspace": "reporting",
              "namespace": "default",
              "spans": [
                {
                  "Range": {
                    "Inclusion": 0,
                    "Low": [
                      "null"
                    ]
                  }
                }
              ],
              "using": "gsi"
            }
          ]
        },
        {
          "#operator": "Fetch",
          "as": "rgm",
          "keyspace": "reporting",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "(((((`rgm`.`type`) = \"DocumentStatus\") and (not ((meta(`rgm`).`id`) like \"_sync%\"))) and any `x` in [lower((`rgm`.`fullName`)), lower((`rgm`.`lastName`))] satisfies (`x` like \"an%\") end) and ((`rgm`.`verificationStatusCode`) in [\"ICMCN\", \"IC\"]))"
              },
              {
                "#operator": "InitialGroup",
                "aggregates": [
                  "count(*)"
                ],
                "group_keys": []
              }
            ]
          }
        },
        {
          "#operator": "IntermediateGroup",
          "aggregates": [
            "count(*)"
          ],
          "group_keys": []
        },
        {
          "#operator": "FinalGroup",
          "aggregates": [
            "count(*)"
          ],
          "group_keys": []
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "count(*)"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "SELECT Count(*)\nFROM reporting rgm \nWHERE rgm.type = \"DocumentStatus\" \nAND meta(rgm).id NOT LIKE \"_sync%\" \nAND ANY x IN [lower(rgm.fullName),lower(rgm.lastName)] SATISFIES x like \"an%\" END\nAND rgm.verificationStatusCode in [\"ICMCN\",\"IC\"]\n;"
  }
]

As per my understanding, the explain plan should show Index scan without any need of Use Index clause.
Please let me know if I am missing something here.
P.S. the index is online and built 100%.

2

Query optimizer uses rule based optimizations.

When query is completely covered it avoids InetsectScan. Otherwise it uses all qualified indexes and does InterSect Scan.
In your case there is array index and it is with IN clause (more than one span) so it is not covered.
Try the following index (switched equality predicate to leading so that it can perform better).

CREATE INDEX IX_GlobalSearch_Name_DocumentStatus ON reporting(verificationStatusCode, (distinct (array x for x in [lower(fullName), lower(lastName)] end)),[lower(fullName), lower(lastName)] ) WHERE ((type = "DocumentStatus") and (not ((meta().id) like "_sync%")))

It used intesectScan because of predicate on META().id https://issues.couchbase.com/browse/MB-28172

3

The leading key (verificaitonStatus) is not going to be used for equality predicate. There will always be ‘IN’ clause for verificationStatusCode. However, changing its position in the index statement works.

4

FYI , IN and equality is same. it is different way of representing.