Picking correct index when running N1QL queries

SQL++
1

Env: Couchbase 4.1.0-5005.

Data Model: graph is a bucket which contains documents of type “arc”. Here is a sample document

{
  "createdAt": "2016-03-04T00:19:28Z",
  "head": {
    "id": "999",
    "name": "ABC",
    "type": "channel"
  },
  "relation": {
    "follow": true
  },
  "tail": {
    "id": "1000",
    "name": "Guest 1000",
    "type": "user"
  },
  "type": "arc"
}

Indexes: There are two indexes defined on this bucket related to the query that isn’t performing well.

CREATE PRIMARY INDEX `#primary` ON `graph` USING GSI

CREATE INDEX `idx_audience` ON `graph`(`head.id`) WHERE ((`type` = "arc") and (`relation.follow` = true)) USING GSI

Issue: The query

SELECT s.tail.id FROM graph s WHERE s.type='arc' AND s.relation.follow=true AND s.head.id='999'

doesn’t use the secondary index (idx_audience). Instead it uses the primary index (as per the query plan generated using EXPLAIN). This causes the query performance to deteriorate linearly.

Adding a USE INDEX hint also doesn’t seem to help. (Query plan generated using EXPLAIN for the query below still shows that the primary index is being used).

SELECT s.tail.id FROM graph s USE INDEX (idx_audience USING GSI) WHERE s.type='arc' AND s.relation.follow=true AND s.head.id='999’

How can performance of the query be improved?

Thanks.

2

Can you try
explain SELECT s.tail.id FROM graph s WHERE s.`type`=‘arc’ AND s.`relation.follow`=true AND s.head.id=‘999’ ;

(Added the back-ticks similar to your create index where clause.)

This should use the index.

{
    "requestID": "ae243242-6e89-4ed9-b9e3-b69a3f2bab8b",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "IndexScan2",
                        "index": "idx_audience",
                        "index_id": "f6deb00f9f4e5efa",
                        "index_projection": {
                            "primary_key": true
                        },
               ...
3

Isha: Thanks for the quick response. It still doesn’t work for me. Here is the output of EXPLAIN…

cbq> CREATE INDEX `idx_audience` ON `graph`(`head.id`) WHERE ((`type` = "arc") and (`relation.follow` = true)) USING GSI;
{
    "requestID": "dc934c7d-fcde-4ee7-93c6-96d465e0c5a5",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "13.838256944s",
        "executionTime": "13.838186109s",
        "resultCount": 0,
        "resultSize": 0
    }
}

cbq> explain SELECT s.tail.id FROM graph s WHERE s.`type`='arc' AND s.`relation.follow`=true AND s.head.id='999' ;
{
    "requestID": "c380c29e-f13b-4c43-80a6-80c7937a0376",
    "signature": "json",
    "results": [
        {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "PrimaryScan",
                    "index": "#primary",
                    "keyspace": "graph",
                    "namespace": "default",
                    "using": "gsi"
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Fetch",
                                "as": "s",
                                "keyspace": "graph",
                                "namespace": "default"
                            },
                            {
                                "#operator": "Filter",
                                "condition": "((((`s`.`type`) = \"arc\") and ((`s`.`relation.follow`) = true)) and (((`s`.`head`).`id`) = \"999\"))"
                            },
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "expr": "((`s`.`tail`).`id`)"
                                    }
                                ]
                            },
                            {
                                "#operator": "FinalProject"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "7.486443ms",
        "executionTime": "7.405761ms",
        "resultCount": 1,
        "resultSize": 1589
    }
}
4

Hi @lakshsubra, the suggestion from @isha works with later versions. You can upgrade to 4.5.1 or later, or add head_id = 999 to your index WHERE clause.