# Aggregation on nested document always takes 2-3 seconds?

**URL:** <https://discuss.elastic.co/t/aggregation-on-nested-document-always-takes-2-3-seconds/15787>\
**Category:** Elasticsearch\
**Created:** [February 13, 2014, 6:34pm UTC](https://discuss.elastic.co/t/aggregation-on-nested-document-always-takes-2-3-seconds/15787 "2014-02-13T18:34:01Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Luke\_Scott](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luke_scott/32/1810_2.png) [@Luke\_Scott](https://discuss.elastic.co/u/Luke_Scott)\
**Post date:** [February 13, 2014, 6:34pm UTC](https://discuss.elastic.co/t/aggregation-on-nested-document-always-takes-2-3-seconds/15787/1 "2014-02-13T18:34:01Z")

</div>

I have an index that uses 1 level of nested documents. When I run a query  
on it the result comes back in about 20-200 milliseconds. When I add a  
facet or an aggregation involving the nested documents the uncached  
response always takes 2-3 seconds, regardless of how many documents have  
been selected, even zero.

My map looks like this:

{  
"document": {  
"dynamic": "strict",  
"properties": {  
"account\_id": {  
"type": "long"  
},  
"data": {  
"type": "nested",  
"properties": {  
"key": {  
"type": "string",  
"index": "not\_analyzed"  
},  
"string": {  
"type": "string",  
"index": "not\_analyzed",  
"fields": {  
"token": {  
"type": "string"  
}  
}  
},  
"integer": {  
"type": "long"  
},  
"date": {  
"type": "date",  
"format": "dateOptionalTime"  
}  
}  
}  
}  
}  
}

There are 3.6 million documents in this index. My query looks like this:

{  
"query": {  
"bool":{  
"must":[  
{"term":{"account\_id": 1}},  
{  
"nested":{  
"path":"data",  
"query":{"term":{"key":"amount"}}  
}  
}  
]  
}  
}  
}

The result to the above query is 0 documents because account\_id 1 doesn't  
have any documents with a key of "amount". Uncached this returns in about  
10-150ms:

{  
"took": 9,  
"timed\_out": false,  
"\_shards": {  
"total": 5,  
"successful": 5,  
"failed": 0  
},  
"hits": {  
"total": 0,  
"max\_score": null,  
"hits": []  
}  
}

When I add an aggregation to the query:

{  
...  
"aggs" : {  
"report" : {  
"nested" : {  
"path" : "data"  
},  
"aggs" : {  
"amount" : {  
"filter" : {  
"query": {"term": {"key":"amount"}}  
},  
"aggs": {  
"sum": {  
"sum" : { "field" : "integer" }  
}  
}  
}  
}  
}  
}  
}

Uncached the query returns in about 2-3 seconds:

{  
"took": 2770,  
"timed\_out": false,  
"\_shards": {  
"total": 5,  
"successful": 5,  
"failed": 0  
},  
"hits": {  
"total": 0,  
"max\_score": null,  
"hits": []  
},  
"aggregations": {  
"report": {  
"doc\_count": 0,  
"amount": {  
"doc\_count": 0,  
"sum": {  
"value": 0  
}  
}  
}  
}  
}

If I run the same thing a second time (cached) it runs in 26 milliseconds.  
If I clear the cache and run it again it takes 2 seconds.

Why is this aggregation always taking 2-3 seconds, even though the query is  
returning 0 documents? The same thing happens with a statistical facet.

- 

Luke

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/a82323a6-9a81-436b-a2d2-cc26e918cb7c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/a82323a6-9a81-436b-a2d2-cc26e918cb7c%40googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [February 13, 2014, 10:49pm UTC](https://discuss.elastic.co/t/aggregation-on-nested-document-always-takes-2-3-seconds/15787/2 "2014-02-13T22:49:25Z")

</div>

Very likely this problem is not related to nested documents but to  
fielddata loading because of the "integer" field. Field data is a  
column-oriented view of the data that is, by default, lazily loaded from  
the inverted index on the first time that it is needed, and then cached  
until the end of life of the segment it belongs to. So only the first  
request that needs it is supposed to be slow.

It is possible to load field data eagerly[1] in order to make sure that  
field data loading is never going to impact response times. This way you  
should not get such slow response times on the first queries.

Another option would be to use doc values[2] that will store field data on  
disk instead of loading it from the inverted index. Since data will already  
be stored in a column-oriented way, there will be no need to uninvert data  
from the inverted index (which is costly and probably the reason of your  
slow queries).

[1]

> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.

[2]

> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.

On Thu, Feb 13, 2014 at 7:34 PM, Luke Scott [luke@visionlaunchers.com](mailto:luke@visionlaunchers.com)wrote:

> I have an index that uses 1 level of nested documents. When I run a query  
> on it the result comes back in about 20-200 milliseconds. When I add a  
> facet or an aggregation involving the nested documents the uncached  
> response always takes 2-3 seconds, regardless of how many documents have  
> been selected, even zero.
> 
> My map looks like this:
> 
> {  
> "document": {  
> "dynamic": "strict",  
> "properties": {  
> "account\_id": {  
> "type": "long"  
> },  
> "data": {  
> "type": "nested",  
> "properties": {  
> "key": {  
> "type": "string",  
> "index": "not\_analyzed"  
> },  
> "string": {  
> "type": "string",  
> "index": "not\_analyzed",  
> "fields": {  
> "token": {  
> "type": "string"  
> }  
> }  
> },  
> "integer": {  
> "type": "long"  
> },  
> "date": {  
> "type": "date",  
> "format": "dateOptionalTime"  
> }  
> }  
> }  
> }  
> }  
> }
> 
> There are 3.6 million documents in this index. My query looks like this:
> 
> {  
> "query": {  
> "bool":{  
> "must":[  
> {"term":{"account\_id": 1}},  
> {  
> "nested":{  
> "path":"data",  
> "query":{"term":{"key":"amount"}}  
> }  
> }  
> ]  
> }  
> }  
> }
> 
> The result to the above query is 0 documents because account\_id 1 doesn't  
> have any documents with a key of "amount". Uncached this returns in about  
> 10-150ms:
> 
> {  
> "took": 9,  
> "timed\_out": false,  
> "\_shards": {  
> "total": 5,  
> "successful": 5,  
> "failed": 0  
> },  
> "hits": {  
> "total": 0,  
> "max\_score": null,  
> "hits":   
> }  
> }
> 
> When I add an aggregation to the query:
> 
> {  
> ...  
> "aggs" : {  
> "report" : {  
> "nested" : {  
> "path" : "data"  
> },  
> "aggs" : {  
> "amount" : {  
> "filter" : {  
> "query": {"term": {"key":"amount"}}  
> },  
> "aggs": {  
> "sum": {  
> "sum" : { "field" : "integer" }  
> }  
> }  
> }  
> }  
> }  
> }  
> }
> 
> Uncached the query returns in about 2-3 seconds:
> 
> {  
> "took": 2770,  
> "timed\_out": false,  
> "\_shards": {  
> "total": 5,  
> "successful": 5,  
> "failed": 0  
> },  
> "hits": {  
> "total": 0,  
> "max\_score": null,  
> "hits":   
> },  
> "aggregations": {  
> "report": {  
> "doc\_count": 0,  
> "amount": {  
> "doc\_count": 0,  
> "sum": {  
> "value": 0  
> }  
> }  
> }  
> }  
> }
> 
> If I run the same thing a second time (cached) it runs in 26 milliseconds.  
> If I clear the cache and run it again it takes 2 seconds.
> 
> Why is this aggregation always taking 2-3 seconds, even though the query  
> is returning 0 documents? The same thing happens with a statistical facet.
> 
> - 
> 
> Luke
> 
> --  
> You received this message because you are subscribed to the Google Groups  
> "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an  
> email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
> To view this discussion on the web visit  
> [https://groups.google.com/d/msgid/elasticsearch/a82323a6-9a81-436b-a2d2-cc26e918cb7c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/a82323a6-9a81-436b-a2d2-cc26e918cb7c%40googlegroups.com)  
> .  
> For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

--  
Adrien Grand

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j5RsKkENHLXtCN-BEizgi6jwci\_Ed8SU%3Dsu5i8hGVHa0w%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j5RsKkENHLXtCN-BEizgi6jwci_Ed8SU%3Dsu5i8hGVHa0w%40mail.gmail.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 1:50am UTC](https://discuss.elastic.co/t/aggregation-on-nested-document-always-takes-2-3-seconds/15787/3 "2017-07-06T01:50:21Z")

</div>