Speeding Up Slow Elasticsearch Queries With the Profile API

An application used Elasticsearch for customer search, and as the data grew, some searches got slow. The account this post is based on reported searches taking up to 60 seconds; that figure is from the original account and was not re-measured on that system. This article shows how the Profile API points at the cause, and re-runs the three queries on a synthetic index so the effect is measured rather than asserted.

Key Takeaways #

• The Profile API ("profile": true) breaks a search down into the Lucene queries it actually ran, so you can see what a query_string expanded into.
• A query_string with no fields searches index.query.default_field, which defaults to *: every eligible field. Elastic’s docs warn that this can be expensive on mappings with many fields.
• A trailing wildcard (Ryan*) is a different search from Ryan: it matches more terms, so removing it changes the results, not just the speed.

Understanding the Problem #

In the reported case the query searched across all fields in the customer index, and it had been written long before. As the number of documents grew, the time per search grew too.

The original query structure was as follows:

{
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "Ryan*"
          }
        }
      ],
      "filter": [
        {
          "bool": {
            "must": [
              {
                "terms": {
                  "merchant_id": [2]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Utilizing the Profile API #

To diagnose the slow query, add "profile": true to the search body. The response includes a breakdown of how each part of the query was executed. For a query_string with no fields, the profile shows the query expanded into a clause per field and per sub-field, which is the excessive-fields problem the original account describes.

Optimizing the Query #

To cut that, the number of searchable fields was limited to the ones the interface needs. The original account says these were first name, last name and email; the query below also lists reference, card_token, card_number and public_id. Do not copy card_number into a search index: storing and searching card numbers brings payment-data compliance obligations that a search box does not need. The updated query:

{
  "profile": true,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "Ryan*",
            "fields": [
              "first_name",
              "last_name",
              "email",
              "reference",
              "card_token",
              "card_number",
              "public_id"
            ]
          }
        }
      ],
      "filter": [
        {
          "bool": {
            "must": [
              {
                "terms": {
                  "merchant_id": [2]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

In the original account this cut the time from several seconds to a few milliseconds. We did not see that system, so see the local measurement below for a number we produced.

Further Improvements #

The next step was to ask whether wildcard search is needed at all. Without the trailing *, Ryan only matches the whole term (after analysis), not names that merely start with it. If users need prefix search, keep the wildcard or use a purpose-built option such as an edge n-gram analyzer; if they search by whole names, drop it. The new query:

{
  "profile": true,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "Ryan",
            "fields": [
              "first_name",
              "last_name",
              "email",
              "reference",
              "card_token",
              "card_number",
              "public_id"
            ]
          }
        }
      ],
      "filter": [
        {
          "bool": {
            "must": [
              {
                "terms": {
                  "merchant_id": [2]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

The original account reports millisecond-range times for this version.

A local re-run (synthetic data, not the original system) #

We ran the three queries on Elasticsearch 8.15.0, single node in Docker on a laptop: 30,000 documents and 128 fields (the 8 named above plus 120 filler text fields, each containing the word “Ryan”). Each query ran six times with the request cache off; the figures are Elasticsearch’s took in milliseconds.

In the profile, the no-fields query expanded to a clause for each field and keyword sub-field (a 5,596-character description); the seven-field versions expanded to seven. The hit counts are not comparable between the first row and the others, because the filler fields all contain “Ryan”. Between rows two and three they are: dropping the wildcard halved the hits, because Ryanna and Ryanson stopped matching. This is one synthetic data set on one machine, so treat the ratios as an illustration, not a benchmark of your cluster.

Conclusion #

Use the Profile API to see what a slow query_string really searches. Name the fields you need, and decide on purpose whether users need prefix matching, since removing the wildcard changes what they get back. The speed-ups above are measured on a synthetic index and reported for the original system, not guaranteed for yours.