From: Richard Huxton <dev@archonet.com>
To: Hemant Pandey <hemant.pandey@flextronicssoftware.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update is more affected( taking more time) than Select
Date: Fri, 02 Sep 2005 10:48:11 +0100
Message-ID: <43181FDB.7070106@archonet.com> (raw)
In-Reply-To: <OF546C1EDC.5ADA2404-ON65257070.0014275B-65257070.001632F8@flextronicssoftware.com>
References: <OF546C1EDC.5ADA2404-ON65257070.0014275B-65257070.001632F8@flextronicssoftware.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, hemant.pandey@flextronicssoftware.com
Subject: Re: Update is more affected( taking more time) than Select
In-Reply-To: <43181FDB.7070106@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox