The first question is: Why is it important that you not use a lot of CPU time? The query will be bottlenecked on some resource; if you could introduce enough additional disk access, the CPU time used per second would go down, but would that really be an improvement? What resource would you prefer to saturate? Understanding why you have emphasized this might help guide people to providing an answer that you will find useful.
As suggested in a comment, your query may run faster with a join rather than a correlated subquery. Something like this:
UPDATE temp
SET customername = user.customername
FROM user
WHERE user.customerid = temp.customerid;
Another important thing to know is whether you want to update all rows in the table. Are some of the values already correct? If so, you will get a big performance boost by not updating the rows that don't need it. Add AND temp.customername is distinct from user.customername
to the WHERE
clause.
If you limit the number of rows updated in each statement, and VACUUM ANALYZE
after each UPDATE, you will avoid table bloat. If the point of the desire to minimize CPU time is to avoid a performance impact on concurrent transactions, this would give you the opportunity to introduce a short delay (in the form of a sleep
or something similar) before you start the next UPDATE
of a set of rows.
Even better, why are you redundantly storing the information in the temp table rather than joining to it when needed? (Sometimes there is a good reason; quite often there isn't.)
UPDATE TEMP SET CUSTOMERNAME = ( select customername from user where user.customerid = temp.customerid ) ;
user.customerid
and ontemp.customerid
? Adding an index if there isn't one might help.