RE: Partial aggregates pushdown - Mailing list pgsql-hackers

Hi Mr.Momjian, Mr.Pyhalov, hackers.

> From: Bruce Momjian <bruce@momjian.us>
> Sent: Thursday, June 22, 2023 12:44 AM
> On Tue, Jun 20, 2023 at 09:59:11AM +0300, Alexander Pyhalov wrote:
> > > Therefore, it seems like it would be near-zero cost to just call
> > > conn =
> > > GetConnection() and then PQserverVersion(conn), and ReleaseConnection().
> > > You can then use the return value of PQserverVersion() to determine
> > > if you can push down partial aggregates.
> >
> > Hi.
> > Currently we don't get remote connection while planning if
> > use_remote_estimate is not set.
> > Such change would require to get remote connection in planner, not in
> > executor.
> > This can lead to change of behavior (like errors in explain when user
> > mapping is wrong - e.g. bad password is specified).
> > Also this potentially can lead to establishing connections even when
> > plan node is not actually used (like extreme example - select
> > sum(score) from t limit 0).
> > I'm not saying we shouldn't do it - just hint at possible consequences.
>
> Agreed.  I noticed it was doing FDW connections during optimization, but didn't see the postgres_fdw option that
would
> turn it off.
> Interestingly, it is disabled by default.
>
> After considering the options, I think we should have a postgres_fdw option called "planner_version_check", and
default
> that false.  When false, a remote server version check will not be performed during planning and partial aggregates
willbe 
> always be considered.  When true, a version check will be performed during planning and partial aggregate pushdown
> disabled for pre-PG 17 foreign servers during the query.
>
> If we want to be more specific, we can call it "check_partial_aggregate_support".
Thank you both for your advice.
We will address the compatibility issues as follows.

Approach1-3:
I will add a postgres_fdw option "check_partial_aggregate_support".
This option is false, default.
Only if this option is true, postgres_fdw connect to the remote server and get the version of the remote server.
And if the version of the remote server is less than PG17, then partial aggregate push down to the remote server is
disable.

Sincerely yours,
Yuuki Fujii

--
Yuuki Fujii
Information Technology R&D Center Mitsubishi Electric Corporation

> -----Original Message-----
> From: Bruce Momjian <bruce@momjian.us>
> Sent: Thursday, June 22, 2023 12:44 AM
> To: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
> Cc: Fujii Yuki/藤井 雄規(MELCO/情報総研 ＤＭ最適Ｇ) <Fujii.Yuki@df.MitsubishiElectric.co.jp>;
> PostgreSQL-development <pgsql-hackers@postgresql.org>; Andres Freund <andres@anarazel.de>; Tom Lane
> <tgl@sss.pgh.pa.us>; Tomas Vondra <tomas.vondra@enterprisedb.com>; Julien Rouhaud <rjuju123@gmail.com>;
> Daniel Gustafsson <daniel@yesql.se>; Ilya Gladyshev <i.gladyshev@postgrespro.ru>
> Subject: Re: Partial aggregates pushdown
>
> On Tue, Jun 20, 2023 at 09:59:11AM +0300, Alexander Pyhalov wrote:
> > > Therefore, it seems like it would be near-zero cost to just call
> > > conn =
> > > GetConnection() and then PQserverVersion(conn), and ReleaseConnection().
> > > You can then use the return value of PQserverVersion() to determine
> > > if you can push down partial aggregates.
> >
> > Hi.
> > Currently we don't get remote connection while planning if
> > use_remote_estimate is not set.
> > Such change would require to get remote connection in planner, not in
> > executor.
> > This can lead to change of behavior (like errors in explain when user
> > mapping is wrong - e.g. bad password is specified).
> > Also this potentially can lead to establishing connections even when
> > plan node is not actually used (like extreme example - select
> > sum(score) from t limit 0).
> > I'm not saying we shouldn't do it - just hint at possible consequences.
>
> Agreed.  I noticed it was doing FDW connections during optimization, but didn't see the postgres_fdw option that
would
> turn it off.
> Interestingly, it is disabled by default.
>
> After considering the options, I think we should have a postgres_fdw option called "planner_version_check", and
default
> that false.  When false, a remote server version check will not be performed during planning and partial aggregates
willbe 
> always be considered.  When true, a version check will be performed during planning and partial aggregate pushdown
> disabled for pre-PG 17 foreign servers during the query.
>
> If we want to be more specific, we can call it "check_partial_aggregate_support".
>
> --
>   Bruce Momjian  <bruce@momjian.us>        https://momjian.us
>   EDB                                      https://enterprisedb.com
>
>   Only you can decide what is important to you.