Remove duplicate table scan in logical apply worker and code refactoring
Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>
From: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>
To: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Date: 2024-07-25T10:30:21Z
Lists: pgsql-hackers
Attachments
- v1-0002-refactor-the-partition-related-logic-in-worker.c.patch (application/octet-stream) patch v1-0002
- perftest.conf (application/octet-stream)
- v1-0001-avoid-duplicate-table-scan-for-cross-partition-up.patch (application/octet-stream) patch v1-0001
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
Commits
-
Avoid duplicate table scans for cross-partition updates during logical replication.
- a67da49e1d98 18.0 landed