The rules, verbatim#
The planner lives in one module of the FlatQL backend, and its docstring states the contract. These are the rules as written in the code, not a summary:
Correctness rules for the pushdown (so the DuckDB stage never sees 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.
The phrase that matters is "biased sample". A LIMIT 100 sent to the cluster is only correct if the cluster saw the whole WHERE; otherwise it would return 100 rows of which the engine keeps 12. When the planner cannot prove a pushdown exact, it falls back to a scan capped at 50,000 rows per index.
A "groupable" field is one the cluster can bucket or sort on: keyword, the numeric types, boolean, date, ip. A text field is not, but when it has a .keyword sub-field the planner uses that for exact filters, sorts and groups. You never write .keyword in FlatQL SQL.
Three statements, three plans#
Each plan below is what Show as DSL displays for the statement, with the mode label it carries. The example indices are the ones used across these docs: orders, organizations, suppliers and logs-*.
1. Filter only#
SELECT order_id, amount, status
FROM orders
WHERE status = 'paid' AND currency = 'EUR';
Both conjuncts compare a keyword column to a literal, so both become term filters. The statement is a single table with no aggregation, so the planner also pushes a size: with no LIMIT written, it uses the row count the worksheet asked for. The request runs as a bounded search, mode search:
{
"query": {
"bool": {
"filter": [
{ "term": { "status": "paid" } },
{ "term": { "currency": "EUR" } }
]
}
},
"_source": ["amount", "currency", "order_id", "status"],
"size": 200
}
Only the columns the statement mentions, in the SELECT list or the WHERE, are requested through _source. DuckDB receives at most 200 rows and does almost nothing.
2. Filter, sort and limit#
SELECT order_id, amount, placed_at
FROM orders
WHERE status = 'paid' AND placed_at >= '2026-01-01'
ORDER BY placed_at DESC
LIMIT 50;
The date comparison becomes a range. Because the whole WHERE pushed and the statement is a simple single-table scan, ORDER BY and LIMIT push too. Each sort key is sent with "missing": "_last" so documents without the field sort the same way SQL would put NULLs last:
{
"query": {
"bool": {
"filter": [
{ "term": { "status": "paid" } },
{ "range": { "placed_at": { "gte": "2026-01-01" } } }
]
}
},
"_source": ["amount", "order_id", "placed_at", "status"],
"sort": [ { "placed_at": { "order": "desc", "missing": "_last" } } ],
"size": 50
}
The cluster returns exactly 50 rows in the right order; an OFFSET is added to the pushed size and skipped in the engine.
3. GROUP BY as a composite aggregation#
SELECT region, status_code, COUNT(*) AS requests, AVG(duration_ms) AS avg_ms
FROM "logs-*"
WHERE level = 'ERROR'
GROUP BY region, status_code;
region is a keyword, status_code is an integer, both aggregates are in the allowed set, and the WHERE pushed in full. The planner emits a composite aggregation with one terms source per key, mode aggregation:
{
"query": { "bool": { "filter": [ { "term": { "level": "ERROR" } } ] } },
"size": 0,
"aggs": {
"g": {
"composite": {
"size": 1000,
"sources": [
{ "k0": { "terms": { "field": "region", "missing_bucket": true } } },
{ "k1": { "terms": { "field": "status_code", "missing_bucket": true } } }
]
},
"aggs": { "m1": { "avg": { "field": "duration_ms" } } }
}
}
}
COUNT(*) is read from each bucket's doc_count. FlatQL pages through the buckets with after_key, 1,000 per request, until the cluster runs out or 50,000 groups have been collected (AGG_BUCKET_CAP); past that the result is marked truncated. missing_bucket: true keeps rows where a key is absent, matching SQL's NULL group. COUNT(DISTINCT col) becomes a cardinality aggregation, noted as approximate. The group by guide has more patterns; the composite aggregation entry explains the mechanism.
What disqualifies a pushdown#
The planner is conservative on purpose. These are the cases, taken from the translation code, where a predicate, a sort or a grouping stays in the engine.
A function on the column. LENGTH(name) > 10, DATE_TRUNC('month', placed_at) = '2026-01-01' or amount * 1.2 > 100 has no 1:1 filter, so the conjunct is not pushed. There is one deliberate exception: UPPER(col) = 'X', LOWER(col) LIKE 'ab%' and CONTAINS(UPPER(col), 'X') on a keyword field become case-insensitive term or wildcard queries, provided the literal is already in the matching case.
Equality on a text field. When a text column has a .keyword sub-field, description = 'x' becomes an exact term on that sub-field. When it has none, the planner sends a match_phrase to narrow the fetch and leaves the exact comparison to DuckDB. Range, LIKE and IN on a text field without a sub-field are not pushed at all. Use MATCH when you want the cluster's full-text behaviour.
Numbers against keywords. status_code = 500 on an integer field pushes. The same comparison on a keyword field does not, because "36" > 50 is numeric in SQL and lexicographic in a keyword range; the two are not equivalent.
OR, when one branch fails. OR is pushed as a bool.should with minimum_should_match: 1, across fields too, as long as both branches push. If either branch cannot be translated, the whole OR conjunct stays in the engine. NOT pushes as must_not combined with an exists filter so that NULLs are dropped as SQL requires.
Sorting on an expression. ORDER BY LOWER(name) or ORDER BY amount * rate cannot be sent, so neither the sort nor the LIMIT is pushed, and the index is scanned up to the cap.
More than one SELECT. A JOIN, a CTE or a subquery in FROM makes the statement multi-table or multi-select, and the LIMIT, ORDER BY and GROUP BY pushdowns are reserved for single-table, single-select statements. The WHERE conjuncts still push per table.
A grouping key that is not a plain field. GROUP BY DATE_TRUNC('day', placed_at) is a function, so the whole aggregation runs in the engine over a scan. The same goes for GROUP BY combined with DISTINCT, a window function or QUALIFY, and for aggregates outside the allowed set such as MEDIAN, LISTAGG or PERCENTILE_CONT.
A partially pushed WHERE. The moment one conjunct fails, LIMIT, ORDER BY and GROUP BY all stay home, because any of them would bias the sample, and the result notes that up to 50,000 rows were scanned and filtered in the engine.
How Show as DSL exposes it#
Every result carries one entry per index with the request body that was sent and a mode label: search for a bounded request, scroll (up to 50,000 rows) for a capped scan, aggregation for a composite aggregation, plus a flag for whether the whole WHERE was pushed. The Show as DSL panel renders this next to the grid; query history keeps the plan with each entry.
You can also ask for the plan without running anything:
EXPLAIN SELECT region, COUNT(*) FROM "logs-*" WHERE level = 'ERROR' GROUP BY region;
EXPLAIN returns one row per stage: a cluster row per index with the mode and the DSL body, an engine row with the SQL DuckDB will execute, and a note row for every warning the planner would attach.
Rewriting so more pushes#
Replace a function on a date with a range on the raw column:
-- scans: DATE_TRUNC is a function on the column
SELECT COUNT(*) FROM orders WHERE DATE_TRUNC('month', placed_at) = '2026-01-01';
-- pushes: a range the cluster can apply
SELECT COUNT(*) FROM orders WHERE placed_at >= '2026-01-01' AND placed_at < '2026-02-01';
Filter on keyword fields for equality, and use MATCH for words in text:
SELECT service, COUNT(*) AS n
FROM "logs-*"
WHERE level = 'ERROR' AND MATCH(message, 'timeout upstream')
GROUP BY service;
MATCH becomes a match query, so the whole WHERE pushes and the GROUP BY runs as an aggregation.
Keep the statement single-select when you want the cluster to sort, limit or aggregate; a CTE adds a second SELECT and switches the planner to scan mode for that table. For joins, put the selective filter on the smaller side: filtered tables are fetched first and their join keys go to the other side as a terms filter, as described in why Elasticsearch SQL can't do joins. And write a LIMIT; on a pushable statement it becomes the request size. The pushdown reference lists every operator and its translation.
Show as DSL and EXPLAIN are in the Free plan. Create an account, run a statement and read the plan it produced.