Improve elasticsearch aggreation performance

I am using elasticsearch aggregations to calculate counts for a faceted search. Therefore I define my general search query (e.g. status.keyword) and exlcude this filter from the status.keyword aggregation itself (the query is generated by the app).

While the search query itself takes ~1 sec to execute, all aggregations together add another 1.5 sec., which I try to optimize. The terms have around 10-200 distinct values and there are 5mil. documents in total.

Are there ways to optimize the aggregation performance in the query below?


{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": []
          }
        }
      ],
      "must_not": [],
      "should": [],
      "filter": [
        {
          "terms": {
            "status.keyword": [
              "xxx"
            ]
          }
        }
      ]
    }
  },
  "sort": [
    {
      "collected": {
        "order": "desc"
      }
    }
  ],
  "aggs": {
    "all_docs": {
      "global": {},
      "aggs": {
        "A": {
          "filter": {
            "bool": {
              "filter": [
                {
                  "terms": {
                    "status.keyword": [
                      "xxx"
                    ]
                  }
                }
              ],
              "should": [],
              "must": [],
              "must_not": []
            }
          },
          "aggs": {
            "filtered": {
              "terms": {
                "field": "A.keyword",
                "size": 1000
              }
            }
          }
        },
        "B": {
          "filter": {
            "bool": {
              "filter": [
                {
                  "terms": {
                    "status.keyword": [
                      "xxx"
                    ]
                  }
                }
              ],
              "should": [],
              "must": [],
              "must_not": []
            }
          },
          "aggs": {
            "filtered": {
              "terms": {
                "field": "B.keyword",
                "size": 100,
                "min_doc_count": 0,
                "order": {
                  "_key": "asc"
                }
              }
            }
          }
        },
        "C": {
          "filter": {
            "bool": {
              "filter": [
                {
                  "terms": {
                    "status.keyword": [
                      "xxx"
                    ]
                  }
                }
              ],
              "should": [],
              "must": [],
              "must_not": []
            }
          },
          "aggs": {
            "filtered": {
              "terms": {
                "field": "C.keyword",
                "size": 100
              }
            }
          }
        },
        "D": {
          "filter": {
            "bool": {
              "filter": [
                {
                  "terms": {
                    "status.keyword": [
                      "xxx"
                    ]
                  }
                }
              ],
              "should": [],
              "must": [],
              "must_not": []
            }
          },
          "aggs": {
            "filtered": {
              "terms": {
                "field": "D.keyword",
                "size": 100,
                "order": {
                  "_key": "asc"
                }
              }
            }
          }
        },
        "E": {
          "filter": {
            "bool": {
              "filter": [
                {
                  "terms": {
                    "status.keyword": [
                      "xxx"
                    ]
                  }
                }
              ],
              "should": [],
              "must": [],
              "must_not": []
            }
          },
          "aggs": {
            "filtered": {
              "terms": {
                "field": "E.keyword",
                "size": 1000
              }
            }
          }
        },
        "F": {
          "filter": {
            "bool": {
              "filter": [],
              "should": [],
              "must": [],
              "must_not": []
            }
          },
          "aggs": {
            "filtered": {
              "terms": {
                "field": "F.keyword",
                "min_doc_count": 0
              }
            }
          }
        }
      }
    }
  }
}

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.