1

I have created a secondary index in couchbase:

CREATE INDEX `data` ON `bucket`(`field1`,`field2`,`field3`, lower(`field4`)) WHERE (`field5` = "CONSTANT")

When I execute this query on couchbase:

select document.* from bucket AS document WHERE document.field5 = "CONSTANT" 
AND document.field1 =  "docId"
and (document.field3 like "%" or 
            lower(document.field4) like "%");

Even without giving the value for one of the leading predicates(field2), the query still seems to work properly. Can anyone explain why?

This is the query plan that is returned when adding "EXPLAIN" in front of the query:

    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "IndexScan3",
            "as": "document",
            "index": "data",
            "index_id": "6588bcds21b213",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "bucket",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"docId\"",
                    "inclusion": 3,
                    "low": "\"docId\""
                  }
                ]
              }
            ],
            "using": "gsi"
          },
          {
            "#operator": "Fetch",
            "as": "document",
            "keyspace": "bucket",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "((((`document`.`field5`) = \"CONSTANT\") and ((`document`.`field1`) = \"docId\")) and (((`document`.`field3`) like \"%\"]) or (lower((`document`.`field4`)) like \"%\")))"
                },
                {
                  "#operator": "Project",
                  "result_terms": [
                    {
                      "expr": "`document`",
                      "star": true
                    }
                  ]
                }
              ]
            }
          }
        ]
      }
Madhumitha
  • 11
  • 1
  • Do you have a primary index defined? It might be falling back to that. – Matthew Groves Apr 27 '20 at 21:37
  • @MatthewGroves Yes there is a primary index that is defined for the entire couchbase. But on clicking the query "plan", it seems to use the index that was created for this purpose i.e. "data" index. – Madhumitha Apr 28 '20 at 06:08

0 Answers0