Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rgprW-008BXN-L1 for pgsql-general@arkaria.postgresql.org; Sun, 03 Mar 2024 17:42:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rgprV-001Xbe-5P for pgsql-general@arkaria.postgresql.org; Sun, 03 Mar 2024 17:42:57 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rgprU-001XbW-Qo for pgsql-general@lists.postgresql.org; Sun, 03 Mar 2024 17:42:57 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rgprR-002fWZ-Qh for pgsql-general@postgresql.org; Sun, 03 Mar 2024 17:42:56 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 423HgrZv3299635; Sun, 3 Mar 2024 12:42:53 -0500 From: Tom Lane To: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= cc: PG-General Mailing List Subject: Re: postgres_fdw aggregate pushdown for group by with expressions In-reply-to: <5FC17CB3-3048-455D-B638-B48949F78D06@kleczek.org> References: <5FC17CB3-3048-455D-B638-B48949F78D06@kleczek.org> Comments: In-reply-to =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= message dated "Sun, 03 Mar 2024 14:16:01 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3299633.1709487773.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Sun, 03 Mar 2024 12:42:53 -0500 Message-ID: <3299634.1709487773@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk =3D?utf-8?Q?Micha=3DC5=3D82_K=3DC5=3D82eczek?=3D writ= es: > I=E2=80=99ve performed some more tests and it seems expressions with =E2= =80=9Cextract=E2=80=9D 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 ge= ts * a different answer than we do, the foreign table's columns are not mark= ed * 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