# Query performance problems upgrading from ES 6.8.6. to ES 7.6.2, with exact term filtering + range **URL:** \ **Category:** Elasticsearch\ **Created:** [May 8, 2020, 3:57pm UTC](https://discuss.elastic.co/t/query-performance-problems-upgrading-from-es-6-8-6-to-es-7-6-2-with-exact-term-filtering-range/231767 "2020-05-08T15:57:47Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![jensljungblad](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jensljungblad/32/67925_2.png) [@jensljungblad](https://discuss.elastic.co/u/jensljungblad)\ **Post date:** [May 8, 2020, 3:57pm UTC](https://discuss.elastic.co/t/query-performance-problems-upgrading-from-es-6-8-6-to-es-7-6-2-with-exact-term-filtering-range/231767/1 "2020-05-08T15:57:47Z")
Hi! We are trying to upgrade our deployment from an Elastic Heroku-addon (Premium 3) to a new deployment on [elastic.co](http://elastic.co), using the I/O optimized general purpose setup. Old cluster: `8GB RAM + 192GB storage * 1 node * 2 zones` New cluster: `8GB RAM + 240GB storage * 1 node * 2 zones` The old deployment is running 6.8.6, and the new one is running 7.6.2. Apart from the version upgrade, we’ve made two changes: 1. We’ve updated our mapping from ```auto { event: { properties: { action: { type: :keyword }, actor_id: { type: :long }, actor_type: { type: :keyword }, authenticated_by_id: { type: :long }, authenticated_by_type: { type: :keyword }, code: { type: :keyword }, created_at: { type: :date }, id: { type: :long }, message: { type: :text, index: false }, object_id: { type: :long }, object_type: { type: :keyword }, owner_id: { type: :long }, owner_type: { type: :keyword }, partition: { type: :long }, previous_event_id: { type: :long }, reference_id: { type: :long }, reference_type: { type: :keyword }, success: { type: :boolean } } } } ``` to ```auto { dynamic: "strict", properties: { action: { type: :keyword }, actor_id: { type: :keyword }, actor_type: { type: :keyword }, authenticated_by_id: { type: :keyword }, authenticated_by_type: { type: :keyword }, code: { type: :keyword }, created_at: { type: :date }, details: { type: :object, enabled: false }, id: { type: :keyword }, message: { type: :text, index: false }, object_id: { type: :keyword }, object_type: { type: :keyword }, reference_id: { type: :keyword }, reference_type: { type: :keyword }, root_id: { type: :keyword }, root_type: { type: :keyword }, sequence: { type: :keyword }, success: { type: :boolean } } } ``` So we added `dynamic: strict`, plus we renamed a few of the fields (`owner -> root`, `partition -> sequence`). I'm assuming the only potentially interesting thing here is that we changed some `long` properties to `keyword`. We did that based on the recommendation in [https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html). We're _only_ interested in doing exact matches on these properties, so term queries. 1. We applied a sort on the index settings: ```auto index: { "sort.field": ["created_at", "id"], "sort.order": ["desc", "desc"] } ``` We did this because of the advice in [https://www.elastic.co/guide/en/elasticsearch/reference/master/index-modules-index-sorting.html](https://www.elastic.co/guide/en/elasticsearch/reference/master/index-modules-index-sorting.html). Our queries always sort on these properties, and our data is a time series. All in all, we have approximately 600 million documents in the index in the old deployment, which we re-index to the new deployment. Switching our production application to query this new cluster, with the exact same query as before, apart from the renamed `owner_id` and `owner_type`, left us with quite a big drop in performance. Hoping someone here can shed some light on what we're doing wrong. Here's two screenshots from Kibana from the old vs new cluster. Now, I don't know that much about ES, but one thing that stands out to me is Query Cache is much bigger in the old cluster. Is it possible we need to run the new cluster for a significant amount of time to build it up? ![old_cluster_metrics](https://us1.discourse-cdn.com/elastic/original/3X/f/1/f16889e487c002f7020554dcd1131cb931e2b963.png) ![new_cluster_metrics](https://us1.discourse-cdn.com/elastic/original/3X/a/9/a9335d9dbda0755b045b1cf9ca429e2e8ce8af26.png) ### The query First let me explain what we _want_ to do. We want to query the documents, allowing for _exact match filtering_ on: - `action` - `actor_id` - `actor_type` - `object_id` - `object_type` - `root_id` - `root_type` - `reference_id` - `reference_type` In addition, we want to allow filtering with a range on `created_at`. Typically, only these are used: - `root_id` - `root_type` - `reference_id` - `reference_type` The current query we have that does this looks something like this: ```json { "query": { "bool": { "filter": [ { "bool": { "must": { "terms": { "root_id": ["123456"] } } } }, { "bool": { "must": { "term": { "root_type": "Place" } } } }, { "bool": { "must": { "term": { "reference_type": "Place" } } } }, { "bool": { "must": { "terms": { "reference_id": ["123456"] } } } } ] } }, "sort": [ { "created_at": "desc" }, { "id": "desc" } ] } ``` And here is one with a range query: ```json { "query": { "bool": { "filter": [ { "bool": { "must": { "terms": { "root_id": ["123456"] } } } }, { "bool": { "must": { "term": { "root_type": "Place" } } } }, { "bool": { "must": { "term": { "reference_type": "Place" } } } }, { "bool": { "must": { "terms": { "reference_id": ["123456"] } } } }, { "bool": { "must": { "range": { "created_at": { "gte": "2020-04-01", "lte": "2020-05-01" } } } } } ] } }, "sort": [ { "created_at": "desc" }, { "id": "desc" } ] } ``` Both queries perform better in the old cluster. 1. Does anything here look like it would perform slower in ES 7.6 compared to 6.8? 2. Is there a better query for achieving what we want that would perform better in ES 7.6? Thanks! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 5, 2020, 3:57pm UTC](https://discuss.elastic.co/t/query-performance-problems-upgrading-from-es-6-8-6-to-es-7-6-2-with-exact-term-filtering-range/231767/2 "2020-06-05T15:57:49Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.