I'm not sure I will be correct. Tom, could you please check this?
I usually work with many PLPGSQL functions. They are mostly same fast like
SQL functions. But now, I found a difference. My SQL function was much
more faster than the PLPGSQL one. I signed the "slow" statement in the
PLPGSQL function, see below. Sorry for the Hungarian column names and for
the missing tables. The appropriate functions are cikk_ikon_fast and
cikk_ikon_slow. Well...
CREATE TABLE cikk (
az SERIAL PRIMARY KEY,
nev varchar(80) NOT NULL,
mennyisegi_egyseg int4 NOT NULL REFERENCES mennyisegi_egyseg(az),
szin int4 NOT NULL REFERENCES szin(az),
tipus int4 NOT NULL DEFAULT 1,
megjegyzes varchar(250),
felvitel_allapot int4 references cikkfelvitel_allapot
NOT NULL DEFAULT 1);
CREATE INDEX ckk_ndx_nv on cikk using btree ( nev varchar_ops );
CREATE FUNCTION cikk_tipus (int4) RETURNS int4
AS 'select tipus from cikk where $1 = az;'
LANGUAGE 'SQL';
CREATE TABLE cikkstruktura (
fajta int4 NOT NULL REFERENCES cikk (az) on update cascade,
valtozat int4 NOT NULL REFERENCES cikk(az) ON DELETE CASCADE,
CONSTRAINT cikkstruktura_pk PRIMARY KEY (fajta, valtozat));
CREATE INDEX ckkstrktr_ndx_fjt on cikkstruktura using btree ( fajta int4_ops );
CREATE INDEX ckkstrktr_ndx_vltzt on cikkstruktura using btree ( valtozat int4_ops );
CREATE FUNCTION cikk_fajta (int4) RETURNS int4 as '
SELECT fajta FROM cikkstruktura WHERE valtozat = $1
AND cikk_tipus(fajta) = 3;
' LANGUAGE 'SQL';
CREATE FUNCTION cikk_ikon_fast(int4) RETURNS int4 AS 'SELECT (CASE
WHEN (cikk_fajta($1)=4) THEN (CASE
WHEN (cikk_tipus($1)=2) THEN 33 ELSE 32 END) ELSE (CASE
WHEN (cikk_fajta($1)=33) THEN (CASE
WHEN (cikk_tipus($1)=2) THEN 9 ELSE 8 END) ELSE -1 END) END)
FROM cikk; ' language 'sql';
-- Wow! A complex query! :-)
CREATE FUNCTION cikk_ikon_slow(int4) RETURNS int4 AS '
DECLARE
_fajta int4;
_elvi bool;
begin
SELECT INTO _fajta, _elvi
cikk_fajta(az),
tipus = 2
FROM cikk WHERE az = $1;
-- This query makes it slow. The main part for being slow is:
-- _fajta := cikk_fajta($1), in short.
if _fajta = 4 then
if _elvi then
return 33;
else
return 32;
end if;
end if;
if _fajta = 33 then
if _elvi then
return 9;
else
return 8;
end if;
end if;
return -1;
end;
' language 'plpgsql';
-- OK, this is much easier to understand, but slow.
--
In our application it is usual to call the function cikk_ikon a lot of
times. On my 233 MHz Pentium-2 the fast one is 1 sec, the slow one is 5
secs for ~1000 lines.
As I can explain, the reason why PLPGSQL function is slow, because it
sends more than one query to the backend instead of the SQL one, which
makes the backend to do only one query and this query is much more
optimized than those more ones separately. My theory in such optimizations
is to send as few queries to the backend as I can, preferring being the
queries much more complex instead of many and easy-to-process.
Comments?
Regards, Zoltan
