Pushdown rules: what runs on the cluster and what runs in the engine

Every SQL statement is split in two. The part that translates exactly to Query DSL runs on the cluster; the rest runs in FlatQL's engine over the rows that came back. Knowing where the line falls is the difference between a 40 ms query and a scan.

The rules#

These are the correctness rules from the engine, quoted as written, so the engine never works on a biased sample:

• WHERE: a conjunct is pushed only when it maps 1:1 to a filter (column vs literal on a field we can filter exactly). The full WHERE still runs in DuckDB; pushing just shrinks the fetch.
• LIMIT / ORDER BY: pushed only for single-table statements with no aggregation, DISTINCT, window or QUALIFY, and only when the whole WHERE was pushed.
• GROUP BY: pushed as a composite aggregation when every grouping key is a plain groupable field, every aggregate is COUNT / SUM / AVG / MIN / MAX (plus approximate COUNT DISTINCT) on a plain field, and the WHERE was pushed in full.
• Otherwise the index is scanned up to the fetch cap and the response carries a warning.

"Single-table" means one index, one SELECT, no JOIN. A statement with a CTE or a subquery has more than one SELECT, so its LIMIT and GROUP BY stay in the engine even when it names one index. Its WHERE conjuncts still push, as long as each column is qualified with the table alias (o.country, not country).

Which predicates push#

A conjunct pushes when one side is a plain column and the other a literal, and the field can be filtered exactly: keyword, numeric, date, boolean, ip and similar types, or a text field that has a .keyword sub-field.

What does not push: a function or arithmetic on the column (DATE_TRUNC('day', created_at) = '2026-11-03', founded_year + 1 > 2015), a column compared with another column, IN with a subquery, a numeric literal against a keyword field ('36' > 50 is numeric in SQL but lexicographic in a keyword range, so FlatQL refuses it), and LIKE on a text field without a keyword sub-field.

Equality on a text field with no keyword sub-field becomes a match_phrase, which is the closest exact filter the cluster offers; the engine then applies the real =.

The fetch cap#

When a statement is not fully pushable, FlatQL scrolls the index with the pushed filters, in four parallel slices of 5,000 documents, and stops at 50,000 rows per index per statement. If the index had more matching documents the result carries this warning in the execution line and in the DSL tab:

organizations has 1,240,000 matching documents; only the first 50,000 were scanned. Add WHERE filters that Elasticsearch can apply (=, IN, ranges, LIKE on keyword fields) or a LIMIT to get complete results.

A WHERE that pushed only in part adds:

Part of the WHERE clause on organizations could not be sent to Elasticsearch, so up to 50,000 rows were scanned and filtered here.

Pushed GROUP BY has its own limit: a composite aggregation returns at most 50,000 groups, and COUNT(DISTINCT) on the cluster is approximate (about 1 percent off at large counts). The engine says both in the execution line. The limits page collects every cap in one place.

Example 1: filter, sort and limit on the cluster#

SELECT name, country, founded_year
FROM organizations
WHERE country IN ('DEU', 'AUT', 'CHE') AND founded_year >= 2015
ORDER BY founded_year DESC
LIMIT 100;

The cluster received one POST /organizations/_search in search mode: a bool filter with a terms on country and a range on founded_year, a sort on founded_year descending with missing values last, _source limited to the three columns, and size: 100. The engine received 100 rows and did nothing but display them.

{
  "query": {"bool": {"filter": [
    {"terms": {"country": ["DEU", "AUT", "CHE"]}},
    {"range": {"founded_year": {"gte": 2015}}}
  ]}},
  "_source": ["country", "founded_year", "name"],
  "sort": [{"founded_year": {"order": "desc", "missing": "_last"}}],
  "size": 100
}

Example 2: GROUP BY as a composite aggregation#

SELECT status, currency, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE placed_at >= '2026-01-01'
GROUP BY status, currency
ORDER BY revenue DESC;

The cluster received a size: 0 search in aggregation mode: the range filter on placed_at, and a composite aggregation with two terms sources (status, currency, each with missing_bucket: true) and two metrics underneath, a value_count for the row count and a sum on amount. FlatQL paged through the buckets 1,000 at a time. The engine sorted the buckets by revenue; the execution line reads GROUP BY was computed by Elasticsearch aggregations.

Example 3: a partial pushdown, and the fix#

SELECT service, COUNT(*) AS errors
FROM "logs-*"
WHERE level = 'ERROR'
  AND DATE_TRUNC('day', "@timestamp") = '2026-11-03'
GROUP BY service;

level = 'ERROR' pushes. DATE_TRUNC(...) = ... is an expression on a column, so it does not, and because the WHERE was not pushed in full the GROUP BY cannot be pushed either. The cluster received a scroll (up to 50,000 rows) request with only the term on level, and the engine grouped whatever came back, with the partial-WHERE warning attached.

Rewrite the date test as a range:

SELECT service, COUNT(*) AS errors
FROM "logs-*"
WHERE level = 'ERROR'
  AND "@timestamp" >= '2026-11-03' AND "@timestamp" < '2026-11-04'
GROUP BY service;

Now every conjunct pushes, the grouping key is a plain keyword field, and the cluster received a size: 0 aggregation across every index matching the pattern: a term on level, a range on @timestamp, and a composite on service with a count.

Writing statements that push down#

• Filter on keyword, numeric, date and boolean fields with =, IN, ranges and BETWEEN. Use MATCH for text fields.
• Write dates as ranges, not as DATE_TRUNC or YEAR() on the column.
• Put a LIMIT on single-table statements; it becomes size on the search.
• Keep GROUP BY keys as plain fields and aggregates as COUNT, SUM, AVG, MIN, MAX or COUNT(DISTINCT). MEDIAN, LISTAGG, STRING_AGG and the percentile functions run in the engine over a scan.
• In joins, CTEs and subqueries, qualify every column with its alias so the WHERE conjuncts can be attributed to an index. An unqualified column in a multi-table statement also makes FlatQL fetch every field of every index instead of just the ones you selected.
• Check the DSL tab after a run. The mode chip (search, aggregation, scroll) tells you which case you are in. Show as DSL explains the tab.

For GROUP BY patterns in depth see Elasticsearch GROUP BY in SQL. Joins covers how join keys are pushed to the other index.