Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E9B0CB5DC2A for ; Mon, 31 Oct 2011 12:33:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 87663-03 for ; Mon, 31 Oct 2011 15:33:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 760BFB5DC27 for ; Mon, 31 Oct 2011 12:33:37 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Mon, 31 Oct 2011 10:33:35 -0500 Message-Id: <4EAE797D0200002500042899@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Mon, 31 Oct 2011 10:33:33 -0500 From: "Kevin Grittner" To: "Shivakumar Ramannavar" , Subject: Re: My Query to insert and retrieve takes time References: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-3.101 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-1.201 X-Spam-Level: X-Archive-Number: 201110/138 X-Sequence-Number: 38590 Shivakumar Ramannavar 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