Re: Slow count(*) again...

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