public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hayato Kuroda (Fujitsu) <[email protected]>
To: 'Masahiko Sawada' <[email protected]>
To: Amit Kapila <[email protected]>
Cc: Jan Wieck <[email protected]>
Cc: [email protected] <[email protected]>
Subject: RE: Initial COPY of Logical Replication is too slow
Date: Thu, 26 Mar 2026 12:46:27 +0000
Message-ID: <OS9PR01MB12149620E338A567BF8FBD14CF556A@OS9PR01MB12149.jpnprd01.prod.outlook.com> (raw)
In-Reply-To: <OS9PR01MB121494C802D79DAAEA1B1D073F556A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
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>
<CAD21AoCz7HjEr3oeb=haK31YHxHZLcvD_wx_a-+xLPKywq++3A@mail.gmail.com>
<TY4PR01MB16907733B75A99117F013AFCA947FA@TY4PR01MB16907.jpnprd01.prod.outlook.com>
<CAD21AoA9YgiY1rVKMPZwB00WU_G4UfzoawY=7hyd7hpvBPcK6w@mail.gmail.com>
<CAA4eK1KoSi60dtakJzn0MxNnHF1Yf4indSAffTjJxQG_31jsgQ@mail.gmail.com>
<CAD21AoB4B3MOxJ7-v9YLjV5fTOtaLRUhX3jN3kqhEi7D7-uY4A@mail.gmail.com>
<[email protected]>
<CAD21AoCmHpKrNg9D3mcOA973CZ5N_dBLxb8pERpSxEeRLSQxpA@mail.gmail.com>
<CAD21AoAEVyxwn_bMWHvcU-Gcz3aUVjAtMbdgfoJ8MZNiLLEh0g@mail.gmail.com>
<CAA4eK1Jkouj=w+PHzMB6v890ES3QOLf=cUTvZmGFr-WMQW2OnA@mail.gmail.com>
<CAD21AoB4_n7+s=uM9apX1JVtvGvgM8ismAx_uMxvDmUXfQULsw@mail.gmail.com>
<CAD21AoBJcxRcaWQot302diaxoDcsnezRhnZa7p8UrPh5AGNeHQ@mail.gmail.com>
<OS9PR01MB121494C802D79DAAEA1B1D073F556A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
> I ran the performance testing independently for the 0001 patch. Overall
> performance looked
> very nice, new function spent O(1) time based on the total number of tables.
> It seems good enough.
...and I tested 0002 as well with the same settings, and the trend was the same as 0001.
Both 0001 and 0002 were applied and below SQL was run, which was same was what
tablesync worker would try:
```
SELECT DISTINCT
(CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)
THEN NULL ELSE gpt.attrs END)
FROM pg_get_publication_tables(ARRAY['pub'], 16535) gpt,
pg_class c
WHERE c.oid = gpt.relid;
```
And below is the result. Each cell shows the execution time of the SQL. HEAD
column is the case when [1] was done. 0001 column is the case for [2].
Looks like the SQL used by 0002 looks slightly faster, which is same as the
expectation. JOIN was removed once.
Total tables HEAD [ms] 0001 [ms] 0001 + 0002 [ms]
50 5.77 4.19 3.74
500 15.75 4.28 3.76
5000 120.39 4.22 3.79
50000 1741.89 4.60 4.11
500000 73287.16 4.95 4.38
Attached graph visualized the table.
[1]:
```
SELECT DISTINCT
(CASE WHEN (array_length(gpt.attrs, 1) = 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 = 17885 AND c.oid = gpt.relid
AND p.pubname IN ( 'pub' );
```
[2]:
```
SELECT DISTINCT
(CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)
THEN NULL ELSE gpt.attrs END)
FROM pg_publication p,
LATERAL pg_get_publication_tables(p.pubname, 16535) gpt,
pg_class c
WHERE c.oid = gpt.relid
AND p.pubname IN ( 'pub' );
```
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[image/png] pg_get_publication_tables.png (30.4K, 2-pg_get_publication_tables.png)
download | view image
view thread (51+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: RE: Initial COPY of Logical Replication is too slow
In-Reply-To: <OS9PR01MB12149620E338A567BF8FBD14CF556A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox