MIME-Version: 1.0
References: 
 <CAB-JLwbBFNuASyEnZWP0Tck9uNkthBZqi6WoXNevUT6+mV8XmA@mail.gmail.com>
In-Reply-To: 
 <CAB-JLwbBFNuASyEnZWP0Tck9uNkthBZqi6WoXNevUT6+mV8XmA@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Fri, 19 Dec 2025 17:58:59 -0800
Message-ID: 
 <CAD21AoA6i2ui8FMZeuU_KxX4t-fM8G==zTW2Dp6-goujttrpew@mail.gmail.com>
Subject: Re: Initial COPY of Logical Replication is too slow
To: Marcos Pegoraro <marcos@f10.com.br>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoA6i2ui8FMZeuU_KxX4t-fM8G%3D%3DzTW2Dp6-goujttrpew%40mail.gmail.com>
Precedence: bulk

Hi,

On Sat, Dec 6, 2025 at 4:19=E2=80=AFAM Marcos Pegoraro <marcos@f10.com.br> =
wrote:
>
> Subscriber needs to ask publisher about tables and fields to COPY and it =
uses pg_get_publication_tables for that, and it is too slow when the number=
 of tables is high because on every table it's subscribed it has to run thi=
s select.

Yeah, if we pass a publication that a lot of tables belong to to
pg_get_publication_tables(), it could take a long time to return as it
needs to construct many entries.

> We can get the same result with a join on pg_publication_rel.

You changed the query not to use pg_get_publication_tables():

-                                                "SELECT DISTINCT"
-                                                "  (CASE WHEN
(array_length(gpt.attrs, 1) =3D c.relnatts)"
-                                                "   THEN NULL ELSE
gpt.attrs END)"
-                                                "  FROM pg_publication p,"
-                                                "  LATERAL
pg_get_publication_tables(p.pubname) gpt,"
-                                                "  pg_class c"
-                                                " WHERE gpt.relid =3D
%u AND c.oid =3D gpt.relid"
-                                                "   AND p.pubname IN ( %s =
)",
-                                                lrel->remoteid,
-                                                pub_names->data);
+                       "SELECT CASE WHEN cardinality(r.prattrs) <>
relnatts THEN "
+                                               "r.prattrs END FROM pg_clas=
s c "
+                                               "LEFT JOIN LATERAL
(SELECT DISTINCT prattrs FROM "
+                                               "pg_publication_rel r
INNER JOIN pg_publication p "
+                                               "ON p.oid =3D r.prpubid
WHERE c.oid =3D r.prrelid AND "
+                                               "pubname in ( %s )) r
ON TRUE WHERE c.oid =3D %u",
+                                                pub_names->data,
+                                                lrel->remoteid);

Simply replacing pg_get_publication_tables() with joining on
pg_publication_rel doesn't work since pg_get_publication_tables()
cares for several cases, for example where the specified columns are
generated columns and the specified table is a partitioned table etc.
Therefore the patch doesn't pass the regression tests.

I think it would make more sense to introduce a dedicated SQL function
that takes the reloid as well as the list of publications and returns
the relation's the column list and row filter expression while
filtering unnecessary rows inside the function.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com