performance results:
- 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:
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:
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 (80.1 KB)
notperformant.zip (85.4 KB)