Re: [GENERAL] query is wery slow with _t() function

[Richard Huxton]

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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org