Slow UPDATE FROM Query in Large Table I'm currently trying to merge two very large (11 million rows) tables, and my query has been running for over two days with no end in sight.

My basic query is:

UPDATE us_demand
SET ...
FROM us_demand_addtl AS sub
WHERE us_demand.geoid10=sub.geoid10;


The tables each have a spatial column that is indexed (but not one of the updated columns), and geoid10 is NOT indexed in either table.

Machine Specs:

2x 7200RPM 1tb Drives in Raid

Intel i74790k - 4.00ghz quad core

32gb of RAM

Analyze Result:


I have two questions.  Why is this operation so slow, and what can I do to speed it up?
I would index geoid10 on both tables.  If you can commit partial results, commit every 1000 to 10,000 updates.  You should be able to do the updates in a stored procedure.  This may reduce the overhead for lock contention.