Some advice on the single table query （Elasticsearch）

1

A variety of full-table scans occur when we use Dremio query ES (single table )

We think the implementation of the ES query function is sufficient, so it is recommended to change the ES single table query to HTTP API

thx

2

Hi @JoyJava,

Could you share the query profile from Dremio, so we could determine if the query was pushed down to ES API or not.

Also I’d recommend you reading on how ES pushdown works within Dremio:

http://docs.dremio.com/data-sources/elasticsearch.html #Elastic Pushdowns section

Thanks,
Danny

3

Hi @dbrody,

We found some scenarios where the pushdown failed, most of which have been submitted to the forums,
E.g:

  1. Order by + limit
  2. Subqueries : (SELECT * FROM “es-test”. “Cif.apply_main”. “Default” where _id in (SELECT _id FROM “es-test”. “Cif.tom_orders”. “Default” where _id <5000) )
  3. in (element) > planner.in.subquery.threshold
  4. count (distinct xxxx)

thx

4

shardsNumber = 5 and count(distinct xxx) pushdown error

image

image
image.png1699×450 26.6 KB

image