Re: postgres_fdw aggregate pushdown for group by with expressions

> On 3 Mar 2024, at 10:34, Michał Kłeczek <michal(at)kleczek(dot)org> wrote:
>
> Hi,
>
> I have the following foreign table:
>
> CREATE FOREIGN TABLE t1 (
> grouping_column text,
> date_column date,
> whatever_data int
> );
>
> The query is:
>
> SELECT
> sum(whatever_data)
> FROM
> t1
> GROUP BY
> grouping_colulmn, extract(YEAR FROM date_column), extract(MONTH FROM date_column);
>
> From my (preliminary) testing postgres_fdw will not push down this aggregate query
> - it will happily push down query with only “grouping_column” or “grouping_column, date_column" in GROUP BY
>
> Is there a way to somehow push down the query with expressions in GROUP BY?

I’ve performed some more tests and it seems expressions with “extract” function are not pushed down at all -
the WHERE criteria from the following query are not pushed down as well and filter is performed locally:

SELECT
*
FROM
t1
WHERE extract(YEAR FROM date_column) = 2023

I was under impression that “extract” meets all criteria for pushable expressions - looks like I am wrong?

What would be the expression to achieve the same but such that postgres_fdw would push it down?

Thanks,
Michal