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 1qoOXV-0016ob-Rp for pgsql-hackers@arkaria.postgresql.org; Thu, 05 Oct 2023 13:37:18 +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 1qoOXT-00DcMk-E2 for pgsql-hackers@arkaria.postgresql.org; Thu, 05 Oct 2023 13:37:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoOXT-00DcMc-1U for pgsql-hackers@lists.postgresql.org; Thu, 05 Oct 2023 13:37:16 +0000 Received: from mail-ed1-x535.google.com ([2a00:1450:4864:20::535]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qoOXM-000B2P-Cm for pgsql-hackers@lists.postgresql.org; Thu, 05 Oct 2023 13:37:15 +0000 Received: by mail-ed1-x535.google.com with SMTP id 4fb4d7f45d1cf-533f193fc8dso1698799a12.2 for ; Thu, 05 Oct 2023 06:37:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696513027; x=1697117827; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=bh41/wVpXKA3GZJGrZhIc4ym0YzCpOKCDGLY+7hEy5I=; b=AdtusfgOMXXIjWSL1unOd5R8QNcoCfY9nGvBZ7jGdUD1A3wTc+CYpN+/JphStreuDH Gp7H4RdU+FghJ6i2aHq255eWg/NE57WGvYW0RAgwFsq8jzweAAYymb9o8PHPUY87JDpE e6N0EaPmQ3XWprxEf6Lj1Enp94DFV8d3vbiCRxIpQhA8btECv02S6TCo+TY8nKxfhyaX tNNHTuSTFwXIT6IkzqIWr0/OjS7HYefn8RmYRYxpyhMzBZVu1pKwAmTeMiJcOuPKwGII LeUSBsHKQWB4hvZc2Ar8/NqdJf+4f2H+sKAevwKzxtabjzUZ6L6PHswyMDQ/96/gxvbP GNvQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696513027; x=1697117827; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=bh41/wVpXKA3GZJGrZhIc4ym0YzCpOKCDGLY+7hEy5I=; b=oavVH3zJE+ikSoQjpc0nOSMShRnPKuKlaDpkDM4cTAoEW+zyzFuDlYN38homAxBp0e aIGAnRRZ6ilPEzuPK24i2dyPAyoqDmY0xLZrb3QjLVypWHlUZ/2taozVEVzU7q5BWH2R ZAVkV784XTRluOt5+C4ZRwlHr3iEI8U5ho6rzE3xiffpKJY25RNi1myuzNMUiDrJjUKb IiihTGrIXFK/Zj7Zk4K6EKCb1I9lKjtSAM90+NvpAKzztHppptJcxwUklh+8Cl+ZaKak EOR8hvhRuzHpCngvrejrLcFgbhEy1qWr8N0zP08JZE1qCyop+byJQ6jd1DgrKeAMmd2J 3E7A== X-Gm-Message-State: AOJu0YwLB+GDlsGh/gk3IpMXvP6WnMZ/KI1QHrOCsWUGCtYL2ZP+Gy+r ErDWtfMkjtn95BBFijwWTyLuBvrIHcTUeEsFcri2nE68 X-Google-Smtp-Source: AGHT+IGKik0Z6PiQ2vJ2K9qSdfI5JrE1abkjYU2Df4iUiSdzn4CEwypj807bcOdaQ9/6XNiu3lN54+NjGVIauNXK0z0= X-Received: by 2002:aa7:d94e:0:b0:530:bd6b:7a94 with SMTP id l14-20020aa7d94e000000b00530bd6b7a94mr5343515eds.24.1696513026930; Thu, 05 Oct 2023 06:37:06 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Jean-Christophe Arnu Date: Thu, 5 Oct 2023 15:36:55 +0200 Message-ID: Subject: Re: FDW pushdown of non-collated functions To: PostgreSQL Hackers Content-Type: multipart/alternative; boundary="00000000000053bc5d0606f83888" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000053bc5d0606f83888 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Dear Hackers, I figured out this email was sent at release time. The worst time to ask for thoughts on a subject IMHO. Anyway, I hope this email will pop the topic over the stack! Thank you! Le ven. 8 sept. 2023 =C3=A0 16:41, Jean-Christophe Arnu = a =C3=A9crit : > Dear hackers, > > I recently found a weird behaviour involving FDW (postgres_fdw) and > planning. > > Here=E2=80=99s a simplified use-case: > > Given a remote table (say on server2) with the following definition: > > CREATE TABLE t1( > ts timestamp without time zone, > x bigint, > x2 text > ); > --Then populate t1 table:INSERT INTO t1 > SELECT > current_timestamp - 1000*random()*'1 day'::interval > ,x > ,''||x > FROM > generate_series(1,100000) as x; > > > This table is imported in a specific schema on server1 (we do not use > use_remote_estimate) also with t1 name in a specific schema: > > On server1: > > CREATE SERVER server2 > FOREIGN DATA WRAPPER postgres_fdw > OPTIONS ( > host '127.0.0.1', > port '9002', > dbname 'postgres', > use_remote_estimate 'false' > ); > CREATE USER MAPPING FOR jc > SERVER server2 > OPTIONS (user 'jc'); > CREATE SCHEMA remote; > > IMPORT FOREIGN SCHEMA public > FROM SERVER server2 > INTO remote ; > > On a classic PostgreSQL 15 version the following query using date_trunc() > is executed and results in the following plan: > > jc=3D# explain (verbose,analyze) select date_trunc('day',ts), count(1) fr= om remote.t1 group by date_trunc('day',ts) order by 1; > QUERY PLAN = -----------------= ---------------------------------------------------------------------------= --------------------------------------- > Sort (cost=3D216.14..216.64 rows=3D200 width=3D16) (actual time=3D116.6= 99..116.727 rows=3D1001 loops=3D1) > Output: (date_trunc('day'::text, ts)), (count(1)) > Sort Key: (date_trunc('day'::text, t1.ts)) > Sort Method: quicksort Memory: 79kB > -> HashAggregate (cost=3D206.00..208.50 rows=3D200 width=3D16) (actu= al time=3D116.452..116.532 rows=3D1001 loops=3D1) > Output: (date_trunc('day'::text, ts)), count(1) > Group Key: date_trunc('day'::text, t1.ts) > Batches: 1 Memory Usage: 209kB > -> Foreign Scan on remote.t1 (cost=3D100.00..193.20 rows=3D256= 0 width=3D8) (actual time=3D0.384..106.225 rows=3D100000 loops=3D1) > Output: date_trunc('day'::text, ts) > Remote SQL: SELECT ts FROM public.t1 > Planning Time: 0.077 ms > Execution Time: 117.028 ms > > > Whereas the same query with date_bin() > > jc=3D# explain (verbose,analyze) select date_bin('1day',ts,'2023-01-01'),= count(1) from remote.t1 group by 1 order by 1; > = QUERY PLAN = = ------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------- > Foreign Scan (cost=3D113.44..164.17 rows=3D200 width=3D16) (actual time= =3D11.297..16.312 rows=3D1001 loops=3D1) > Output: (date_bin('1 day'::interval, ts, '2023-01-01 00:00:00'::timest= amp without time zone)), (count(1)) > Relations: Aggregate on (remote.t1) > Remote SQL: SELECT date_bin('1 day'::interval, ts, '2023-01-01 00:00:0= 0'::timestamp without time zone), count(1) FROM public.t1 GROUP BY 1 ORDER = BY date_bin('1 day'::interval, ts, '2023-01-01 00:00:00'::timestamp without= time zone) ASC NULLS LAST > Planning Time: 0.114 ms > Execution Time: 16.599 ms > > > > With date_bin() the whole expression is pushed down to the remote server, > whereas with date_trunc() it=E2=80=99s not. > > I dived into the code and live debugged. It turns out that decisions to > pushdown or not a whole query depends on many factors like volatility and > collation. In the date_trunc() case, the problem is all about collation ( > date_trunc() on timestamp without time zone). And decision is made in the > foreign_expr_walker() in deparse.c ( > https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dcontri= b/postgres_fdw/deparse.c;h=3Defaf387890e3f85c419748ec3af5d1e9696c9c4c;hb=3D= 86648dcdaec67b83cec20a9d25b45ec089a7c624#l468 > ) > > First the function is tested as shippable (able to be pushed down) and > date_trunc() and date_bin() both are. > > Then parameters sub-expressions are evaluated with collation and > =E2=80=9Cshippability=E2=80=9D, and they all are with both functions. > > Then we arrive at this code portion: > > if (fe->inputcollid =3D=3D InvalidOid) > /* OK, inputs are all noncollatable */ ;else if (inner_cxt.state !=3D F= DW_COLLATE_SAFE || > fe->inputcollid !=3D inner_cxt.collation) > return false; > > For date_trunc() function : > > - > > fe variable contains the sub-expressions/arguments merged constraints > such as fe->inputcollid. This field is evaluated to 100 (default > collation) so codes jumps to else statement and evaluates the if > predicates. This 100 inputcollationid is due to text predicate 'day'. > - > > inner_cxt.state contains FDW_COLLATE_STATE but inner_cxt.collation > contains 0 (InvalidOid) so the control flow returns false thus the > function cannot be pushed down. > > For date_bin() function : > > - fe variable contains the sub-expressions/arguments merged > constraints. Here, fe->inputcollid is evaluated to 0 (InvalidOid) thus > skips the else statement and continues the control flow in the > function. > > For date_bin(), all arguments are =E2=80=9Cnon-collatable=E2=80=9D argume= nts (timestamp > without time zone and interval). > > So the situation is that date_trunc() is a =E2=80=9Cnon-collatable=E2=80= =9D function > failing to be pushed down whereas it may be a good idea to do so. > > Maybe we could add another condition to the first if statement in order t= o > allow a =E2=80=9Cno-collation=E2=80=9D function to be pushed down even if= they have > =E2=80=9Ccollatable=E2=80=9D parameters. I=E2=80=99m not sure about the p= ossible regressions of > behaviour of this change, but it seems to work fine with date_trunc() and > date_part() (which suffers the same problem). > > Here=E2=80=99s the following change > > /* > * If function's input collation is not derived from a foreign > * Var, it can't be sent to remote. > */if (fe->inputcollid =3D=3D InvalidOid || > fe->funccollid =3D=3D InvalidOid) > /* OK, inputs are all noncollatable */ ;else if (inner_cxt.state !=3D F= DW_COLLATE_SAFE || > fe->inputcollid !=3D inner_cxt.collation) > return false; > > I don=E2=80=99t presume this patch is free from side effects or fits all = use-cases. > > A patch (tiny) is attached to this email. This patch works against > master/head at the time of writing. > Thank you for any thoughts. > > -- > Jean-Christophe Arnu > --=20 Jean-Christophe Arnu --00000000000053bc5d0606f83888 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Dear Hackers,

I figured out = this email was sent at release time. The worst time to ask for thoughts on = a subject IMHO. Anyway, I hope this email will pop the topic over the stack= !
Thank you!

<= div dir=3D"ltr" class=3D"gmail_attr">Le=C2=A0ven. 8 sept. 2023 =C3=A0=C2=A0= 16:41, Jean-Christophe Arnu <jcarnu@= gmail.com> a =C3=A9crit=C2=A0:

Dear hackers,

I recently fo= und a weird behaviour involving FDW (postgres_fdw) and plannin= g.

Here=E2=80=99s a simplified use-case:

Given a remote table (= say on server2) with the following definition:

<=
span>CREATE TABLE t1(
  ts timestamp without time zone,
  x  bigint,
  x2 text
);

--Then populate t1 table:
INSERT INTO t1=20
       SELECT=20
        current_timestamp - 1000*random()*'1 day'::interva=
l
        ,x
        ,''||x =20
       FROM=20
        generate_series(1,100000) as=
 x;

This table is imported in a specific schema on server= 1 (we do not use use_remote_estimate) also with t= 1 name in a specific schema:

On server1:

=
CREATE SERVER server2=20
       FOREIGN DATA WRAPPER  postgres_fdw=20
       OPTIONS (
              host '127.0.0.1',=20
              port '9002',=20
              dbname 'postgres',=20
              use_remote_estimate 'false'
       );

CREATE USER MAPPING FOR jc=20
       SERVER server2=20
       OPTIONS (user 'jc');

CREATE SCHEMA remote;

IMPORT FOREIGN SCHEMA public=20
       FROM SERVER server2=20
       INTO remote ;

On a classic PostgreSQL 15 version the following query usin= g date_trunc() is executed and results in the following plan:<= /p>

jc=3D# explain (verbose,analyze) select date_trunc('day',ts), count(1<=
/span>) from remote.t1 group by date=
_trunc('day',ts) order by 1;
                                                            QUERY PLAN     =
                                                       =20
