# Upgrade to 7.x aggregation performance degradation
**URL:** https://discuss.elastic.co/t/upgrade-to-7-x-aggregation-performance-degradation/327449
**Category:** Elasticsearch
**Created:** [March 10, 2023, 11:40am UTC](https://discuss.elastic.co/t/upgrade-to-7-x-aggregation-performance-degradation/327449 "2023-03-10T11:40:38Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@dbajra94](https://discuss.elastic.co/u/dbajra94)
#### Post date: [March 10, 2023, 11:40am UTC](https://discuss.elastic.co/t/upgrade-to-7-x-aggregation-performance-degradation/327449/1 "2023-03-10T11:40:38Z")
We are currently in process of migrating our Cloud tenants to 7.16.3 in preparation for an internal 8.6 Elasticsearch upgrade of our software. However, we have noticed that our aggregation queries have suffered a hard unexplainable hit in terms of performance.
We could see when migrating our test cluster with 3 nodes and around 1613 total primary shards our aggregation queries a lot of times decreased by up to 200% in performance. From my personal investigation, it seems to be connected to aggregations on keyword fields which is probably not the best thing to do anyway. However, we were surprised about the huge jump in aggregation times between 2 ES versions.
I prepared a small example based on one of our tenants which has an index size of 22.5GB split into 3 primary shards and 2 replicas. Since we don't have an identical cluster to compare this to anymore we have set up a dedicated 6.8 ES cluster to compare with. This only holds around 33 primary shards but I hope it is still comparable.
Index Mapping:
```auto
{
"CustomerA_export_Article" : {
"mappings" : {
"properties" : {
"booleanFields" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"aggregationValue" : {
"type" : "keyword"
},
"attrId" : {
"type" : "keyword"
},
"formattedValue" : {
"type" : "keyword",
"normalizer" : "lcandasciifolding_normalizer"
},
"itemId" : {
"type" : "long"
},
"languageId" : {
"type" : "long"
},
"referenceId" : {
"type" : "long"
},
"referenceTargetId" : {
"type" : "long"
},
"searchType" : {
"type" : "keyword",
"index" : false
},
"tableId" : {
"type" : "keyword"
},
"value" : {
"type" : "boolean"
}
}
},
"dateFields" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"aggregationValue" : {
"type" : "keyword"
},
"attrId" : {
"type" : "keyword"
},
"formattedValue" : {
"type" : "keyword",
"normalizer" : "lcandasciifolding_normalizer"
},
"itemId" : {
"type" : "long"
},
"languageId" : {
"type" : "long"
},
"referenceId" : {
"type" : "long"
},
"referenceTargetId" : {
"type" : "long"
},
"searchType" : {
"type" : "keyword",
"index" : false
},
"tableId" : {
"type" : "keyword"
},
"value" : {
"type" : "keyword"
}
}
},
"numericFields" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"aggregationValue" : {
"type" : "keyword"
},
"attrId" : {
"type" : "keyword"
},
"formattedValue" : {
"type" : "keyword",
"normalizer" : "lcandasciifolding_normalizer"
},
"itemId" : {
"type" : "long"
},
"languageId" : {
"type" : "long"
},
"referenceId" : {
"type" : "long"
},
"referenceTargetId" : {
"type" : "long"
},
"searchType" : {
"type" : "keyword",
"index" : false
},
"tableId" : {
"type" : "keyword"
},
"value" : {
"type" : "double"
}
}
},
"references" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"languageId" : {
"type" : "long"
},
"referenceId" : {
"type" : "long"
},
"referenceTargetId" : {
"type" : "long"
},
"referenceTargetType" : {
"type" : "keyword"
}
}
},
"simpleSearchText" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"languageId" : {
"type" : "long"
},
"value" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 10922,
"normalizer" : "lcandasciifolding_normalizer"
}
},
"analyzer" : "whitespace_analyzer"
}
}
},
"textFields" : {
"type" : "nested",
"include_in_parent" : true,
"properties" : {
"aggregationValue" : {
"type" : "keyword",
"ignore_above" : 10922
},
"attrId" : {
"type" : "keyword"
},
"formattedValue" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 10922,
"normalizer" : "lcandasciifolding_normalizer"
}
},
"analyzer" : "whitespace_analyzer"
},
"itemId" : {
"type" : "long"
},
"languageId" : {
"type" : "long"
},
"referenceId" : {
"type" : "long"
},
"referenceTargetId" : {
"type" : "long"
},
"searchType" : {
"type" : "keyword",
"index" : false
},
"tableId" : {
"type" : "keyword"
},
"value" : {
"type" : "text",
"analyzer" : "whitespace_analyzer"
}
}
}
}
}
}
}
```
Aggregation Query: (EDIT: I accidentally uploaded a stripped down query, however the full query is too big for this post. It is a basically a repetition of more of the "textFields -\> aggregationValue" buckets)
```auto
GET CustomerA_export_*/_search
{
"size": "0",
"aggs": {
"index_buckets_Article": {
"filter": {
"bool": {
"must": [
{
"terms": {
"_index": [
"CustomerA_export_Article"
]
}
},
{
"bool": {
"should": [
{
"bool": {
"must": [
{
"nested": {
"path": "simpleSearchText",
"query": {
"bool": {
"must": [
{
"bool": {
"should": [
{
"match": {
"simpleSearchText.languageId": 1
}
},
{
"match": {
"simpleSearchText.languageId": "0"
}
}
]
}
},
{
"wildcard": {
"simpleSearchText.value.keyword": "*small*"
}
}
]
}
}
}
}
]
}
}
]
}
}
]
}
},
"aggs": {
"attribute_6611": {
"nested": {
"path": "textFields"
},
"aggs": {
"nested_6611": {
"filter": {
"bool": {
"must": [
{
"term": {
"textFields.attrId": {
"value": "6611"
}
}
},
{
"bool": {
"should": [
{
"match": {
"textFields.languageId": "1"
}
},
{
"match": {
"textFields.languageId": "0"
}
}
]
}
}
]
}
},
"aggs": {
"values": {
"terms": {
"field": "textFields.aggregationValue",
"size": 1000,
"order": {
"_key": "asc"
}
}
}
}
}
}
},
"attribute_6849": {
"nested": {
"path": "textFields"
},
"aggs": {
"nested_6849": {
"filter": {
"bool": {
"must": [
{
"term": {
"textFields.attrId": {
"value": "6849"
}
}
},
{
"bool": {
"should": [
{
"match": {
"textFields.languageId": "1"
}
},
{
"match": {
"textFields.languageId": "0"
}
}
]
}
}
]
}
},
"aggs": {
"values": {
"terms": {
"field": "textFields.aggregationValue",
"size": 1000,
"order": {
"_key": "asc"
}
}
}
}
}
}
},
"attribute_6918": {
"nested": {
"path": "textFields"
},
"aggs": {
"nested_6918": {
"filter": {
"bool": {
"must": [
{
"term": {
"textFields.attrId": {
"value": "6918"
}
}
},
{
"bool": {
"should": [
{
"match": {
"textFields.languageId": "1"
}
},
{
"match": {
"textFields.languageId": "0"
}
}
]
}
}
]
}
},
"aggs": {
"values": {
"terms": {
"field": "textFields.aggregationValue",
"size": 1000,
"order": {
"_key": "asc"
}
}
}
}
}
}
},
"attribute_6920": {
"nested": {
"path": "textFields"
},
"aggs": {
"nested_6920": {
"filter": {
"bool": {
"must": [
{
"term": {
"textFields.attrId": {
"value": "6920"
}
}
},
{
"bool": {
"should": [
{
"match": {
"textFields.languageId": "1"
}
},
{
"match": {
"textFields.languageId": "0"
}
}
]
}
}
]
}
},
"aggs": {
"values": {
"terms": {
"field": "textFields.aggregationValue",
"size": 1000,
"order": {
"_key": "asc"
}
}
}
}
}
}
}
}
}
}
}
```
This aggregation query takes 573ms on our 6.8 instance but 1138ms on our 7.1 instance when doing a POST \_cache/clear beforehand. The more aggregations on our "keyword" field i add the more the performance between versions differentiates. Therefore my question would be if there are any known changes that could be causing this?
Here are the results of the query on both instances...
...6.8:
```auto
{
"took" : 556,
"timed_out" : false,
"_shards" : {
"total" : 6,
"successful" : 6,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : 434226,
"max_score" : 0.0,
"hits" : []
},
"aggregations" : {
"index_buckets_Article" : {
"doc_count" : 1,
"attribute_7043" : {
"doc_count" : 30,
"nested_7043" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_9154" : {
"doc_count" : 30,
"nested_9154" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7151" : {
"doc_count" : 30,
"nested_7151" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_9159" : {
"doc_count" : 30,
"nested_9159" : {
"doc_count" : 1,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "Track Pants ~~||~~ 15469",
"doc_count" : 1
}
]
}
}
},
"attribute_8477" : {
"doc_count" : 40,
"nested_8477" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_9147" : {
"doc_count" : 30,
"nested_9147" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7198" : {
"doc_count" : 40,
"nested_7198" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7121" : {
"doc_count" : 40,
"nested_7121" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7055" : {
"doc_count" : 30,
"nested_7055" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6849" : {
"doc_count" : 30,
"nested_6849" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6918" : {
"doc_count" : 30,
"nested_6918" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6611" : {
"doc_count" : 30,
"nested_6611" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_8419" : {
"doc_count" : 30,
"nested_8419" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7209" : {
"doc_count" : 30,
"nested_7209" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6933" : {
"doc_count" : 30,
"nested_6933" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7208" : {
"doc_count" : 30,
"nested_7208" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7207" : {
"doc_count" : 30,
"nested_7207" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7119" : {
"doc_count" : 40,
"nested_7119" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6920" : {
"doc_count" : 30,
"nested_6920" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7206" : {
"doc_count" : 30,
"nested_7206" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6921" : {
"doc_count" : 30,
"nested_6921" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
}
}
}
}
```
...7.1:
```auto
{
"took" : 1138,
"timed_out" : false,
"_shards" : {
"total" : 6,
"successful" : 6,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : null,
"hits" : []
},
"aggregations" : {
"index_buckets_Article" : {
"meta" : { },
"doc_count" : 1,
"attribute_7043" : {
"doc_count" : 30,
"nested_7043" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_9154" : {
"doc_count" : 30,
"nested_9154" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7151" : {
"doc_count" : 30,
"nested_7151" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_9159" : {
"doc_count" : 30,
"nested_9159" : {
"doc_count" : 1,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "Track Pants ~~||~~ 15469",
"doc_count" : 1
}
]
}
}
},
"attribute_8477" : {
"doc_count" : 40,
"nested_8477" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_9147" : {
"doc_count" : 30,
"nested_9147" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7198" : {
"doc_count" : 40,
"nested_7198" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7121" : {
"doc_count" : 40,
"nested_7121" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7055" : {
"doc_count" : 30,
"nested_7055" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6849" : {
"doc_count" : 30,
"nested_6849" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6918" : {
"doc_count" : 30,
"nested_6918" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6611" : {
"doc_count" : 30,
"nested_6611" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_8419" : {
"doc_count" : 30,
"nested_8419" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7209" : {
"doc_count" : 30,
"nested_7209" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6933" : {
"doc_count" : 30,
"nested_6933" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7208" : {
"doc_count" : 30,
"nested_7208" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7207" : {
"doc_count" : 30,
"nested_7207" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7119" : {
"doc_count" : 40,
"nested_7119" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6920" : {
"doc_count" : 30,
"nested_6920" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_7206" : {
"doc_count" : 30,
"nested_7206" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
},
"attribute_6921" : {
"doc_count" : 30,
"nested_6921" : {
"doc_count" : 0,
"values" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : []
}
}
}
}
}
}
```
---
### Author:  [@dbajra94](https://discuss.elastic.co/u/dbajra94)
#### Post date: [March 14, 2023, 8:51am UTC](https://discuss.elastic.co/t/upgrade-to-7-x-aggregation-performance-degradation/327449/2 "2023-03-14T08:51:39Z")
Unfortunately still struggling with the root cause of this issue.
I can definitely see a huge performance gap the more aggregations of textFields.aggregationValue (type keyword) i add. However i can not find a reason why this should behave so much differently between 6.8 and 7.16
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [April 11, 2023, 8:52am UTC](https://discuss.elastic.co/t/upgrade-to-7-x-aggregation-performance-degradation/327449/3 "2023-04-11T08:52:37Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.