X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1D8AED7907 for ; Fri, 2 Sep 2005 07:00:27 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26037-10 for ; Fri, 2 Sep 2005 10:00:25 +0000 (GMT) Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by svr1.postgresql.org (Postfix) with ESMTP id E8DCAD78F9 for ; Fri, 2 Sep 2005 07:00:22 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 29993412E02; Fri, 2 Sep 2005 11:00:18 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 6A3AB15F1F; Fri, 2 Sep 2005 10:55:31 +0100 (BST) Received: from mainbox.archonet.com ([127.0.0.1]) by localhost (mainbox [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26359-10; Fri, 2 Sep 2005 10:55:26 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 7D37E15F1C; Fri, 2 Sep 2005 10:48:11 +0100 (BST) Message-ID: <43181FDB.7070106@archonet.com> Date: Fri, 02 Sep 2005 10:48:11 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Hemant Pandey Cc: pgsql-performance@postgresql.org Subject: Re: Update is more affected( taking more time) than Select References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.057 required=5 tests=[AWL=0.007, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200509/47 X-Sequence-Number: 14355 Hemant Pandey wrote: > Operation A: 6 Fetch + 1 Update > Operation B: 9 Fetch > Operation C: 5 Fetch + 3 Update ( Tables has 140 records) > > I have run these operations while Auto Vacumm is running and observed the > time taken in thse operations. I found that Operation C is taking highest > time and A is the lowest. > So i inferrred that, UPDATE takes more time. > > Now i run these operations again, without running Auto Vacuum. I observed > that, time taken for operation A & B is almost same but time for Operation > C is increasing. > > I am not able to analyze, why only for operation C, time is increasing?? > Does auto vacuum affects more on UPDATE. Depends on what is happening. Without vacuuming (automatic or manual) a table will tend to have "holes" since an update with MVCC is basically a delete and an insert. Since you say the table has only 140 records, almost any operation will tend to scan rather than use an index. However, if you issue lots of updates you will end up with many "holes" which have to be scanned past. PG won't know they are there because its statistics will be out of date unless you have analysed that table recently. So - everything will start to get slower. So - for a small, rapidly updated table make sure you vacuum a lot (perhaps as often as once a minute). Or, run autovacuum and let it cope. -- Richard Huxton Archonet Ltd