Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Wed, 25 Feb 2026 18:29:23 -0300
Message-Id: <DGOD23S40O08.9WSMEUDJUIVA@gmail.com>
Cc: "PostgreSQL Hackers" <pgsql-hackers@lists.postgresql.org>
Subject: Re: Initial COPY of Logical Replication is too slow
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "Masahiko Sawada" <sawada.mshk@gmail.com>, "Marcos Pegoraro"
 <marcos@f10.com.br>
References: <CAB-JLwbBFNuASyEnZWP0Tck9uNkthBZqi6WoXNevUT6+mV8XmA@mail.gmail.com> <CAD21AoA6i2ui8FMZeuU_KxX4t-fM8G==zTW2Dp6-goujttrpew@mail.gmail.com> <CAB-JLwZpp=7c9_r0beWWJxRh2BS_2Vvth8UDv7H57DBeaqggVg@mail.gmail.com> <CAD21AoDT3sL2COprsRumM9zEpL1Bk5VWboK4V2mRnjGua8xfeA@mail.gmail.com> <CAD21AoDQM62GOtaTzD_CVMSsFhv6o9c0Au1dSM1QuxeKFkWAKw@mail.gmail.com>
In-Reply-To: <CAD21AoDQM62GOtaTzD_CVMSsFhv6o9c0Au1dSM1QuxeKFkWAKw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/DGOD23S40O08.9WSMEUDJUIVA%40gmail.com>
Precedence: bulk

Hi

On Wed Feb 25, 2026 at 4:03 PM -03, Masahiko Sawada wrote:
>> After more investigation of slowness, it seems that the
>> list_concat_unique_oid() called below is quite slow when the database
>> has a lot of tables to publish:
>>
>>     relids =3D GetPublicationRelations(pub_elem->oid,
>>                                      pub_elem->pubviaroot ?
>>                                      PUBLICATION_PART_ROOT :
>>                                      PUBLICATION_PART_LEAF);
>>     schemarelids =3D GetAllSchemaPublicationRelations(pub_elem->oid,
>>                                                      pub_elem->pubviaroo=
t ?
>>                                                      PUBLICATION_PART_RO=
OT :
>>                                                      PUBLICATION_PART_LE=
AF);
>>     pub_elem_tables =3D list_concat_unique_oid(relids, schemarelids);
>>
>> This is simply because it's O(n^2), where n is the number of oids in
>> schemarelids in the test case. A simple change would be to do sort &
>> dedup instead. With the attached experimental patch, the
>> pg_get_publication_tables() execution time gets halved in my
>> environment (796ms -> 430ms with 50k tables). If the number of tables
>> is not large, this method might be slower than today but it's not a
>> huge regression.
>>
>> In the initial tablesync cases, it could be optimized further in a way
>> that we introduce a new SQL function that gets the column list and
>> expr of the specific table. This way, we can filter the result by
>> relid at an early stage instead of getting all information and
>> filtering by relid as the tablesync worker does today, avoiding
>> overheads of gathering system catalog scan results.
>
> I've drafted this idea and I find it looks like a better approach. The
> patch introduces the pg_get_publication_table_info() SQL function that
> returns the column list and row filter expression like
> pg_get_publication_tables() returns but it checks only the specific
> table unlike pg_get_publication_tables(). On my env, the tablesync
> worker's query in question becomes 0.6ms from 288 ms with 50k tables
> in one publication. Feedback is very welcome.
>

Thanks for patch. I did a review and here are my comments:

+	values[0] =3D ObjectIdGetDatum(pub->oid);
+	values[1] =3D ObjectIdGetDatum(relid);
+
+	values[0] =3D ObjectIdGetDatum(pub->oid);
+	values[1] =3D ObjectIdGetDatum(relid);

Duplicated assignments?

--------------

+		  /* ALL TALBES publication */

Typo on TALBES

--------------

+ * Common routine for pg_get_publication_tables() and
+ * pg_get_publication_table_info() to construct the result tuple.
+ */
+static HeapTuple
+construct_published_rel_tuple(published_rel *table_info, TupleDesc tuple_d=
esc)

construct_published_rel_tuple is only being used on
pg_get_publication_table_info(). Perhaps it can also be used on "if
(funcctx->call_cntr < list_length(table_infos))" block on
pg_get_publication_tables()?

--------------

Is new regression tests needed or the current ones already cover the new
function engouth? The code of pg_get_publication_table_info seems well
coveraged.

--------------

It seems that pgindent is missing on src/backend/catalog/pg_publication.c

--
Matheus Alcantara
EDB: https://www.enterprisedb.com