Re: Slow count(*) again...

From: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow count(*) again...
Date: 2010-10-13 07:19:26
Message-ID: 4CB55D7E.8080902@catalyst.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2010-10-13 07:19:27 Re: Issues with two-server Synch Rep
Previous Message Robert Haas 2010-10-13 06:50:06 Re: Issues with Quorum Commit

Browse pgsql-performance by date

  From Date Subject
Next Message Reuven M. Lerner 2010-10-13 07:30:45 SQL functions vs. PL/PgSQL functions
Previous Message Neil Whelchel 2010-10-13 06:47:19 Re: Slow count(*) again...