Re: postgres_fdw aggregate pushdown for group by with expressions

On 3/3/24 05:16, Michał Kłeczek wrote:
> 
> 
>> 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
>>
>> 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.klaver@aklaver.com