---------------------------------------------------------------------=
--------------------------------------------------------------
 Sort  (cost=3D216.14..216.64<=
/span> rows=3D200 width=3D16) (actual time=3D116.699<=
/span>..116.727 rows=3D=
1001 loops=3D1)
   Output: (date_trunc('day'::text, ts)), (count=
(1))
   Sort Key: (date_trunc('day'::text, t1.ts))
   Sort Method: quicksort  Memory: 79kB
   ->  HashAggregate  (cost=3D206.00..208.50 rows=
=3D200 width=3D16) (actual time=3D116.452..116=
.532 rows=3D1001 loops=
=3D1)
         Output: (date_trunc('day'::text, ts)), =
count(1)
         Group Key: date_trunc('day'::tex=
t, t1.ts)
         Batches: 1  Memory Usage: 209kB
         ->  Foreign Scan o=
n remote.t1  (cost=3D100.00..193.20 rows=3D2560 widt=
h=3D8) (actual time=3D<=
span>0.384..106.225 rows=3D100000 loops=3D1)
               Output: date_trunc('day'::text, ts)
               Remote SQL: SELECT ts FROM public.t1
 Planning Time: 0.077 ms
 Execution Time: 117.028 ms

Whereas the same query with date_bin()

jc=3D# explain (verbose,analyze) select da= te_bin('1day',ts,'2023-01-01'), <= span>count(1) from remote.t1 group by 1 order by 1; = QUERY PLAN = = =20 ---------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------- Foreign Scan (cost=3D113.44.= .164.17 rows=3D200 width=3D16) (actual time=3D= 11.297..16.312 rows=3D1001 loops=3D1) Output: (date_bin('1 day'::interval, t= s, '2023-01-01 00:00:00'::timestamp without time zone)), (count(1)) Relations: Aggregate on (remote.t1) Remote SQL: SELECT date_bin('1 day&#= 39;::interval, ts, '2023-01-01 00:00:00'<= /span>::timestamp without time zone)= , count(1) FROM public.t1 GROU= P BY 1 ORDER BY = date_bin('1 day'::interval, ts, = 9;2023-01-01 00:00:00'::timestamp without time zone) ASC NULLS LAST Planning Time: 0.114 ms Execution Time: 16.599 ms

