{"id":8955,"date":"2020-07-29T11:02:28","date_gmt":"2020-07-29T18:02:28","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=8955"},"modified":"2023-05-24T23:55:10","modified_gmt":"2023-05-25T06:55:10","slug":"analytics-query-optimization-lets-demystify-listify","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/","title":{"rendered":"Analytics Query Optimization &#8211; Let&#8217;s Demystify Listify"},"content":{"rendered":"<p>Couchbase Analytics is designed to support ad hoc queries by leveraging efficient parallel processing. This is particularly helpful when the results of one query lead you to seek answers from yet another another query. (For analysts, in other words, all the time!) As with any data platform, these secondary queries can sometimes result in run times significantly different from the first. We recently worked with a customer who saw a noticeable drop in performance after making a small change in his query. The process we went through to isolate and resolve the issue is outlined below.<\/p>\n<h4>The Data<\/h4>\n<p>As always, the first step in understanding a query is to become familiar with the underlying data. In this case, the data is comprised of JSON documents, each of which is a description of client charges for services. Charges for each session are embedded within an array:<\/p>\n<pre class=\"lang:js decode:true\">{\r\n\"customer\": 1000002,\r\n\"dtype\": \"session\",\r\n\"sessionCharges\": [\r\n    {\r\n    \"qty\": 1,\r\n    \"revCode\": \"B\",\r\n    \"svc\": 44284,\r\n    \"amt\": 40,\r\n    \"svcDate\": 1592889276\r\n    },\r\n    {\r\n    \"qty\": 1,\r\n    \"revCode\": \"A\",\r\n    \"svc\": 28078,\r\n    \"amt\": 24,\r\n    \"svcDate\": 1592891076\r\n    },\r\n    {\r\n    \"qty\": 1,\r\n    \"revCode\": \"B\",\r\n    \"svc\": 38968,\r\n    \"amt\": 26,\r\n    \"svcDate\": 1592893416\r\n    }\r\n  ]\r\n}\r\n\r\n<\/pre>\n<p>A couple of notes on the design of the data model:<\/p>\n<ul>\n<li>They were smart about the fieldnames, which are long enough to be descriptive, but short enough to save space. The fieldnames within the array could easily have been written as &#8220;quantity&#8221;, &#8220;revenueCode&#8221;, &#8220;service&#8221;, &#8220;amount&#8221;, &#8220;serviceDate&#8221;. Across 100 million instances, however, this would have required an additional 3.73GB of space before compression.<\/li>\n<li>They used epoch dates, without milliseconds. If these dates were stored in ISO 8601 format (&#8220;2020-07- 24T18:17:49&#8221; or with milliseconds &#8220;2020-07-24T18:17:49.000&#8221;) they would require double the storage footprint.<\/li>\n<\/ul>\n<h4>Initial Query<\/h4>\n<p>The initial query was designed to retrieve the highest revenue customer\/service combination within each of the revenue code categories:<\/p>\n<pre class=\"lang:tsql decode:true\">select latestService, customer, revCode, service,\r\n       round(totalDollar) dollars, totalQuant from (\r\nselect s.customer,\r\n       c.svc as service,\r\n       c.revCode,\r\n       sum(c.amt) as totalDollar,\r\n       sum(c.qty) as totalQuant,\r\n       millis_to_str(max(c.svcDate)*1000, '1111-11-11') as latestService\r\nfrom sessions s unnest s.sessionCharges c\r\ngroup by s.customer, c.revCode, c.svc \r\n) a\r\nwhere rank() over (partition by revCode order by totalDollar desc) = 1\r\norder by revCode, totalDollar;\r\n<\/pre>\n<p>A few notes on the query design:<\/p>\n<ul>\n<li>The array contents are accessed by the &#8220;unnest&#8221; verb. This allows the values to be aggregated and returned as a flattened record.<\/li>\n<li>The latest service date is converted from epoch to human-readable date via the millis_to_str() function.<\/li>\n<li>The window function &#8220;rank() over partition&#8221; is used in the where clause of the outer query as an efficient means of returning only the top value within each revenue code.<\/li>\n<li>The round() function is cosmetic, but commonly used in reports where the pennies aren&#8217;t significant.<\/li>\n<\/ul>\n<h4>The Slowdown<\/h4>\n<p>Problems arose when a volume discount computation (20% off when the cumulative quantity of a service is 10 or more) was added. This new query took six times as long to run!<\/p>\n<pre class=\"lang:tsql decode:true\">select latestService, customer, revCode, service,\r\n       round(totalDollar) dollars, round(adjDollar) dollarsAdj, totalQuant from (\r\nselect s.customer,\r\n       c.svc as service,\r\n       c.revCode,\r\n       sum(c.amt) as totalDollar,\r\n       case\r\n         when sum(c.qty) &lt; 10 then sum(c.amt)\r\n         else sum(c.amt) * .8\r\n       end as adjDollar,\r\n       sum(c.qty) as totalQuant,\r\n       millis_to_str(max(c.svcDate)*1000, '1111-11-11') as latestService\r\nfrom sessions s unnest s.sessionCharges c\r\ngroup by s.customer, c.svc, c.revCode\r\n) a\r\nwhere rank() over (partition by revCode order by totalDollar desc) = 1\r\norder by revCode, totalDollar;<\/pre>\n<p>How can we track down the cause of this?<\/p>\n<h4>Explain Yourself<\/h4>\n<p>The Analytics query console makes it easy to inspect the execution plan for any query, simply by clicking the &#8220;Plan&#8221; button. Figure (1) below shows the query plan for this query. Don&#8217;t worry about the minuscule scale of the diagram; you can use the controls to expand and contract it. You can also click on each individual step for detailed information.<\/p>\n<div id=\"attachment_8956\" style=\"width: 310px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-8956\" class=\"size-medium wp-image-8956\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/07\/Figure1-300x117.png\" alt=\"Figure 1\" width=\"300\" height=\"117\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/Figure1-300x117.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/Figure1-1024x398.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/Figure1-768x299.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/Figure1-1536x597.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/Figure1-20x8.png 20w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/Figure1-1320x513.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/Figure1.png 1954w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><p id=\"caption-attachment-8956\" class=\"wp-caption-text\">Figure 1<\/p><\/div>\n<p>If you prefer, you can also click the &#8220;Plan Text&#8221; button to generate a JSON document describing the plan:<\/p>\n<pre class=\"lang:js decode:true\">{\r\n  \"operator\": \"distribute-result\",\r\n  \"expressions\": [\r\n    \"$$157\"\r\n  ],\r\n  \"operatorId\": \"1.1\",\r\n  \"physical-operator\": \"DISTRIBUTE_RESULT\",\r\n  \"execution-mode\": \"PARTITIONED\",\r\n  \"inputs\": [\r\n    {\r\n      \"operator\": \"exchange\",\r\n      \"operatorId\": \"1.2\",\r\n      \"physical-operator\": \"ONE_TO_ONE_EXCHANGE\",\r\n      \"execution-mode\": \"PARTITIONED\",\r\n      \"inputs\": [\r\n        {\r\n          \"operator\": \"project\",\r\n          \"variables\": [\r\n            \"$$157\"\r\n          ],\r\n          \"operatorId\": \"1.3\",\r\n          \"physical-operator\": \"STREAM_PROJECT\",\r\n          \"execution-mode\": \"PARTITIONED\",\r\n          \"inputs\": [\r\n            {\r\n              \"operator\": \"assign\",\r\n              \"variables\": [\r\n                \"$$157\"\r\n              ], ...\r\netc.\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>This can become very deeply nested (and somewhat difficult to read) as queries become more complex.\u00a0 A trick our engineers often use is to prepend the command &#8220;explain text&#8221; in front of the query:<\/p>\n<pre class=\"lang:tsql decode:true\">explain text\r\nselect ...\r\netc.;<\/pre>\n<p>This provides a detailed description of the plan without the squiggly brackets:<\/p>\n<pre class=\"lang:default decode:true\">distribute result [$$157]\r\n-- DISTRIBUTE_RESULT  |PARTITIONED|\r\n  exchange\r\n  -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|\r\n    project ([$$157])\r\n    -- STREAM_PROJECT  |PARTITIONED|\r\n      assign [$$157] &lt;- [{\"latestService\": millis-to-local(numeric-multiply($$168, 1000), \"1111-11-11\"), \"customer\": $$customer, \"revCode\": $$revCode, \"service\": $$svc, \"dollars\": round($$166), \"dollarsAdj\": round($$126), \"totalQuant\": $$167}]\r\n      -- ASSIGN  |PARTITIONED|\r\n        exchange\r\n        -- SORT_MERGE_EXCHANGE [$$revCode(ASC), $$166(ASC) ]  |PARTITIONED|\r\n          order (ASC, $$revCode) (ASC, $$166)\r\n          -- STABLE_SORT [$$revCode(ASC), $$166(ASC)]  |PARTITIONED|\r\n            exchange\r\n            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|\r\netc.<\/pre>\n<p>This is what we will use to search for clues.<\/p>\n<h4>Let&#8217;s Make a List<\/h4>\n<p>We&#8217;re pretty sure that the slowdown was introduced when we added the CASE statement defining field adjDollars, so let&#8217;s start by searching for case logic. We find it on line 33:<\/p>\n<pre class=\"lang:default decode:true \">assign [$$125] &lt;- [switch-case(TRUE, lt($$165, 10), $$117, $$124)]\r\n<\/pre>\n<p>As we follow the logic in the lines following, we see the execution plan continuing to assign and exchange variables in a partitioned fashion. On line 44, however we see the following:<\/p>\n<pre class=\"lang:default decode:true\">aggregate [$$117] &lt;- [listify($$196)]\r\n<\/pre>\n<p>In cases where query performance or memory consumption are a concern, the &#8220;listify&#8221; function can be a red flag.\u00a0 (One engineer jokingly calls it &#8220;an evil function&#8221;.)\u00a0 It has its uses, particularly in the internal transformation of array aggregates, but it can also be used by the execution plan as the function of last resort.\u00a0 In our case, we see the variable $$117 is the result of this listify process. This $$117, we can see in the switch-case above, is associated with the sum(c.amt) object in the query. So let&#8217;s try to move the computation of the sums out of the interior switch case and make them part of a LET:<\/p>\n<pre class=\"lang:tsql decode:true \">explain text\r\nselect latestService, customer, revCode, service,\r\n       round(totalDollar) dollars, round(adjDollar) dollarsAdj, totalQuant from (\r\nselect s.customer,\r\n       c.svc as service,\r\n       c.revCode,\r\n       totalDollar,\r\n       case\r\n         when totalQuant &lt; 10 then totalDollar\r\n         else totalDollar * .8\r\n       end as adjDollar,\r\n       totalQuant,\r\n       millis_to_str(max(c.svcDate)*1000, '1111-11-11') as latestService\r\nfrom sessions s unnest s.sessionCharges c\r\ngroup by s.customer, c.svc, c.revCode\r\nlet totalQuant = sum(c.qty), totalDollar = sum(c.amt)\r\n) a\r\nwhere rank() over (partition by revCode order by totalDollar desc) = 1\r\norder by revCode, totalDollar;\r\n<\/pre>\n<p>Once we have done this and have re-examined explain plan, we see that its length is cut in half, and that the execution process no longer includes listify functions. Running the query, then, should show much better performance, which in fact it does. Problem solved!<\/p>\n<p>One final note: Our engineers are always looking for ways to improve our query optimizer. If in the course of optimizing one of your queries you do find in the explain text a rogue &#8220;listify&#8221; function (which is already rare, and getting rarer), please open a support ticket at support.couchbase.com, uploading your explain plan so they can have a look at it.\u00a0 In fact, directly as a result of their work with the customer mentioned above, this particular example (switch-case used with an aggregate function) will be, as of the 6.6 release of Couchbase, automatically rewritten by the optimizer.<\/p>\n<h4>Docs and Resources<\/h4>\n<p>The Couchbase docs site contains links to the N1QL for Analytics Language reference: <a href=\"https:\/\/docs.couchbase.com\/server\/current\/analytics\/introduction.html\">https:\/\/docs.couchbase.com\/server\/current\/analytics\/introduction.html<\/a>. You can also find a link there to a tutorial on the language and to the book by Don Chamberlin (co-inventor of the SQL language): <a href=\"https:\/\/www.couchbase.com\/sql-plus-plus-for-sql-users\/\">https:\/\/www.couchbase.com\/sql-plus-plus-for-sql-users<\/a>.<\/p>\n<p>&nbsp;<\/p>\n<p><em>Thanks very much to Dmitry Lychagin and Mike Carey for their careful review of this post.<\/em><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Couchbase Analytics is designed to support ad hoc queries by leveraging efficient parallel processing. This is particularly helpful when the results of one query lead you to seek answers from yet another another query. (For analysts, in other words, all [&hellip;]<\/p>\n","protected":false},"author":41576,"featured_media":8958,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[2294],"tags":[],"ppma_author":[9066],"class_list":["post-8955","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-analytics"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v25.7.1 (Yoast SEO v25.7) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Analytics Query Optimization - Let&#039;s Demystify Listify - The Couchbase Blog<\/title>\n<meta name=\"description\" content=\"Inspecting the execution plan via &quot;explain text&quot; can help you design your queries to optimize the performance of your Analytics service on Couchbase.\" \/>\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\/analytics-query-optimization-lets-demystify-listify\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Analytics Query Optimization - Let&#039;s Demystify Listify\" \/>\n<meta property=\"og:description\" content=\"Inspecting the execution plan via &quot;explain text&quot; can help you design your queries to optimize the performance of your Analytics service on Couchbase.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/\" \/>\n<meta property=\"og:site_name\" content=\"The Couchbase Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-07-29T18:02:28+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-05-25T06:55:10+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/07\/BlogImage.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"666\" \/>\n\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Peter Reale\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Peter Reale\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/\"},\"author\":{\"name\":\"Peter Reale\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/391cf559b28ca6b4c1660a1ce283752c\"},\"headline\":\"Analytics Query Optimization &#8211; Let&#8217;s Demystify Listify\",\"datePublished\":\"2020-07-29T18:02:28+00:00\",\"dateModified\":\"2023-05-25T06:55:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/\"},\"wordCount\":945,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/BlogImage.jpg\",\"articleSection\":[\"Couchbase Analytics\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/\",\"name\":\"Analytics Query Optimization - Let's Demystify Listify - The Couchbase Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/BlogImage.jpg\",\"datePublished\":\"2020-07-29T18:02:28+00:00\",\"dateModified\":\"2023-05-25T06:55:10+00:00\",\"description\":\"Inspecting the execution plan via \\\"explain text\\\" can help you design your queries to optimize the performance of your Analytics service on Couchbase.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#primaryimage\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/BlogImage.jpg\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/BlogImage.jpg\",\"width\":666,\"height\":350},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.couchbase.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Analytics Query Optimization &#8211; Let&#8217;s Demystify Listify\"}]},{\"@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\/391cf559b28ca6b4c1660a1ce283752c\",\"name\":\"Peter Reale\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/ec988e11a3058e02331c83244e993ea2\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/19012c1e7433fcde0d634a14a0f76610c40ce876b6c5a04b23d43c2181301761?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/19012c1e7433fcde0d634a14a0f76610c40ce876b6c5a04b23d43c2181301761?s=96&d=mm&r=g\",\"caption\":\"Peter Reale\"},\"description\":\"Peter Reale is a Senior Solutions Engineer at Couchbase and has been in the data business since 1984. He is based in Los Angeles.\",\"url\":\"https:\/\/www.couchbase.com\/blog\/author\/peter-reale\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Analytics Query Optimization - Let's Demystify Listify - The Couchbase Blog","description":"Inspecting the execution plan via \"explain text\" can help you design your queries to optimize the performance of your Analytics service on Couchbase.","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\/analytics-query-optimization-lets-demystify-listify\/","og_locale":"en_US","og_type":"article","og_title":"Analytics Query Optimization - Let's Demystify Listify","og_description":"Inspecting the execution plan via \"explain text\" can help you design your queries to optimize the performance of your Analytics service on Couchbase.","og_url":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/","og_site_name":"The Couchbase Blog","article_published_time":"2020-07-29T18:02:28+00:00","article_modified_time":"2023-05-25T06:55:10+00:00","og_image":[{"width":666,"height":350,"url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2020\/07\/BlogImage.jpg","type":"image\/jpeg"}],"author":"Peter Reale","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Peter Reale","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#article","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/"},"author":{"name":"Peter Reale","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/391cf559b28ca6b4c1660a1ce283752c"},"headline":"Analytics Query Optimization &#8211; Let&#8217;s Demystify Listify","datePublished":"2020-07-29T18:02:28+00:00","dateModified":"2023-05-25T06:55:10+00:00","mainEntityOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/"},"wordCount":945,"commentCount":0,"publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/BlogImage.jpg","articleSection":["Couchbase Analytics"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/","url":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/","name":"Analytics Query Optimization - Let's Demystify Listify - The Couchbase Blog","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#primaryimage"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/BlogImage.jpg","datePublished":"2020-07-29T18:02:28+00:00","dateModified":"2023-05-25T06:55:10+00:00","description":"Inspecting the execution plan via \"explain text\" can help you design your queries to optimize the performance of your Analytics service on Couchbase.","breadcrumb":{"@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#primaryimage","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/BlogImage.jpg","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2020\/07\/BlogImage.jpg","width":666,"height":350},{"@type":"BreadcrumbList","@id":"https:\/\/www.couchbase.com\/blog\/analytics-query-optimization-lets-demystify-listify\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.couchbase.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Analytics Query Optimization &#8211; Let&#8217;s Demystify Listify"}]},{"@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\/391cf559b28ca6b4c1660a1ce283752c","name":"Peter Reale","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/ec988e11a3058e02331c83244e993ea2","url":"https:\/\/secure.gravatar.com\/avatar\/19012c1e7433fcde0d634a14a0f76610c40ce876b6c5a04b23d43c2181301761?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/19012c1e7433fcde0d634a14a0f76610c40ce876b6c5a04b23d43c2181301761?s=96&d=mm&r=g","caption":"Peter Reale"},"description":"Peter Reale is a Senior Solutions Engineer at Couchbase and has been in the data business since 1984. He is based in Los Angeles.","url":"https:\/\/www.couchbase.com\/blog\/author\/peter-reale\/"}]}},"authors":[{"term_id":9066,"user_id":41576,"is_guest":0,"slug":"peter-reale","display_name":"Peter Reale","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/19012c1e7433fcde0d634a14a0f76610c40ce876b6c5a04b23d43c2181301761?s=96&d=mm&r=g","author_category":"","last_name":"Reale, Senior Solutions Engineer, Couchbase","first_name":"Peter","job_title":"","user_url":"","description":"Peter Reale is a Senior Solutions Engineer at Couchbase and has been in the data business since 1984.  He is based in Los Angeles."}],"_links":{"self":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/8955","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\/41576"}],"replies":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/comments?post=8955"}],"version-history":[{"count":0,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/8955\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media\/8958"}],"wp:attachment":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media?parent=8955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/categories?post=8955"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/tags?post=8955"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=8955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}