From: Tom Lane <tgl@sss.pgh.pa.us>
To: Manuel Wenger <manuel.wenger@ticinocom.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Trigger performance problem
Date: Tue, 17 May 2005 13:47:23 -0400
Message-ID: <705.1116352043@sss.pgh.pa.us> (raw)
In-Reply-To: <5A838EC3CE99804991EF9D37875C5A6F3343@exchange.ticinocom.com>
References: <5A838EC3CE99804991EF9D37875C5A6F3343@exchange.ticinocom.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, manuel.wenger@ticinocom.com
Subject: Re: Trigger performance problem
In-Reply-To: <705.1116352043@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox