Optimizing UPDATE multiple tables with specific commit size

Question

I'm trying to optimize my procedure for updating multiple tables with huge amount of data. (millions of rows). The commit size is set to 50.000 to fit memory capacity. Here is what I have now. The issue is - it is still slow for PostgreSQL 12.

All tables have indexes - order_id, scenario_id and some other (cannot be changed). Rough rows count to change is about 3 mln.

Thanks in advance for any suggestions!

P.S. some statistics:

PostgreSQL 14.2 on x85_64-pc-linux-gnu RedHat 4.8.5-44, 64bit
execution time 5m 45s

PostgreSQL 12.2 on x85_64-pc-linux-gnu RedHat 4.8.5-39, 64bit
execution time 50m 15s

Local test with 2300460 records
PostgresSQL 12.3 Win 10 64bit RAM 32gb
execution time 5hrs 23mins - 7122 rows/min

Local test with 460000 records
PostgresSQL 12.3 Win 10 64bit RAM 32gb
execution time 47 minutes - 9787 rows/min

Local test with 690000 records
PostgresSQL 12.3 Win 10 64bit RAM 32gb
execution time 66m 18s - 10454 rows/min

create or replace procedure schema_name.update_tables(
    task_number_input varchar,
    incorrect_scenario_id_input varchar,
    correct_scenario_id_input varchar,
    input_user varchar,
    commitSize numeric = 50000
)
    language plpgsql
as
$$
declare
    loopCounter        numeric := 1;
    totalSuccessCount  numeric := 0;
    currentCount       numeric := 0;
    errorCount         numeric := 0;
    migration_order_id record;
begin
    
    for migration_order_id in (
        SELECT *
        from schema_name.migration_scenario
        where status is false
          and task_number = task_number_input
          and scenario_id = correct_scenario_id_input
          and old_scenario_id = incorrect_scenario_id_input
    )
        loop
            begin
                
                    update schema_name.audit_catalog
                        set scenario_id = migration_order_id.scenario_id
                        where scenario_id = migration_order_id.old_scenario_id;

                /* ...and so on */

                    update schema_name.user_registration
                        set scenario_id = migration_order_id.scenario_id
                        where order_id = migration_order_id.order_id;

                update schema_name.migration_scenario
                set status           = true,
                    note             = note || now()::timestamp,
                    last_modify_time = (extract(epoch from now()) * 1000),
                    last_modify_user = input_user
                where order_id = migration_order_id.order_id;

            exception
                when others then
                    errorCount = errorCount + 1;
            end;
            if loopCounter % commitSize = 0 then
                commit;
            end if;
            loopCounter = loopCounter + 1;
        end loop;

end
$$;

some better aproach after different tests. Commit happens at the end of each loop.

Local test with 690000 records
PostgresSQL 12.3 Win 10 64bit RAM 32gb
execution time 38 minutes - 18157 rows/min

loopSize := ceiling((SELECT cast(COUNT(*) as float)
            from schema_name.migration_scenario
            WHERE status is false
              and task_number = task_number_input
              and scenario_id = correct_scenario_id_input
              and old_scenario_id = incorrect_scenario_id_input)/limitSize);            

    for i in 1..loopSize loop begin

        with migration_order_id as (
            SELECT *
            from schema_name.migration_scenario
            WHERE status is false
              and task_number = task_number_input
              and scenario_id = correct_scenario_id_input
              and old_scenario_id = incorrect_scenario_id_input
            limit limitSize
            ),
             audit_catalog_upd as (
                update schema_name.audit_catalog ac
                set scenario_id = migration_order_id.scenario_id
                from migration_order_id
                where ac.scenario_id = migration_order_id.old_scenario_id),

             /* and so on */

             user_registration_upd as (
                update schema_name.user_registration ur
                set scenario_id = migration_order_id.scenario_id
                from migration_order_id
                where ur.order_id = migration_order_id.order_id)

                update schema_name.migration_scenario ms
                set status           = true,
                    note             = ms.note || ' ' || now()::timestamp,
                    last_modify_time = (extract(epoch from now()) * 1000),
                    last_modify_user = input_user
                from migration_order_id moi
                where ms.order_id = moi.order_id;

hope it still could be better...

EXPLAIN ANALYZE https://explain.depesz.com/s/ZJI9

same request - different result (morning) https://explain.depesz.com/s/wEPs