With date_bin() the whole expression is pushed= down to the remote server, whereas with date_trunc() it=E2=80= =99s not.

I dived into the code and live debugged. It turns out that decisions to=20 pushdown or not a whole query depends on many factors like volatility=20 and collation. In the date_trunc() case, the problem is all ab= out collation (date_trunc() on timestamp without time zo= ne). And decision is made in the foreign_expr_walker() = in deparse.c (https://git.postgresql.org/gitw= eb/?p=3Dpostgresql.git;a=3Dblob;f=3Dcontrib/postgres_fdw/deparse.c;h=3Defaf= 387890e3f85c419748ec3af5d1e9696c9c4c;hb=3D86648dcdaec67b83cec20a9d25b45ec08= 9a7c624#l468)

First the function is tested as shippable (able to = be pushed down) and date_trunc() and date_bin() b= oth are.

Then parameters sub-expressions are evaluated with collation= and =E2=80=9Cshippability=E2=80=9D, and they all are with both functions.<= /p>

Then we arrive at this code portion:

if (f=
e->inputcollid =3D=3D InvalidOid)
  /* OK, inputs are all noncollatable */ ;
else if (inner_cxt.state !=3D FDW_COLLATE_SAFE ||
             fe->inputcollid !=3D inner_cxt.collation)
  return false;

For date_trunc() function :

  • fe variable contains the sub-expressions/arguments merged c= onstraints such as fe->inputcollid. This field is evaluated= to 100 (default collation) so codes jumps to else statement and evaluates the if predicates. This 100 inputcollationid is due to text predicate 'day'.

  • inner_cxt.state contains FDW_COLLATE_STATE but= inner_cxt.collation contains 0 (InvalidOid= ) so the control flow returns false thus the function cannot be push= ed down.

For date_bin() function :

  • fe variable contains the sub-expressions/arguments merged constraints. Her= e, fe->inputcollid is evaluated to 0 (In= validOid) thus skips the else statement and continues t= he control flow in the function.

For date_bin(), a= ll arguments are =E2=80=9Cnon-collatable=E2=80=9D arguments (timestam= p without time zone and interval).

So the situati= on is that date_trunc() is a =E2=80=9Cnon-collatable=E2=80=9D = function failing to be pushed down whereas it may be a good idea to do so.<= /p>

Maybe we could add another condition to the first if statement in order to=20 allow a =E2=80=9Cno-collation=E2=80=9D function to be pushed down even if t= hey have=20 =E2=80=9Ccollatable=E2=80=9D parameters. I=E2=80=99m not sure about the pos= sible regressions of=20 behaviour of this change, but it seems to work fine with date_trunc()= and date_part() (which suffers the same problem).

<= p>Here=E2=80=99s the following change

/*
* If function's input collation is not derived from a foreign
* Var, it can't be sent to remote.
*/
if (fe->inputcollid =3D=3D InvalidOid ||
        fe->funccollid =3D=3D InvalidOid)
  /* OK, inputs are all noncollatable */ ;
else if (inner_cxt.state !=3D FDW_COLLATE_SAFE ||
             fe->inputcollid !=3D inner_cxt.collation)
     return false;

I don=E2=80=99t presume this patch is free from side effect= s or fits all use-cases.

A patch (tiny) is attached to this email. Th= is patch works against master/head at the time of writing.

Thank you= for any thoughts.

--
Jean-Chri= stophe Arnu


--
Jean-Christop= he Arnu
--00000000000053bc5d0606f83888--