Couchbase Stops using index

SQL++
1

Hello,
I run this query

select body.nominativo from production_tlp2020where ANY array_element IN SUFFIXES(UPPER(body.nominativo)) SATISFIES array_element like  '%PIL%' END
and _type='record'

and it correctly uses this index layer_record_nominativo
After 5 minutes, same query (whitout adding any data to db) uses another index type_index
I have changed the query to this

select body.nominativo from production_tlp2020 use index(layer_record_nominativo)
where ANY array_element IN SUFFIXES(UPPER(body.nominativo)) SATISFIES array_element like  '%PIL%' END
and _type='record'

but it still uses the wrong index.
Here the indexes

CREATE INDEX layer_record_nominativoONproduction_tlp2020((distinct (array array_elementforarray_element in suffixes(upper((body.nominativo))) end))) WHERE (_type = "record")

CREATE INDEX type_indexONproduction_tlp2020(_type)

So, after a reload of the server it uses the correct index for minutes and later switch to wrong index, what can I do? Thanks

2

USE INDEX also not using that index means not qualified.

SELECT * FROM system:indexes and see if layer_record_nominativo index has right keys and where clause.
Try drop and re-create and see if that helps.

3

This is the index result

    {
      "condition": "(`_type` = \"record\")",
      "datastore_id": "http://127.0.0.1:8091",
      "id": "e8d75bf6bd5332a3",
      "index_key": [
        "(distinct (array `array_element` for `array_element` in suffixes(upper((((((((((((((((((((((((`r`.`production_tlp2020`).`r`).`production_tlp2020`).`r`).`production_tlp2020`).`r`).`production_tlp2020`).`r`).`production_tlp2020`).`r`).`data`).`r`).`production_tlp2020`).`r`).`production_tlp2020`).`r`).`production_tlp2020`).`r`).`production_tlp2020`).`r`).`production_tlp2020`).`body`).`nominativo`))) end))"
      ],
      "keyspace_id": "production_tlp2020",
      "name": "layer_record_nominativo",
      "namespace_id": "default",
      "state": "online",
      "using": "gsi"
    }

if I recreate idex it works for a random time and then stops to work

4

In memory index key meta data is corrupted.
Thanks providing the query and index and you can track this MB-38502 .

Options you have are:

  1. kill cbq-enigne (it will automatically restart). problem might repeat again.
5

@Mauro_Sala,

If the index is not already dropped, can you please run the below command and let us know the output:

/opt/couchbase/install/bin/cbindex -auth=username:password -server 127.0.0.1:8091 -type list

Thanks,
Varun