# Dremio not pushing filters during mangoscan hence costing performance issues

**URL:** <https://community.dremio.com/t/dremio-not-pushing-filters-during-mangoscan-hence-costing-performance-issues/12174>\
**Category:** Uncategorized\
**Created:** [August 13, 2024, 8:44am UTC](https://community.dremio.com/t/dremio-not-pushing-filters-during-mangoscan-hence-costing-performance-issues/12174 "2024-08-13T08:44:52Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![pavankumargundeti](https://avatars.discourse-cdn.com/v4/letter/p/e9a140/32.png) [@pavankumargundeti](https://community.dremio.com/u/pavankumargundeti)\
**Post date:** [August 13, 2024, 8:44am UTC](https://community.dremio.com/t/dremio-not-pushing-filters-during-mangoscan-hence-costing-performance-issues/12174/1 "2024-08-13T08:44:52Z")

</div>

performance results:

1. Time taken: \<1 sec  
sql query:  
SELECT \*,TO\_DATE(“time”) as “date” FROM “c8y\_source”.“t482751”.“measurements” where “type” = ‘88564359-6116-438c-9adf-da9f7776363f’ and “time” \>= ‘2024-08-06 00:00:00.000’ AND “time” \<= ‘2024-08-31 00:00:00.000’ limit 100000  
physical plan sample mangoscan:

```auto
00-08 MongoScan(table=[[c8y_source, t482751, measurements]], MongoQuery=[use t482751; db.measurements.find({"$and": [{"$and": [{"type": {"$ne": null}}, {"type": {"$eq": "88564359-6116-438c-9adf-da9f7776363f"}}]}, {"$and": [{"time": {"$ne": null}}, {"time": {"$gte": {"$date": "2024-08-06T00:00:00Z"}}}]}, {"$and": [{"time": {"$ne": null}}, {"time": {"$lte": {"$date": "2024-08-31T00:00:00Z"}}}]}]},

```

profile: performant.zip  
2.Time taken 39s:  
query:  
select \*,TO\_DATE(“time”) as “date” from (  
SELECT \* FROM “c8y\_source”.“t482751”.“measurements” where “type” = ‘88564359-6116-438c-9adf-da9f7776363f’ and “time” \>= ‘2024-08-06 00:00:00.000’ AND “time” \<= ‘2024-08-31 00:00:00.000’ )  
where “date” \>= TO\_DATE(‘2024-08-06’,‘YYYY-MM-DD’)  
AND “date” \<= TO\_DATE(‘2024-08-31’,‘YYYY-MM-DD’)

physical plan:

```auto
00-07 Filter(condition=[AND(=($2, '88564359-6116-438c-9adf-da9f7776363f'), >=($0, 2024-08-06 00:00:00), <=($0, 2024-08-31 00:00:00), >=(TO_DATE($0), 2024-08-06), <=(TO_DATE($0), 2024-08-31))]) : rowType = RecordType(TIMESTAMP(3) 
00-08 MongoScan(table=[[c8y_source, t482751, measurements]], MongoQuery=[use t482751; db.measurements.find({}, 

```

notperformant.zip  
query 1: perform \<1 sec because the filters are part of mango scan. Pushdown happened  
query 2: perform worst 39sec because the filters are not part of the mangoscan. hence complete data is fetched in to the demio and filters step is applied.  
I want in query 2 atleast subset of filters(fixed columns ) to be used in mangoscan and on top of it transformed fields filter needs to apply at dremio.

@balaji.ramaswamy any help here. Please analyze the profiles. Only delta between queries is “date” which is custom field is used it is not pushing all the filters to mangoscan. Here filters can be split. Static/existing fields can pushdown and tranformed one can apply at dremio layer.

Let us know any way we can override the physical plans to pass needed filters params in mangoscan for query 2.

Thanks,  
Pavan

[performant.zip](https://community.dremio.com/uploads/short-url/yWIpbVBTQAHwNrHW495wPphVH6S.zip) (80.1 KB)  
[notperformant.zip](https://community.dremio.com/uploads/short-url/oOUFe6NiTinkkb8wttXT1JxC6yt.zip) (85.4 KB)

---

<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:** [August 13, 2024, 4:16pm UTC](https://community.dremio.com/t/dremio-not-pushing-filters-during-mangoscan-hence-costing-performance-issues/12174/2 "2024-08-13T16:16:20Z")

</div>

@pavankumargundeti

Currently any type conversion will not get pushed down. Is it possible to store the column in MongoDB as date?

Thanks,  
Bali