From: "Fujii.Yuki@df.MitsubishiElectric.co.jp"
	<Fujii.Yuki@df.MitsubishiElectric.co.jp>
To: Bruce Momjian <bruce@momjian.us>, Alexander Pyhalov
	<a.pyhalov@postgrespro.ru>
CC: 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>,
	"Fujii.Yuki@df.MitsubishiElectric.co.jp"
	<Fujii.Yuki@df.MitsubishiElectric.co.jp>
Subject: RE: Partial aggregates pushdown
Thread-Topic: Partial aggregates pushdown
Thread-Index:
 AQHYPYasTfJpCf0a4028YJm7JfY5bazLVbIAgAA/QgCAzsLGAICupJqAgAOhGYCAC47OQIAAf6sAgAAdMNCAAEyGgIAAtjrAgAD/KACABTrjIIAEWzOAgAyCOHCApWPpgIAAT1AggArXpoCAAFPI0IABO+iAgAMFFICABR/vAIAACpQAgABDk4CAAU/RgIBJ2XYAgAgC/oCAAC5WgIAGnISAgAQxEbCAAFHVgIAA0HSAgArptoCAAGksgIACJPSAgADj/XA=
Date: Thu, 22 Jun 2023 05:23:33 +0000
Deferred-Delivery: Thu, 22 Jun 2023 05:23:31 +0000
Message-ID:
 <OS3PR01MB6660315447C037EB4E6193D39522A@OS3PR01MB6660.jpnprd01.prod.outlook.com>
References: <ZDj4nwJANUU+kam8@momjian.us>
 <OS3PR01MB66607DC466F7F3B3909DA3CA954EA@OS3PR01MB6660.jpnprd01.prod.outlook.com>
 <8ffedecae0d21a3cd93805baf1dcc0de@postgrespro.ru>
 <OS3PR01MB66604E404B003A621B0C03A69552A@OS3PR01MB6660.jpnprd01.prod.outlook.com>
 <ZINW022iuYzf2ZwO@momjian.us>
 <OS3PR01MB6660B7C247726BC0E36DF4D69554A@OS3PR01MB6660.jpnprd01.prod.outlook.com>
 <ZIcfn8pnO4t29O5g@momjian.us>
 <OS3PR01MB666038A12CE1F9D81785D27E9555A@OS3PR01MB6660.jpnprd01.prod.outlook.com>
 <ZJD2BWVqRlJgz3n6@momjian.us>
 <f47c4cb0965a8de2a19322970e7ba7e3@postgrespro.ru>
 <ZJMavbn5trJ/wQj+@momjian.us>
