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

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;

On Tue, Feb 17, 2009 at 11:17 AM, Alexander Gorban
<alex.gorban@gmail.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

В Втр, 17/02/2009 в 12:24 -0500, Robert Haas пишет:
> On Tue, Feb 17, 2009 at 11:17 AM, Alexander Gorban
> <alex.gorban@gmail.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.

On Tue, Feb 17, 2009 at 12:46 PM, Alexander Gorban
<alex.gorban@gmail.com> wrote:
> В Втр, 17/02/2009 в 12:24 -0500, Robert Haas пишет:
>> On Tue, Feb 17, 2009 at 11:17 AM, Alexander Gorban
>> <alex.gorban@gmail.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.

Well, that does sound weird... can you post the full definition for
the images_meta table?  Are there any other triggers on that table?
Is it referenced by any foreign keys?  How fast is the insert if you
drop the trigger?

...Robert

I do no, but you really need rescale the image when he comes to database? or can you doing this after, in a schudeled job?
If you need resize the image  en it comes, I believe you pay a price related about performance because the this is working to save image, the toast strtucture are receiving the data and  the server need sync this... all at same time...
can you mail the times for the insert with and without this trigger?




--
Ivo Nascimento - Iann
-------------------------------------
|   twitter: ivonascimento .     |
|   http://ianntech.com.br.      |
|   ZCE ID 227463685            |
-------------------------------------

> Well, that does sound weird... can you post the full definition for
> the images_meta table?  Are there any other triggers on that table?
> Is it referenced by any foreign keys?  How fast is the insert if you
> drop the trigger?
>
> ...Robert

Yes, weird. Something was wrong in my own code, after I've rewrite it to
send you full sources of problem example, execution times of image
insertion and direct scaling function call became the same. Insertion of
4000x2667px (2MB) image and direct function call for downscaling
original image to 800x600px and 128x128px both takes 1.6 sec. Sorry for
confusion. And it is almost the same time that takes command line
utility to do the task. So, practically there is no overhead of using
triggers for such purposes.

Nevertheless here is my sources, maybe there is a better way to solve
the task?
http://www.filedropper.com/imscalepgexample