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 1qecgN-007IPo-5F for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Sep 2023 14:42:03 +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 1qecgL-00B79a-GK for pgsql-hackers@arkaria.postgresql.org; Fri, 08 Sep 2023 14:42:01 +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 1qecgL-00B79P-1i for pgsql-hackers@lists.postgresql.org; Fri, 08 Sep 2023 14:42:00 +0000 Received: from mail-ed1-x52c.google.com ([2a00:1450:4864:20::52c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qecgF-003uGG-Ts for pgsql-hackers@lists.postgresql.org; Fri, 08 Sep 2023 14:42:00 +0000 Received: by mail-ed1-x52c.google.com with SMTP id 4fb4d7f45d1cf-52eed139ec2so1201631a12.2 for ; Fri, 08 Sep 2023 07:41:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1694184114; x=1694788914; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=x24jwMt3BUYw8cnHZaX2w3rbGljYVitTjPc4TjgmCBw=; b=ePHRpUS+JfGkXlYTLdJWLCgG25rxVvMy2fkfhxahM7OUMqpRU+S+SwFczd981M1mjX g5hBKYskMcysSr7psNY3jBdzGbOsXKAxXsA7oB8A5NraETWWric9ZHeh99nzFgsZ0R4j Cyvz1xkzrFnfkJHNOhArBih4SYW3Xb9cILRKs9nwT9wGr3mWqa514cN7VG2y961rcfZP z+SjtG8eSJoYHGQp040BCd9RL8hdc8SzF+c9b8d0hRsMhyAeVGRDLiF1chB2LSBQZ75f vdxvEJKSZ4Vno+E9wGvBWdy+VIpp7NLNluH188O/2lk7P0CIRIiFBmls+dcwwPFm3bAb QN+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1694184114; x=1694788914; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=x24jwMt3BUYw8cnHZaX2w3rbGljYVitTjPc4TjgmCBw=; b=lqwqZ+fym4D+ozR+fXbyU8uB7ME8Eo5ljCtlAng7Km4JcJ4iHmp5rmZLfqFPybZ3h+ BxZNPBdr2xOkhU62DgbO7p44ljA6BAAK+/uLoeZGYEfo2phCIJzHw5ncOMDAXoWND+We EV7MEXC8W0Cuk7WP2RRTNkYIGJqxllrget65SUQe9Twhjf+KvKfaijj5pdzAWUb0wxR2 HvJjrSaElGqzB2GOsNn5UXJQtbG4olgfRvWMZXLNQYmiwr8b3OyJR9jRvD12rdbrn7GU LvOlIvgRB2PHKgNymsl/OsPz92FtAiuqbbyQtHg+6G2aRO1OMSaJwuV+0KGfQ383qgYi Me2Q== X-Gm-Message-State: AOJu0YyAQ/pQYR/ZO7PL3/qDvQlXc3V4SMskFh472BUmAna8Y7CsExvf tnLNMdVfggczwntBMbL9eu+gxCZk04ZiJ+BarMjLYZ4Pwccyew== X-Google-Smtp-Source: AGHT+IHw8w0ebdUVQMWWWDUph3CxQBvrn4qXzF84IVWyb78KIKUIMhFfKthkez6sk9njRw58InNj4W2cSlqMAtYJOSw= X-Received: by 2002:a05:6402:1254:b0:523:b133:5c7e with SMTP id l20-20020a056402125400b00523b1335c7emr2318222edw.1.1694184113867; Fri, 08 Sep 2023 07:41:53 -0700 (PDT) MIME-Version: 1.0 From: Jean-Christophe Arnu Date: Fri, 8 Sep 2023 16:41:42 +0200 Message-ID: Subject: FDW pushdown of non-collated functions To: PostgreSQL Hackers Content-Type: multipart/mixed; boundary="0000000000004aaee50604d9fa0b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004aaee50604d9fa0b Content-Type: multipart/alternative; boundary="0000000000004aaee30604d9fa09" --0000000000004aaee30604d9fa09 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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) from 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.699..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) (actual 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=3D2560 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'::timestamp without time zone)), (count(1)) Relations: Aggregate on (remote.t1) Remote SQL: SELECT date_bin('1 day'::interval, ts, '2023-01-01 00:00:00'::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=3Dcontrib/= postgres_fdw/deparse.c;h=3Defaf387890e3f85c419748ec3af5d1e9696c9c4c;hb=3D86= 648dcdaec67b83cec20a9d25b45ec089a7c624#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 FDW_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 argument= s (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 to allow a =E2=80=9Cno-collation=E2=80=9D function to be pushed down even if t= hey have =E2=80=9Ccollatable=E2=80=9D parameters. I=E2=80=99m not sure about the pos= sible 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 FDW_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 us= e-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. --=20 Jean-Christophe Arnu --0000000000004aaee30604d9fa09 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Dear hackers,

I recently found a weird behaviour involving FDW (postgres_fdw= ) and planning.

Here=E2=80=99s a simplifi= ed use-case:

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

CR=
EATE TABLE t1(
  ts timestamp without time zo=
ne,
  x  bigint,
  x2 text
);

--Then populate t1 table:
INSERT INTO t1=20
       SELECT=20
        current_timestamp - 10=
00*random()*'1 day&=
#39;::interval
        ,x
        ,''||x =20
       FROM=20
        generate_series(1,100000) as x;

This table is imported in a specific s= chema on server1 (we do not use use_remote_estimate) also with t1 name in a specific schema:

On server1:

CREATE =
SERVER server2=20
       FOREIGN DATA WRAPPER  post=
gres_fdw=20
       OPTIONS (
              host '127.0.0.1',=20
              port '9002',=
=20
              dbname 'postgres',=20
              use_remote_estimate 'fa=
lse'
       );

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 using date_trunc() is executed and results in= the following plan:

jc=3D# explain (verb=
ose,analyze) select date_trunc('day',ts), count(1)=
 from remote.t1 group by=
 date_trunc('day',ts) order by 1;
                                                            QUERY PLAN     =
                                                       =20
----------------------------------------=
---------------------------------------------------------------------------=
----------------
 Sort  (cost=3D216.14..216.64 rows=3D200 width=
=3D16) (actual time=3D116.699..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<=
/span>) (actual 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=3D10=
0.00..193.20 rows=3D2560 width=3D8) (actual =
time=3D0.384..106<=
span class=3D"gmail-hljs-number">.225 rows=3D100000 loops=3D1)
               Output: date_trunc('da=
y'::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,ana=
lyze) select date_bin('1day',ts,'2023-01-01'), co=
unt(1) from remote.t1 gro=
up by 1 order by =
1;
                                                                           =
                                             QUERY PLAN                    =
                                                                           =
                        =20
----------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------------------------------------------------
 Foreign Scan  (cost=3D113.44=
..164.17 rows=3D2=
00 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'::timestamp without time zone)), (count(1))
   Relations: Aggregate on (remot=
e.t1)
   Remote SQL: SELECT date_bin(&=
#39;1 day'::interval, ts,=
 '2023-01-01 00:00:00'::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_tr= unc() 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 a= s shippable (able to be pushed down) and date_trunc() and date_bin() both are.

Then parameters su= b-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->i=
nputcollid =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 con= tains 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 arguments (tim= estamp 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 ma= y be a good idea to do so.

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 class=3D"gmail-part">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 Inv=
alidOid ||
        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 effects or fits all use-cases.

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

Thank you for any thoughts.

--
Jean-= Christophe Arnu
--0000000000004aaee30604d9fa09-- --0000000000004aaee50604d9fa0b Content-Type: text/x-patch; charset="US-ASCII"; name="postgres_fdw_no_collation_function_pushdown.patch" Content-Disposition: attachment; filename="postgres_fdw_no_collation_function_pushdown.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_lmapdj490 ZGlmZiAtLWdpdCBhL2NvbnRyaWIvcG9zdGdyZXNfZmR3L2RlcGFyc2UuYyBiL2NvbnRyaWIvcG9z dGdyZXNfZmR3L2RlcGFyc2UuYwppbmRleCAwOWQ2ZGQ2MGRkLi4xNmI5MzhlYmIyIDEwMDY0NAot LS0gYS9jb250cmliL3Bvc3RncmVzX2Zkdy9kZXBhcnNlLmMKKysrIGIvY29udHJpYi9wb3N0Z3Jl c19mZHcvZGVwYXJzZS5jCkBAIC01NjgsNyArNTY4LDggQEAgZm9yZWlnbl9leHByX3dhbGtlcihO b2RlICpub2RlLAogCQkJCSAqIElmIGZ1bmN0aW9uJ3MgaW5wdXQgY29sbGF0aW9uIGlzIG5vdCBk ZXJpdmVkIGZyb20gYSBmb3JlaWduCiAJCQkJICogVmFyLCBpdCBjYW4ndCBiZSBzZW50IHRvIHJl bW90ZS4KIAkJCQkgKi8KLQkJCQlpZiAoZmUtPmlucHV0Y29sbGlkID09IEludmFsaWRPaWQpCisJ CQkJaWYgKGZlLT5pbnB1dGNvbGxpZCA9PSBJbnZhbGlkT2lkIHx8CisJCQkJCSBmZS0+ZnVuY2Nv bGxpZCA9PSBJbnZhbGlkT2lkKQogCQkJCQkgLyogT0ssIGlucHV0cyBhcmUgYWxsIG5vbmNvbGxh dGFibGUgKi8gOwogCQkJCWVsc2UgaWYgKGlubmVyX2N4dC5zdGF0ZSAhPSBGRFdfQ09MTEFURV9T QUZFIHx8CiAJCQkJCQkgZmUtPmlucHV0Y29sbGlkICE9IGlubmVyX2N4dC5jb2xsYXRpb24pCg== --0000000000004aaee50604d9fa0b--