Re: postgres_fdw aggregate pushdown for group by with expressions

Hi All,

> On 3 Mar 2024, at 10:34, Michał Kłeczek <michal@kleczek.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

I was able to make it work by creating an extension and installing it on both sides with functions:

year(date) IMMUTABLE
month(date) IMMUTABLE

But it looks there are more issues - the following queries work fine (ie. are pushed down to remote server):

SELECT * FROM t1 WHERE grouping_column LIKE ‘abcd%’;
and
SELECT sum(whatever_data) FROM t1 GROUP BY grouping_column, year(date_column), month(date_column)

But together - unfortunately not:

SELECT grouping_column, sum(whatever_data) FROM t1 WHERE grouping_column LIKE ‘abcd%' GROUP BY grouping_column,
year(date_column)

In this case aggregate is performed locally.
Not sure if this is because of statistics (but I set fdw_tuple_cost to a very high value 99999 to try to force planner
topush down aggregates) 

—
Michal