On 3/3/24 05:16, Michał Kłeczek wrote:
On 3 Mar 2024, at 10:34, Michał Kłeczek <michal@xxxxxxxxxxx> 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@xxxxxxxxxxx
