Message-ID: <49D4F8A5.7090009@andrew.cmu.edu>
Date: Thu, 02 Apr 2009 13:40:53 -0400
From: Tony Cebzanov <tonyceb@andrew.cmu.edu>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X; en-US;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.6.0
MIME-Version: 1.0
To: Craig Ringer <craig@postnewspapers.com.au>
CC: pgsql-sql@postgresql.org
Subject: Re: Performance problem with row count trigger
References: <49D4CA3A.3050005@andrew.cmu.edu>
	<49D4EB7A.1070002@postnewspapers.com.au>
In-Reply-To: <49D4EB7A.1070002@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi Craig, thanks for your help.

Craig Ringer wrote:
> MVCC bloat from the constant updates to the assoc_count table, maybe?

That's what a coworker suggested might be happening.  The fact that a
no-op trigger performs fine but the UPDATE trigger doesn't would seem to
confirm that it's something in the trigger SQL and not in the trigger
mechanism itself.

> If you're using 8.3, I'd expect HOT to save you here. Are you using an
> older version of PostgreSQL? If not, have you by any chance defined an
> index on assoc_count ?

I'm running 8.3.7, which is the most recent version from Macports.
There's no index of any kind on dataset.assoc_count.

Having read up on HOT, it sounds like it would be helpful.  Is there
anything I need to do to enable HOT in 8.3.7, or is it always used?

> Also, try to keep records in your `dataset' table as narrow as possible.
> If the catalog_id, t_begin, t_end, ctime and mtime fields do not change
> almost as often as the assoc_count field, split them into a separate
> table with a foreign key referencing dataset_id, rather than storing
> them directly in the dataset table.

ctime is the creation time of the dataset, so it's never supposed to be
updated.  mtime is the last time the dataset was changed, and there's
another trigger to update that timestamp whenever the dataset table
changes.  So, at best, I'd be able to remove the ctime column from the
dataset table, but I'd have to add the foreign key, so I don't think
that would be helpful.

Your mention of the ctime and mtime columns made me think the update
timestamp trigger may be contributing to the problem as well (since the
assoc INSERT trigger would presumably cause the dataset UPDATE trigger
to fire), but dropping that trigger yielded no improvement, so I think
it's something else.