PostgreSQL Update Statement Performance

Question

I have a table with these columns:

id (int) 
col1 (int) 
col2 (varchar) 
date1 (date) 
col3 (int) 
cumulative_col3 (int)

and about 750k rows.

I want to update the cumulative_col3 with the sum of col3 of same col1, col2 and previous to date of date1.

I have indexes on (date1), (date1, col1, col2) and (col1, col2).

I have tried the following query but it takes a long time to complete.

update table_name
set cumulative_col3 = (select sum(s.col3)
                       from table_name s
                       where s.date1 <= table_name.date1
                         and s.col1 = table_name.col1
                         and s.col2 = table_name.col2);

What can I do to improve the performance of this query?

user330315 · Accepted Answer · 2022-10-15 07:11:09Z

2

You can try to calculate the running sum in a derived table instead:

update table_name
  set cumulative_col3 = t.cum_sum
from (
  select id,
         sum(s.col3) over (partition by col1, col2 order by date1) as cum_sum
  from table_name
) s  
where s.id = table_name.id;

This assumes that id is the primary key of the table.

answered Oct 15, 2022 at 7:11

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

obinini Over a year ago

Hi @a_horse_with_no_name, thanks, this worked and completed so far in just under a minute (50-58sec).

obinini Over a year ago

Hi @a_horse_with_no_name, is it possible to re-write the derived table as a sub-query?, for a situation where one wants to do same in a database that does not support window functions at the moment.

Tim Biegeleisen · Accepted Answer · 2022-10-15 05:55:47Z

0

You might try adding the following index to your table:

CREATE INDEX idx ON table_name (date1, col1, col2, col3);

This index, if used, should allow the correlated sum subquery to be evaluated faster.

answered Oct 15, 2022 at 5:55

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

obinini Over a year ago

thanks for your response. unfortunately it didn't quite help. thanks again.

deroby Over a year ago

This suggestion can't work in this situation because we're doing a range scan on date1, putting it in front pretty much nixes the use of col1 and col2. Having col3 in there makes it covering thus avoiding an extra fetch from the table, that I can understand.

Collectives™ on Stack Overflow

PostgreSQL Update Statement Performance

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related