Shivakumar Ramannavar <shivasr@xxxxxxxxx> wrote: > my problem is that it takes 8.314 milliseconds to update one > record > EXPLAIN ANALYZE UPDATE terminal > Update ... actual time=0.074..0.074 If you look at the EXPLAIN ANALYZE of the UPDATE, you'll see that the UPDATE itself actually took only a small fraction of one ms. My guess is that your write-ahead log (WAL) is on a 7200 or 7500 RPM drive, and that you're committing each update separately. Further, I would bet that you're not going through a RAID controller with battery-backup (BBU) cache configured for write-back. And you're also using a single connection to do all the updates. Each commit must wait until the WAL is persisted, which can mean waiting for a disk drive to spin all the way around again -- which for a 7200 RPM drive takes 8.3 ms and for a 7500 RPM drive takes 8 ms. Without better hardware, you face a hard limit on how many database transactions can commit on a single connection based on that rotational delay. > There are around 300,000 update operations and it is taking approx > 80 min, One way to solve the problem would be to BEGIN a transaction, do your 300,000 updates, and then COMMIT the transaction. Another would be to use a good RAID controller. A third would be to turn off synchronized_commit. (Be sure to read the description of that in the documentation to understand the implications.) Or you could use a number of connections working in parallel. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
