CODEWITHSUNDEEP
elasticsearch
gao jiawei
gao jiawei

Reputation: 349

How to explain the query in elasticsearch

I have a complex query in elasticsearch. it's slow. I want to optimize it. but i can'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(*:*)"
}
]
}

Upvotes: 34

Views: 38056

Answers (4)

BenG
BenG

Reputation: 15154

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

Upvotes: 67

greggers
greggers

Reputation: 181

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.

Upvotes: 8

Arbo
Arbo

Reputation: 438

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.

Upvotes: 3

stepwise_refinement
stepwise_refinement

Reputation: 385

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.

Upvotes: 1

Related Questions