Remove duplicate table scan in logical apply worker and code refactoring

From: "Zhijie Hou (Fujitsu)" <houzj(dot)fnst(at)fujitsu(dot)com>
To: "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Remove duplicate table scan in logical apply worker and code refactoring
Date: 2024-07-25 10:30:21
Message-ID: OS0PR01MB571623E39984D94CBB5341D994AB2@OS0PR01MB5716.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

When reviewing the code in logical/worker.c, I noticed that when applying a
cross-partition update action, it scans the old partition twice.
I am attaching the patch 0001 to remove this duplicate table scan.

The test shows that it brings noticeable improvement:

Steps
-----
Pub:
create table tab (a int not null, b int);
alter table tab replica identity full;
insert into tab select 1,generate_series(1, 1000000, 1);

Sub:
create table tab (a int not null, b int) partition by range (b);
create table tab_1 partition of tab for values from (minvalue) to (5000000);
create table tab_2 partition of tab for values from (5000000) to (maxvalue);
alter table tab replica identity full;

Test query:
update tab set b = 6000000 where b > 999900; -- UPDATE 100

Results (The time spent by apply worker to apply the all the UPDATEs):
Before 14s
After 7s
-----

Apart from above, I found there are quite a few duplicate codes related to partition
handling(e.g. apply_handle_tuple_routing), so I tried to extract some
common logic to simplify the codes. Please see 0002 for this refactoring.

Best Regards,
Hou Zhijie

Attachment Content-Type Size
v1-0002-refactor-the-partition-related-logic-in-worker.c.patch application/octet-stream 24.4 KB
perftest.conf application/octet-stream 302 bytes
v1-0001-avoid-duplicate-table-scan-for-cross-partition-up.patch application/octet-stream 3.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2024-07-25 10:42:15 Re: Conflict detection and logging in logical replication
Previous Message Peter Eisentraut 2024-07-25 10:19:39 Re: Add 64-bit XIDs into PostgreSQL 15