Elasticsearch 8.5 Failing to Optimize Query Performance with Large Datasets and Filters
I'm stuck on something that should probably be simple. I've been working on this all day and I'm sure I'm missing something obvious here, but I tried several approaches but none seem to work. Quick question that's been bugging me - I'm working with Elasticsearch 8.5, and I've noticed that my queries are performing poorly when filtering on large datasets. I'm using a simple `match` query combined with a `bool` filter to search through a large index of user data, which contains millions of records. The query is taking over 2 seconds to return results, and I really need to optimize this for a better user experience. Here's the query I'm currently using: ```json { "query": { "bool": { "must": [ { "match": { "name": "John Doe" } } ], "filter": [ { "term": { "status": "active" } }, { "range": { "last_login": { "gte": "2023-01-01" } } } ] } } } ``` I've tried a few things to improve the performance: 1. I've ensured that the `status` and `last_login` fields are both mapped as keywords and dates respectively, and they have appropriate indexing. 2. I've experimented with adding `size: 10` to limit the results, but it hasn't significantly improved the latency. 3. I've also tried using `filters` in the query to cache results, but the performance hasn't changed much. When I check the `/_cat/indices?v` API, I see that my index size is quite large, and I suspect that could be a contributing factor. However, I need to retrieve relevant documents quickly as users may search frequently. I'm pondering whether I should consider denormalizing my data or implementing a different indexing strategy to speed up these queries. Any insights on enhancing the performance of filtering with large datasets in Elasticsearch would be greatly appreciated. My development environment is macOS. This is part of a larger application I'm building. How would you solve this? I'm working on a service that needs to handle this. I recently upgraded to Json latest. My team is using Json for this mobile app. Thanks for taking the time to read this!