![]() ![]() 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. Like in Oracle, it can be a good idea to drop an index then re-create it after a big batch update. Using unlogged tables will at least halve your write load and reduce the number of seeks, so they can be a LOT faster. These unlogged tables get truncated if the DB shuts down uncleanly while they're dirty. The POSTGreSQL equivalent to "no logging" is to use unlogged tables. Any unexpected problem like power loss or an OS crash will leave your database unrecoverable while fsync=off. 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. Two bulk inserts, one into an unlogged table and one into a WAL-logged table without constraints, will probably be faster. 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. INSERT INTO consistent.master SELECT * FROM master_tmp Now DROP all constraints on consistent.master, then: You're rewriting most rows anyway, and it's going to be slow with your horrifying write situation. In this case, I think you'd be wasting your time trying to optimise the query. Running a DB on a TrueCrypt volume will be torture for writes, especially random writes. Reads will be reasonably fast, but writes make RAID 5 look fast. TrueCrypt slows things down more than "a bit" for writes. 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. Your biggest issue is doing huge amounts of write-heavy, seek-heavy work on a laptop hard drive. With proper indexing, SELECT queries are usually quite fast. Other operations on this database never take an abornmal amount of time. There are no triggers anywhere in this table or anywhere else in the database. ![]() No other queries or operations were running. This is a personal, high end (as of 1 year ago) laptop. The arrest_id field was blank for all rows with citing_jurisdiction=1. I realize rank() produces a numeric value, but I have to use character varying(20) because I have other rows where citing_jurisdiction1 that use non-numeric data for this field. The arrest_id field had no constraints except NOT NULL.Īrrest_id is of type character varying(20). All other fields in the query are indexed. There are 8 indexes and 2 foreign keys on this table. The arrest_id field had no index for foreign key. The WITH part only takes about 3 minutes to run. That slows things down a bit, but not enough to cause a query to take that many hours. The hard drive is whole drive-encrypted with TrueCrypt 7.1a. Even with that WHERE clause, I'm still operating on over 2 million rows. > Seq Scan on master (cost=13.45 rows=2086645 width=268)Ĭiting_jurisdiction=1 only excludes a few tens of thousands of rows. ![]() Sort Key: _timestamp, _dl, _street_number, _street_name The laptop's power setting were on High performance (Windows 7 圆4). The HDD was being used so hard that any other app ran considerably more slowly than normal. 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 table had recently been vacuumed, analyzed, and reindexed. 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. I ran the below update on it: WITH stops AS ( ![]() I have a Postgres table with ~2.1 million rows. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |