Message-ID: <4CB55D7E.8080902@catalyst.net.nz>
Date: Wed, 13 Oct 2010 20:19:26 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.12) Gecko/20100915 Thunderbird/3.0.8
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Slow count(*) again...
References: <201010091626.18636.neil.whelchel@gmail.com>
	<4CB2410C.6010603@postnewspapers.com.au>
	<201010102115.58729.neil.whelchel@gmail.com>
	<201010122347.20542.neil.whelchel@gmail.com>
In-Reply-To: <201010122347.20542.neil.whelchel@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 13/10/10 19:47, Neil Whelchel wrote:
>
> Nope...
> So, possible conclusions are:
> 1. Even with VACUUM database table speed degrades as tables are updated.
> 2. Time testing on a freshly INSERTed table gives results that are not real-
> world.
> 3. Filesystem defragmentation helps (some).
> 4. Cache only makes a small difference once a table has been UPDATEd.
>
> I am going to leave this configuration running for the next day or so. This
> way I can try any suggestions and play with any more ideas that I have.
> I will try these same tests on ext4 later, along with any good suggested
> tests.
> I will try MySQL with the dame data with both XFS and ext4.
> -Neil-
>
>    

I think that major effect you are seeing here is that the UPDATE has 
made the table twice as big on disk (even after VACUUM etc), and it has 
gone from fitting in ram to not fitting in ram - so cannot be 
effectively cached anymore.

This would not normally happen in real life (assuming UPDATEs only 
modify a small part of a table  per transaction). However administration 
updates (e.g 'oh! -  ref 1 should now be ref 2 please update 
everything') *will* cause the table size to double.

This is an artifact of Postgres's non overwriting storage manager - 
Mysql will update in place and you will not see this.

Try VACUUM FULL on the table and retest.

regards

Mark