Message-ID: <4548CC84.9000605@archonet.com>
Date: Wed, 01 Nov 2006 16:34:12 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060918)
MIME-Version: 1.0
To: Ben <bench@silentmedia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: big transaction slows down over time - but disk seems
References: <Pine.LNX.4.64.0610312141430.5452@GRD.cube42.tai.silentmedia.com>
	<45486959.60103@archonet.com>
	<A054DD90-B18E-430E-B3B0-A9DA5DA41B26@silentmedia.com>
In-Reply-To: <A054DD90-B18E-430E-B3B0-A9DA5DA41B26@silentmedia.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ben wrote:
> My transaction calls the same stored procedure many times over. Over the 
> lifetime of the transaction, that stored procedure slows down by roughly 
> 2 orders of magnitude. The procedure itself tries to look up several 
> strings in dictionary tables, and if the strings aren't there (most of 
> them will be) it inserts them. All those dictionary tables have indexes. 
> After it has converted most of the strings into ids, it does another 
> lookup on a table and if it finds a matching row (should be the common 
> case) it updates a timestamp column of that row; otherwise, it inserts a 
> new row.

Which would suggest Heikki's guess was pretty much right and it's dead 
rows that are causing the problem.

Assuming most updates are to this timestamp, could you try a test case 
that does everything *except* update the timestamp. If that runs 
blazingly fast then we've found the problem.

If that is the problem, there's two areas to look at:
1. Avoid updating the same timestamp more than once (if that's happening)
2. Update timestamps in one go at the end of the transaction (perhaps by 
loading updates into a temp table).
3. Split the transaction in smaller chunks of activity.

> So.... there isn't much table size changing, but there are a lot of 
> updates. Based on pg_stat_user_tables I suspect that the procedure is 
> using indexes more than table scans. Is there a better way to know?

Not really. You can check the plans of queries within the function, but 
there's no way to capture query plans of running functions.

-- 
   Richard Huxton
   Archonet Ltd