Hey / () team. This question is in reference to this feature that was introduced in ES 6.x (elastic.co/blog/index-sor).
When creating an index, it is possible to select a key that acts as a primary index for sorting. For example, if one were
Conversation
creating an index for tweets, they could pick the "created_at" key to create an index that makes sorting on that key much faster. When specifying a key for creating the index, a sort order is also used (ascending / descending). What I've noticed though is a potential bug or
1
performance issue related to the sort order. If one chooses "created_at" as the key for the index and selects "desc" for descending sort, queries that utilize that specific sort direction are optimized. However, if a query specifies the opposite sort direction, the performance is
1
much slower. I'm not sure if this is an Elasticsearch issue or a Lucene issue, so I tagged both for this discussion. As a side example using Postgres, if an index is created on a column and a sort direction is specified, the index can still be used when sorting in the opposite
1
direction. I'm curious why the performance is much worse in Elasticsearch when a query specifies a sort direction opposite of them direction originally specified when creating the index. It is almost as if the index isn't even used if the sort direction is the opposite of what is
1
originally specified.
Any ideas?
1
Replying to
This is the downside of index sorting indeed, you need to choose whether to optimize for ascending or descending order.
1
FYI we have new sort optimizations since 7.6 that don't rely on index sorting (actually the best case scenario for these optimizations is to have data indexed in random order) and work with both orders
Quote Tweet
Last week we upgraded our production @elastic cluster from v7.5.1 to v7.6.1. Here is the difference in query time.
On average we see a ~3x improvement on query time
This is not a benchmark (not the same queries)
Iff you haven’t upgraded yet, go for it!
On average we see a ~3x improvement on query time
This is not a benchmark (not the same queries)
Iff you haven’t upgraded yet, go for it!
1
3