I am running a SQL query in starburst-presto. It's connected to elasticsearch using the relevant connector.
The SQL has an "order by" clause. This clause is not pushing down to elasticsearch. Basically, I want to sort the data in elasticsearch based on a specific field and return the result. The query with "order by" is taking a lot of time using presto. Is it possible to manage is somehow to get an optimal performance?
SQL: select e.employee_id from elasticsearch.es."employee:id:""2390571"" && (doj_timestamp:(>=15965454 && <=15972366)) sort=employee_id:desc" e offset 0 limit 5;
The above query is returning random results.
Can anyone please help here?
Your query has both ORDER BY and LIMIT, so in Presto it is called a Top N query. Presto currently does not provide Top N pushdown, but this feature is in the works.
Please file an issue for Elasticsearch connector TopN pushdown. We will implement it anyway, but direct user feedback helps understand issue priorities.
You can learn more on the #pushdown channel on Presto community slack.