On 08/21/2014 08:41 AM, Victor d'Agostino wrote:

UPDATE MYBIGTABLE SET date = (SELECT date FROM INDEXEDTABLE WHERE
INDEXEDTABLE.email_id=MYBIGTABLE.email_id) WHERE date is null;

I may be wrong here, but wouldn't this style of query force a nested loop? Over several million rows, that would take an extremely long time. You might want to try this syntax instead:

UPDATE MYBIGTABLE big
   SET date = idx.date
  FROM INDEXEDTABLE idx
 WHERE idx.email_id = big.email_id
   AND big.date IS NULL;

This transaction is still running and will end in several days. It only
uses 1 core.

That's not your problem. I suspect if you checked your RAID IO, you'd see 100% IO utilization because instead of a sequence scan, it's performing a random seek for every update.

My question is : Can I add new records in the table or will it generate
locks ?

Your update statement will only lock the rows being updated. You should be able to add new rows, but with the IO consuming your RAID, you'll probably see significant write delays that resemble lock waits.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to