Query does not hit index

SQL++
1

Hi All,

Could anyone help? I have a a query that doesn’t want to hit my tailored index. Below is the query I’m running and the index it should be hitting and also the index that gets hit instead.

Below is my query:
SELECT a.*
FROM assets a
WHERE ANY name IN SUFFIXES(LOWER(a.displayName)) SATISFIES name LIKE ‘metadata%’ END
AND a.type = ‘asset’
AND a.workspaceId IN [102,103]
ORDER BY a.createdOn DESC
LIMIT 5000

Index:
CREATE INDEX asset-search ON assets((distinct (array name for name in suffixes(lower(displayName)) end)),workspaceId,type,createdOn) WHERE (type = “asset”)

Hits this index instead:
CREATE INDEX asset-workspaceId ON assets(workspaceId) WHERE ((self.type) = “asset”)

Thanks!

2

Post the EXPLAIN. It should have used asset-search.

Also try with

SELECT a.* 
FROM  asset a  USE INDEX (`asset-search` )
WHERE ANY name IN SUFFIXES(LOWER(a.displayName)) SATISFIES name LIKE "metadata%" END 
AND a.type = "asset" 
AND a.workspaceId IN [102,103] 
ORDER BY a.createdOn DESC 
LIMIT 5000;

If u need SUFFIXES only when you have leading wildcard characters.

3

Here is the explain. I also tried using the index hint, but that also hit the same asset-workspaceId index.

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "IndexScan3",
            "as": "a",
            "index": "asset-workspaceId",
            "index_id": "87c72e73a46b141c",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "assets",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "102",
                    "inclusion": 3,
                    "low": "102"
                  }
                ]
              },
              {
                "exact": true,
                "range": [
                  {
                    "high": "103",
                    "inclusion": 3,
                    "low": "103"
                  }
                ]
              }
            ],
            "using": "gsi"
          },
          {
            "#operator": "Fetch",
            "as": "a",
            "keyspace": "assets",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "((any `name` in suffixes(lower((`a`.`displayName`))) satisfies (`name` like \"metadata%\") end and ((`a`.`type`) = \"asset\")) and ((`a`.`workspaceId`) in [102, 103]))"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "`a`",
                      "star": true
                    }
                  ]
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Order",
        "limit": "5000",
        "sort_terms": [
          {
            "desc": true,
            "expr": "(`a`.`createdOn`)"
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "5000"
      },
      {
        "#operator": "FinalProject"
      }
    ]
  },
  "text": "SELECT a.*\nFROM assets a\nWHERE ANY name IN SUFFIXES(LOWER(a.displayName)) SATISFIES name LIKE 'metadata%' END\nAND a.type = 'asset'\nAND a.workspaceId IN [102,103]\nORDER BY a.createdOn DESC\nLIMIT 5000"
}
4

Post exact Index definition of asset-search. If use index not using means it is not qualified. Go to UI and Indexes and post the definition from there. Or
select * from system:indexes where name = “asset-search”;

5

This is the definition:

CREATE INDEX 'asset-search' ON assets ((distinct (array 'name' for 'name' in suffixes(lower('displayName')) end)),'workspaceId','type','createdOn') WHERE ('type' = "asset")

6

Is those single quotes?? It must be back ticks.

CREATE INDEX `asset-search` ON assets((distinct (array name for name in suffixes(lower(displayName)) end)),workspaceId,type,createdOn)
WHERE (type = "asset");
7

I turned them into single quotes because the forum preformatted text didn’t like backticks. I guess I’m not aware of how to format code on here.

8

Highlight all text you want press the

</> Button

9

Another attempt :slight_smile:

CREATE INDEX `asset-search` ON `assets`((distinct (array `name` for `name` in suffixes(lower(`displayName`)) end)),`workspaceId`,`type`,`createdOn`) WHERE (`type` = "asset")
10

Is this index is built 100%
can u paste
select * from system:indexes where name = "asset-search"

11

Yes it is, here’s a screenshot

image
image.png1280×168 11.5 KB

[ { "indexes": { "condition": "(`type` = \"asset\")", "datastore_id": "http://127.0.0.1:8091", "id": "25f571b4d19f2fb1", "index_key": [ "(distinct (array `name` for `name` in suffixes(lower(((((((((((((((((((((((((((((((((((((((((((((((((((((`a`.`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`a`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`asset`).`assets`).`displayName`))) end))", "`workspaceId`", "`type`", "`createdOn`" ], "keyspace_id": "assets", "name": "asset-search", "namespace_id": "default", "state": "online", "using": "gsi" } } ]

12

Strange. What version of CB.

Can u drop and create index and try again

13

We’re running Couchbase Server Enterprise Edition 6.0.0 build 1693.

I’ll try recreating the index

14

So recreating the asset-search index worked, but the strange thing is that this actually worked on initial setup and then a few days later it stopped working.

15

I don’t know what happened but the last item posted is some reason corrupted. If happens again post again will open a Issue.

16

Thanks for the help! We’ll keep on eye on it!