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?

  • 2
    Probably not related but if 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) – a_horse_with_no_name Sep 3 '13 at 18:57
  • Also, it may be useful to do this in smaller chunks and VACUUM the table between the updates. As an UPDATE in PostgreSQL is basically a DELETE (marking the row invisible to later transactions but leaving it physically in place) and an INSERT (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 the UPDATE and vacuuming will make the deleted row's space reusable. – dezso Sep 4 '13 at 15:56
  • @dezso How is it possible to do the update in smaller chunks? I don't see a LIMIT clause for UPDATE in the postgresql documentation. Is there a configuration setting in the postgresql.conf file I could set for UPDATE? – theCrux Sep 4 '13 at 21:02
  • About the limit, replace update_assets amu by (select * from update_assets order by amsid limit 10000) amu – Daniel Vérité Sep 5 '13 at 8:28
  • The explain thinks it will update 3303562 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. – Daniel Vérité Sep 5 '13 at 8:37

First, 4 hours seems like a long time for this. I have done big updates and would not expect it to take this long were an inner join is involved (antijoins are more troublesome).

First to your questions:

  1. 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? Maybe, if you rewrote the table after (using cluster, or select into).

  2. Does postgresql always change data to text data for these types of joins? Nope. So turning everything to text will not help.

So what you can do is to look and see what is happening. Initial troubleshooting steps need to be:

  1. Run top and watch the query. Is it I/O bound or CPU bound? What is going on memory-usage-wise?

  2. Does your db fit comfortably in RAM? If not consider allocating more RAM to this vm.

  3. Check the data types for the join columns. Make sure these are the same. Bad things can happen when it is casting to text for a join like this.

  4. Check for locks. Maybe the process is continuing slowly during a transactional period and waiting for locks periodically?

I had something similar happen recently with a query very much like yours. My update of 3.5 million rows would never finish. The culprit turned out to be the indexes on the table being updated.

The solution I finally came upon what to drop all indexes on the table being updated before running the update statement. Once I did that, the update finished in a few minutes. Once the udpate was I re-created the indexes and was back in business. This probably won't help you at this point but it may someone else looking for answers.

I'd keep the index on the table you are pulling the data from though since that one won't have to keep updating any indexes and should help with finding the data you want to update.

import psycopg2
import time
import datetime
start = time.time()
incremental_commit_size = 1000
throttle_time = 0
print("Program Start Time %s: " ,start)
print('Connecting to the PostgreSQL database...')
connectstr = "host=127.0.0.1 dbname=testdb user=testdb password=testdb port=5432"
handle = psycopg2.connect(connectstr) 
print('Connection to the PostgreSQL database SUCCESSFULL...')
cursor = handle.cursor() 
cursor2 = handle.cursor() 
#db_version = cursor.fetchone()
sql ="select phone_number from temp_first_call_data"
cursor.execute(sql)
#output = cursor.fetchmany(incremental_commit_size)
cnt = 0
while 1:
    loop_start = time.time()
    output = cursor.fetchmany(incremental_commit_size)
    if not output:
        break
    for row in output:
        #print(row)
        sql = "update table_name set column1 = 1,colm2 = current_timestamp::timestamp(0) where colmn3 = %s"
        #print(row)
        cursor2.execute(sql, ([row[0]]))
        #update_rows=cursor2.rowcount
        #print(update_rows)
#   print("Commit Called")
    handle.commit()
    cnt = cnt+1
        print("Loop counter:",cnt)
    loop_end = time.time()
    print("Each batch Update time in %s seconds" % (loop_end - loop_start))
    time.sleep(throttle_time)
#print("Final Commit Called")
handle.commit()
cursor.close()
handle.close()
print('Database connection closed.')
end = time.time()
print("Total Execution Time   %s seconds" % (end - start))
  • 1
    The best answers provide some explanation as to how their code works: for instance, how it resolves the issues the OP listed. Or, in this case, perhaps a note as to what programming language you're using, as I'm pretty sure this isn't SQL. (99+% sure it's Python) Since the OP didn't ask for a Python (or any non-SQL) solution, you might also say why you think it's better to do this via Python. It looks like you're looping through records with a cursor; I wouldn't expect that to be faster than doing a single statement. – RDFozz Aug 7 at 15:28

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.