Call of function inside trigger much slower than explicit function call

Поиск
Список
Период
Сортировка
От Alexander Gorban
Тема Call of function inside trigger much slower than explicit function call
Дата
Msg-id 1234887434.14815.37.camel@gas-laptop
обсуждение исходный текст
Ответы Re: Call of function inside trigger much slower than explicit function call
Список 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;


В списке pgsql-performance по дате отправления:

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: Query composite index range in an efficient way
Следующее
От: Scott Carey
Дата:
Сообщение: Re: suggestions for postgresql setup on Dell 2950 , PERC6i controller