How to explain the query in elasticsearch

Question

I have a complex query in elasticsearch. It's slow. I want to optimize it, but I don't know how to work. How to explain the query, like SQL explain?

I see elastichsearch _valite/query?explain. It can explain score. But I need to view detailed execution plan.

{
  "post_filter": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "base.sysCode": "2801"
                }
              },
              {
                "term": {
                  "base.status": [
                    12,
                    0
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "fields": [
    "base.sysCode",
    "base.orderNo"
  ]
}

result

{
  "valid": true,
  "_shards": {
    "total": 1,
    "successful": 1,
    "failed": 0
  },
  "explanations": [
    {
      "index": "odi_bus_betad_2013",
      "valid": true,
      "explanation": "ConstantScore(*:*)"
    }
  ]
}

Is this your full query, If not could you post the full query? — ChintanShah25
– ChintanShah25, Commented Nov 15, 2015 at 16:27
For performance reasons use only a _post_filter if you need to differentiate search results and aggregations. — Henrik
– Henrik, Commented Sep 12, 2016 at 16:10

BenG · Accepted Answer · 2018-11-19 15:06:27Z

68

Explain API

Computes a score explanation for a query and a specific document. This can give useful feedback whether a document matches or didn’t match a specific query.

Add "explain": true

GET /_search
{
    "explain": true,
    "query" : {
        "term" : { "user" : "kimchy" }
    }
}

Explain Documentation

Profile API

Provides detailed timing information about the execution of individual components in a search request. It gives the user insight into how search requests are executed at a low level so that the user can understand why certain requests are slow, and take steps to improve them.

Add "profile": true

GET /_search
{
  "profile": true,
  "query" : {
    "match" : { "user" : "kimchy" }
  }
}

Profile Documentation

edited Nov 19, 2018 at 15:06

answered Jun 21, 2017 at 11:48

BenG

15.2k5 gold badges48 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dinerdo Over a year ago

Why is this not the selected answer?

ttt Over a year ago

I had to remove the ?explain after _search and it started working. Thanks!

greggers · Accepted Answer · 2017-10-12 21:07:21Z

8

Stick the profile keyword into your query if you're running a recent version:

GET binary/_search
{
   "profile": true,
    "query": {
     ...
        }
    }
}

If you had X-Pack, you could use the GUI based profiler to see where it's sluggish. Sadly not available in the open-source version, but it's just a prettier version of the output from the above - https://www.elastic.co/guide/en/kibana/5.6/xpack-profiler.html

You might be able to do a 30 day trial, or if you are lucky, maybe you already have the X-Pack licence.

edited Oct 12, 2017 at 21:07

answered Oct 12, 2017 at 21:02

greggers

1911 silver badge7 bronze badges

Comments

Arbo · Accepted Answer · 2017-10-12 20:50:09Z

You did everything right. You ran your query with explain and Elasticsearch did show you the detailed execution plan, under explanations.

However, the detailed execution plan here apparently has no details at all; this is because you actually did not run a query at all. You ran just a post_filter (the very first keyword in your example.)

Because you didn't specify a query at all, Elasticsearch matches every single document in your index and applies a constant score to all of them. This is why, under explanations, you see: "explanation": "ConstantScore(*:*)"

Because every single document in your index was matched, the post_filter you specified is applied against each one of them. This in itself is already slow; to make it even worse, post filters are never cached. (https://www.elastic.co/guide/en/elasticsearch/guide/current/_post_filter.html) So explain would never tell you any filter or cache information for your example, even if it could.

So, to answer your question: explain already returned the best detailed execution plan Elasticsearch could offer given your specific example. You can try a regular filter instead of a post_filter to give Elasticsearch a chance to build caches and increase performance.

stepwise_refinement · Accepted Answer · 2015-11-15 14:28:15Z

1

The usage for the Explain API is here

The explain api computes a score explanation for a query and a specific document. This can give useful feedback whether a document matches or didn’t match a specific query.

answered Nov 15, 2015 at 14:28

stepwise_refinement

3852 silver badges13 bronze badges

3 Comments

gao jiawei Over a year ago

I don't need to score calculation. i need a filter and cache info.

stepwise_refinement Over a year ago

Ah I see, it may be out of the scope of my knowledge. Indices Stats could provide some of the information you are looking for.

Suomynona Over a year ago

is there any explain feature that computes the score of all fetched documents? I mean not just one by one explanation?

Collectives™ on Stack Overflow

How to explain the query in elasticsearch

4 Answers 4

2 Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related