CODEWITHSUNDEEP
sqlpostgresqlupdatesquery-performance
Saurabh Omar
Saurabh Omar

Reputation: 39

Slow Update in Postgres

Trying to update a column in a 4.4 million rows table. I have been able to reduce my query time from 30+ minutes to 14 minutes with the following version:

update t_settlement x
    set dt_ad_decode = y.decode
    from (Select dt_amountdescription_1to1, dt_decode as decode
          from d_amountdescription_1to1
         ) y
    where x."AmountDescription" = y.dt_amountdescription_1to1;

I am sure there must be ways to improve this further and would be grateful if somebody could help me in this regards.

Regards

Saurabh

Upvotes: 0

Views: 506

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

First, why are you using a subquery? Write this more simply as:

update t_settlement s
    set dt_ad_decode = ad.dt_decode
    from d_amountdescription_1to1 ad
    where s."AmountDescription" = ad.dt_amountdescription_1to1;

This shouldn't affect performance, but it simplifies the query.

Next, you want an index on d_amountdescription_1to1(dt_amountdescription_1to1) or, better yet, d_amountdescription_1to1(dt_amountdescription_1to1, dt_decode):

create index idx_d_amountdescription_1to1_2
    on d_amountdescription_1to1(dt_amountdescription_1to1, dt_decode)

Upvotes: 2

Related Questions