The limit 10 appears in the dremio query ES SQL, resulting in a full table scan

1

SELECT * FROM “ES_databus”.“aa”.“default” where deleted = ‘0’ and audit_type in (‘C’,‘P’) order by audit_time DESC LIMIT 10 offset 0

query plan:
xxxxx], pushdown
=[{
“from” : 0,
“size” : 4000,
“query” : {
“bool” : {
“must” : [ {
“match” : {
“deleted” : {
“query” : “0”,
“type” : “boolean”
}
}
}, {
“bool” : {
“should” : [ {
“match” : {
“audit_type” : {
“query” : “C”,
“type” : “boolean”
}
}
}, {
“match” : {
“audit_type” : {
“query” : “P”,
“type” : “boolean”
}
}
} ]
}
} ]
}
}
}]) :

thx

2

@JoyJava we currently support pushing down filters, aggregations, projects and limits into Elasticsearch but not sorting operations. That’s why in your situation, the filters are pushed down, but not the ORDER BY + LIMIT combination. This is an improvement we are considering – we’ll reach out when available.

3

Can a single table query use the storage engine’s API?

thx

4

@JoyJava depends on the query and the operations in it.

5

Is this getting addressed in current release?