Search against Index with customized routing is slow

I'm trying to verify whether customized routing can improve search performance.

I created two indices: the old one was indexed without routing, the new one was indexed with customized routing. Then I ran the same search request against both indices.

What I found is:

  1. Search request WITH routing parameter against the new index is 200% faster than the search against the old index.
  2. Search request WITHOUT routing parameter against the new index is 15-20% slower than the search against the old index.

Is this expected? Not all of my use cases use routing field as the query filter. So I don't know whether I should indexing with or without customized routing.

How many shards does your index have? What is the cardinality if the field used for routing?

Hi Chris,

My index has 12 primary shards + 2 replicas. However, my Elasticsearch cluster for testing only has 4 data nodes. My Elasticsearch cluster for production has 45 data nodes though. Do you think I can get better or equal performance in production cluster?

As for cardinality of this field, there are 500 distinct values. So I feel this is a good candidate field for routing.

The optimal number and size of shards depend a lot on your data and queries. Routing reduces the load on the cluster as only a single shard is queried per index. This can also as you have seen help speed up some queries. It may be that the number of shards needed to properly benefit from routing is not optimal when routing is not used so I am not surprised this is taking longer. I would recommend trying with different shard counts to see what the sweet spot for your particular use case is.

I'll try with smaller number of shards to verify your hypothesis. My original assumption was when routing is not used in the search request, it should just go to all of the shards. So I expect the query performance should be the same as before.

The size and number of shards can affect latency and you need to test to see what is optimal for your use case.

Hi Chris,

I found the root cause: when the search request includes Scripted Metric Aggregation, it becomes slower compared to using the old index without customized routing. Is it make sense?