From: John Huttley <John@mib-infotech.co.nz>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow updates, poor IO
Date: Sun, 28 Sep 2008 11:33:56 +1300
Message-ID: <48DEB4D4.1020100@mib-infotech.co.nz> (raw)
In-Reply-To: <dcc563d10809270809g17938b7fvf28a3624028e0f3b@mail.gmail.com>
References: <48DBE587.3050406@mib-infotech.co.nz>
<20080926131556.GA1005@commandprompt.com>
<48DD6A4A.10209@mib-infotech.co.nz>
<dcc563d10809270809g17938b7fvf28a3624028e0f3b@mail.gmail.com>
Scott Marlowe wrote:
> On Fri, Sep 26, 2008 at 5:03 PM, John Huttley <John@mib-infotech.co.nz> wrote:
>
>> Hi Andrew,
>> There are two problems.
>> The first is the that if there is a table with a index and an update is
>> performed on a non indexed field,
>> the index is still re indexed.
>>
>
> I assume you mean updated, not reindexed, as reindexed has a different
> meaning as regards postgresql. Also, this is no longer true as of
> version 8.3. If you're updating non-indexed fields a lot and you're
> not running 8.3 you are doing yourself a huge disservice.
>
>
Yes sorry, I mean all indexes are updated even when the updated field is
not indexed.
I'm running 8.3.3
>> this is part of the trade-offs of MVCC.
>>
>
> was... was a part of the trade-offs.
>
>
You are thinking of HOT?
I don't think it applies in the case of full table updates??
>> We should reasonably expect that the total amount of IO will go up, over a
>> non-indexed table.
>>
>> The second thing is that the disk IO throughput goes way down.
>>
>> This is not an issue with MVCC, as such, except that it exposes the effect
>> of a write to an indexed field.
>>
>
> It's really an effect of parallel updates / writes / accesses, and is
> always an issue for a database running on a poor storage subsystem. A
> db with a two drive mirror set is always going to be at a disadvantage
> to one running on a dozen or so drives in a RAID-10
>
>
Oh well, I'm forever going to be disadvantaged.
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: John@mib-infotech.co.nz, scott.marlowe@gmail.com
Subject: Re: Slow updates, poor IO
In-Reply-To: <48DEB4D4.1020100@mib-infotech.co.nz>
* 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