Adaptive Indexes (nested values) not working?

SQL++
1

Having problems with Adaptive index not being detected.

Running Couchbase 6.5 locally, but also have similar behaviors in 6.0.3 elsewhere… In all cases, the index provided is the only index on the bucket.

I have the following index:

create index adaptive_idx  ON `data1`(eventId, DISTINCT PAIRS({er.ri, er.pi, er.lti}))
WHERE split(meta().id, "::")[0] = "howie"

and the following document named howie::one

{
  "eventId": "1",
  "er": {
    "ri": 1,
    "pi": 1,
    "lti": 1
  }
}

And the following query:

SELECT meta().id from `data1` WHERE eventId = "1" and er.ri=1 AND  split(meta().id, "::")[0] = "howie"

which returns the correct document, however the query plan does a FETCH on the document, which seems totally incorrect:

{
    "#operator": "Sequence",
    "~children": [
        {
            "#operator": "DistinctScan",
            "scan": {
                "#operator": "IndexScan3",
                "index": "adaptive_idx",
                "index_id": "45c1e7beeec66fa4",
                "index_projection": {
                    "primary_key": true
                },
                "keyspace": "data1",
                "namespace": "default",
                "spans": [
                    {
                        "exact": true,
                        "range": [
                            {
                                "high": "\"1\"",
                                "inclusion": 3,
                                "low": "\"1\""
                            }
                        ]
                    }
                ],
                "using": "gsi"
            }
        },
        {
            "#operator": "Fetch",
            "keyspace": "data1",
            "namespace": "default"
        },
        {
            "#operator": "Parallel",
            "~child": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Filter",
                        "condition": "((((`data1`.`eventId`) = \"1\") and (((`data1`.`er`).`ri`) = 1)) and ((split((meta(`data1`).`id`), \"::\")[0]) = \"howie\"))"
                    },
                    {
                        "#operator": "InitialProject",
                        "result_terms": [
                            {
                                "expr": "(meta(`data1`).`id`)"
                            }
                        ]
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            }
        }
    ]
}

And, the query advisor suggests:

CREATE INDEX adv_er_ri_split_meta_data1_id_0_eventId ON `data1`(`er`.`ri`,split((meta(`data1`).`id`), '::')[0],`eventId`)

If I further simplify the index to only handle what I’m trying to adaptively hande:

create index adaptive_idx  ON `data1`(DISTINCT PAIRS({er.ri, er.pi, er.lti}))
WHERE split(meta().id, "::")[0] = "howie"

and also adjust my query:

SELECT meta().id from `data1` WHERE er.ri=1 AND  split(meta().id, "::")[0] = "howie"

Couchbase can’t figure out what to do:

[
  {
    "code": 4000,
    "msg": "No index available on keyspace data1 that matches your query. Use CREATE INDEX or CREATE PRIMARY INDEX to create an index, or check that your expected index is online.",
    "query": "SELECT meta().id from `data1` WHERE er.ri=1 AND  split(meta().id, \"::\")[0] = \"howie\""
  }
]
2

I noticed that the generated index (upon submission) becomes:

 Definition CREATE INDEX `adaptive_idx` ON `data1`((distinct (pairs({"lti": (`er`.`lti`), "pi": (`er`.`pi`), "ri": (`er`.`ri`)})))) WHERE ((split((meta().`id`), "::")[0]) = "howie")

compared to the airport example:

CREATE INDEX `ai_airport_day_faa`
ON `travel-sample`(DISTINCT PAIRS({airportname, city, faa, type}))
WHERE type = "airport";

which becomes:

CREATE INDEX `ai_airport_day_faa` ON `data1`((distinct (pairs({"airportname": `airportname`, "city": `city`, "faa": `faa`, "type": `type`})))) WHERE (`type` = "airport")
3

There are restrictions for adaptive index it will not work for all the cases. https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/adaptive-indexing.html

Avoid Adaptive index if you can. If GSI index doesn’t work use FTS index.

1 Like
4

Thanks. Multiple covered indexes will work for us; just more to maintain.