Thread

BUG #19083: Foreign inner join is the case for char() keys but not for varchar()

PG Bug reporting form <noreply@postgresql.org> — 2025-10-13T12:18:21Z

The following bug has been logged on the website:

Bug reference:      19083
Logged by:          Anton Ratundalov
Email address:      a.ratundalov@postgrespro.ru
PostgreSQL version: 18.0
Operating system:   Debian 6.1.148-1 (2025-08-26) x86_64 GNU/Linux
Description:        

DDL :

\unset :database
\unset :user
\unset :host
\unset :port

SELECT current_database() AS database \gset
SELECT current_user AS USER \gset
\set host localhost
SELECT setting AS port FROM pg_settings WHERE name='port' \gset

CREATE extension postgres_fdw;

CREATE server f_srv FOREIGN data wrapper postgres_fdw options (host :'host',
port :'port', dbname :'database');
CREATE USER mapping FOR :user server f_srv options (USER :'user');

CREATE TABLE tc1 (a int, b int, c char(40)) partition by hash(c);
CREATE TABLE tc1_ft (a int, b int, c char(40));
CREATE TABLE tc1_p1 partition of tc1 FOR values WITH (modulus 2, remainder
0);
CREATE FOREIGN TABLE tc1_p2 partition of tc1 FOR values WITH (modulus 2,
remainder 1) server f_srv options (table_name 'tc1_ft');


CREATE TABLE tc2 (a int, b int, c char(40)) partition by hash(c);
CREATE TABLE tc2_ft (a int, b int, c char(40));
CREATE TABLE tc2_p1 partition of tc2 FOR values WITH (modulus 2, remainder
0);
CREATE FOREIGN TABLE tc2_p2 partition of tc2 FOR values WITH (modulus 2,
remainder 1) server f_srv options (table_name 'tc2_ft');


CREATE TABLE tv1 (a int, b int, c varchar(40)) partition by hash(c);
CREATE TABLE tv1_ft (a int, b int, c varchar(40));
CREATE TABLE tv1_p1 partition of tv1 FOR values WITH (modulus 2, remainder
0);
CREATE FOREIGN TABLE tv1_p2 partition of tv1 FOR values WITH (modulus 2,
remainder 1) server f_srv options (table_name 'tv1_ft');


CREATE TABLE tv2 (a int, b int, c varchar(40)) partition by hash(c);
CREATE TABLE tv2_ft (a int, b int, c varchar(40));
CREATE TABLE tv2_p1 partition of tv2 FOR values WITH (modulus 2, remainder
0);
CREATE FOREIGN TABLE tv2_p2 partition of tv2 FOR values WITH (modulus 2,
remainder 1) server f_srv options (table_name 'tv2_ft');

set enable_partitionwise_join TO ON;

PLANS :

EXPLAIN(VERBOSE, COSTS OFF) SELECT tc1.a, tc2.a FROM tc1 JOIN tc2 ON tc1.c =
tc2.c WHERE tc1.c='0002';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: tc1.a, tc2.a
   Relations: (public.tc1_p2 tc1) INNER JOIN (public.tc2_p2 tc2)
   Remote SQL: SELECT r4.a, r5.a FROM (public.tc1_ft r4 INNER JOIN
public.tc2_ft r5 ON (((r5.c = '0002')) AND ((r4.c = '0002'))))
(4 rows)


EXPLAIN(VERBOSE, COSTS OFF) SELECT tv1.a, tv2.a FROM tv1 JOIN tv2 ON tv1.c =
tv2.c WHERE tv1.c='0002';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop
   Output: tv1.a, tv2.a
   ->  Foreign Scan on public.tv1_p2 tv1
         Output: tv1.a, tv1.c
         Remote SQL: SELECT a, c FROM public.tv1_ft WHERE ((c = '0002'))
   ->  Materialize
         Output: tv2.a, tv2.c
         ->  Foreign Scan on public.tv2_p2 tv2
               Output: tv2.a, tv2.c
               Remote SQL: SELECT a, c FROM public.tv2_ft WHERE ((c =
'0002'))
(10 rows)


In the second case foreign join path is not considered because
have_partkey_equi_join returns false in contrast to the first case.  This
difference is determined by different equivalence classes exprs_known_equal
( called by the have_partkey_equi_join ) operates. In the first case it's
(T_Var, T_Var, T_Const) while in the second (T_RelabelType, T_RelabelType,
T_Const).

Proposed fix is to remove any relabel decorations ( like it's done
match_expr_to_partition_keys ) inside exprs_known_equal.