{"id":4935,"date":"2018-04-03T19:31:02","date_gmt":"2018-04-04T02:31:02","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=4935"},"modified":"2025-06-13T23:43:08","modified_gmt":"2025-06-14T06:43:08","slug":"understanding-index-grouping-aggregation-couchbase-n1ql-query","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/","title":{"rendered":"Understanding Index Grouping And Aggregation in Couchbase N1QL Query"},"content":{"rendered":"<p><a href=\"https:\/\/www.couchbase.com\/\"><span style=\"font-weight: 400\">Couchbase<\/span><\/a> <a href=\"https:\/\/docs.couchbase.com\/server\/5.5\/n1ql\/n1ql-language-reference\/index.html\"><span style=\"font-weight: 400\">N1QL<\/span><\/a><span style=\"font-weight: 400\"> is a modern query processing engine designed to provide aggregate SQL for JSON by index on distributed data with a flexible data model. Modern databases are deployed on massive clusters. Using JSON provides a flexible data mode. N1QL supports enhanced group by index SQL for JSON to make query processing easier.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Applications and database drivers submit the N1QL query to one of the available Query nodes on a cluster. The Query node analyzes the query, uses metadata on underlying objects to figure out the optimal execution plan, which it then executes. During execution, depending on the query, using applicable indexes, query node works with index and data nodes to retrieve data and perform the planned operations. Because Couchbase is a modular clustered database, you scale out data, index, and query services to fit your performance and availability goals.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Prior to Couchbase 5.5, even when a query with GROUP BY and\/or \u00a0aggregates is covered by an index, the query fetched all relevant data from the indexer and performed grouping\/aggregation of the data within the query engine.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4941 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.48-PM-300x169.png\" alt=\"\" width=\"772\" height=\"435\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.48-PM-300x169.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.48-PM-1024x578.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.48-PM-768x433.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.48-PM-1536x867.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.48-PM-20x11.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.48-PM-1320x745.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.48-PM.png 1666w\" sizes=\"auto, (max-width: 772px) 100vw, 772px\" \/><\/p>\n<p><span style=\"font-weight: 400\">In Couchbase 5.5 query planner enhanced to intelligently requests the indexer to perform grouping and aggregation in addition to range scan <\/span><b>for covering index<\/b><span style=\"font-weight: 400\">. The Indexer has been enhanced to perform grouping, COUNT(), SUM(), MIN(), MAX(), AVG(), and related operations on-the-fly. \u00a0<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4940 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.59-PM-300x164.png\" alt=\"\" width=\"782\" height=\"427\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.59-PM-300x164.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.59-PM-1024x559.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.59-PM-768x419.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.59-PM-1536x838.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.59-PM-20x11.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.59-PM-1320x720.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-3.46.59-PM.png 1682w\" sizes=\"auto, (max-width: 782px) 100vw, 782px\" \/><\/p>\n<p><span style=\"font-weight: 400\">This requires no changes to the user query, but a good index design to cover the query and order the index keys is required. \u00a0Not every query will benefit from this optimization, and not every index can accelerate every grouping and aggregation operation. Understanding the right patterns will help you design your indexes and queries. Index grouping and aggregation on global secondary index is supported with both storage engines: Standard GSI and Memory Optimized GSI (MOI). Index grouping and aggregation is supported in Enterprise Edition only.<\/span><\/p>\n<p><span style=\"font-weight: 400\">This reduction step of performing the GROUP BY and Aggregation by the indexer reduces the amount of data transfer and disk I\/O, resulting in:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Improved query response time<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Improved resource utilization<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Low latency<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">High scalability<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Low Total Cost of Ownership<\/span><\/li>\n<\/ul>\n<h2><b>Performance<\/b><\/h2>\n<p><span style=\"font-weight: 400\">The Index grouping and aggregations can improve query performance by orders of magnitude and reduce the latencies drastically. The following table list few sample query latency measurements.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Index :<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">CREATE INDEX idx_ts_type_country_city ON `travel-sample` (type, country, city);<\/pre>\n<p>&nbsp;<\/p>\n<div class=\"responsive-table\">\n<table style=\"height: 1284px\" width=\"1025\">\n<tbody>\n<tr>\n<td><span style=\"font-weight: 400\">Query<\/span><\/td>\n<td><span style=\"font-weight: 400\">Description<\/span><\/td>\n<td><span style=\"font-weight: 400\">5.0 Latencies <\/span><\/td>\n<td><span style=\"font-weight: 400\">5.5 Latencies<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">SELECT t.type, COUNT(type) AS cnt\u00a0<\/span><span style=\"font-weight: 400\">FROM `travel-sample` AS t <\/span><span style=\"font-weight: 400\">WHERE t.type IS NOT NULL <\/span><span style=\"font-weight: 400\">GROUP BY t.type;<\/span><\/td>\n<td>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">GROUP BY leading index key <\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Aggregation <\/span><\/li>\n<\/ul>\n<\/td>\n<td><span style=\"font-weight: 400\">230ms<\/span><\/td>\n<td><span style=\"font-weight: 400\">13ms<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">SELECT t.type, COUNT(1) AS cnt, <\/span><span style=\"font-weight: 400\">COUNT(DISTINCT city) AS cntdcity <\/span><span style=\"font-weight: 400\">FROM `travel-sample` AS t <\/span><span style=\"font-weight: 400\">WHERE t.type IN [&#8220;hotel&#8221;,&#8221;airport&#8221;] <\/span><span style=\"font-weight: 400\">GROUP BY t.type, t.country;<\/span><\/td>\n<td>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">GROUP BY multiple \u00a0leading index keys<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Multiple Aggregates<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Distinct Aggregate<\/span><\/li>\n<\/ul>\n<\/td>\n<td><span style=\"font-weight: 400\">40ms<\/span><\/td>\n<td><span style=\"font-weight: 400\">7ms<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">SELECT t.country, COUNT(city) AS cnt <\/span><span style=\"font-weight: 400\">FROM `travel-sample` AS t\u00a0<\/span><span style=\"font-weight: 400\">WHERE <\/span><b>t.type = &#8220;airport&#8221; <\/b><span style=\"font-weight: 400\">GROUP BY t.country;<\/span><\/td>\n<td>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">GROUP BY first non-equality leading index key<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Aggregation<\/span><\/li>\n<\/ul>\n<\/td>\n<td><span style=\"font-weight: 400\">25ms<\/span><\/td>\n<td><span style=\"font-weight: 400\">3ms<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">SELECT t.city, cnt <\/span><span style=\"font-weight: 400\">FROM `travel-sample` AS t <\/span><span style=\"font-weight: 400\">WHERE t.type IS NOT NULL <\/span><span style=\"font-weight: 400\">GROUP BY t.city <\/span><span style=\"font-weight: 400\">LETTING cnt = COUNT(city) <\/span><span style=\"font-weight: 400\">HAVING cnt &gt; 0 ;<\/span><\/td>\n<td>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">GROUP BY non-leading index key<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">LETTING clause<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">HAVING clause<\/span><\/li>\n<\/ul>\n<\/td>\n<td><span style=\"font-weight: 400\">300ms<\/span><\/td>\n<td><span style=\"font-weight: 400\">160ms<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<h2><b>Index Grouping and Aggregation Overview<\/b><\/h2>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4938 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM-300x161.png\" alt=\"\" width=\"861\" height=\"462\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM-300x161.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM-1024x549.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM-768x412.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM-20x11.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM-1320x708.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png 1388w\" sizes=\"auto, (max-width: 861px) 100vw, 861px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400\">The above figure shows all the possible phases a SELECT query goes through to return the results. <\/span><span style=\"font-weight: 400\">\u00a0<\/span><span style=\"font-weight: 400\">The filtering process takes the initial keyspace and produces an optimal subset of the documents the query is interested in. To produce the smallest possible subset, indexes are used to apply as many predicates as possible. Query predicate indicates the subset of the data interested. During the query planning phase, we select the indexes to be used. Then, for each index, we decide the predicates to be applied by each index. The query predicates are translated into range scans \u00a0in the query plan and passed to Indexer. <\/span><\/p>\n<p><span style=\"font-weight: 400\">If the query doesn&#8217;t have JOINs and is covered by index, both Fetch and Join phases can be eliminated.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4937 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.20-AM-300x162.png\" alt=\"\" width=\"839\" height=\"453\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.20-AM-300x162.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.20-AM-1024x552.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.20-AM-768x414.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.20-AM-20x11.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.20-AM-1320x711.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.20-AM.png 1366w\" sizes=\"auto, (max-width: 839px) 100vw, 839px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400\">When all predicates are exactly translated to range scans Filter phase also can be eliminated. In that situation Scan and Aggregates are side by side, and since indexer has ability to do aggregation that phase can be done on indexer node. In some cases Sort, Offset, Limit phases can also be done indexer node.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4936 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.31-AM-300x163.png\" alt=\"\" width=\"837\" height=\"455\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.31-AM-300x163.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.31-AM-1024x556.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.31-AM-768x417.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.31-AM-20x11.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.31-AM-1320x716.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-11.29.31-AM.png 1356w\" sizes=\"auto, (max-width: 837px) 100vw, 837px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400\">The following flow chart describes how query planner decides to perform index aggregation for each query block of the query. If the index aggregation is not possible aggregations are done in query engine.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4943 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-4.01.01-PM-300x234.png\" alt=\"\" width=\"944\" height=\"736\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-4.01.01-PM-300x234.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-4.01.01-PM-768x600.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-4.01.01-PM-20x16.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-4.01.01-PM-1320x1031.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-4.01.01-PM.png 1388w\" sizes=\"auto, (max-width: 944px) 100vw, 944px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"font-weight: 400\">For example, let&#8217;s compare the previous vs. current performance of using GROUP BY and examine the EXPLAIN plan of the following query that uses an index defined on the Couchbase `travel-sample` bucket:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">CREATE INDEX `def_type` ON `travel-sample`(`type`);\r\n<\/pre>\n<p><span style=\"font-weight: 400\">Consider the query: <\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">SELECT type, COUNT(type)\r\nFROM `travel-sample`\r\nWHERE type IS NOT MISSING\r\nGROUP BY type;<\/pre>\n<p><span style=\"font-weight: 400\">Before Couchbase version 5.5, this query engine fetched relevant data from the indexer and grouping and aggregation of the data \u00a0is done within query engine. This simple query takes about 250 ms.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4939 alignnone\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-02-at-7.39.06-PM-300x47.png\" alt=\"\" width=\"848\" height=\"133\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-02-at-7.39.06-PM-300x47.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-02-at-7.39.06-PM-1024x160.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-02-at-7.39.06-PM-768x120.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-02-at-7.39.06-PM-1536x240.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-02-at-7.39.06-PM-20x3.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-02-at-7.39.06-PM-1320x206.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-02-at-7.39.06-PM.png 1690w\" sizes=\"auto, (max-width: 848px) 100vw, 848px\" \/><\/p>\n<p><span style=\"font-weight: 400\">Now, in Couchbase version 5.5, this query use the same def_type index, but executes in under 20 ms. In the explain below, you can see fewer steps and the lack of the grouping step after the index scan because the index scan step does the grouping and aggregation as well.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4949 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.11-PM-300x36.png\" alt=\"\" width=\"850\" height=\"102\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.11-PM-300x36.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.11-PM-1024x124.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.11-PM-768x93.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.11-PM-20x2.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.11-PM-1320x159.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.11-PM.png 1524w\" sizes=\"auto, (max-width: 850px) 100vw, 850px\" \/><\/p>\n<p><span style=\"font-weight: 400\">As the data and query complexity grows, the performance benefit (both latency and throughput) will grow as well. \u00a0\u00a0<\/span><\/p>\n<h2><b>Understanding EXPLAIN of Index Grouping and Aggregation<\/b><\/h2>\n<p><span style=\"font-weight: 400\">Looking at the explain of the query:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">EXPLAIN SELECT type, COUNT(type) FROM `travel-sample` WHERE type IS NOT MISSING GROUP BY type;{<\/pre>\n<pre class=\"theme:github lang:mysql decode:true\">{\r\n  \"plan\": {\r\n    \"#operator\": \"Sequence\",\r\n    \"~children\": [\r\n      {\r\n        \"#operator\": \"IndexScan3\",\r\n        \"covers\": [\r\n          \"cover ((`travel-sample`.`type`))\",\r\n          \"cover ((meta(`travel-sample`).`id`))\",\r\n          \"cover (count(cover ((`travel-sample`.`type`))))\"\r\n        ],\r\n        \"index\": \"def_type\",\r\n        \"index_group_aggs\": {\r\n          \"aggregates\": [\r\n            {\r\n              \"aggregate\": \"COUNT\",\r\n              \"depends\": [\r\n                0\r\n              ],\r\n              \"expr\": \"cover ((`travel-sample`.`type`))\",\r\n              \"id\": 2,\r\n              \"keypos\": 0\r\n            }\r\n          ],\r\n          \"depends\": [\r\n            0\r\n          ],\r\n          \"group\": [\r\n            {\r\n              \"depends\": [\r\n                0\r\n              ],\r\n              \"expr\": \"cover ((`travel-sample`.`type`))\",\r\n              \"id\": 0,\r\n              \"keypos\": 0\r\n            }\r\n          ]\r\n        },\r\n        \"index_id\": \"b948c92b44c2739f\",\r\n        \"index_projection\": {\r\n          \"entry_keys\": [\r\n            0,\r\n            2\r\n          ]\r\n        },\r\n        \"keyspace\": \"travel-sample\",\r\n        \"namespace\": \"default\",\r\n        \"spans\": [\r\n          {\r\n            \"exact\": true,\r\n            \"range\": [\r\n              {\r\n                \"inclusion\": 1,\r\n                \"low\": \"null\"\r\n              }\r\n            ]\r\n          }\r\n        ],\r\n        \"using\": \"gsi\"\r\n      },\r\n      {\r\n        \"#operator\": \"Parallel\",\r\n        \"~child\": {\r\n          \"#operator\": \"Sequence\",\r\n          \"~children\": [\r\n            {\r\n              \"#operator\": \"InitialProject\",\r\n              \"result_terms\": [\r\n                {\r\n                  \"expr\": \"cover ((`travel-sample`.`type`))\"\r\n                },\r\n                {\r\n                  \"expr\": \"cover (count(cover ((`travel-sample`.`type`))))\"\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 type, COUNT(type) FROM `travel-sample` WHERE type IS NOT MISSING GROUP BY type;\"\r\n}<\/pre>\n<p>You will see &#8220;index_group_aggs&#8221; in the IndexScan section (i.e &#8220;#operator&#8221;: &#8220;IndexScan3&#8221;). If &#8220;index_group_aggs&#8221; is MISSING then query service is performing grouping and aggregation. If present query is using Index grouping and aggregation and it has all relevant information indexer required for grouping and aggregation. The following table describe how to interpret the various information of index_group_aggs object.<\/p>\n<div class=\"responsive-table\">\n<table>\n<tbody>\n<tr>\n<td><b>Field Name<\/b><\/td>\n<td><b>Description<\/b><\/td>\n<td><b>Line numbers from Example<\/b><\/td>\n<td><b>Explain Text in Example<\/b><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">aggregates<\/span><\/td>\n<td><span style=\"font-weight: 400\">Array of Aggregate objects, and each object represents one aggregate. The absence of this item means only group by is present in the query.<\/span><\/td>\n<td><span style=\"font-weight: 400\">14-24<\/span><\/td>\n<td><span style=\"font-weight: 400\">aggregates<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\"> \u00a0\u00a0aggregate<\/span><\/td>\n<td><span style=\"font-weight: 400\">Aggregate operation (<\/span><span style=\"font-weight: 400\">MAX\/MIN\/SUM\/COUNT\/COUNTN)<\/span><span style=\"font-weight: 400\">.<\/span><\/td>\n<td><span style=\"font-weight: 400\">16<\/span><\/td>\n<td><span style=\"font-weight: 400\">COUNT<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">distinct<\/span><\/td>\n<td><span style=\"font-weight: 400\">Aggregate modifier is DISTINCT<\/span><\/td>\n<td><span style=\"font-weight: 400\">&#8211;<\/span><\/td>\n<td><span style=\"font-weight: 400\">False(When true only it appears)<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\"> \u00a0\u00a0depends<\/span><\/td>\n<td><span style=\"font-weight: 400\">List of index key positions(starting with 0) the aggregate expression depends on.<\/span><\/td>\n<td><span style=\"font-weight: 400\">17-19<\/span><\/td>\n<td><span style=\"font-weight: 400\">0 <\/span><span style=\"font-weight: 400\">(because type is 0th index key of def_type index)<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\"> \u00a0\u00a0expr<\/span><\/td>\n<td><span style=\"font-weight: 400\">aggregate expression<\/span><\/td>\n<td><span style=\"font-weight: 400\">20<\/span><\/td>\n<td><span style=\"font-weight: 400\">cover ((`travel-sample`.`type`))<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\"> \u00a0\u00a0id<\/span><\/td>\n<td><span style=\"font-weight: 400\">Unique ID given internally and will be used in <\/span><span style=\"font-weight: 400\">index_projection<\/span><\/td>\n<td><span style=\"font-weight: 400\">21<\/span><\/td>\n<td><span style=\"font-weight: 400\">2<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\"> \u00a0\u00a0keypos<\/span><\/td>\n<td><span style=\"font-weight: 400\">Indicator to that tells use expression at the index key position or from the expr field.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">A value &gt; -1 means the \u00a0aggregate expression is exactly matches the corresponding index key position( starting with 0).<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">A value of -1 means the ] aggregate expression does not exactly match with the index key position and use expression from expr field.<\/span><\/li>\n<\/ul>\n<\/td>\n<td><span style=\"font-weight: 400\">22<\/span><\/td>\n<td><span style=\"font-weight: 400\">0 <\/span><span style=\"font-weight: 400\">(because type is 0th index key of def_type index)<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">depends<\/span><\/td>\n<td><span style=\"font-weight: 400\">List of index key positions the groups\/aggregates expressions depends on (consolidated list)<\/span><\/td>\n<td><span style=\"font-weight: 400\">25-27<\/span><\/td>\n<td><span style=\"font-weight: 400\">0<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">group<\/span><\/td>\n<td><span style=\"font-weight: 400\">Array of GROUP BY objects, and each object represents one group key. The absence of this item means there is no GROUP BY clause present in the query.<\/span><\/td>\n<td><span style=\"font-weight: 400\">28-37<\/span><\/td>\n<td><span style=\"font-weight: 400\">group<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\"> \u00a0\u00a0depends<\/span><\/td>\n<td><span style=\"font-weight: 400\">List of index key positions(starting with 0) the group expression depends on.<\/span><\/td>\n<td><span style=\"font-weight: 400\">30-32<\/span><\/td>\n<td><span style=\"font-weight: 400\">0<\/span><\/p>\n<p><span style=\"font-weight: 400\">(because type is 0th key of index key of def_type index)<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\"> \u00a0\u00a0expr<\/span><\/td>\n<td><span style=\"font-weight: 400\">group expression.<\/span><\/td>\n<td><span style=\"font-weight: 400\">33<\/span><\/td>\n<td><span style=\"font-weight: 400\">cover ((`travel-sample`.`type`))<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\"> \u00a0\u00a0id<\/span><\/td>\n<td><span style=\"font-weight: 400\">Unique ID given internally and will be used in <\/span><span style=\"font-weight: 400\">index_projection.<\/span><\/td>\n<td><span style=\"font-weight: 400\">34<\/span><\/td>\n<td><span style=\"font-weight: 400\">0<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\"> \u00a0\u00a0keypos<\/span><\/td>\n<td><span style=\"font-weight: 400\">Indicator to that tells use expression at the index key position or from the expr field.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">A value &gt; -1 means the \u00a0group expression is exactly matches the corresponding index key position( starting with 0).<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">A value of -1 means the group key does not exactly match with the index key position and use expression from expr field.<\/span><\/li>\n<\/ul>\n<\/td>\n<td><span style=\"font-weight: 400\">35<\/span><\/td>\n<td><span style=\"font-weight: 400\">0 <\/span><span style=\"font-weight: 400\">(because type is 0th index key of def_type index)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p><span style=\"font-weight: 400\">The covers field is array and it has all the index keys, document key(META().id), group keys \u00a0expressions that are not exactly matched with index keys (sorted by id), aggregates sorted by id. Also &#8220;Index_projection&#8221; will have all the group\/aggregate ids.<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">\"covers\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((`travel-sample`.`type`))\", \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u2190 Index key (0)\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover ((meta(`travel-sample`).`id`))\", \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u2190 document key (1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"cover (count(cover ((`travel-sample`.`type`))))\" \u00a0\u00a0\u00a0\u00a0\u2190 aggregate (2)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]<\/pre>\n<p><span style=\"font-weight: 400\">In above case group expression `type` is same Index key of index `def_type`. It is not included twice.<\/span><\/p>\n<h2><b>Details of <\/b><b>Index Grouping and Aggregation<\/b><\/h2>\n<p><span style=\"font-weight: 400\">We will use examples to show how Index grouping and aggregations works. To follow the examples <\/span><span style=\"font-weight: 400\">please create a bucket \u201cdefault\u201d and insert the following documents:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">INSERT INTO default (KEY,VALUE)\r\n    VALUES (\"ga0001\", {\"c0\":1, \"c1\":10, \"c2\":100, \"c3\":1000, \"c4\":10000, \"a1\":[{\"id\":1}, {\"id\":1}, {\"id\":2}, {\"id\":3}, {\"id\":4}, {\"id\":5}]}),\r\n    VALUES (\"ga0002\", {\"c0\":1, \"c1\":20, \"c2\":200, \"c3\":2000, \"c4\":20000, \"a1\":[{\"id\":1}, {\"id\":1}, {\"id\":2}, {\"id\":3}, {\"id\":4}, {\"id\":5}]}),\r\n    VALUES (\"ga0003\", {\"c0\":1, \"c1\":10, \"c2\":300, \"c3\":3000, \"c4\":30000, \"a1\":[{\"id\":1}, {\"id\":1}, {\"id\":2}, {\"id\":3}, {\"id\":4}, {\"id\":5}]}),\r\n    VALUES (\"ga0004\", {\"c0\":1, \"c1\":20, \"c2\":400, \"c3\":4000, \"c4\":40000, \"a1\":[{\"id\":1}, {\"id\":1}, {\"id\":2}, {\"id\":3}, {\"id\":4}, {\"id\":5}]}),\r\n    VALUES (\"ga0005\", {\"c0\":2, \"c1\":10, \"c2\":100, \"c3\":5000, \"c4\":50000, \"a1\":[{\"id\":1}, {\"id\":1}, {\"id\":2}, {\"id\":3}, {\"id\":4}, {\"id\":5}]}),\r\n    VALUES (\"ga0006\", {\"c0\":2, \"c1\":20, \"c2\":200, \"c3\":6000, \"c4\":60000, \"a1\":[{\"id\":1}, {\"id\":1}, {\"id\":2}, {\"id\":3}, {\"id\":4}, {\"id\":5}]}),\r\n    VALUES (\"ga0007\", {\"c0\":2, \"c1\":10, \"c2\":300, \"c3\":7000, \"c4\":70000, \"a1\":[{\"id\":1}, {\"id\":1}, {\"id\":2}, {\"id\":3}, {\"id\":4}, {\"id\":5}]}),\r\n    VALUES (\"ga0008\", {\"c0\":2, \"c1\":20, \"c2\":400, \"c3\":8000, \"c4\":80000, \"a1\":[{\"id\":1}, {\"id\":1}, {\"id\":2}, {\"id\":3}, {\"id\":4}, {\"id\":5}]});<\/pre>\n<h4><\/h4>\n<h3><span style=\"font-weight: 400\">Example 1: Group by leading index keys<\/span><\/h3>\n<p><span style=\"font-weight: 400\">Let consider the following query and index:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">SELECT d.c0 AS c0, d.c1 AS c1, SUM(d.c3) AS sumc3,\r\n       AVG(d.c4) AS avgc4, COUNT(DISTINCT d.c2) AS dcountc2\r\nFROM default AS d\r\nWHERE d.c0 &gt; 0\r\nGROUP BY d.c0, d.c1\r\nORDER BY d.c0, d.c1\r\nOFFSET 1\r\nLIMIT 2;<\/pre>\n<p>Required Index:<\/p>\n<pre class=\"theme:github lang:mysql decode:true\">CREATE INDEX idx1 ON default(c0, c1, c2, c3, c4);<\/pre>\n<p><span style=\"font-weight: 400\">\u00a0 \u00a0 \u00a0The query has GROUP BY and multiple aggregates, some of aggregates has DISTINCT modifier. The query can be covered by index idx1 and the predicate (d.c0 &gt; 0) can be converted into exact range scan and passed it to index scan. So, the index and query combination qualifies Index grouping and aggregations.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Indexes are naturally ordered and grouped by the order of the index key definition. In the above query, the GROUP BY keys (d.c0, d.c1) exactly matches with the leading keys (c0, c1) of the index. Therefore, index has each group data together, indexer will produce one row per group i.e. Full aggregation. \u00a0Also, query has aggregate that has DISTINCT modifier and it exactly matches with one of the index keys with position less than or equal to number of group keys plus one (i.e. there 2 group keys, DISTINCT modifier can be any one of index key at position 0,1,2 because index key followed by group keys and DISTINCT modifier can applied without sort). Therefore, the query above is suitable for indexer to handle grouping and aggregation. <\/span><\/p>\n<p><span style=\"font-weight: 400\">If group by missing one of the leading index key and there is equality predicate, then special optimization is done by treating the index key implicitly present in group keys and determine if Full aggregation is possible or not. For partition index the all the partition keys needs to present in the group keys to generate Full aggregations.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4948 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.34-PM-300x79.png\" alt=\"\" width=\"870\" height=\"229\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.34-PM-300x79.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.34-PM-1024x268.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.34-PM-768x201.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.34-PM-20x5.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.34-PM-1320x346.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.04.34-PM.png 1512w\" sizes=\"auto, (max-width: 870px) 100vw, 870px\" \/><\/p>\n<p><span style=\"font-weight: 400\">The above graphical execution tree shows index scan (IndexScan3) performing scan and index grouping aggregations. The results from the \u00a0index scan are projected.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Let&#8217;s look at the text based explain :<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">{\r\n  \"plan\": {\r\n    \"#operator\": \"Sequence\",\r\n    \"~children\": [\r\n      {\r\n        \"#operator\": \"Sequence\",\r\n        \"~children\": [\r\n          {\r\n            \"#operator\": \"IndexScan3\",\r\n            \"as\": \"d\",\r\n            \"covers\": [\r\n              \"cover ((`d`.`c0`))\",\r\n              \"cover ((`d`.`c1`))\",\r\n              \"cover ((`d`.`c2`))\",\r\n              \"cover ((`d`.`c3`))\",\r\n              \"cover ((`d`.`c4`))\",\r\n              \"cover ((meta(`d`).`id`))\",\r\n              \"cover (count(distinct cover ((`d`.`c2`))))\",\r\n              \"cover (countn(cover ((`d`.`c4`))))\",\r\n              \"cover (sum(cover ((`d`.`c3`))))\",\r\n              \"cover (sum(cover ((`d`.`c4`))))\"\r\n            ],\r\n            \"index\": \"idx1\",\r\n            \"index_group_aggs\": {\r\n              \"aggregates\": [\r\n                {\r\n                  \"aggregate\": \"COUNT\",\r\n                  \"depends\": [\r\n                    2\r\n                  ],\r\n                  \"distinct\": true,\r\n                  \"expr\": \"cover ((`d`.`c2`))\",\r\n                  \"id\": 6,\r\n                  \"keypos\": 2\r\n                },\r\n                {\r\n                  \"aggregate\": \"COUNTN\",\r\n                  \"depends\": [\r\n                    4\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c4`))\",\r\n                  \"id\": 7,\r\n                  \"keypos\": 4\r\n                },\r\n                {\r\n                  \"aggregate\": \"SUM\",\r\n                  \"depends\": [\r\n                    3\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c3`))\",\r\n                  \"id\": 8,\r\n                  \"keypos\": 3\r\n                },\r\n                {\r\n                  \"aggregate\": \"SUM\",\r\n                  \"depends\": [\r\n                    4\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c4`))\",\r\n                  \"id\": 9,\r\n                  \"keypos\": 4\r\n                }\r\n              ],\r\n              \"depends\": [\r\n                0,\r\n                1,\r\n                2,\r\n                3,\r\n                4\r\n              ],\r\n              \"group\": [\r\n                {\r\n                  \"depends\": [\r\n                    0\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c0`))\",\r\n                  \"id\": 0,\r\n                  \"keypos\": 0\r\n                },\r\n                {\r\n                  \"depends\": [\r\n                    1\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c1`))\",\r\n                  \"id\": 1,\r\n                  \"keypos\": 1\r\n                }\r\n              ]\r\n            },\r\n            \"index_id\": \"d06df7c5d379cd5\",\r\n            \"index_order\": [\r\n              {\r\n                \"keypos\": 0\r\n              },\r\n              {\r\n                \"keypos\": 1\r\n              }\r\n            ],\r\n            \"index_projection\": {\r\n              \"entry_keys\": [\r\n                0,\r\n                1,\r\n                6,\r\n                7,\r\n                8,\r\n                9\r\n              ]\r\n            },\r\n            \"keyspace\": \"default\",\r\n            \"limit\": \"2\",\r\n            \"namespace\": \"default\",\r\n            \"offset\": \"1\",\r\n            \"spans\": [\r\n              {\r\n                \"exact\": true,\r\n                \"range\": [\r\n                  {\r\n                    \"inclusion\": 0,\r\n                    \"low\": \"0\"\r\n                  }\r\n                ]\r\n              }\r\n            ],\r\n            \"using\": \"gsi\"\r\n          },\r\n          {\r\n            \"#operator\": \"Parallel\",\r\n            \"maxParallelism\": 1,\r\n            \"~child\": {\r\n              \"#operator\": \"Sequence\",\r\n              \"~children\": [\r\n                {\r\n                  \"#operator\": \"InitialProject\",\r\n                  \"result_terms\": [\r\n                    {\r\n                      \"as\": \"c0\",\r\n                      \"expr\": \"cover ((`d`.`c0`))\"\r\n                    },\r\n                    {\r\n                      \"as\": \"c1\",\r\n                      \"expr\": \"cover ((`d`.`c1`))\"\r\n                    },\r\n                    {\r\n                      \"as\": \"sumc3\",\r\n                      \"expr\": \"cover (sum(cover ((`d`.`c3`))))\"\r\n                    },\r\n                    {\r\n                      \"as\": \"avgc4\",\r\n                      \"expr\": \"(cover (sum(cover ((`d`.`c4`)))) \/ cover (countn(cover ((`d`.`c4`)))))\"\r\n                    },\r\n                    {\r\n                      \"as\": \"dcountc2\",\r\n                      \"expr\": \"cover (count(distinct cover ((`d`.`c2`))))\"\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      {\r\n        \"#operator\": \"Limit\",\r\n        \"expr\": \"2\"\r\n      }\r\n    ]\r\n  },\r\n  \"text\": \"SELECT d.c0 AS c0, d.c1 AS c1, SUM(d.c3) AS sumc3, AVG(d.c4) AS avgc4, COUNT(DISTINCT d.c2) AS dcountc2 FROM default AS d\\nWHERE d.c0 &gt; 0 GROUP BY d.c0, d.c1 ORDER BY d.c0, d.c1 OFFSET 1  LIMIT 2;\"\r\n}<\/pre>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">The &#8220;index_group_aggs&#8221; (lines 24-89) in the IndexScan section (i.e &#8220;#operator&#8221;: &#8220;IndexScan3&#8221;) shows query using index grouping and aggregations. <\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">If query uses \u00a0index grouping and aggregation the predicates are exactly converted to range scans and passed to index scan as part of spans, so there will not be any Filter operator in the explain.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"> As group by keys exactly match the leading index keys, indexer will produce full aggregations. Therefore, we also eliminate grouping in query service (There is no InitialGroup, IntermediateGroup, FinalGroup operators in the explain). <\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Indexer projects &#8220;index_projection&#8221; (lines 99-107) including all group keys and aggregates.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Query ORDER BY matches with leading index keys and GROUP BY is on leading index keys we can use index order. This can be found in explain (lines 91-98) and will not use &#8220;#operator&#8221;: &#8220;Order&#8221; between line 164-165. \u00a0<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">As query can use index order and there is no HAVING clause in the query the &#8220;offset&#8221; and &#8220;limit&#8221; values can be passed to indexer. <\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">This can be found at line 112, 110. The &#8220;offset&#8221; can be applied only once you will not see &#8220;#operator&#8221;: &#8220;Offset&#8221; between line 164-165, But re-applying &#8220;limit&#8221; is no-op. This can be seen at line 165-168.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Query contains AVG(x) it has been rewritten as SUM(x)\/COUNTN(x). The COUNTN(x) only counts when x is numeric value.<\/span><\/li>\n<\/ul>\n<h3><span style=\"font-weight: 400\">Example 2: Group by leading index keys, LETTING, HAVING <\/span><\/h3>\n<p><span style=\"font-weight: 400\">Let consider the following query and index:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true \">SELECT d.c0 AS c0, d.c1 AS c1, sumc3 AS sumc3,\r\n       AVG(d.c4) AS avgc4, COUNT(DISTINCT d.c2) AS dcountc2\r\nFROM default AS d\r\nWHERE d.c0 &gt; 0\r\nGROUP BY d.c0, d.c1\r\nLETTING sumc3 = SUM(d.c3)\r\nHAVING sumc3 &gt; 0\r\nORDER BY d.c0, d.c1\r\nOFFSET 1\r\nLIMIT 2;<\/pre>\n<p><span style=\"font-weight: 400\">Required Index:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">CREATE INDEX idx1 ON default(c0, c1, c2, c3, c4);<\/pre>\n<p><span style=\"font-weight: 400\">The above query is similar to Example 1 but it has LETTING, HAVING clause. Indexer will not be able to handle these and thus LETTING and HAVING clauses are applied in query service after grouping and aggregations. Therefore you see Let, Filter operators after IndexScan3 in execution tree. Having clause is filter and further eliminates items thus &#8220;offset&#8221;, &#8220;limit&#8221; can&#8217;t be pushed to indexer and need to be applied in query service, but we still can use index order.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4947 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.02-PM-300x54.png\" alt=\"\" width=\"1239\" height=\"223\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.02-PM-300x54.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.02-PM-768x139.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.02-PM-20x4.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.02-PM-1320x239.png 1320w\" sizes=\"auto, (max-width: 1239px) 100vw, 1239px\" \/><\/p>\n<h3><span style=\"font-weight: 400\">Example 3: Group by non-leading index keys<\/span><\/h3>\n<p><span style=\"font-weight: 400\">Let consider the following query and index:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true \">SELECT d.c1 AS c1, d.c2 AS c2, SUM(d.c3) AS sumc3,\r\n       AVG(d.c4) AS avgc4, COUNT(d.c2) AS countc2\r\nFROM default AS d\r\nWHERE d.c0 &gt; 0\r\nGROUP BY d.c1, d.c2\r\nORDER BY d.c1, d.c2\r\nOFFSET 1\r\nLIMIT 2;<\/pre>\n<p><span style=\"font-weight: 400\">Required Index:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">CREATE INDEX idx1 ON default(c0, c1, c2, c3, c4);\r\n\r\n<\/pre>\n<p><span style=\"font-weight: 400\"> \u00a0\u00a0\u00a0\u00a0<\/span><span style=\"font-weight: 400\">The query has GROUP BY and multiple aggregates. The query can be covered by index idx1 and the predicate (d.c0 &gt; 0) can be converted into exact range scan and passed it to index scan. So, the index and query combination qualifies Index grouping and aggregations.<\/span><\/p>\n<p><span style=\"font-weight: 400\">In the above query, the GROUP BY keys (d.c1, d.c2) do NOT match the leading keys (c0, c1) of the index. The groups are scattered across the index. Therefore, indexer will produce multiple rows per each group i.e. Partial aggregation. In case of partial aggregation query service does group merge, query can&#8217;t use index order or push &#8220;offset&#8221;, &#8220;limit&#8221; to indexer. \u00a0In case of partial aggregation if any aggregate has DISTINCT modifier index grouping and aggregation is not possible. The query above is suitable for indexer to handle grouping and aggregation. <\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4946 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.16-PM-300x60.png\" alt=\"\" width=\"885\" height=\"177\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.16-PM-300x60.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.16-PM-768x153.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.16-PM-20x4.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.16-PM-1320x262.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.16-PM.png 1520w\" sizes=\"auto, (max-width: 885px) 100vw, 885px\" \/><\/p>\n<p><span style=\"font-weight: 400\">The above graphical execution tree shows index scan (IndexScan3) performing scan and index grouping aggregations. The results from the index scan are grouped again and projected.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Let&#8217;s look at the text based explain :<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">{\r\n  \"plan\": {\r\n    \"#operator\": \"Sequence\",\r\n    \"~children\": [\r\n      {\r\n        \"#operator\": \"Sequence\",\r\n        \"~children\": [\r\n          {\r\n            \"#operator\": \"IndexScan3\",\r\n            \"as\": \"d\",\r\n            \"covers\": [\r\n              \"cover ((`d`.`c0`))\",\r\n              \"cover ((`d`.`c1`))\",\r\n              \"cover ((`d`.`c2`))\",\r\n              \"cover ((`d`.`c3`))\",\r\n              \"cover ((`d`.`c4`))\",\r\n              \"cover ((meta(`d`).`id`))\",\r\n              \"cover (count(cover ((`d`.`c2`))))\",\r\n              \"cover (countn(cover ((`d`.`c4`))))\",\r\n              \"cover (sum(cover ((`d`.`c3`))))\",\r\n              \"cover (sum(cover ((`d`.`c4`))))\"\r\n            ],\r\n            \"index\": \"idx1\",\r\n            \"index_group_aggs\": {\r\n              \"aggregates\": [\r\n                {\r\n                  \"aggregate\": \"COUNT\",\r\n                  \"depends\": [\r\n                    2\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c2`))\",\r\n                  \"id\": 6,\r\n                  \"keypos\": 2\r\n                },\r\n                {\r\n                  \"aggregate\": \"COUNTN\",\r\n                  \"depends\": [\r\n                    4\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c4`))\",\r\n                  \"id\": 7,\r\n                  \"keypos\": 4\r\n                },\r\n                {\r\n                  \"aggregate\": \"SUM\",\r\n                  \"depends\": [\r\n                    3\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c3`))\",\r\n                  \"id\": 8,\r\n                  \"keypos\": 3\r\n                },\r\n                {\r\n                  \"aggregate\": \"SUM\",\r\n                  \"depends\": [\r\n                    4\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c4`))\",\r\n                  \"id\": 9,\r\n                  \"keypos\": 4\r\n                }\r\n              ],\r\n              \"depends\": [\r\n                1,\r\n                2,\r\n                3,\r\n                4\r\n              ],\r\n              \"group\": [\r\n                {\r\n                  \"depends\": [\r\n                    1\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c1`))\",\r\n                  \"id\": 1,\r\n                  \"keypos\": 1\r\n                },\r\n                {\r\n                  \"depends\": [\r\n                    2\r\n                  ],\r\n                  \"expr\": \"cover ((`d`.`c2`))\",\r\n                  \"id\": 2,\r\n                  \"keypos\": 2\r\n                }\r\n              ],\r\n              \"partial\": true\r\n            },\r\n            \"index_id\": \"d06df7c5d379cd5\",\r\n            \"index_projection\": {\r\n              \"entry_keys\": [\r\n                1,\r\n                2,\r\n                6,\r\n                7,\r\n                8,\r\n                9\r\n              ]\r\n            },\r\n            \"keyspace\": \"default\",\r\n            \"namespace\": \"default\",\r\n            \"spans\": [\r\n              {\r\n                \"exact\": true,\r\n                \"range\": [\r\n                  {\r\n                    \"inclusion\": 0,\r\n                    \"low\": \"0\"\r\n                  }\r\n                ]\r\n              }\r\n            ],\r\n            \"using\": \"gsi\"\r\n          },\r\n          {\r\n            \"#operator\": \"Parallel\",\r\n            \"~child\": {\r\n              \"#operator\": \"Sequence\",\r\n              \"~children\": [\r\n                {\r\n                  \"#operator\": \"InitialGroup\",\r\n                  \"aggregates\": [\r\n                    \"sum(cover (count(cover ((`d`.`c2`)))))\",\r\n                    \"sum(cover (countn(cover ((`d`.`c4`)))))\",\r\n                    \"sum(cover (sum(cover ((`d`.`c3`)))))\",\r\n                    \"sum(cover (sum(cover ((`d`.`c4`)))))\"\r\n                  ],\r\n                  \"group_keys\": [\r\n                    \"cover ((`d`.`c1`))\",\r\n                    \"cover ((`d`.`c2`))\"\r\n                  ]\r\n                }\r\n              ]\r\n            }\r\n          },\r\n          {\r\n            \"#operator\": \"IntermediateGroup\",\r\n            \"aggregates\": [\r\n              \"sum(cover (count(cover ((`d`.`c2`)))))\",\r\n              \"sum(cover (countn(cover ((`d`.`c4`)))))\",\r\n              \"sum(cover (sum(cover ((`d`.`c3`)))))\",\r\n              \"sum(cover (sum(cover ((`d`.`c4`)))))\"\r\n            ],\r\n            \"group_keys\": [\r\n              \"cover ((`d`.`c1`))\",\r\n              \"cover ((`d`.`c2`))\"\r\n            ]\r\n          },\r\n          {\r\n            \"#operator\": \"FinalGroup\",\r\n            \"aggregates\": [\r\n              \"sum(cover (count(cover ((`d`.`c2`)))))\",\r\n              \"sum(cover (countn(cover ((`d`.`c4`)))))\",\r\n              \"sum(cover (sum(cover ((`d`.`c3`)))))\",\r\n              \"sum(cover (sum(cover ((`d`.`c4`)))))\"\r\n            ],\r\n            \"group_keys\": [\r\n              \"cover ((`d`.`c1`))\",\r\n              \"cover ((`d`.`c2`))\"\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\": \"InitialProject\",\r\n                  \"result_terms\": [\r\n                    {\r\n                      \"as\": \"c1\",\r\n                      \"expr\": \"cover ((`d`.`c1`))\"\r\n                    },\r\n                    {\r\n                      \"as\": \"c2\",\r\n                      \"expr\": \"cover ((`d`.`c2`))\"\r\n                    },\r\n                    {\r\n                      \"as\": \"sumc3\",\r\n                      \"expr\": \"sum(cover (sum(cover ((`d`.`c3`)))))\"\r\n                    },\r\n                    {\r\n                      \"as\": \"avgc4\",\r\n                      \"expr\": \"(sum(cover (sum(cover ((`d`.`c4`))))) \/ sum(cover (countn(cover ((`d`.`c4`))))))\"\r\n                    },\r\n                    {\r\n                      \"as\": \"countc2\",\r\n                      \"expr\": \"sum(cover (count(cover ((`d`.`c2`)))))\"\r\n                    }\r\n                  ]\r\n                }\r\n              ]\r\n            }\r\n          }\r\n        ]\r\n      },\r\n      {\r\n        \"#operator\": \"Order\",\r\n        \"limit\": \"2\",\r\n        \"offset\": \"1\",\r\n        \"sort_terms\": [\r\n          {\r\n            \"expr\": \"cover ((`d`.`c1`))\"\r\n          },\r\n          {\r\n            \"expr\": \"cover ((`d`.`c2`))\"\r\n          }\r\n        ]\r\n      },\r\n      {\r\n        \"#operator\": \"Offset\",\r\n        \"expr\": \"1\"\r\n      },\r\n      {\r\n        \"#operator\": \"Limit\",\r\n        \"expr\": \"2\"\r\n      },\r\n      {\r\n        \"#operator\": \"FinalProject\"\r\n      }\r\n    ]\r\n  },\r\n  \"text\": \"SELECT d.c1 AS c1, d.c2 AS c2, SUM(d.c3) AS sumc3, AVG(d.c4) AS avgc4, COUNT(d.c2) AS countc2 FROM default AS d WHERE d.c0 &gt; 0 GROUP BY d.c1, d.c2 ORDER BY d.c1, d.c2 OFFSET 1  LIMIT 2;\"\r\n}<\/pre>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">The &#8220;index_group_aggs&#8221; (lines 24-88) in the IndexScan section (i.e &#8220;#operator&#8221;: &#8220;IndexScan3&#8221;) shows query using index grouping and aggregations. <\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">If query uses \u00a0index grouping and aggregation the predicates are exactly converted to range scans and passed to index scan as part of spans, so there will not be any Filter operator in the explain.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"> As group by keys did NOT match the leading index keys, indexer will produce partial aggregations. This can be seen as &#8220;partial&#8221;:true inside &#8220;index_group_aggs&#8221; at line 87. Query service does Group merging (see line 119-161)<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Indexer projects &#8220;index_projection&#8221; (lines 91-99) containing group keys and aggregates.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">If the Indexer generates partial aggregations query can&#8217;t use index order and requires explicit sort, and &#8220;offset&#8221;, &#8220;limit&#8221; can&#8217;t be pushed to indexer. The plan will have explicit &#8220;Order&#8221;, &#8220;Offset&#8221;, and &#8220;Limit&#8221; operators (line 197 &#8211; 217)<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Query contains AVG(x) which has been rewritten as SUM(x)\/COUNTN(x). The COUNTN(x) only counts when x is numeric value.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">During Group merge<\/span>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">MIN becomes MIN of MIN<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">MAX becomes MAX of MAX<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">SUM becomes SUM of SUM<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">COUNT becomes SUM of COUNT<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">CONTN becomes SUM of COUNTN<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">AVG becomes SUM of SUM divided by SUM of COUNTN<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3><span style=\"font-weight: 400\">Example 4: Group and Aggregation with array index<\/span><\/h3>\n<p><span style=\"font-weight: 400\">Let consider the following query and index:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true \">SELECT d.c0 AS c0, d.c1 AS c1, SUM(d.c3) AS sumc3,\r\n       AVG(d.c4) AS avgc4, COUNT(DISTINCT d.c2) AS dcountc2\r\nFROM default AS d\r\nWHERE d.c0 &gt; 0 AND d.c1 &gt;= 10 AND ANY v IN d.a1 SATISFIES v.id = 3 END\r\nGROUP BY d.c0, d.c1\r\nORDER BY d.c0, d.c1\r\nOFFSET 1\r\nLIMIT 2;\r\n<\/pre>\n<p><span style=\"font-weight: 400\">Required Index:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">CREATE INDEX idxad1 ON default(c0, c1, DISTINCT ARRAY v.id FOR v IN a1 END, c2, c3, c4);<\/pre>\n<p><span style=\"font-weight: 400\">The query has GROUP BY and multiple aggregates, some of aggregates has DISTINCT modifier. The query predicate has ANY clause and query can be covered by array index index idxad1. The predicate (d.c0 &gt; 0 AND d,c11 &gt;= 10 AND ANY v IN d.a1 SATISFIES <\/span><span style=\"font-weight: 400\">v.id <\/span><span style=\"font-weight: 400\">= 3 END ) can be converted into exact range scans and passed to index scan. For array index Indexer maintain separate element for each array index key, in order to use index group and aggregation the SATISFIES predicate must have a single equality predicate and the array index key must have DISTINCT modifier. Therefore index and query combination is suitable to handle Index grouping and aggregations.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4945 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.38-PM-300x83.png\" alt=\"\" width=\"886\" height=\"245\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.38-PM-300x83.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.38-PM-1024x284.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.38-PM-768x213.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.38-PM-20x6.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.38-PM-1320x366.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.05.38-PM.png 1494w\" sizes=\"auto, (max-width: 886px) 100vw, 886px\" \/><\/p>\n<p><span style=\"font-weight: 400\">This example is similar to example 1 except it uses an array index. The above graphical execution tree shows index scan (IndexScan3) performing scan, index grouping aggregations, order, offset and limit. The results from the \u00a0index scan are projected.<\/span><\/p>\n<h3><span style=\"font-weight: 400\">Example 5: Group and Aggregation of UNNEST Operation<\/span><\/h3>\n<p><span style=\"font-weight: 400\">Let consider the following query and index:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">SELECT v.id AS id, d.c0 AS c0, SUM(v.id) AS sumid,\r\n       AVG(d.c1) AS avgc1\r\nFROM default AS d UNNEST d.a1 AS v\r\nWHERE v.id &gt; 0\r\nGROUP BY v.id, d.c0;<\/pre>\n<p><span style=\"font-weight: 400\">Required Index:<\/span><\/p>\n<pre class=\"theme:github lang:mysql decode:true\">CREATE INDEX idxaa1 ON default(ALL ARRAY v.id FOR v IN a1 END, c0, c1);<\/pre>\n<p><span style=\"font-weight: 400\">The query has GROUP BY and multiple aggregates. The query has UNNEST on array d.a1 and have predicate on the array key (v.id &gt; 0). \u00a0The index idxaa1 qualifies query (For Unnest to use Array index for Index scan the array index must be leading key and array variable in the index definition must match with UNNEST alias). The predicate (v.id &gt; 0) can be converted into exact range scans and passed to index scan. \u00a0Therefore index and query combination is suitable to handle Index grouping and aggregations.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-4944 aligncenter\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2018\/04\/Screen-Shot-2018-04-03-at-7.06.17-PM-300x101.png\" alt=\"\" width=\"864\" height=\"291\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.06.17-PM-300x101.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.06.17-PM-1024x345.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.06.17-PM-768x259.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.06.17-PM-20x7.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.06.17-PM-1320x445.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-7.06.17-PM.png 1526w\" sizes=\"auto, (max-width: 864px) 100vw, 864px\" \/><\/p>\n<p><span style=\"font-weight: 400\">The above graphical execution tree shows index scan (IndexScan3) performing scan, index grouping aggregations. The results from the \u00a0index scan are projected. The UNNEST is special type of JOIN between parent and each array element. <\/span><span style=\"font-weight: 400\">Therefore, \u00a0the UNNEST repeats the parent document fields (d.c0, d.c1) and the <\/span><span style=\"font-weight: 400\">d.c0, dc.1<\/span><span style=\"font-weight: 400\"> reference would have duplicates compared to the original <\/span><span style=\"font-weight: 400\">d<\/span><span style=\"font-weight: 400\"> documents (Need to aware this while using in SUM(), AVG()).<\/span><\/p>\n<h2><b>Rules for Index Grouping and Aggregation<\/b><\/h2>\n<p><span style=\"font-weight: 400\"> \u00a0<\/span><span style=\"font-weight: 400\">\u00a0The Index grouping and aggregation are per query block, and decision on whether or not use index grouping\/aggregation is made only after index selection process.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Query block should not contain Joins, NEST, SUBqueries.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Query block must be covered by singline index.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Query block should not contain ARRAY_AGG()<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Query block can&#8217;t be correlated<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">All the predicates must be exactly translated into range scans.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">GROUP BY, Aggregate expressions can&#8217;t reference any subquires, named parameters, positional parameters.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">GROUP BY keys, aggregate expressions can be index keys, document key, expression on index keys, or expression on document key<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Index needs to be able to do grouping and aggregation on all the aggregates in query block otherwise no index aggregation. (i.e. ALL or None)<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Aggregate contain DISTINCT modifier<\/span>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"> The group keys must exactly match with leading index keys (if the query contains equality predicate on the index key, then it assumes this index key is implicitly included in GROUP keys if not already present). <\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">The aggregate expression must be on one of the n+1 leading index keys (n represent number of group keys).<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"> In case of partition index the partition keys must exactly match with group keys.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h2><b>Summary<\/b><\/h2>\n<p><span style=\"font-weight: 400\">When you analyze the explain plan, correlate the predicates in the explain to the spans and make sure all the predicate exactly translated to range scans and query is covered. Ensure query using index grouping and aggregations, and if possible query using full aggregations from indexer by adjusting index keys for better performance.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Couchbase N1QL is a modern query processing engine designed to provide aggregate SQL for JSON by index on distributed data with a flexible data model. Modern databases are deployed on massive clusters. Using JSON provides a flexible data mode. N1QL [&hellip;]<\/p>\n","protected":false},"author":7586,"featured_media":4938,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[1814,1816,9417,1812],"tags":[2173,1572,2042,1505,1261,1725],"ppma_author":[9067],"class_list":["post-4935","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-application-design","category-couchbase-server","category-performance","category-n1ql-query","tag-aggregates","tag-database","tag-grouping","tag-index","tag-json","tag-nosql-database"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v25.8 (Yoast SEO v25.8) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL Group by Index | Aggregate Index SQL | Couchbase<\/title>\n<meta name=\"description\" content=\"Index grouping and aggregations can improve query performance magnitude and reduce the latencies to make SQL for JSON query processing easier.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Understanding Index Grouping And Aggregation in Couchbase N1QL Query\" \/>\n<meta property=\"og:description\" content=\"Index grouping and aggregations can improve query performance magnitude and reduce the latencies to make SQL for JSON query processing easier.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/\" \/>\n<meta property=\"og:site_name\" content=\"The Couchbase Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-04-04T02:31:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-06-14T06:43:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1388\" \/>\n\t<meta property=\"og:image:height\" content=\"744\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Sitaram Vemulapalli\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sitaram Vemulapalli\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"21 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/\"},\"author\":{\"name\":\"Sitaram Vemulapalli\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/ead1d6aa77984d26b03977adca6f174f\"},\"headline\":\"Understanding Index Grouping And Aggregation in Couchbase N1QL Query\",\"datePublished\":\"2018-04-04T02:31:02+00:00\",\"dateModified\":\"2025-06-14T06:43:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/\"},\"wordCount\":2976,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png\",\"keywords\":[\"aggregates\",\"database\",\"grouping\",\"Index\",\"JSON\",\"NoSQL Database\"],\"articleSection\":[\"Application Design\",\"Couchbase Server\",\"High Performance\",\"SQL++ \/ N1QL Query\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/\",\"name\":\"SQL Group by Index | Aggregate Index SQL | Couchbase\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png\",\"datePublished\":\"2018-04-04T02:31:02+00:00\",\"dateModified\":\"2025-06-14T06:43:08+00:00\",\"description\":\"Index grouping and aggregations can improve query performance magnitude and reduce the latencies to make SQL for JSON query processing easier.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#primaryimage\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png\",\"width\":1388,\"height\":744},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.couchbase.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Understanding Index Grouping And Aggregation in Couchbase N1QL Query\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"name\":\"The Couchbase Blog\",\"description\":\"Couchbase, the NoSQL Database\",\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\",\"name\":\"The Couchbase Blog\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png\",\"width\":218,\"height\":34,\"caption\":\"The Couchbase Blog\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/ead1d6aa77984d26b03977adca6f174f\",\"name\":\"Sitaram Vemulapalli\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/988725d1a67be1227a105a4071c69e2b\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/67edc83f4123b955cf7c20e1c509799b94a1fb14d1aedb3c226b998081714da3?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/67edc83f4123b955cf7c20e1c509799b94a1fb14d1aedb3c226b998081714da3?s=96&d=mm&r=g\",\"caption\":\"Sitaram Vemulapalli\"},\"description\":\"Sitaram Vemulapalli is a Principal Software Engineer at Couchbase. Prior to Couchbase, he served as an architect for IBM Informix SQL and has more than 20 years experience in database design and development. Sitaram holds a master's degree in system science and automation from the Indian Institute of Science, India.\",\"url\":\"https:\/\/www.couchbase.com\/blog\/author\/sitaram-vemulapallicouchbase-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Group by Index | Aggregate Index SQL | Couchbase","description":"Index grouping and aggregations can improve query performance magnitude and reduce the latencies to make SQL for JSON query processing easier.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/","og_locale":"en_US","og_type":"article","og_title":"Understanding Index Grouping And Aggregation in Couchbase N1QL Query","og_description":"Index grouping and aggregations can improve query performance magnitude and reduce the latencies to make SQL for JSON query processing easier.","og_url":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/","og_site_name":"The Couchbase Blog","article_published_time":"2018-04-04T02:31:02+00:00","article_modified_time":"2025-06-14T06:43:08+00:00","og_image":[{"width":1388,"height":744,"url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png","type":"image\/png"}],"author":"Sitaram Vemulapalli","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Sitaram Vemulapalli","Est. reading time":"21 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#article","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/"},"author":{"name":"Sitaram Vemulapalli","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/ead1d6aa77984d26b03977adca6f174f"},"headline":"Understanding Index Grouping And Aggregation in Couchbase N1QL Query","datePublished":"2018-04-04T02:31:02+00:00","dateModified":"2025-06-14T06:43:08+00:00","mainEntityOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/"},"wordCount":2976,"commentCount":0,"publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png","keywords":["aggregates","database","grouping","Index","JSON","NoSQL Database"],"articleSection":["Application Design","Couchbase Server","High Performance","SQL++ \/ N1QL Query"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/","url":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/","name":"SQL Group by Index | Aggregate Index SQL | Couchbase","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#primaryimage"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png","datePublished":"2018-04-04T02:31:02+00:00","dateModified":"2025-06-14T06:43:08+00:00","description":"Index grouping and aggregations can improve query performance magnitude and reduce the latencies to make SQL for JSON query processing easier.","breadcrumb":{"@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#primaryimage","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2018\/04\/Screen-Shot-2018-04-03-at-10.08.36-AM.png","width":1388,"height":744},{"@type":"BreadcrumbList","@id":"https:\/\/www.couchbase.com\/blog\/understanding-index-grouping-aggregation-couchbase-n1ql-query\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.couchbase.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Understanding Index Grouping And Aggregation in Couchbase N1QL Query"}]},{"@type":"WebSite","@id":"https:\/\/www.couchbase.com\/blog\/#website","url":"https:\/\/www.couchbase.com\/blog\/","name":"The Couchbase Blog","description":"Couchbase, the NoSQL Database","publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.couchbase.com\/blog\/#organization","name":"The Couchbase Blog","url":"https:\/\/www.couchbase.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","width":218,"height":34,"caption":"The Couchbase Blog"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/ead1d6aa77984d26b03977adca6f174f","name":"Sitaram Vemulapalli","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/988725d1a67be1227a105a4071c69e2b","url":"https:\/\/secure.gravatar.com\/avatar\/67edc83f4123b955cf7c20e1c509799b94a1fb14d1aedb3c226b998081714da3?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/67edc83f4123b955cf7c20e1c509799b94a1fb14d1aedb3c226b998081714da3?s=96&d=mm&r=g","caption":"Sitaram Vemulapalli"},"description":"Sitaram Vemulapalli is a Principal Software Engineer at Couchbase. Prior to Couchbase, he served as an architect for IBM Informix SQL and has more than 20 years experience in database design and development. Sitaram holds a master's degree in system science and automation from the Indian Institute of Science, India.","url":"https:\/\/www.couchbase.com\/blog\/author\/sitaram-vemulapallicouchbase-com\/"}]}},"authors":[{"term_id":9067,"user_id":7586,"is_guest":0,"slug":"sitaram-vemulapallicouchbase-com","display_name":"Sitaram Vemulapalli","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/67edc83f4123b955cf7c20e1c509799b94a1fb14d1aedb3c226b998081714da3?s=96&d=mm&r=g","author_category":"","last_name":"Vemulapalli","first_name":"Sitaram","job_title":"","user_url":"","description":"Sitaram Vemulapalli is a Principal Software Engineer at Couchbase. Prior to Couchbase, he served as an architect for IBM Informix SQL and has more than 20 years experience in database design and development. Sitaram holds a master's degree in system science and automation from the Indian Institute of Science, India."}],"_links":{"self":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/4935","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/users\/7586"}],"replies":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/comments?post=4935"}],"version-history":[{"count":0,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/4935\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media\/4938"}],"wp:attachment":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media?parent=4935"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/categories?post=4935"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/tags?post=4935"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=4935"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}