N1QL execution plan

SQL++
1

We have a N1QL query is taking minutes to run, even we have an index for it. The explain shows the index is used, but it should not have as the performance indicates.

When I hint for another index, the performance improves too. Is possible that the index actually not in used? How can I tell?

select * from TA USE INDEX (company_index1) where _class = "aaa.bbb.ccc.ddd.eee"
AND idoc.header.cid = "12345"
AND idoc.header.uid = “5678”

CREATE INDEX company_index1 ON TA(_class,idoc.header.cid,idoc.header.uid,assignments.context) WHERE (_class = “aaa.bbb.ccc.ddd.eee”) USING GSI

EXPLAIN select * from TA USE INDEX (company_index1) where _class = "aaa.bbb.ccc.ddd.eee"
AND idoc.header.cid = "12345"
AND idoc.header.uid = “5678”

Shows [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “company_index1”,
“keyspace”: “TA”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“successor(”")"
],
“Inclusion”: 1,
“Low”: [
""[

2

The Plan is not right. Try the following. The spans should have predicate values.

    DROP INDEX TA.company_index1;
    CREATE INDEX company_index1 ON TA(idoc.header.cid,idoc.header.uid,assignments.context) WHERE _class = "aaa.bbb.ccc.ddd.eee";
    EXPLAIN select * from TA USE INDEX (company_index1) where _class = "aaa.bbb.ccc.ddd.eee"
    AND idoc.header.cid = "12345" AND idoc.header.uid = "5678";

Check the following links which will help how to improve performance
https://dzone.com/articles/designing-index-for-query-in-couchbase-n1ql
https://dzone.com/articles/n1ql-a-practicle-guide

3

Hum. The change suggested makes sense. However, EXPLAIN shows that the primary index is used, instead of “company_index1”, after the change , therefore the performance still sucks.

[
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “#primary”,
“keyspace”: “TA”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “TA”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((((TA._class) = “aaa.bbb.ccc.ddd.eee”) and ((((TA.idoc).header).cid) = “12345”)) and ((((TA.idoc).header).uid) = “5678”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
]

4

Is company_index1 built successfully and is online. What is couchbase version you are using.
post the output of

select * from system:indexes where name = "company_index1";

5

Yes, the index was built, online, ready. We are using Couchbase Enterprise Edition 4.1.1-5914

[
{
“indexes”: {
“condition”: “(_class = “aaa.bbb.ccc.ddd.eee”)”,
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “ad3ae69f118f7c3e”,
“index_key”: [
"idoc.header.cid",
"idoc.header.uid",
"assignments.context"
],
“keyspace_id”: “TA”,
“name”: “company_index1”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “gsi”
}
}
]

I am running these queries from Query Workbench.

6

I think I have fixed the issue. The query right now takes millisecond to return the result.

Our source code put “quote” on field “idoc.header.cid”. We should be using idoc.header.cid, without quote.

7

Thank you, vsr1 for giving the hints and guiding us through this.

8

You are welcome. Quotes make it constant. Constant in index keys makes full scan of the index.