Remote partitionwise doesn't work for varchar join keys - Mailing list pgsql-hackers

Hi,

Faced a strange behaviour while attempting to join remote partitions
with varchar join keys.

Setup is as follows:

Servers : s1, s2, s3. s2 and s3 are linked to s1 via postgres_fdw as
foreign data sources.

Simple local tables on s2 and s3:
CREATE TABLE IF NOT exists tv1 (a int, b int, c varchar(40));
CREATE TABLE IF NOT exists tv2 (a int, b int, c varchar(40));

Partitioned tables on s1:

CREATE TABLE IF NOT exists tv1 (a int, b int, c varchar(40))
partition by hash(c);
CREATE TABLE IF NOT exists tv1_p1 partition of tv1
FOR values WITH (modulus 3, remainder 0);
CREATE FOREIGN TABLE IF NOT exists tv1_p2 partition of tv1
FOR values WITH (modulus 3, remainder 1) server s2 options (table_name 
'tv1');
CREATE FOREIGN TABLE IF NOT exists tv1_p3 partition of tv1
FOR values WITH (modulus 3, remainder 2) server s3 options (table_name 
'tv1');

CREATE TABLE IF NOT exists tv2 (a int, b int, c varchar(40))
partition by hash(c);
CREATE TABLE IF NOT exists tv2_p1 partition of tv2
FOR values WITH (modulus 3, remainder 0);
CREATE FOREIGN TABLE IF NOT exists tv2_p2 partition of tv2
FOR values WITH (modulus 3, remainder 1) server s2 options (table_name 
'tv2');
CREATE FOREIGN TABLE IF NOT exists tv2_p3 partition of tv2
FOR values WITH (modulus 3, remainder 2) server s3 options (table_name 
'tv2');

All the following queries are executed on s1.

Put some data to tv1, tv2:

INSERT INTO tv1 SELECT i, i, to_char(i/50, 'FM0000')
FROM generate_series(0, 599, 2) i;
INSERT INTO tv2 SELECT i, i, to_char(i/50, 'FM0000')
FROM generate_series(0, 599, 2) i;

Now, get a plan for partitionwise join on tv1.c = tv2.c

EXPLAIN(VERBOSE) SELECT tv1.a, tv2.a FROM tv1
JOIN tv2 ON tv1.c = tv2.c
WHERE tv2.c='0002';

=>
                                       QUERY PLAN
----------------------------------------------------------------------
  Nested Loop  (cost=200.00..237.63 rows=9 width=8)
    Output: tv1.a, tv2.a
    ->  Foreign Scan on public.tv1_p2 tv1  (cost=100.00..118.75 rows=3 
width=102)
          Output: tv1.a, tv1.c
          Remote SQL: SELECT a, c FROM public.tv1 WHERE ((c = '0002'))
    ->  Materialize  (cost=100.00..118.77 rows=3 width=102)
          Output: tv2.a, tv2.c
          ->  Foreign Scan on public.tv2_p2 tv2 (cost=100.00..118.75 
rows=3 width=102)
                Output: tv2.a, tv2.c
                Remote SQL: SELECT a, c FROM public.tv2 WHERE ((c = '0002'))
(10 rows)

If we append another remote partition to the query

EXPLAIN(VERBOSE) SELECT tv1.a, tv2.a
FROM tv1 JOIN tv2 ON tv1.c = tv2.c
WHERE tv2.c='0002' OR tv2.c='0000';

get a plan:

              QUERY PLAN
----------------------------------------------------------------------
  Append  (cost=100.00..339.60 rows=40 width=8)
    ->  Foreign Scan  (cost=100.00..169.70 rows=20 width=8)
          Output: tv1_1.a, tv2_1.a
          Relations: (public.tv1_p2 tv1_1) INNER JOIN (public.tv2_p2 tv2_1)
          Remote SQL: SELECT r5.a, r7.a
      FROM (public.tv1 r5 INNER JOIN public.tv2 r7 ON (((r5.c = r7.c))
      AND (((r7.c = '0002') OR (r7.c = '0000')))))
    ->  Foreign Scan  (cost=100.00..169.70 rows=20 width=8)
          Output: tv1_2.a, tv2_2.a
          Relations: (public.tv1_p3 tv1_2) INNER JOIN (public.tv2_p3 tv2_2)
          Remote SQL: SELECT r6.a, r8.a
      FROM (public.tv1 r6 INNER JOIN public.tv2 r8 ON (((r6.c = r8.c))
      AND (((r8.c = '0002') OR (r8.c = '0000')))))
(9 rows)

If we substitute char() for varchar() join is pushed down to remote
server in both cases ( one and two remote partitions ).

To get varchar() keys behave the same way as char() the patch,
that you can find in attachment, is proposed.