# Performance of sorting on nested field **URL:** \ **Category:** Elasticsearch\ **Created:** [June 5, 2015, 5:24pm UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030 "2015-06-05T17:24:02Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![micpalmia](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/micpalmia/32/541_2.png) [@micpalmia](https://discuss.elastic.co/u/micpalmia)\ **Post date:** [June 5, 2015, 5:24pm UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030/1 "2015-06-05T17:24:02Z")
Hi all, on **Elasticsearch 1.5.0** , I'm dealing with ~50 million documents. My index contains an integer field and a nested object also containing an integer field. ``` { "example":{ "properties":{ "id":{ "type":"integer" }, "nested":{ "type":"nested", "properties":{ "q":{ "type":"string" }, "id":{ "type":"integer" } } } } } } ``` If I run a simple `match_all` query sorting it by (the external) id, the result is returned in around 200 ms. If on the other hand I try running the same, sorting it using `"sort": "nested.id"`, **the query takes more than 2 minutes to return**. I haven't specified any special treatment for the field data, but the same timing applies both trying the query multiple times and specifying eager loading for the nested id field data. Why is this happening? Would anybody be able to point me to any resources explaining how sorting is carried on when applied to nested fields? Is there any way I can make the two speeds at least _comparable_? ---
**Author:** ![Jason\_Wee](https://avatars.discourse-cdn.com/v4/letter/j/7ea924/32.png) [@Jason\_Wee](https://discuss.elastic.co/u/Jason_Wee)\ **Post date:** [June 6, 2015, 3:15pm UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030/2 "2015-06-06T15:15:07Z")
when you run this query, with and without nested sort, did you enable explain? could reveal some information. ---
**Author:** ![micpalmia](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/micpalmia/32/541_2.png) [@micpalmia](https://discuss.elastic.co/u/micpalmia)\ **Post date:** [June 8, 2015, 8:22am UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030/3 "2015-06-08T08:22:12Z")
The `explain` feature only provides information about the query part, not about the sorting. ---
**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:** [June 8, 2015, 9:55pm UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030/4 "2015-06-08T21:55:51Z")
Sorting on nested fields is a bit slower since elasticsearch needs to go to all your nested docs to figure out the minimum value to use as a sort value. However I'm surprised this can go from 200ms to several minutes. Can you try to capture some node hot threads while the query is running? [https://www.elastic.co/guide/en/elasticsearch/reference/current/cluster-nodes-hot-threads.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/cluster-nodes-hot-threads.html) ---
**Author:** ![micpalmia](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/micpalmia/32/541_2.png) [@micpalmia](https://discuss.elastic.co/u/micpalmia)\ **Post date:** [June 9, 2015, 7:09am UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030/5 "2015-06-09T07:09:24Z")
I've actually proceeded quite a little in this, but I still have a very hard time understanding the overall reason for my problem. - If the nested field exists in all documents (or, actually, just most of them), the sort is almost as fast as if sorting on a normal field; - If the nested object containing the field only exists in a very small number of documents, the search is extremely slow (like a couple of order of magnitudes slower); - If a `nested_filter` is used when sorting on the nested field, the behavior is parallel to what previously explained: - if the filter returns little or no documents, the search is extremely slow; - if the filter returns enough documents (~40k for a search with total number of hits ~) the search is once again extremely slow. I will certainly try to look into your suggestion. ---
**Author:** ![szymzet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/szymzet/32/7493_2.png) [@szymzet](https://discuss.elastic.co/u/szymzet)\ **Post date:** [February 1, 2016, 7:43am UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030/6 "2016-02-01T07:43:36Z")
I've hit the same problem with sorting using nested objects. Could you perhaps share with us how have you solved this issue? ---
**Author:** ![micpalmia](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/micpalmia/32/541_2.png) [@micpalmia](https://discuss.elastic.co/u/micpalmia)\ **Post date:** [February 1, 2016, 9:18am UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030/7 "2016-02-01T09:18:42Z")
My use case required me to return a set of documents sorted using nested objects, followed by everything else scored normally. I wanted to solve this by adding two sort values: the nested sorting first and `_score` second. Unfortunately this turned out to be extremely slow for the reasons mentioned above (I had searches with millions of results, with only a handful of documents containing nested fields). My solution was to perform two distinct searches at the application level (still in a way that was completely transparent to the caller): - first filter for documents containing the nested object you want to search on, and sort them: this is going to be fast since you are ensuring that the nested documents are actually there; - than (if necessary) perform a search for the rest of the documents and return them if needed. **IMPORTANT** After we deployed this system, [memory consumption skyrocketed](https://discuss.elastic.co/t/heap-filled-with-fixedbitsetfiltercache/34524): the only solution was to move to Elasticsearch 2.1 (where the memory issue is completely solved). I have the impression that the memory issues were caused by nested sorting, but I did not have the resources to investigate this thoroughly. Just a warning. ---
**Author:** ![szymzet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/szymzet/32/7493_2.png) [@szymzet](https://discuss.elastic.co/u/szymzet)\ **Post date:** [February 8, 2016, 6:12am UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030/8 "2016-02-08T06:12:00Z")
Thank you for the response 😄 Updating to Elasticsearch 2.x has speed up the sorting on nested fields by orders of magnitude. Same query on 1.5.2 took 60 seconds, but was less than 200 milliseconds on 2.1.2. Other approach we took (which doesn't require upgrading to 2.x) was to make multiple calls to Elasticsearch, dividing the whole result set into subset **having** values of the nested fields (and sorting those values) and a subset **missing** values of the nested fields (which didn't require sorting, or had a fallback sort applied). The final result was build on the application side and pagination is possible because each call returns the total count of the subset. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 11:18pm UTC](https://discuss.elastic.co/t/performance-of-sorting-on-nested-field/2030/9 "2017-07-05T23:18:17Z")