# Simple query trying to return billions of rows

**URL:** <https://community.dremio.com/t/simple-query-trying-to-return-billions-of-rows/2629>\
**Category:** Uncategorized\
**Created:** [January 26, 2019, 4:36pm UTC](https://community.dremio.com/t/simple-query-trying-to-return-billions-of-rows/2629 "2019-01-26T16:36:25Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jhaynie](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jhaynie/32/1131_2.png) [@jhaynie](https://community.dremio.com/u/jhaynie)\
**Post date:** [January 26, 2019, 4:36pm UTC](https://community.dremio.com/t/simple-query-trying-to-return-billions-of-rows/2629/1 "2019-01-26T16:36:25Z")

</div>

We have a pretty simple query that took 33 minutes, 23 seconds and is trying to return almost 2 billion rows. It doesn’t match that many records in the DB.

 ![18%20AM](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/d/d6db2bc4f95fb4ea19300211a03eb2fc0eb47ffe.png)

Here’s the profile

[d38ae1f1-0de4-4c1e-8e69-a033bf99607b.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/1/1f33202fb74cd0afeee3eccf9daf095f89dccd57.zip) (2.1 MB)

This is running latest 3.0 of Dremio.

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [January 27, 2019, 10:39pm UTC](https://community.dremio.com/t/simple-query-trying-to-return-billions-of-rows/2629/2 "2019-01-27T22:39:58Z")

</div>

Hi @jhaynie

It looks like what is called an expanding and that can cause such behavior. See below on how your query progressed operator wise. It is always bottom up. We started at MAJOR FRAGMENT 25 JSON\_SUB\_SCAN (all the way to the bottom) and we were doing good until PROJECT MAJOR FRAGMENT 7 the PROJECT that had a result of 3,341 records. The subsequent HASH\_JOIN, MAJOR FRAGMENT 6 has 1,545,905 but the output from the HASH\_JOIN which is the PROJECT (MAJOR FRAGMENT 5) is 1,915,864,488 (~ 2B records)

Can you please check your JOIN and see if there are any JOINS missing or you have duplicate rows from the JOINS? Screenshot attached too

Kindly let us know if you have any questions

> {“majorFragmentId”:0,“operatorId”:0,“name”:“SCREEN”,“records”:1}  
> {“majorFragmentId”:0,“operatorId”:1,“name”:“PROJECT”,“records”:1}  
> {“majorFragmentId”:0,“operatorId”:2,“name”:“WRITER\_COMMITTER”,“records”:1}  
> {“majorFragmentId”:0,“operatorId”:3,“name”:“ARROW\_WRITER”,“records”:1915864488}  
> {“majorFragmentId”:0,“operatorId”:4,“name”:“PROJECT”,“records”:1915864488}  
> {“majorFragmentId”:0,“operatorId”:5,“name”:“PROJECT”,“records”:1915864488}  
> {“majorFragmentId”:0,“operatorId”:6,“name”:“HASH\_JOIN”,“records”:1545905}  
> {“majorFragmentId”:0,“operatorId”:7,“name”:“PROJECT”,“records”:3341}  
> {“majorFragmentId”:0,“operatorId”:8,“name”:“HASH\_JOIN”,“records”:4583}  
> {“majorFragmentId”:0,“operatorId”:9,“name”:“PROJECT”,“records”:3341}  
> {“majorFragmentId”:0,“operatorId”:10,“name”:“PROJECT”,“records”:3341}  
> {“majorFragmentId”:0,“operatorId”:11,“name”:“HASH\_JOIN”,“records”:3342}  
> {“majorFragmentId”:0,“operatorId”:12,“name”:“SELECTION\_VECTOR\_REMOVER”,“records”:3341}  
> {“majorFragmentId”:0,“operatorId”:13,“name”:“SELECTION\_VECTOR\_REMOVER”,“records”:3341}  
> {“majorFragmentId”:0,“operatorId”:14,“name”:“PROJECT”,“records”:3341}  
> {“majorFragmentId”:0,“operatorId”:15,“name”:“HASH\_JOIN”,“records”:17}  
> {“majorFragmentId”:0,“operatorId”:16,“name”:“FILTER”,“records”:4174}  
> {“majorFragmentId”:0,“operatorId”:17,“name”:“FILTER”,“records”:4174}  
> {“majorFragmentId”:0,“operatorId”:18,“name”:“SELECTION\_VECTOR\_REMOVER”,“records”:3341}  
> {“majorFragmentId”:0,“operatorId”:19,“name”:“PROJECT”,“records”:16}  
> {“majorFragmentId”:0,“operatorId”:20,“name”:“PARQUET\_ROW\_GROUP\_SCAN”,“records”:1}  
> {“majorFragmentId”:0,“operatorId”:21,“name”:“JSON\_SUB\_SCAN”,“records”:4174}  
> {“majorFragmentId”:0,“operatorId”:22,“name”:“JSON\_SUB\_SCAN”,“records”:4174}  
> {“majorFragmentId”:0,“operatorId”:23,“name”:“FILTER”,“records”:4174}  
> {“majorFragmentId”:0,“operatorId”:24,“name”:“PARQUET\_ROW\_GROUP\_SCAN”,“records”:16}  
> {“majorFragmentId”:0,“operatorId”:25,“name”:“JSON\_SUB\_SCAN”,“records”:4174}

 ![expanding-join](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/e/e00d7b7c4e9a711109b21772e01d94676a386c20.png)

---

<div class="post-metadata">

**Author:** ![jhaynie](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jhaynie/32/1131_2.png) [@jhaynie](https://community.dremio.com/u/jhaynie)\
**Post date:** [January 30, 2019, 4:18am UTC](https://community.dremio.com/t/simple-query-trying-to-return-billions-of-rows/2629/3 "2019-01-30T04:18:41Z")

</div>

we found a table that wasn’t fully joined and that fixed it … thanks.