Message-ID: <49D4EB7A.1070002@postnewspapers.com.au>
Date: Fri, 03 Apr 2009 00:44:42 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.21 (X11/20090319)
MIME-Version: 1.0
To: Tony Cebzanov <tonyceb@andrew.cmu.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Performance problem with row count trigger
References: <49D4CA3A.3050005@andrew.cmu.edu>
In-Reply-To: <49D4CA3A.3050005@andrew.cmu.edu>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Tony Cebzanov wrote:

> The throughput of the first batch of 1,000 is diminished, but still
> tolerable, but after 10,000 inserts, it's gotten much worse.  This
> pattern continues, to the point where performance is unacceptable after
> 20k or 30k inserts.
> 
> To rule out the performance of the trigger mechanism itself, I swapped
> the trigger out for one that does nothing.  The results were the same as
> without the trigger (the  first set of numbers), which leads me to
> believe there's something about the UPDATE statement in the trigger that
> is causing this behavior.

MVCC bloat from the constant updates to the assoc_count table, maybe?

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 ?

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.

--
Craig Ringer