To: "Manuel Wenger" <manuel.wenger@ticinocom.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Trigger performance problem 
In-reply-to: <5A838EC3CE99804991EF9D37875C5A6F3343@exchange.ticinocom.com> 
References: <5A838EC3CE99804991EF9D37875C5A6F3343@exchange.ticinocom.com>
Comments: In-reply-to "Manuel Wenger" <manuel.wenger@ticinocom.com>
	message dated "Mon, 16 May 2005 15:06:49 +0200"
Date: Tue, 17 May 2005 13:47:23 -0400
Message-ID: <705.1116352043@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Manuel Wenger" <manuel.wenger@ticinocom.com> writes:
> We're having a performance problem with PostgresQL 8.0.2 running on
> RHEL3 Update 4. There is a frequently updated table logging all our ADSL
> customer logins which has 2 related triggers. An INSERT on that table,
> "calls", takes about 300ms to execute according to the logs, and the
> process takes up to 30% of the server CPU. When removing the triggers it
> drops to 10-20ms.

You need to figure out exactly which operation(s) inside the triggers
is so expensive.  You could try removing commands one at a time and
timing the modified triggers.

Just on general principles, I'd guess that this might be the problem:

> 	delete from currentip where ip is null;

Since an IS NULL test isn't indexable by a normal index, this is going
to cause a full scan of the currentip table every time.  I don't really
understand why you need that executed every time anyway ... why is it
this trigger's responsibility to clean out null IPs?  But if you really
do need to make that run quickly, you could create a partial index with
a WHERE clause of "ip is null".

			regards, tom lane