Call of function inside trigger much slower than explicit function call

From: Alexander Gorban <alex(dot)gorban(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Call of function inside trigger much slower than explicit function call
Date: 2009-02-17 16:17:14
Message-ID: 1234887434.14815.37.camel@gas-laptop
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I have table containing bytea and text columns. It is my storage for
image files and it's labels. Labels can be 'original' and 'thumbnail'.
I've C-function defined in *.so library and corresponding declaration in
postgres for scaling image. This function scale image and insert it into
the same table with the label 'thumbnail'. I have trigger on before
insert or update on the table which make thumbnail for image labeled as
'original'.

Inserting single image into the table takes about 3 SECONDS!. But call
of scaling function directly in psql command prompt is approximately 20
times faster. If I comment out scaling function call in the trigger,
insertion, and it is evident, becomes immediate (very fast).

Here my somehow pseudo code:

CREATE TABLE images_meta
(
data bytea,
label text
);

CREATE FUNCTION imscale(data bytea, width integer)
RETURNS integer AS 'libmylib.so', 'imscale' LANGUAGE 'c';

CREATE FUNCTION auto_scale() RETURNS trigger AS $$
DECLARE
notused integer;
BEGIN
IF NEW.label = 'original' THEN
notused := imscale(NEW.data, 128);
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Carey 2009-02-17 16:19:23 Re: suggestions for postgresql setup on Dell 2950 , PERC6i controller
Previous Message Kevin Grittner 2009-02-17 16:16:13 Re: Query composite index range in an efficient way