title: ElasticSearch terms and cardinality performance with high cardinality fields
body: <h1>TL;DR</h1>
  
  <p>My ElasticSearch query takes forever compared to the same query on SQL Server.<br>
  Am I doing something wrong?  Is there any way to boost my query's performance?<br>
  <strong>Is it just one of those things RDBMS does better than NoSQL?</strong></p>
  
  <h1>Premise</h1>
  
  <p>Let's say I have a business that takes orders and delivers requested items.  </p>
  
  <ul>
  <li>I would like to know the average amount of unique items per order.  </li>
  <li>My orders data is arranged per item ordered - each order has one or more records containing Order ID, Item ID and more.</li>
  <li>I have a one node setup for development purposes</li>
  <li>The results (performance-wise) are the same whether I have 4 GB heap space (on a 12 GB machine) or 16 GB heap space (on a 32 GB machine)</li>
  <li>The index has billions of records, but the query filters it to about 300,000 records</li>
  <li>The Order and Item ID's are of type <em>keyword</em> (textual by nature) and I have no way to change that.</li>
  <li>In this particular case, the average unique item count is 1.65  - with many orders containing only one unique item, others contain 2 and a few contain up to 25 unique items.  </li>
  </ul>
  
  <h1>The Problem</h1>
  
  <p>Using ElasticSearch, I would have to use <em>Terms Aggregation</em> to group documents by order ID, <em>Cardinality Aggregation</em> to get unique item count, and <em>Average Bucket</em> aggregation to get the average item count per order.</p>
  
  <p>This takes about 23 seconds on both my setups. Same query takes less than 2 seconds with the same dataset on SQL Server.</p>
  
  <h1>Additional Information</h1>
  
  <h2>ElasticSearch Query</h2>
  
  <pre><code>{
     "size":0,
     "query":{
        "bool":{
           "filter":[
              {
                 ...
              }
           ]
        }
     },
     "aggs":{
        "OrdersBucket":{
           "terms":{
              "field":"orderID",
              "execution_hint":"global_ordinals_hash",
              "size":10000000
           },
           "aggs":{
              "UniqueItems":{
                 "cardinality":{
                    "field":"itemID"
                 }
              }
           }
        },
        "AverageItemCount":{
           "avg_bucket":{
              "buckets_path":"OrdersBucket&gt;UniqueItems"
           }
        }
     }
  }
  </code></pre>
  
  <p>At first my query generated <em>OutOfMemoryException</em> which brought my server down.<br>
  Issuing the same request on my higher ram setup yielded the following circuit breaker:  </p>
  
  <blockquote>
  <pre><code>[request] Data too large, data for [&lt;reused_arrays&gt;] would be
  [14383258184/13.3gb], which is larger than the limit of
  [10287002419/9.5gb]
  </code></pre>
  </blockquote>
  
  <p>ElasticSearch github has several (currently) open issues on this matter:</p>
  
  <p><a href="https://github.com/elastic/elasticsearch/issues/15892" rel="nofollow noreferrer">Cardinality aggregation should not reserve a fixed amount of memory per bucket #15892</a>  </p>
  
  <p><a href="https://github.com/elastic/elasticsearch/issues/24788" rel="nofollow noreferrer">global_ordinals execution mode for the terms aggregation has an adversarially impact on children aggregations that expect dense buckets #24788</a>  </p>
  
  <p><a href="https://github.com/elastic/elasticsearch/issues/24359" rel="nofollow noreferrer">Heap Explosion on even small cardinality queries in ES 5.3.1 / Kibana 5.3.1 #24359</a>  </p>
  
  <p>All of which led me to use execution hint "global_ordinals_hash" which allowed the query to complete successfully (albeit taking it's time..)</p>
  
  <h2>Analogous SQL Query</h2>
  
  <pre><code>SELECT AVG(CAST(uniqueCount.amount AS FLOAT)) FROM 
  (   SELECT o.OrderID, COUNT(DISTINCT o.ItemID) AS amount 
      FROM Orders o
      WHERE ...
      GROUP BY o.OrderID 
  ) uniqueCount
  </code></pre>
  
  <p>And this, as I've said, is very very fast.</p>
  
  <h2>orderID field mapping</h2>
  
  <pre><code>{
     "orderID":{
        "full_name":"orderID",
        "mapping":{
           "orderID":{
              "type":"keyword",
              "boost":1,
              "index":true,
              "store":false,
              "doc_values":true,
              "term_vector":"no",
              "norms":false,
              "index_options":"docs",
              "eager_global_ordinals":true,
              "similarity":"BM25",
              "fields":{
                 "autocomplete":{
                    "type":"text",
                    "boost":1,
                    "index":true,
                    "store":false,
                    "doc_values":false,
                    "term_vector":"no",
                    "norms":true,
                    "index_options":"positions",
                    "eager_global_ordinals":false,
                    "similarity":"BM25",
                    "analyzer":"autocomplete",
                    "search_analyzer":"standard",
                    "search_quote_analyzer":"standard",
                    "include_in_all":true,
                    "position_increment_gap":-1,
                    "fielddata":false
                 }
              },
              "null_value":null,
              "include_in_all":true,
              "ignore_above":2147483647,
              "normalizer":null
           }
        }
     }
  }
  </code></pre>
  
  <p>I have set <em>eager_global_ordinals</em> trying to boost performance, but to no avail.</p>
  
  <h2>Sample Document</h2>
  
  <pre><code>{
              "_index": "81cec0acbca6423aa3c2feed5dbccd98",
              "_type": "order",
              "_id": "AVwpLZ7GK9DJVcpvrzss",
              "_score": 0,
              "_source": {
          ...
                 "orderID": "904044A",
                 "itemID": "23KN",
          ...
              }
  }
  </code></pre>
  
  <p>Irrelevant fields removed for brevity and undisclosable content</p>
  
  <h2>Sample Output</h2>
  
  <pre><code>{
     "OrdersBucket":{
        "doc_count_error_upper_bound":0,
        "sum_other_doc_count":0,
        "buckets":[
           {
              "key":"910117A",
              "doc_count":16,
              "UniqueItems":{
                 "value":16
              }
           },
           {
              "key":"910966A",
              "doc_count":16,
              "UniqueItems":{
                 "value":16
              }
           },
          ...
           {
              "key":"912815A",
              "doc_count":1,
              "UniqueItems":{
                 "value":1
              }
           },
           {
              "key":"912816A",
              "doc_count":1,
              "UniqueItems":{
                 "value":1
              }
           }
        ]
     },
     "AverageItemCount":{
        "value":1.3975020363833832
     }
  }
  </code></pre>
  
  <p><strong>Any help will be much appreciated :)</strong></p>