Index sorting with Elasticsearch 6.2.4 for query performance

[ShilpiRachna]

Hi,

We are working on improving our query performance. Our queries are always filtered by customer account id and project id. Multiple accounts can exist on the same shard. We use routing based on account id to query the right shard. Would sorting by account id and project id improve query performance? We believe that the filter matches would be extremely fast due to sorting and the number of documents that need to be matched for the actual term queries would also be in same / sequential segments, thus improving their performance as well. Has anyone tried sorting in a similar scenario and seen query performance improvement?

If not sorting, is there any other way to logically partition account and project data? We cannot have one index per account as the number of accounts is huge and the data size per account / project is not very large (on average).

Thanks!
Shilpi

[jimczi (Jimferenczi)]

Index sorting can improve conjunctions as explained here:
https://www.elastic.co/guide/en/elasticsearch/reference/current/index-modules-index-sorting-conjunctions.html
It could also improve the compression on disk if documents that share the same account id have similar content. This blogpost summarizes the benefit of this approach:

Space Savings: A Lesser Known Benefit of Index Sorting in Elasticsearch ...