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 |
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 |