# Some advice on the single table query （Elasticsearch）

**URL:** <https://community.dremio.com/t/some-advice-on-the-single-table-query-elasticsearch/687>\
**Category:** Uncategorized\
**Created:** [January 29, 2018, 12:49am UTC](https://community.dremio.com/t/some-advice-on-the-single-table-query-elasticsearch/687 "2018-01-29T00:49:48Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![JoyJava](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joyjava/32/293_2.png) [@JoyJava](https://community.dremio.com/u/JoyJava)\
**Post date:** [January 29, 2018, 12:49am UTC](https://community.dremio.com/t/some-advice-on-the-single-table-query-elasticsearch/687/1 "2018-01-29T00:49:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dbrody](https://avatars.discourse-cdn.com/v4/letter/d/b9bd4f/32.png) [@dbrody](https://community.dremio.com/u/dbrody)\
**Post date:** [January 29, 2018, 10:09pm UTC](https://community.dremio.com/t/some-advice-on-the-single-table-query-elasticsearch/687/2 "2018-01-29T22:09:27Z")

</div>

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](http://docs.dremio.com/data-sources/elasticsearch.html) #Elastic Pushdowns section

Thanks,  
Danny

---

<div class="post-metadata">

**Author:** ![JoyJava](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joyjava/32/293_2.png) [@JoyJava](https://community.dremio.com/u/JoyJava)\
**Post date:** [January 30, 2018, 2:51am UTC](https://community.dremio.com/t/some-advice-on-the-single-table-query-elasticsearch/687/3 "2018-01-30T02:51:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JoyJava](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joyjava/32/293_2.png) [@JoyJava](https://community.dremio.com/u/JoyJava)\
**Post date:** [January 30, 2018, 2:58am UTC](https://community.dremio.com/t/some-advice-on-the-single-table-query-elasticsearch/687/4 "2018-01-30T02:58:27Z")

</div>

**shardsNumber = 5 and count(distinct xxx) pushdown error**

![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/e300eda534a5b2f2675ebd49c7ce5006f1c77007.png)

 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/ea616670adfd6978d71dfca3edff181d56ac737e.png)

![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/e063789c35c457607386bea3e2c9c9cdadd82e3b.png)