pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgres_fdw aggregate pushdown for group by with expressions
10+ messages / 3 participants
[nested] [flat]

* postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-03 09:34  Michał Kłeczek <michal@kleczek.org>
  0 siblings, 2 replies; 10+ messages in thread

From: Michał Kłeczek @ 2024-03-03 09:34 UTC (permalink / raw)
  To: pgsql-general

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?

Thanks,
Michal




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-03 13:16  Michał Kłeczek <michal@kleczek.org>
  parent: Michał Kłeczek <michal@kleczek.org>
  1 sibling, 2 replies; 10+ messages in thread

From: Michał Kłeczek @ 2024-03-03 13:16 UTC (permalink / raw)
  To: pgsql-general



> 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?

Thanks,
Michal






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-03 16:19  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Michał Kłeczek <michal@kleczek.org>
  1 sibling, 1 reply; 10+ messages in thread

From: Adrian Klaver @ 2024-03-03 16:19 UTC (permalink / raw)
  To: Michał Kłeczek <michal@kleczek.org>; pgsql-general

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






^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-03 17:04  Michał Kłeczek <michal@kleczek.org>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 10+ messages in thread

From: Michał Kłeczek @ 2024-03-03 17:04 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general

Thanks for help.

>> 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?

16.1 on both sides

> 
> What does
> 
> select proname, prosrc,  provolatile from pg_proc where proname = 'extract';
> 
> return?

extract | extract_date | i

(and bunch of similar records for other types - for timestampz provolatile is “s”)

—
Michal=

^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-03 17:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Michał Kłeczek <michal@kleczek.org>
  1 sibling, 2 replies; 10+ messages in thread

From: Tom Lane @ 2024-03-03 17:42 UTC (permalink / raw)
  To: Michał Kłeczek <michal@kleczek.org>; +Cc: pgsql-general

=?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org> writes:
> I’ve performed some more tests and it seems expressions with “extract” function are not pushed down at all -

