Hi All, > On 3 Mar 2024, at 10:34, Michał Kłeczek <mic...@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 to push down aggregates) — Michal
