Re: Call of function inside trigger much slower than explicit function call

В Втр, 17/02/2009 в 12:24 -0500, Robert Haas пишет:
> On Tue, Feb 17, 2009 at 11:17 AM, Alexander Gorban
> <alex(dot)gorban(at)gmail(dot)com> wrote:
> > 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;
>
> Well my first guess is that when you actually do the insertion you
> have to transfer the file from the client to the database, but when
> you subsequently call the function by hand you're calling it on data
> that is already in the database, so there's no transfer time... how
> big are these images, anyway?
>
> ...Robert

Also I've defined function to load images from disk directly inside sql
query:

CREATE FUNCTION bytea_load_from_file(path text) RETURNS BYTEA
AS 'libmylib.so','bytea_load_from_file' LANGUAGE C;

and use it in both cases - for insertion of image and to call function
directly. So, there is no difference it times spent for image loading.
Here is code that I use
1. Insertion example:
test_base=# insert INTO images_meta(label,data) VALUES('original',
bytea_load_from_file('/tmp/test.jpg'));

2. Direct call:
test_base=#select imscale(bytea_load_from_file('/tmp/test.jpg'),128);

I realize, that insertion require more operations to perform (insert
initial image, fire after insert trigger, insert thumbnail, fire trigger
again after insertion thumbnail). But these operations not seems very
hard.

Size of image, that I use for tests is about 2MB. That is why 3sec. it
is very long time to process it.