In-Reply-To: <ZJMavbn5trJ/wQj+@momjian.us>
Accept-Language: ja-JP, en-US
Content-Language: ja-JP
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?iso-2022-jp?B?ZWdkSmFVMmJMZ0l2U0ZVR0NKZXUvSlZidk5iZnBGUHRQU3dNYnJCUS8w?=
 =?iso-2022-jp?B?djkrZThnSjYwRGVSaE9VME8rTmRYcGwwR0FBbEp3QW90NWJGd2lDY21m?=
 =?iso-2022-jp?B?cGl5K1lENWZ4UFN5aFcrT1hhanVmdStmZytoTDU5WW5QV2JBR1hUUTBD?=
 =?iso-2022-jp?B?azQvYmFFK3UxdXpISlJpd0VUaWxVSWFobzFCeE5xVm9DY0lHVE9VZDcz?=
 =?iso-2022-jp?B?bm4zcDduS1B5SXMwTTNjczBGbTB1bzRWa1FZNXlBWGRmUkNud29BN0cw?=
 =?iso-2022-jp?B?OFhPczZTc3EzNXUzME1hTzlHSEJZQ0l3ZjhydFZxc1kvaHRrSDdsTzl5?=
 =?iso-2022-jp?B?U2QrWGNlNllOaU9uZ2VnQmttcGdvcWlCYUNVSmM4cGdLYXVGVHdwTVBV?=
 =?iso-2022-jp?B?Q0lIUGJnQ3I4OTNyVXNaaFJCbHZsWkNMSVdMaXZzaHpTNUUrVUlkYWRO?=
 =?iso-2022-jp?B?RXkwL0pPTmlUbmlQd3l2Q3ZBMjNPaEpVTFNDdEwzeG5ab0Y2OXh2aG94?=
 =?iso-2022-jp?B?d2I4eXh2QTZrVy83WmQrYmF2VFVzajlpTmV6U0xSVHBUbG02dkprOXN5?=
 =?iso-2022-jp?B?RWhzcU5iMHZ3b2dvVHh0c3FLajk5QkQ1dWJ0UEdMUEdITHgvQmVVOGQ5?=
 =?iso-2022-jp?B?SHhOcmtSUlprQWxDZVppUWxESm1OcVh0eXkrQlF5Y1ZrTkJtMUFwTGlT?=
 =?iso-2022-jp?B?amlIM2VYVUNHYTc5Mk1FcGluUDdzbWFHR0V6NkczOThrRGNoZ0pnV2dR?=
 =?iso-2022-jp?B?ZjVOa3BBRjIyT2YxODV1dy90aGUzMTVsSW9vSk0rWDRQazBIUGdxcklp?=
 =?iso-2022-jp?B?UW9Id3ExVGJBQzJYNlBXcWJMWVI1clFMaldkWEdBVnhLUFpEeHl0aFdi?=
 =?iso-2022-jp?B?dGhZVzQxdFZwS09SZWpIaXpoc1UwUmhjSkhhNytSenRrdEJCbEphTlZF?=
 =?iso-2022-jp?B?TXRxVUt6NUhweGgyYmpPNWdaYzNCZDJONjNmMFg0VjJTSnQ3VVg2VE9r?=
 =?iso-2022-jp?B?eDE0UCtLZFNzbmxmRGliaExoSkx1eGtlQWUzV01YVmlqY3doZ0toa3RL?=
 =?iso-2022-jp?B?VUJacXdEZFpEdnBMVVFmYnBWRzErVzRyLzhLUmh1SlZ6TXpxdndQbUlo?=
 =?iso-2022-jp?B?OVk0NnN0WFp5aGpYb3ljanQwMHlVOTFvR1ZDN1FKMURTOTVCb1JBZ2hL?=
 =?iso-2022-jp?B?VGRlQWM5Wk9Gb1ZQT2dYTjlJSEhqZEFVOHJBaFF4WFlrWTlWU25WZGFl?=
 =?iso-2022-jp?B?NXFYbXdWNzQxUFRJR3FrdFdUWHU1WVlqUVhtQ1VmeXR4SEhKQi9FT3Iw?=
 =?iso-2022-jp?B?ZW9tN1lsVmptSUlZM0t2WTlGd3cvMFc5eXhpbnN0UVdFKzFWRGo1NlI2?=
 =?iso-2022-jp?B?SVJOWnVjdVVKY2NXUngzRkIzMEZrVW1peTFCbm8xbTFaTEk0ZVltVERR?=
 =?iso-2022-jp?B?SmVTS2VycUdxSHRvZG8yU3dlMkhVQ2VmbDFKaDl4Z29BWEk0TmExTFo1?=
 =?iso-2022-jp?B?SzhHM2pmU0hDeFFDaGw4WkZBOXI0YVdSZGV0bTc3dTZyUDZaY3BwSWxX?=
 =?iso-2022-jp?B?R1Btd0R5YzViRFpjZGUwb1RoRVNMaHNmaHRnVUhIRXB6OE9iSHZ5NHkz?=
 =?iso-2022-jp?B?bVJQdWwvWFJtU3NVbWVoRGZKMXduZy9ETFdMSGM2VFZuSzVqYW5tV1Vm?=
 =?iso-2022-jp?B?U2pHSzQrVmg5R254NlJUUzZocCtvb2IwZERHMEV1QW1ORmQyWFVqQXds?=
 =?iso-2022-jp?B?OUFWdkZOQlpoTXhiamNablN0dVl6cUpVdVl6UVVmallqRjBZYTdueUNF?=
 =?iso-2022-jp?B?enN6NDVlSjV5WUhEM1RtWTA2b1N0SUh1UWNyUkdNUWRzdWFyZnk4bEtn?=
 =?iso-2022-jp?B?MUZhVFJUeGtxaGN0WVo1QnpaVHpDeXQwNktwZVdQSjNDdFk4ZVRJOTlp?=
 =?iso-2022-jp?B?TjZvdnBOT3hha0lyMDI5d3dNR1ljQTZLT3ZMbjc4anV3RUtQRjEyTHFL?=
 =?iso-2022-jp?B?bTV4RURwRHEzRUJsTThrVTlodUZkUmdTbTltcHVhemxHRkswWi9jaXkv?=
 =?iso-2022-jp?B?UjFTM1NGK1NDRXdEMDMyYXRYNWhMNk9NQmliYTNVWUs3aElIQXMvYWxM?=
 =?iso-2022-jp?B?SEUzNUlYVWxtcWdrVUp5WW5rQWo3czVpb0J4MlIydWd1RlVyTWVJaWNv?=
 =?iso-2022-jp?B?V0ZVQzhHaEF4dnkwNVVVL3NOOVZsaUErTVZIeFp3WHpvbDBwa1psOEdG?=
 =?iso-2022-jp?B?ckRkUkdobjNBMjAwRUlJQkJSUFErWWJHeXMrS1QxZEN4K0hFM0l6SXN6?=
 =?iso-2022-jp?B?LzVLVA==?=
