Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 4615464FC03
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 26 Sep 2008 20:04:00 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 21971-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 26 Sep 2008 20:03:56 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gatekeeper.Mib-infotech.co.nz
	(60-234-154-229.bitstream.orcon.net.nz [60.234.154.229])
	by postgresql.org (Postfix) with ESMTP id B850764FC01
	for <pgsql-performance@postgresql.org>;
	Fri, 26 Sep 2008 20:03:56 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by gatekeeper.Mib-infotech.co.nz (Postfix) with ESMTP id 514FD10C6EF;
	Sat, 27 Sep 2008 11:03:52 +1200 (NZST)
X-Virus-Scanned: amavisd-new at mib-infotech.co.nz
Received: from gatekeeper.Mib-infotech.co.nz ([127.0.0.1])
	by localhost (gatekeeper.mib-infotech.co.nz [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id bp5SyScDiSWw; Sat, 27 Sep 2008 11:03:39 +1200 (NZST)
Received: from [192.168.95.202] (mr-creosote.mib-infotech.co.nz
	[192.168.95.202])
	by gatekeeper.Mib-infotech.co.nz (Postfix) with ESMTPA id CEDF510C6EE;
	Sat, 27 Sep 2008 11:03:38 +1200 (NZST)
Message-ID: <48DD6A4A.10209@mib-infotech.co.nz>
Date: Sat, 27 Sep 2008 11:03:38 +1200
From: John Huttley <John@mib-infotech.co.nz>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: Andrew Sullivan <ajs@commandprompt.com>, pgsql-performance@postgresql.org
Subject: Re: Slow updates, poor IO
References: <48DBE587.3050406@mib-infotech.co.nz>
	<20080926131556.GA1005@commandprompt.com>
In-Reply-To: <20080926131556.GA1005@commandprompt.com>
Content-Type: multipart/alternative;
	boundary="------------060609000600090806020504"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.716 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001, RCVD_IN_SORBS_DUL=1.615, RDNS_DYNAMIC=0.1
X-Spam-Level: *
X-Archive-Number: 200809/236
X-Sequence-Number: 31549

This is a multi-part message in MIME format.
--------------060609000600090806020504
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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. this is part of the trade-offs of MVCC.
Apparently this is documented under 'MVCC' in the manual. It should be 
documented under 'performance'

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.
--even if you don't expect it.

--john

Andrew Sullivan wrote:
> Hi,
>
> On Fri, Sep 26, 2008 at 07:24:55AM +1200, John Huttley wrote:
>   
>> I've just had an interesting encounter with the slow full table update 
>> problem that is inherent with MVCC
>>     
>
> Quite apart from the other excellent observations in this thread, what
> makes you think this is an MVCC issue exactly?
>
> A
>
>   

--------------060609000600090806020504
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Andrew,<br>
There are two problems.<br>
The first is the that if there is a table with a index and an update is
performed on a non indexed field,<br>
the index is still re indexed. this is part of the trade-offs of MVCC.<br>
Apparently this is documented under 'MVCC' in the manual. It should be
documented under 'performance'<br>
<br>
We should reasonably expect that the total amount of IO will go up,
over a non-indexed table.<br>
<br>
The second thing is that the disk IO throughput goes way down.<br>
<br>
This is not an issue with MVCC, as such, except that it exposes the
effect of a write to an indexed field.<br>
--even if you don't expect it.<br>
<br>
--john<br>
<br>
Andrew Sullivan wrote:
<blockquote cite="mid:20080926131556.GA1005@commandprompt.com"
 type="cite">
  <pre wrap="">Hi,

On Fri, Sep 26, 2008 at 07:24:55AM +1200, John Huttley wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">I've just had an interesting encounter with the slow full table update 
problem that is inherent with MVCC
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Quite apart from the other excellent observations in this thread, what
makes you think this is an MVCC issue exactly?

A

  </pre>
</blockquote>
</body>
</html>

--------------060609000600090806020504--