# Index sorting

**URL:** <https://discuss.elastic.co/t/index-sorting/173783>\
**Category:** Elasticsearch\
**Created:** [March 25, 2019, 3:12pm UTC](https://discuss.elastic.co/t/index-sorting/173783 "2019-03-25T15:12:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![AAHM](https://avatars.discourse-cdn.com/v4/letter/a/f475e1/32.png) [@AAHM](https://discuss.elastic.co/u/AAHM)\
**Post date:** [March 25, 2019, 3:12pm UTC](https://discuss.elastic.co/t/index-sorting/173783/1 "2019-03-25T15:12:06Z")

</div>

Hi,

I have an index with following setting, where the the index.sort.field is specified at index time to use two keyword fields primary\_id & secondary\_id.

```
{
"settings": {
"number_of_shards": 1,
"number_of_replicas": 0,
"analysis": {
  "analyzer": {
    "folding": {
      "tokenizer": "standard",
      "filter": [
        "lowercase",
        "asciifolding"
      ]
    }
  },
  "normalizer": {
    "lowerasciinormalizer": {
      "type": "custom",
      "filter": [
        "lowercase",
        "asciifolding"
      ]
    }
  }
},
"index" : {
  "sort.field" : ["primary_id", "secondary_id"],
  "sort.order" : ["asc", "asc"]
}
},
"mappings": {
"_doc": {
  "dynamic_templates": [
    {
      "string_as_keyword": {
        "match_mapping_type": "string",
        "mapping": {
          "type": "keyword",
          "normalizer": "lowerasciinormalizer"
        }
      }
    }
  ],
  "properties": {
    "primary_id": {
      "type": "keyword",
      "normalizer": "lowerasciinormalizer"
    },
    "secondary_id": {
      "type": "keyword",
      "normalizer": "lowerasciinormalizer"
    }
    //other fields
  }
}
}
}

```

When I then search the index for example with term query and using default sort (or specifying \_doc sort) I got a different and wrong sort than the expected result order when specifying sort with the primary\_id field.

```
//This returns for example ABCD103921654 first instead of ABCD100317418 when using primary_id for sorting
{
"from" : 0, "size" : 10,
"sort": [
{
"_doc" : {
"order" : "asc"
}
}   
],
"query": {
"term": {
  "name" : {
  "value" : "Express"
}
}
}
}

```

This is on a local ES instance with no shards.

Thanks

---

<div class="post-metadata">

**Author:** ![gbrown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gbrown/32/34482_2.png) [@gbrown](https://discuss.elastic.co/u/gbrown)\
**Post date:** [March 25, 2019, 8:38pm UTC](https://discuss.elastic.co/t/index-sorting/173783/2 "2019-03-25T20:38:52Z")

</div>

Hi!

Index sorting is intended to optimize the index for specific query patterns, not to provide a default sort field for queries against the index. You can find more detail about what kinds of queries index sorting can be used to optimize [in this blog post](https://www.elastic.co/blog/index-sorting-elasticsearch-6-0).

The `_doc` sort order has no guarantees other than being the fastest order - this may be different from the index sort order for a number of reasons. If you want to guarantee that the returned results are ordered by a field (or set of fields), you'll need to specify which field to sort on in the query.

---

<div class="post-metadata">

**Author:** ![AAHM](https://avatars.discourse-cdn.com/v4/letter/a/f475e1/32.png) [@AAHM](https://discuss.elastic.co/u/AAHM)\
**Post date:** [March 27, 2019, 3:30pm UTC](https://discuss.elastic.co/t/index-sorting/173783/3 "2019-03-27T15:30:27Z")

</div>

Thanks Brown for your reply and the link.

I have misunderstood that \_doc sorting is index sorting which would be the same as what is defined in the "index.sort" settings if applied. so specifying sort by \_doc or sort by "index.sort.field" would be the same. But this seems to be wrong.

The use case I'm trying to achieve is to have guaranteed order by the fields defined in the index settings while being as fast as the \_doc sorting. This is especially needed for the scroll API to deep paginate through a large index (~5 million documents) .

> Scroll requests have optimizations that make them faster when the sort order is `_doc` . If you want to iterate over all documents regardless of the order, this is the most efficient option:

Currently, to scroll the whole index while sorting using \_doc takes ~2 mins. The same using the primary\_id index field sorting takes ~4 mins. Is there any way to optimize this or any other better way for scrolling with a provided order?

I know this is not an ideal use case for ES, but this is a user requirement we need to provide in our system.

---

<div class="post-metadata">

**Author:** ![AAHM](https://avatars.discourse-cdn.com/v4/letter/a/f475e1/32.png) [@AAHM](https://discuss.elastic.co/u/AAHM)\
**Post date:** [April 10, 2019, 8:20am UTC](https://discuss.elastic.co/t/index-sorting/173783/4 "2019-04-10T08:20:28Z")

</div>

Hi,

Can you please explain how/why \_doc would be different from index sort?

Currently, to scroll the whole index (~5 million documents) while sorting using \_doc takes ~2 mins. The same using the primary\_id index field sorting takes ~4 mins.  
Is there any way to optimize this or any other better way for scrolling with a provided order?

Regards

---

<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:** [May 8, 2019, 8:20am UTC](https://discuss.elastic.co/t/index-sorting/173783/5 "2019-05-08T08:20:33Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.