Content-Type: text/plain; charset="iso-2022-jp"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-OriginatorOrg: df.MitsubishiElectric.co.jp
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: OS3PR01MB6660.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 9a66ce57-810a-4099-5135-08db72e0df36
X-MS-Exchange-CrossTenant-originalarrivaltime: 22 Jun 2023 05:23:54.5722
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: c5a75b62-4bff-4c96-a720-6621ce9978e5
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: dFmAkdQkz3frkuqNN59evAeC/kgrsIbjFTMLwWc9l1uzH6Ugpd0UtGiBrdRO8b7MO44EKnxX2kGsiHnqHKAKZQ==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TY3PR01MB11922
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/OS3PR01MB6660315447C037EB4E6193D39522A%40OS3PR01MB6660.jpnprd01.prod.outlook.com>
Precedence: bulk

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 =3D
> > > 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.
>=20
> 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.
>=20
> After considering the options, I think we should have a postgres_fdw opti=
on called "planner_version_check", and default
> that false.  When false, a remote server version check will not be perfor=
med during planning and partial aggregates will be
> always be considered.  When true, a version check will be performed durin=
g planning and partial aggregate pushdown
> disabled for pre-PG 17 foreign servers during the query.
>=20
> If we want to be more specific, we can call it "check_partial_aggregate_s=
upport".
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 agg=
regate 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/=1B$BF#0f=1B(B =1B$BM:5,=1B(B(MELCO/=1B$B>pJsAm8&=1B(B =1B=
$B#D#M:GE,#G=1B(B) <Fujii.Yuki@df.MitsubishiElectric.co.jp>;
> PostgreSQL-development <pgsql-hackers@postgresql.org>; Andres Freund <and=
res@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@postgres=
pro.ru>
> Subject: Re: Partial aggregates pushdown
>=20
> 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 =3D
> > > 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.
>=20
> 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.
>=20
> After considering the options, I think we should have a postgres_fdw opti=
on called "planner_version_check", and default
> that false.  When false, a remote server version check will not be perfor=
med during planning and partial aggregates will be
> always be considered.  When true, a version check will be performed durin=
g planning and partial aggregate pushdown
> disabled for pre-PG 17 foreign servers during the query.
>=20
> If we want to be more specific, we can call it "check_partial_aggregate_s=
upport".
>=20
> --
>   Bruce Momjian  <bruce@momjian.us>        https://momjian.us
>   EDB                                      https://enterprisedb.com
>=20
>   Only you can decide what is important to you.