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.
WITH part only takes about 3 minutes to run.
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.
arrest_id field had no constraints except
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.
arrest_id field was blank for all rows with
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.