4.5 Beta covering indexes not used with array indexing

SQL++
1

Hi

It’s great to have a new beta release. Based on brief testing it seems more robust than the previous developer preview.

In the DP covering indexes were not supported with array indexing. Beta release notes do not seem to mention anything about it so one could assume they are supported.

When running queries with cbq it seems covering indexes are not used with array indexing. In the array indexing example below there is a fetch before filter which to my understanding makes the query much slower than the other example without array indexing.

Could anyone please tell if covering indexes supposed to be supported with array indexing already. If they are supported and they work it would be great to understand the problem in my index or query.

Index:

CREATE INDEX ievents ON `events` (company, docType, DISTINCT ARRAY r.`key` FOR r IN related END, time) WHERE docType = "EVENT";

Query example with array indexing:

cbq> EXPLAIN SELECT time FROM `events` USE INDEX (ievents USING GSI) WHERE docType = "EVENT" AND company="company_9064af3e-bcf5-4c1f-b783-f723924856bf" AND (ANY r IN related SATISFIES r.`key` = "item_07139ab1-5bac-48b8-8a92-373de9103edf" END) ORDER BY time LIMIT 10;
{
    "requestID": "6cbcd422-8e8d-4bb5-bd4e-d95891660d67",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "DistinctScan",
                                "scan": {
                                    "#operator": "IndexScan",
                                    "index": "ievents",
                                    "index_id": "666586873fba8e81",
                                    "keyspace": "events",
                                    "namespace": "default",
                                    "spans": [
                                        {
                                            "Range": {
                                                "High": [
                                                    "\"company_9064af3e-bcf5-4c1f-b783-f723924856bf\"",
                                                    "\"EVENT\"",
                                                    "\"item_07139ab1-5bac-48b8-8a92-373de9103edf\""
                                                ],
                                                "Inclusion": 3,
                                                "Low": [
                                                    "\"company_9064af3e-bcf5-4c1f-b783-f723924856bf\"",
                                                    "\"EVENT\"",
                                                    "\"item_07139ab1-5bac-48b8-8a92-373de9103edf\""
                                                ]
                                            }
                                        }
                                    ],
                                    "using": "gsi"
                                }
                            },
                            {
                                "#operator": "Parallel",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "Fetch",
                                            "keyspace": "events",
                                            "namespace": "default"
                                        },
                                        {
                                            "#operator": "Filter",
                                            "condition": "((((`events`.`docType`) = \"EVENT\") and ((`events`.`company`) = \"company_9064af3e-bcf5-4c1f-b783-f723924856bf\")) and any `r` in (`events`.`related`) satisfies ((`r`.`key`) = \"item_07139ab1-5bac-48b8-8a92-373de9103edf\") end)"
                                        },
                                        {
                                            "#operator": "InitialProject",
                                            "result_terms": [
                                                {
                                                    "expr": "(`events`.`time`)"
                                                }
                                            ]
                                        }
                                    ]
                                }
                            }
                        ]
                    },
                    {
                        "#operator": "Order",
                        "limit": "10",
                        "sort_terms": [
                            {
                                "expr": "(`events`.`time`)"
                            }
                        ]
                    },
                    {
                        "#operator": "Limit",
                        "expr": "10"
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            },
            "text": "SELECT time FROM `events` USE INDEX (ievents USING GSI) WHERE docType = \"EVENT\" AND company=\"company_9064af3e-bcf5-4c1f-b783-f723924856bf\" AND (ANY r IN related SATISFIES r.`key` = \"item_07139ab1-5bac-48b8-8a92-373de9103edf\" END) ORDER BY time LIMIT 10"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "6.106834ms",
        "executionTime": "5.965044ms",
        "resultCount": 1,
        "resultSize": 4602
    }
}

Query example without array indexing:

cbq> EXPLAIN SELECT time FROM `events` USE INDEX (ievents USING GSI) WHERE docType = "EVENT" AND company="company_9064af3e-bcf5-4c1f-b783-f723924856bf" ORDER BY time LIMIT 10;
{
    "requestID": "7d6159c1-099f-4367-b19c-3cf333845be3",
    "signature": "json",
    "results": [
        {
            "plan": {
                "#operator": "Sequence",
                "~children": [
                    {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "DistinctScan",
                                "scan": {
                                    "#operator": "IndexScan",
                                    "covers": [
                                        "cover ((`events`.`company`))",
                                        "cover ((`events`.`docType`))",
                                        "cover ((distinct (array (`r`.`key`) for `r` in (`events`.`related`) end)))",
                                        "cover ((`events`.`time`))",
                                        "cover ((meta(`events`).`id`))"
                                    ],
                                    "filter_covers": {
                                        "cover ((`events`.`docType`))": "EVENT"
                                    },
                                    "index": "ievents",
                                    "index_id": "666586873fba8e81",
                                    "keyspace": "events",
                                    "namespace": "default",
                                    "spans": [
                                        {
                                            "Range": {
                                                "High": [
                                                    "\"company_9064af3e-bcf5-4c1f-b783-f723924856bf\"",
                                                    "successor(\"EVENT\")"
                                                ],
                                                "Inclusion": 1,
                                                "Low": [
                                                    "\"company_9064af3e-bcf5-4c1f-b783-f723924856bf\"",
                                                    "\"EVENT\""
                                                ]
                                            }
                                        }
                                    ],
                                    "using": "gsi"
                                }
                            },
                            {
                                "#operator": "Parallel",
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "Filter",
                                            "condition": "((cover ((`events`.`docType`)) = \"EVENT\") and (cover ((`events`.`company`)) = \"company_9064af3e-bcf5-4c1f-b783-f723924856bf\"))"
                                        },
                                        {
                                            "#operator": "InitialProject",
                                            "result_terms": [
                                                {
                                                    "expr": "cover ((`events`.`time`))"
                                                }
                                            ]
                                        }
                                    ]
                                }
                            }
                        ]
                    },
                    {
                        "#operator": "Order",
                        "limit": "10",
                        "sort_terms": [
                            {
                                "expr": "cover ((`events`.`time`))"
                            }
                        ]
                    },
                    {
                        "#operator": "Limit",
                        "expr": "10"
                    },
                    {
                        "#operator": "FinalProject"
                    }
                ]
            },
            "text": "SELECT time FROM `events` USE INDEX (ievents USING GSI) WHERE docType = \"EVENT\" AND company=\"company_9064af3e-bcf5-4c1f-b783-f723924856bf\" ORDER BY time LIMIT 10"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "9.37467ms",
        "executionTime": "9.160171ms",
        "resultCount": 1,
        "resultSize": 4432
    }
}

BR,
Mikko

2

Covering index is supported with array indexing. For covering array index the array also need to be present part of index as separate key. For Covering index you need to include related also as key in the index so that predicate evaluation will have all the required fields.

CREATE INDEX ievents ON `events` (company, docType, DISTINCT ARRAY r.key FOR r IN related END, time,related) WHERE docType = “EVENT”;

2 Likes
3

Hi mikkogy,
You can find more info on this blog http://blog.couchbase.com/2016/may/1.making-most-of-your-arrays…-with-covering-array-indexes-and-more

Let me know any comments.

Happy array indexing!!
-Prasad

2 Likes
5

Many thanks @vsr1 and @prasad,

Adding array to the index seems to work. The blog post is very good reading because release notes or documentation do not seem to mention this at least directly.

BR,
Mikko

1 Like
6

thanks for the feedback… Will update the docs…