MIME-Version: 1.0
References: <CAEzWdqd-22B-bpVdT3yzegiOig9zvJnfJvi=GOMFfHT-Jg8CgQ@mail.gmail.com>
In-Reply-To: <CAEzWdqd-22B-bpVdT3yzegiOig9zvJnfJvi=GOMFfHT-Jg8CgQ@mail.gmail.com>
From: Igor Korot <ikorot01@gmail.com>
Date: Fri, 13 Sep 2024 22:29:12 -0500
Message-ID: <CA+FnnTxqBu+eM1G-8LQhGXEqSdndUYPEk6rc4zSspCivRf9CZQ@mail.gmail.com>
Subject: Re: update faster way
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BFnnTxqBu%2BeM1G-8LQhGXEqSdndUYPEk6rc4zSspCivRf9CZQ%40mail.gmail.com>
Precedence: bulk

Hii,

On Fri, Sep 13, 2024 at 10:22=E2=80=AFPM yudhi s <learnerdatabase99@gmail.c=
om> wrote:
>
> Hello,
> We have to update a column value(from numbers like '123' to codes like 'a=
bc' by looking into a reference table data) in a partitioned table with bil=
lions of rows in it, with each partition having 100's millions rows. As we =
tested for ~30million rows it's taking ~20minutes to update. So if we go by=
 this calculation, it's going to take days for updating all the values. So =
my question is
>
> 1) If there is any inbuilt way of running the update query in parallel (e=
.g. using parallel hints etc) to make it run faster?
> 2) should we run each individual partition in a separate session (e.g. fi=
ve partitions will have the updates done at same time from 5 different sess=
ions)? And will it have any locking effect or we can just start the session=
s and let them run without impacting our live transactions?

Do you have any indexes?
If not - you should, if yes - what are they?

Thank you.

>
> UPDATE tab_part1
> SET column1 =3D reftab.code
> FROM reference_tab reftab
> WHERE tab_part1.column1 =3D subquery.column1;
>
> Regards
> Yudhi