I have two actors in this transaction. One actor is a table "update_assets" is a set of records with data that is up-to-date and new. The second actor is a table "application_assets" which is a table used by an application and needs to be updated. The problem is that the update on this is taking far too long. The UPDATE is now going on 4 hours. The transaction is running on a vm with 3 amd cores allocated to it. 8 gb of ram. The application is not running in the background.
Tables:
update_assets ~3.3 million records 18 columns 1 index on amsid
application_assets ~7 million records 5 columns 1 index on id
Query:
UPDATE application_assets as ams SET number = amu.num, letter = amu.letter FROM update_assets amu WHERE ams.id = amu.amsid AND ams.company_id = '4';
Explain output:
Update on application_assets ams (cost=219382.14..747965.42 rows=3303562 width=85) -> Hash Join (cost=219382.14..747965.42 rows=3303562 width=85) Hash Cond: ((ams.id)::text = (amu.amsid)::text) -> Seq Scan on application_assets ams (cost=0.00..244642.25 rows=7883425 width=63) -> Hash (cost=145825.62..145825.62 rows=3303562 width=55) -> Seq Scan on update_assets amu (cost=0.00..145825.62 rows=3303562 width=55)
If I removed all of the columns on the update_assets table, except for the pertinent columns for this transaction, will that speed up the transaction? I noticed that in the Hash Cond portion of the transaction that it does a :: casting operation on each of the columns. If the columns were already in the text data type format, would the data be updated quicker? Does postgresql always change data to text data for these types of joins? What else can I do to expedite this process?
company_id
is a numeric column, you should not compare it with a string but with a number'4'
is a string,4
is a number. Also please post the definition of the tables involved and any index defined (I don't see a filter condition on company_id in the explain, which makes me wonder if the update you posted and the plan actually relate)VACUUM
the table between the updates. As anUPDATE
in PostgreSQL is basically aDELETE
(marking the row invisible to later transactions but leaving it physically in place) and anINSERT
(creating a new physical entry), updating half the table will increase it by 50% is size. Most probably all these new rows will be on new pages, creating which also have its overhead. Doing some of theUPDATE
and vacuuming will make the deleted row's space reusable.update_assets amu
by(select * from update_assets order by amsid limit 10000) amu
explain
thinks it will update3303562
rows which would be almost half the table? Probably it would be faster to create an empty clone, populate i with two INSERTs statements, and replace the original with the updated clone. Unless foreign keys prevent it.