Slow queries in case a very large number of documents match

[girirajsharma]

Let's say I have a index named test (15 shards and 1 replica) consisting of 10 million docs. Each of the document has a category assigned to it. Let's name the categories as C1, C2, C3...Cn. Consider C1 is assigned to more than 60% of documents.

For my search queries, whenever there is a filter for Category=C1, the response time is quite high (4-7s). The reason is obvious that it has to search across all 15 shards, compute, collate almost 6 million docs and then return top x (say 30) results. Searches for all other category filters have normal and acceptable response time (<100 ms).

What can be best trick to cut down slow queries in case of categories like C1.

[thn (tri-man)]

If each document is assigned to only one category, you can index them under index/type so in your case, you will have test/c1 for all documents assigned to c1.

When you search, you can search with test/c1/_search?q=<query>

[girirajsharma]

@thn Splitting on type should be preferred or splitting on index itself ?

Considering split on type, ES will have less overhead but still, it would have 6 million docs matching (almost all docs in type or index) and that's why it will have to compute/collate/sort across all 6 million docs to return top results.
Will splitting on index/type cut down response time significantly ?
Also, I hope keeping number of shards for this C1 category as 5 or 10 or 15 won't matter much as far as performance accounts, in case there is a index split on category basis.

[thn (tri-man)]

(just want to clarify: I don't work for elastic so what I'm suggesting here is based on my experience with ES)

I suggest to try indexing data with type as a category b/c 6 million documents is not a large data set to support a search where you know you want to search in c1 for example.

Also, keep in mind if your index is not large and does not grow over time, you can cut back on the number of shards. Based on your example, having 15 shards to support 10 million documents is overkill.

Aggregation per category is a different story.