Yeah :-(.  I traced through this, and it seems it's a collation
problem.  Internally, that call looks like
	extract('year'::text, date_column)
The text constant is marked as having collation "default", which means
that extract() is marked as having input collation "default", and then
it falls foul of this rule:

 * ... An expression is considered safe to send
 * only if all operator/function input collations used in it are traceable to
 * Var(s) of the foreign table.  That implies that if the remote server gets
 * a different answer than we do, the foreign table's columns are not marked
 * with collations that match the remote table's columns, which we can
 * consider to be user error.

Of course, extract() doesn't actually care about collation, but
postgres_fdw has no good way to know that.  Nor does it trust the
remote server to have the same set of collations the local one does,
so it doesn't want to try to fix this by sending explicit COLLATE
clauses.

Somebody ought to work on improving that mess sometime.  One thought
that comes to mind is to have a server option authorizing postgres_fdw
to believe that all local collations exist on the remote side.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-03 18:02  Michał Kłeczek <michal@kleczek.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 10+ messages in thread

From: Michał Kłeczek @ 2024-03-03 18:02 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general

Ok, so that means the workaround is to create an extension with year(date) and month(date) functions that internally would call extract.

Not ideal but workable I guess.

—
Michał 

> On 3 Mar 2024, at 18:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org> writes:
>> I’ve performed some more tests and it seems expressions with “extract” function are not pushed down at all -
> 
> Yeah :-(.  I traced through this, and it seems it's a collation
> problem.  Internally, that call looks like
>    extract('year'::text, date_column)
> The text constant is marked as having collation "default", which means
> that extract() is marked as having input collation "default", and then
> it falls foul of this rule:
> 
> * ... An expression is considered safe to send
> * only if all operator/function input collations used in it are traceable to
> * Var(s) of the foreign table.  That implies that if the remote server gets
> * a different answer than we do, the foreign table's columns are not marked
> * with collations that match the remote table's columns, which we can
> * consider to be user error.
> 
> Of course, extract() doesn't actually care about collation, but
> postgres_fdw has no good way to know that.  Nor does it trust the
> remote server to have the same set of collations the local one does,
> so it doesn't want to try to fix this by sending explicit COLLATE
> clauses.
> 
> Somebody ought to work on improving that mess sometime.  One thought
> that comes to mind is to have a server option authorizing postgres_fdw
> to believe that all local collations exist on the remote side.
> 
>            regards, tom lane





^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-04 06:46  Michał Kłeczek <michal@kleczek.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 2 replies; 10+ messages in thread

From: Michał Kłeczek @ 2024-03-04 06:46 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general



> On 3 Mar 2024, at 18:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org> writes:
>> I’ve performed some more tests and it seems expressions with “extract” function are not pushed down at all -
> 
> Yeah :-(.  I traced through this, and it seems it's a collation
> problem.  Internally, that call looks like
> 	extract('year'::text, date_column)
> The text constant is marked as having collation "default", which means
> that extract() is marked as having input collation "default", and then
> it falls foul of this rule:
[snip]

Thanks for explanation - I have a follow-up question.

Does that mean that *any* expression containing text constants is not going to be pushed down??
That would be a really serious issues I’d say.

—
Michal




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-04 07:09  Michał Kłeczek <michal@kleczek.org>
  parent: Michał Kłeczek <michal@kleczek.org>
  1 sibling, 0 replies; 10+ messages in thread

From: Michał Kłeczek @ 2024-03-04 07:09 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general



> On 4 Mar 2024, at 07:46, Michał Kłeczek <michal@kleczek.org> wrote:
> 
> 
> 
>> On 3 Mar 2024, at 18:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 
>> =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org> writes:
>>> I’ve performed some more tests and it seems expressions with “extract” function are not pushed down at all -
>> 
>> Yeah :-(.  I traced through this, and it seems it's a collation
>> problem.  Internally, that call looks like
>> 	extract('year'::text, date_column)
>> The text constant is marked as having collation "default", which means
>> that extract() is marked as having input collation "default", and then
>> it falls foul of this rule:
> [snip]
> 
> Thanks for explanation - I have a follow-up question.
> 
> Does that mean that *any* expression containing text constants is not going to be pushed down??
> That would be a really serious issues I’d say.

I’m afraid it is affecting all expressions containing functions.
This is really troublesome as it blocks push down for some basic (and collation independent) functions like coalesce.

—
Michal




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-04 12:35  Michał Kłeczek <michal@kleczek.org>
  parent: Michał Kłeczek <michal@kleczek.org>
  1 sibling, 0 replies; 10+ messages in thread

From: Michał Kłeczek @ 2024-03-04 12:35 UTC (permalink / raw)
  To: pgsql-general

Hi All,

> 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

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




^ permalink  raw  reply  [nested|flat] 10+ messages in thread

* Re: postgres_fdw aggregate pushdown for group by with expressions
@ 2024-03-04 15:22  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Michał Kłeczek <michal@kleczek.org>
  1 sibling, 0 replies; 10+ messages in thread

From: Tom Lane @ 2024-03-04 15:22 UTC (permalink / raw)
  To: Michał Kłeczek <michal@kleczek.org>; +Cc: pgsql-general

=?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org> writes:
> Does that mean that *any* expression containing text constants is not going to be pushed down??
> That would be a really serious issues I’d say.

If it contains text constants and not text variables, possibly so.
Keep in mind though that this rule is applied after constant-folding,
so that what we're left with is normally going to contain variables
at every level.  extract() is problematic because it combines a
text constant with a datetime (collation-less) variable.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2024-03-04 15:22 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-03 09:34 postgres_fdw aggregate pushdown for group by with expressions Michał Kłeczek <michal@kleczek.org>
2024-03-03 13:16 ` Michał Kłeczek <michal@kleczek.org>
2024-03-03 16:19   ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-03 17:04     ` Michał Kłeczek <michal@kleczek.org>
2024-03-03 17:42   ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-03 18:02     ` Michał Kłeczek <michal@kleczek.org>
2024-03-04 06:46     ` Michał Kłeczek <michal@kleczek.org>
2024-03-04 07:09       ` Michał Kłeczek <michal@kleczek.org>
2024-03-04 15:22       ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-04 12:35 ` Michał Kłeczek <michal@kleczek.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox