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.
[snip]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)
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
