Re: query is wery slow with _t() function

Margusja wrote:
> Hi, I made function:
>
> CREATE FUNCTION _t(varchar,integer) RETURNS varchar AS '
> DECLARE
>    str ALIAS FOR $1;
>    lang ALIAS FOR $2;
>    value varchar;
> BEGIN
>
>
> SELECT t.txt INTO value FROM sys_txt t INNER JOIN sys_txt_code c ON c.id =
> t.code_id WHERE ''#''||c.code||''#'' = str AND t.lang_id = lang;
>
> --RAISE NOTICE ''%'', value;
> IF value IS NULL THEN value := str; END IF;
> RETURN (value);
> END;
> ' LANGUAGE plpgsql immutable

I'm not sure you could describe this function as immutable, since it
queries the databse. If you alter the contents of sys_txt or
sys_txt_code then its results will change.

I'd recommend re-reading that section of the manuals.

> Now I make query without _t() finction and speed is normal.
>
> test=# EXPLAIN ANALYZE SELECT taskid.id, clfID2clfName(taskid.task_type)
> AS task_type, accounts.nimi as account  FROM taskid INNER JOIN accounts ON
> taskid.account = accounts.id ;
>                                                    QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------
>
> Hash Join  (cost=3.36..751.14 rows=2166 width=22) (actual
> time=1.065..203.845 rows=2105 loops=1)
[snip]
> Total runtime: 206.261 ms
> (6 rows)
>
> in table taskid is 2246 records.
>
> Now make query with _t() function and speed is very slow :(
> test=# EXPLAIN ANALYZE SELECT taskid.id,
> _t(clfID2clfName(taskid.task_type),11) AS task_type, accounts.nimi as
> account  FROM taskid INNER JOIN accounts ON taskid.account = accounts.id ;
>                                                    QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------
>
> Hash Join  (cost=3.36..756.55 rows=2166 width=22) (actual
> time=5.568..9093.637 rows=2105 loops=1)
[snip]
> Total runtime: 9098.051 ms

Well, it's not the same query is it? In the second you are calling _t()
for each of your 2105 rows. If it takes 2ms for each call of _t() then
that would account for the difference.

Is there any reason why you are using functions for these simple lookups
rather than joining to the translation table?

--
   Richard Huxton
   Archonet Ltd