# Spark SQL: count(\*) fetches all data

**URL:** <https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [April 13, 2016, 11:37am UTC](https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242 "2016-04-13T11:37:40Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![Preeti\_Raj\_Buchhada](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/preeti_raj_buchhada/32/8971_2.png) [@Preeti\_Raj\_Buchhada](https://discuss.elastic.co/u/Preeti_Raj_Buchhada)\
**Post date:** [April 13, 2016, 11:37am UTC](https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242/1 "2016-04-13T11:37:40Z")

</div>

Hello,

I've got some success working with Spark SQL CLI to access our ES data.  
Environment:  
**ES** : 1.3.2  
**es-hadoop** : elasticsearch-hadoop-2.2.0  
**Spark** : spark-1.4.1-bin-hadoop2.6

The pushdown feature is working great!

However, I noticed that for queries like:

> SELECT count(member\_id),member\_id FROM data WHERE ( member\_id \> 2049510 AND member\_id \< 2049520 ) GROUP BY member\_id ;

es-hadoop ends up reading all data from ES.

And hence these queries are taking way too long. In my case it took 44 minutes!

> 5383 2049512  
> 875911 2049513  
> 363823 2049516  
> 840819 2049519  
> Time taken: 2644.187 seconds, Fetched 4 row(s)

Pushdown was working as confirmed from the logs:

> 2016-04-13 10:13:57 TRACE CommonsHttpTransport:463 - Tx [POST]@[x.x.x.x:9201][index/data/\_search?search\_type=scan&scroll=5m&size=50&\_source=member\_id&preference=\_shards:1;\_only\_node:ShzMilJKRU6rByUEQpbs0g] w/ payload [{"query":{"filtered":{ "query":{"query\_string":{ "query":"datatype:dal" }},"filter": { "and" : [**{"range":{"member\_id":{"gt" :2049510}}},{"range":{"member\_id":{"lt" :2049520}}}]** } }}}]

Is there a better way to get counts grouped by some field with the current es-hadoop version?

---

_[View the full topic](https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242)._