Re: Remove duplicate table scan in logical apply worker and code refactoring

On Thu, Jul 25, 2024 at 4:00 PM Zhijie Hou (Fujitsu)
<houzj(dot)fnst(at)fujitsu(dot)com> wrote:
>
> 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
> -----
>

The idea sounds good to me. BTW, we don't need the following comment
in the 0001 patch:
We
+ * don't call apply_handle_delete_internal() here to avoid
+ * repeating some work already done above to find the
+ * local tuple in the partition.

It is implied by the change and we already follow the same for the update.

--
With Regards,
Amit Kapila.