Timestamp filter pushdown doesn't work in some cases

Deninc commented 2 years ago

When I have this query it does push down: select count(*) from table where "dateTime" >= '2022-06-07';

But for timestamp query it does not: select count(*) from table where "dateTime" >= TO_TIMESTAMP('2022-06-07', 'YYYY-MM-DD');

Using:

fdw 5_4_0
postgres 14.3
mongo 4.4.1

iosifnicolae2 commented 2 years ago

cc https://github.com/EnterpriseDB/mongo_fdw/issues/127

vaibhavdalvi93 commented 2 years ago

Thanks, @Deninc for reporting an issue.

The mongo_fdw don't support pushdown of function in WHERE clause. You can refer document [1] for more details.

[1] https://www.enterprisedb.com/docs/mongo_data_adapter/latest/06_features_of_mongo_fdw/#where-clause-pushdown

Deninc commented 2 years ago

Thanks @vaibhavdalvi93. Any plan for support it in the near future? I think filter by date time is such an essential feature.

vaibhavdalvi93 commented 2 years ago

Pushing down functions in WHERE has some challenges because MongoDB is a NoSQL database. It is not straightforward to map PostgreSQL functions with the corresponding MongoDB collection operators. Also, it requires too much manual work to map Postgres functions into MongoDB functions/operators.

We also need to note that we should be having the same result when pushed down and when not pushed down. Also, result correctness and code maintainability are challenges too.

Due to these factors, we have not prioritised it on our roadmap. However, feel free to post a patch in a pull request. We will definitely review it and take it further.

addisonElliott commented 2 years ago

I agree that pushing down functions to MongoDB is difficult.

In the example given above (TO_TIMESTAMP('2022-06-07', 'YYYY-MM-DD')), the function should be evaluated and simplified before being sent to MongoDB by the planner. It's similar to how if you do WHERE a >= 2 * 4, it'll simplify 2 * 4 to 8.

Upon further review, this isn't an issue with MongoDB FDW. PostgreSQL will evaluate and simplify any immutable functions with constant arguments. The functions TO_TIMESTAMP, TO_CHAR, etc appear to be immutable but actually aren't because they depend on variable settings like datestyle, locale, etc.

If you want to assume they're immutable, you can create a simple pass-through function that's immutable and calls the function. Then PostgreSQL will simplify the function and it'll work correctly in the FDW.

Example function:

CREATE OR REPLACE FUNCTION to_timestamp_imm(text, format) 
  RETURNS TIMESTAMP
AS
$BODY$
    select to_timestamp($1, $2);
$BODY$
LANGUAGE sql
IMMUTABLE;

Sources:

EnterpriseDB / mongo_fdw

Timestamp filter pushdown doesn't work in some cases #162