Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,
                     defendant_dl,
                     offense_street_number,
                     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.

share|improve this question
add comment

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

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

2 Answers

up vote 7 down vote accepted

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:

BEGIN;
SELECT ... INTO TEMPORARY TABLE master_tmp ;
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.

share|improve this answer
    
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
    
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
add comment

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.

share|improve this answer
    
Thanks! We'll see what the Postgres geeks say, too. –  Aren Cambre Mar 28 '12 at 2:10
    
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
1  
@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
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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