{"id":2250,"date":"2016-10-11T06:20:36","date_gmt":"2016-10-11T06:20:36","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=2250"},"modified":"2025-10-09T06:41:31","modified_gmt":"2025-10-09T13:41:31","slug":"1-making-most-of-your-arrays-with-covering-array-indexes-and-more","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/1-making-most-of-your-arrays-with-covering-array-indexes-and-more\/","title":{"rendered":"Making most of your Arrays.. with Covering Array Indexes and more.."},"content":{"rendered":"

Hurrah!! We just released Couchbase Server 4.5<\/a> which brings a bag full of wonderful features and enhancements. My earlier post Making the most of your arrays… with Array Indexing<\/a> introduced the\u00a0Array Indexing functionality. This blog post includes covering array indexes, support for more\u00a0operators such as UNNEST, ALL, ANY AND EVERY etc.,<\/p>\n

Covering Array Indexes<\/h2>\n

\u00a0<\/span>Covering Indexes<\/a> is a performance feature in N1QL, which significantly improves the query performance by avoiding trips to the data-service to fetch any documents. There is no special syntax for creating\/using covering indexes. This is an automatic optimization triggered in N1QL, when a query can\u00a0find\u00a0all required data in the index itself. \u00a0So, covered indexes are just normal GSI\u00a0indexes that are created with all\u00a0index-keys covering\u00a0the data\u00a0needed\u00a0for\u00a0a query. <\/span><\/p>\n

Typically, array indexes are created with required\u00a0elements\/attributes\u00a0with-in\u00a0the array,\u00a0as the index keys.<\/span>\u00a0And, covering indexes don’t need any special attention. However, to create\u00a0Covering Array Indexes, the\u00a0array itself MUST also be<\/span>\u00a0added\u00a0to\u00a0the list of other index keys provided to the CREATE INDEX statement.<\/strong>\u00a0All details of the array are required\u00a0for\u00a0N1QL to properly evaluate predicates in the query. \u00a0For example, the following command creates a covering array index on the ‘schedule[].flight’<\/em> values in the couchbase ‘travel-sample’<\/em> dataset:<\/span><\/p>\n

          CREATE INDEX isched\r\n          ON `travel-sample`(DISTINCT ARRAY i.flight FOR i IN schedule END, schedule)\r\n          WHERE (type = \"route\") AND (array_length(schedule) < 10);<\/code><\/pre>\n
Note that the array field ‘schedule’<\/em>\u00a0itself\u00a0is also required to be specified as an additional index key. To contrast, a non-covering array index doesn’t require\u00a0that, and is created as follows:<\/p>\n
          CREATE INDEX isched\r\n          ON `travel-sample`(DISTINCT ARRAY i.flight FOR i IN schedule END)\r\n          WHERE (type = \"route\") AND (array_length(schedule) < 10);<\/code><\/pre>\n
The following SELECT statement shows how to use\u00a0the covering array index. It finds “the schedules of United Airlines flight routes that have less than 10 scheduled\u00a0flights in a week”<\/em>.<\/p>\n
EXPLAIN SELECT schedule FROM `travel-sample` \r\nUSE INDEX(isched_covered)\r\nWHERE ANY i IN schedule SATISFIES i.flight LIKE \"UA%\" END\r\n      AND array_length(schedule) < 10 \r\n      AND type = \"route\";\r\n[\r\n  {\r\n    \"plan\": {\r\n      \"#operator\": \"Sequence\",\r\n      \"~children\": [\r\n        {\r\n          \"#operator\": \"DistinctScan\",\r\n          \"scan\": {\r\n            \"#operator\": \"IndexScan\",\r\n            \"covers\": [\r\n              \"cover ((distinct (array (`i`.`flight`) for `i` in (`travel-sample`.`schedule`) end)))\",\r\n              \"cover ((`travel-sample`.`schedule`))\",\r\n              \"cover ((meta(`travel-sample`).`id`))\"\r\n            ],\r\n            \"filter_covers\": {\r\n              \"cover ((`travel-sample`.`type`))\": \"route\",\r\n              \"cover ((array_length((`travel-sample`.`schedule`)) < 10))\": true\r\n            },\r\n            \"index\": \"isched_covered\",\r\n            \"index_id\": \"547b2c11add40fa6\",\r\n            \"keyspace\": \"travel-sample\",\r\n            \"namespace\": \"default\",\r\n            \"spans\": [\r\n              {\r\n                \"Range\": {\r\n                  \"High\": [\r\n                    \"\"UB\"\"\r\n                  ],\r\n                  \"Inclusion\": 0,\r\n                  \"Low\": [\r\n                    \"\"UA\"\",\r\n                    \"null\"\r\n                  ]\r\n                }\r\n              }\r\n            ],\r\n            \"using\": \"gsi\"\r\n          }\r\n        },\r\n        {\r\n          \"#operator\": \"Parallel\",\r\n          \"~child\": {\r\n            \"#operator\": \"Sequence\",\r\n            \"~children\": [\r\n              {\r\n                \"#operator\": \"Filter\",\r\n                \"condition\": \"((any `i` in cover ((`travel-sample`.`schedule`)) satisfies ((`i`.`flight`) like \"UA%\") end and cover ((array_length((`travel-sample`.`schedule`)) < 10))) and (cover ((`travel-sample`.`type`)) = \"route\"))\"\r\n              },\r\n              {\r\n                \"#operator\": \"InitialProject\",\r\n                \"result_terms\": [\r\n                  {\r\n                    \"expr\": \"cover ((`travel-sample`.`schedule`))\"\r\n                  }\r\n                ]\r\n              },\r\n              {\r\n                \"#operator\": \"FinalProject\"\r\n              }\r\n            ]\r\n          }\r\n        }\r\n      ]\r\n    },\r\n    \"text\": \"SELECT schedule FROM `travel-sample` USE INDEX(isched_covered)nWHERE ANY i IN schedule SATISFIES i.flight LIKE \"UA%\" ENDn      AND array_length(schedule) < 10 n      AND type = \"route\";\"\r\n  }\r\n]<\/code><\/pre>\n
Note that the SELECT follows the index-selection rules described in the next\u00a0sub-section.<\/p>\n