Skip site navigation (1) Skip section navigation (2)

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Alexander Gorban <alex(dot)gorban(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Call of function inside trigger much slower than explicit function call
Date: 2009-02-17 19:31:04
Message-ID: 603c8f070902171131r866ac0bja9a54db0f37882b@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Feb 17, 2009 at 12:46 PM, Alexander Gorban
<alex(dot)gorban(at)gmail(dot)com> wrote:
> В Втр, 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.

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

In response to

Responses

pgsql-performance by date

Next:From: Ross J. ReedstromDate: 2009-02-17 20:04:17
Subject: Re: TCP network cost
Previous:From: Havasvölgyi OttóDate: 2009-02-17 18:55:50
Subject: Re: Query composite index range in an efficient way

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group