Show as DSL - the Query DSL behind every SQL statement

Every SQL result comes with the requests FlatQL sent to the cluster and the statement the engine executed. The DSL tab is where you check that the fast path was taken.

Opening the tab#

Run a SQL statement and switch the result view from Table to DSL (the other views are Chart, Pivot and JSON). The tab is available for SQL results only; an Elasticsearch request typed in the worksheet is its own DSL.

Take this statement:

SELECT country, COUNT(*) AS orgs, AVG(founded_year) AS avg_year
FROM organizations
WHERE status = 'active' AND founded_year >= 2010
GROUP BY country
ORDER BY orgs DESC
LIMIT 20;

The DSL tab shows one block, headed organizations with the mode chip aggregation:

{
  "query": {
    "bool": {
      "filter": [
        {"term": {"status": "active"}},
        {"range": {"founded_year": {"gte": 2010}}}
      ]
    }
  },
  "size": 0,
  "aggs": {
    "g": {
      "composite": {
        "size": 1000,
        "sources": [{"k0": {"terms": {"field": "country", "missing_bucket": true}}}]
      },
      "aggs": {
        "m0": {"value_count": {"field": "_index"}},
        "m1": {"avg": {"field": "founded_year"}}
      }
    }
  }
}

Above the blocks is a line of notes, here "GROUP BY was computed by Elasticsearch aggregations." Below them a collapsed Statement as executed by the engine shows the SQL the engine ran over the returned buckets: the ORDER BY and LIMIT, applied to a table of pre-aggregated rows.

Reading the breakdown#

Each index in the statement gets a block with four pieces of information.

Pushed down, and therefore visible in the body: WHERE conjuncts as term, terms, range, wildcard, exists, ids, match and match_phrase filters; ORDER BY as sort; LIMIT as size; GROUP BY with COUNT, SUM, AVG, MIN, MAX and COUNT DISTINCT as a composite aggregation; the _source list narrowed to the columns the statement needs; and, in joins, the keys of the side fetched first as a terms filter on the other side (the note reads "suppliers was filtered to the 312 join keys found in orders").

Run by the engine, and therefore visible only in the engine statement: joins, CTEs, window functions, QUALIFY, HAVING, DISTINCT, UNION, any function around a column in WHERE, ORDER BY on an expression, FLATTEN, and aggregates such as MEDIAN or APPROX_PERCENTILE. The pushdown page lists the exact rules; the functions page marks which functions move work to the engine.

A partly pushed WHERE produces a note: "Part of the WHERE clause on orders could not be sent to Elasticsearch, so up to 50,000 rows were scanned and filtered here." Query history keeps the same plan for every run and marks these as "WHERE partly evaluated in the engine", with rows and time per index, so you can find them later across the team.

Copying into Kibana Dev Tools or curl#

The body is a complete _search request. In Kibana Dev Tools, type the request line and paste the body under it:

POST /organizations/_search

followed by the JSON above. From a shell:

curl -s -u "$ES_USER:$ES_PASS" "https://es.example.com:9200/organizations/_search" \
  -H 'Content-Type: application/json' \
  -d '{"query":{"bool":{"filter":[{"term":{"status":"active"}},{"range":{"founded_year":{"gte":2010}}}]}},"size":0,"aggs":{"g":{"composite":{"size":1000,"sources":[{"k0":{"terms":{"field":"country","missing_bucket":true}}}]},"aggs":{"m0":{"value_count":{"field":"_index"}},"m1":{"avg":{"field":"founded_year"}}}}}}'

For a scroll-mode block the body carries "sort": ["_doc"] and "size": 5000; add ?scroll=2m to the URL to reproduce the first page. When you want the bodies as text to paste elsewhere, EXPLAIN SELECT ... returns each one in the detail column of its result row, which copies as a cell.

Debugging a slow statement#

Work down this list with the DSL tab open.

1. Mode is scroll (up to 50,000 rows) with match_all. Nothing was pushed. Usually a function around a column in WHERE, a comparison between a numeric literal and a keyword field, or an unqualified column in a join. Rewrite the predicate as column-versus-literal.
2. Mode is scroll but filters are present, and the result is truncated. The WHERE was pushed and still matched more than 50,000 documents. Add a time range or a LIMIT, or turn the statement into a GROUP BY the cluster can run.
3. Two blocks in scroll mode for a join. The join-key pushdown did not apply: the first side was truncated, the key was an expression, or it had more than 50,000 distinct values. Filter the smaller side harder so it is fetched first and completely.
4. Mode is aggregation but the result says more than 50,000 groups. The key is too fine. Aggregate on a coarser field or filter.
5. Everything is pushed and it is still slow. The cluster itself is slow for this request. Paste the body into Dev Tools with "profile": true, or check the took next to the row count: FlatQL shows the time each index request took, so you can tell cluster time from engine time.

A worked example of a GROUP BY and the aggregation it becomes is on the GROUP BY on Elasticsearch page. If you prefer to see what the cluster's own SQL would do with the same statement, switch to Cluster SQL and the tab shows the cluster's translation instead.