Re: postgres_fdw aggregate pushdown for group by with expressions

[Adrian Klaver]

On 3/3/24 05:16, Michał Kłeczek wrote:

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

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?

What Postgres versions on both ends?

What does

select proname, prosrc,  provolatile from pg_proc where proname = 'extract';

return?

Thanks,
Michal

--
Adrian Klaver
adrian.kla...@aklaver.com