From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Michał Kłeczek <michal(at)kleczek(dot)org>, PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: postgres_fdw aggregate pushdown for group by with expressions |
Date: | 2024-03-03 16:19:10 |
Message-ID: | 2b5c8483-ff9b-4fb2-8ed8-2e77e0f1f936@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3/3/24 05:16, Michał Kłeczek wrote:
>
>
>> 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?
What Postgres versions on both ends?
What does
select proname, prosrc, provolatile from pg_proc where proname = 'extract';
return?
>
> Thanks,
> Michal
>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Michał Kłeczek | 2024-03-03 17:04:31 | Re: postgres_fdw aggregate pushdown for group by with expressions |
Previous Message | Michał Kłeczek | 2024-03-03 13:16:01 | Re: postgres_fdw aggregate pushdown for group by with expressions |