# Inexplicable scanning of rows with joins

**URL:** <https://community.dremio.com/t/inexplicable-scanning-of-rows-with-joins/11539>\
**Category:** Uncategorized\
**Created:** [February 22, 2024, 11:43am UTC](https://community.dremio.com/t/inexplicable-scanning-of-rows-with-joins/11539 "2024-02-22T11:43:30Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mdes](https://avatars.discourse-cdn.com/v4/letter/m/e19b73/32.png) [@mdes](https://community.dremio.com/u/mdes)\
**Post date:** [February 22, 2024, 11:43am UTC](https://community.dremio.com/t/inexplicable-scanning-of-rows-with-joins/11539/1 "2024-02-22T11:43:30Z")

</div>

Hello Dremio crowd,

we have encountered some seemingly strange behavior from the query planner in regards to queries with joins.  
Query 1:  
SELECT  
COUNT(\*)  
FROM  
“folder1”.“folder2”.“k”  
INNER JOIN “folder1”.“folder3”.“ud” ON  
k.m = ud.m AND  
k.bk = ud.bk AND  
k.g = ud.g  
– INNER JOIN “folder1”.“folder2”.“mgj” ON  
– mgj.m = k.m AND  
– mgj.bk = k.bk AND  
– mgj.g = k.g AND mgj.m IS NOT NULL AND mgj.bk IS NOT NULL and mgj.g IS NOT NULL  
INNER JOIN “folder1”.“folder2”.“b” ON  
ud.m = b.m AND  
ud.bk = b.bk AND  
ud.g = b.g AND  
k.id = b.k\_id AND  
b.k\_id IS NOT NULL

Query 2:  
SELECT  
COUNT(\*)  
FROM  
“folder1”.“folder2”.“k”  
INNER JOIN “folder1”.“folder3”.“ud” ON  
k.m = ud.m AND  
k.bk = ud.bk AND  
k.g = ud.g  
INNER JOIN “folder1”.“folder2”.“mgj” ON  
mgj.m = k.m AND  
mgj.bk = k.bk AND  
mgj.g = k.g AND mgj.m IS NOT NULL AND mgj.bk IS NOT NULL and mgj.g IS NOT NULL  
INNER JOIN “folder1”.“folder2”.“b” ON  
ud.m = b.m AND  
ud.bk = b.bk AND  
ud.g = b.g AND  
k.id = b.k\_id AND  
b.k\_id IS NOT NULL

b has around 1bn rows, k has more than 100m rows.  
ud only has 14 rows mit 14 different combinations of m/bk/g and mgj has around 1500 such combinations and rows.  
The curious thing is that the query without the mgj JOIN scans more than 700m rows and takes a very long time, whereas the one with it only scans 20m and is very quick. This also only happens including the NOT NULL conditions in mgj.  
Do we really have to consider joining this table, even if we do not need any information from it?

I did notice that in the planning tab of the raw profile, it says that a cached plan is used for the long query. Could that have something to do with it? If so, how do I clear that cache? I added a random where condition, which removed the cache notification, but also did not make the query faster, so I guess that’s not it.

Unfortunately I cannot share the entire profile.

Best regards

---

<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:** [March 3, 2024, 4:48am UTC](https://community.dremio.com/t/inexplicable-scanning-of-rows-with-joins/11539/2 "2024-03-03T04:48:29Z")

</div>

@mdes It could be possible that the join order changed, very hard to tell without looking at the plan. You can disable plan cache and see if it helps, the only issue is you have to rebuild the plan cache

```auto
planner.query_plan_cache_enabled

```