PostgreSQL – Slow UPDATE FROM Query in Large Table

performancepostgresqlpostgresql-performance

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?

Best Answer

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.

Related Solutions

Mysql – Improve `Update` performance (rows locking issue)

First, each time you UPDATE the status column, you are having to update the index as well (source). Evaluate your indexing to see if you really need the index on the status column. My guess is no, since it has an extremely low cardinality and MySQL probably won't use it anyway.

If you ignore me and think you do need it, follow the advice in the article to drop the index before your loop and re-add it after you're done.

Here are some other things you might do if that doesn't help:

You are taking all the columns from the data but only using number. Don't do a SELECT *, but instead a SELECT number. That won't help your writes, but it is a good performance practice. Only select the columns you're using.
Your number index isn't getting used at all. This means it is not unique enough to be useful for updating. (Slight tangent: how many rows does a single UPDATE affect?) I would drop it, or at least add it to process index.
It looks like process is unique enough for MySQL to whittle the amount of rows down to 16k, instead of 1 million. In light of this, I would add AND process=x to your update statement (I'm assuming you know process from the original SELECT statement):
```
-- FAILED--
UPDATE data SET status = 2, error='$error' WHERE process=X AND number = $data['number']

-- SUCCESS --
UPDATE data SET status = 1 WHERE process=X AND number = $data['number']
```

A hint about unnecessary indexes in InnoDB. InnoDB is using a hidden 'primary key' (since you don't have one defined) and is using that when it writes the indexes. So for each Index you're using, you add the size of the index + the size of the hidden primary key to the data file. If you're not using the index (or MySQL can't use it), you are wasting space and adding overhead each time you insert a new number (same for status, as discussed earlier)

Sql-server – Worse performance on a new server

Raid 5 is slower than raid 10, especially for write-heavy workloads. As such, it is not generally recommended for SQL server and certainly not for tempdb. This alone could easily explain the performance difference.

My recommendation would be to move tempdb to raid 10.

Best Answer

Related Solutions

Mysql – Improve `Update` performance (rows locking issue)

Sql-server – Worse performance on a new server

Related Question