I have a Postgres table with ~2.1 million rows. I ran the below update on it:

WITH stops AS (
    SELECT id,
           rank() OVER (ORDER BY offense_timestamp,
                     offense_street_name) AS stop
    FROM   consistent.master
    WHERE  citing_jurisdiction=1

UPDATE consistent.master
SET arrest_id=stops.stop
FROM stops
WHERE master.id = stops.id;

This query took 39 hours to run. I am running this on a 4 (physical) core i7 Q720 laptop processor, plenty of RAM, nothing else running the vast majority of the time. No HDD space constraints. The table had recently been vacuumed, analyzed, and reindexed.

The whole time the query was running, at least after the initial WITH completed, CPU usage was usually low, and the HDD was in use 100%. The HDD was being used so hard that any other app ran considerably more slowly than normal.

The laptop's power setting were on High performance (Windows 7 x64).

Here's the EXPLAIN:

Update on master  (cost=822243.22..1021456.89 rows=2060910 width=312)
  CTE stops
    ->  WindowAgg  (cost=529826.95..581349.70 rows=2060910 width=33)
          ->  Sort  (cost=529826.95..534979.23 rows=2060910 width=33)
                Sort Key: consistent.master.offense_timestamp, consistent.master.defendant_dl, consistent.master.offense_street_number, consistent.master.offense_street_name
                ->  Seq Scan on master  (cost=0.00..144630.06 rows=2060910 width=33)
                      Filter: (citing_jurisdiction = 1)
  ->  Hash Join  (cost=240893.51..440107.19 rows=2060910 width=312)
        Hash Cond: (stops.id = consistent.master.id)
        ->  CTE Scan on stops  (cost=0.00..41218.20 rows=2060910 width=48)
        ->  Hash  (cost=139413.45..139413.45 rows=2086645 width=268)
              ->  Seq Scan on master  (cost=0.00..139413.45 rows=2086645 width=268)

citing_jurisdiction=1 only excludes a few tens of thousands of rows. Even with that WHERE clause, I'm still operating on over 2 million rows.

The hard drive is whole drive-encrypted with TrueCrypt 7.1a. That slows things down a bit, but not enough to cause a query to take that many hours.

The WITH part only takes about 3 minutes to run.

The arrest_id field had no index for foreign key. There are 8 indexes and 2 foreign keys on this table. All other fields in the query are indexed.

The arrest_id field had no constraints except NOT NULL.

The table has 32 columns total.

arrest_id is of type character varying(20). I realize rank() produces a numeric value, but I have to use character varying(20) because I have other rows where citing_jurisdiction<>1 that use non-numeric data for this field.

The arrest_id field was blank for all rows with citing_jurisdiction=1.

This is a personal, high end (as of 1 year ago) laptop. I am the only user. No other queries or operations were running. Locking seems unlikely.

There are no triggers anywhere in this table or anywhere else in the database.

Other operations on this database never take an abornmal amount of time. With proper indexing, SELECT queries are usually quite fast.

migrated from stackoverflow.com Mar 28 '12 at 15:20

This question came from our site for professional and enthusiast programmers.

  • Those Seq Scan are a bit scary... – rogerdpack Nov 23 '17 at 20:35

I had something similar happen recently with a table of 3.5 million rows. My update would never finish. After a lot of experimenting and frustration, I finally found the culprit. It turned out to be the indexes on the table being updated.

The solution was 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 update completed, 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 indexes on the table you are pulling the data from. That one won't have to keep updating any indexes and should help with finding the data you want to update. It ran fine on a slow laptop.

  • 3
    I am switching the best answer to you. Since I posted this, I have encountered other situations where indexes are the problem, even if the column being updated already has a value and has no index (!). It seems that Postgres has a problem with how it manages indexes on other columns. There's no reason for these other indexes to balloon a update's query time when the only alteration to a table is to update an unindexed column and you're not increasing the allocated space for any row of that column. – Aren Cambre Dec 9 '14 at 2:04
  • 1
    Thanks! Hope it helps others. It would've saved me hours of headaches for something seemingly very simple. – JC Avena Dec 10 '14 at 3:04
  • 4
    @ArenCambre -- there is a reason: PostgreSQL copies the whole row to a different location and marks the old version as deleted. This is how PostgreSQL implements Multi-Version Concurrency Control (MVCC). – Piotr Findeisen Mar 15 '16 at 21:08
  • My question is...why is it the culprit? See also stackoverflow.com/a/35660593/32453 – rogerdpack Nov 24 '17 at 17:23

Your biggest issue is doing huge amounts of write-heavy, seek-heavy work on a laptop hard drive. That's never going to be fast no matter what you do, especially if it's the kind of slower 5400RPM drive shipped in lots of laptops.

TrueCrypt slows things down more than "a bit" for writes. Reads will be reasonably fast, but writes make RAID 5 look fast. Running a DB on a TrueCrypt volume will be torture for writes, especially random writes.

In this case, I think you'd be wasting your time trying to optimise the query. You're rewriting most rows anyway, and it's going to be slow with your horrifying write situation. What I'd recommend is to:

TRUNCATE TABLE consistent.master;
-- Now DROP all constraints on consistent.master, then:
INSERT INTO consistent.master SELECT * FROM master_tmp;
-- ... and re-create any constraints.

I suspect that'll be faster than just dropping and re-creating the constraints alone, because an UPDATE will have fairly random write patterns that'll kill your storage. Two bulk inserts, one into an unlogged table and one into a WAL-logged table without constraints, will probably be faster.

If you have absolutely up-to-date backups and don't mind having to restore your database from backups you can also re-start PostgreSQL with the fsync=off parameter and full_page_writes=off temporarily for this bulk operation. Any unexpected problem like power loss or an OS crash will leave your database unrecoverable while fsync=off.

The POSTGreSQL equivalent to "no logging" is to use unlogged tables. These unlogged tables get truncated if the DB shuts down uncleanly while they're dirty. Using unlogged tables will at least halve your write load and reduce the number of seeks, so they can be a LOT faster.

Like in Oracle, it can be a good idea to drop an index then re-create it after a big batch update. PostgreSQL's planner can't work out that a big update is taking place, pause index updates, then rebuild the index at the end; even if it could, it'd be very hard for it to figure out at which point this was worth doing, especially in advance.

  • This answer is spot on on the large amount of writes and the terrible perf of encryption plus slow laptop drive. I would also note that the presence of 8 indexes produces many extra writes and defeats applicability of HOT in-block row updates, so dropping indexes and using a lower fillfactor on the table may prevent a ton of row migration – dbenhur Mar 28 '12 at 19:03
  • 1
    Good call on boosting HOTs chances with a fillfactor - though with TrueCrypt forcing block read-rewrite cycles in huge blocks I'm not sure it'll help much; row migration might even be faster because growing the table is at least doing linear-ish blocks of writes. – Craig Ringer Mar 30 '12 at 14:42
  • 2.5 years later I am doing something similar but on a larger table. Just to make sure, is it a good idea to drop all indexes, even if the single column I am updating is not indexed? – Aren Cambre Dec 3 '14 at 22:15
  • 1
    @ArenCambre In that case ... well, it's complicated. If most of your updates will be eligible for HOT then it's better to leave the indexes in place. If not, then you'll likely want to drop and re-create. The column isn't indexed, but to be able to do a HOT update there also needs to be free space on the same page, so it depends a bit on how much dead space there is in the table. If it's write-mostly, I'd say drop all indexes. If it's updated lots it might have holes and you might be OK. Tools like pageinspect and pg_freespacemap can help determine this. – Craig Ringer Dec 4 '14 at 4:09
  • Thanks. In this case, it's a boolean column that had an entry into every row already. I was changing the entry on some rows. I just confirmed: the update took only 2 hours after dropping all indexes. Beforehand, I had to stop the update after 18 hours because it was just taking too long. This is despite the fact that the column that was being updated definitely was not indexed. – Aren Cambre Dec 4 '14 at 15:52

Someone will give a better answer for Postgres, but here are a few observations from an Oracle perspective which may apply (and the comments are too long for the comment field).

My first concern would be trying to update 2 million rows in one transaction. In Oracle, you would be writing a before image of each block being updated so that other session still have a consistent read without reading your modified blocks and you have the ability to rollback. That is a long rollback being built up. You are usually better off to do the transactions in small chunks. Say 1,000 records at a time.

If you have indexes on the table, and the table is going to be considered out of operation during maintenance, you are often better off to remove the indexes before a big operation and then recreating it again afterward. Cheaper then constantly trying to maintain the indexes with each updated record.

Oracle allows "no logging" hints on statements to stop the journalling. It speeds up the statements alot, but leaves your db in an "unrecoverable" situation. So you would want to backup before, and backup again immediately afterward. I don't know if Postgres has similar options.

  • PostgreSQL doesn't have problems with a long rollback, doesn't exist. ROLBACK is very fast in PostgreSQL, no matter how big your transaction is. Oracle != PostgreSQL – Frank Heikens Mar 28 '12 at 5:49
  • @FrankHeikens Thanks, that is interesting. I'll have to read up on how the journalling works on Postgres. In order to make the whole concept of transactions work, somehow two different versions of the data need to be maintained during a transaction, the before image and the after image and that is the mechanism I'm referring to. One way or another, I would guess there is a threshold beyond which the resources to maintain the transaction will be too expensive. – Glenn Mar 28 '12 at 11:29
  • 2
    @Glenn postgres keeps the versions of a row in the table itself - see here for an explanation. The compromise is that you get 'dead' tuples hanging around, which are cleaned up asynchronously with what is called 'vacuum' in postgres (Oracle has no need of vacuum because it never has 'dead' rows in the table itself) – Jack Douglas Mar 28 '12 at 16:46
  • @JackDouglas Thanks, that is a useful link. – Glenn Mar 28 '12 at 20:36
  • You're welcome, and rather belatedly: welcome to the site :-) – Jack Douglas Mar 28 '12 at 20:38

Your Answer

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.