Re: Call of function inside trigger much slower than explicit function call - Mailing list pgsql-performance

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