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

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

[Margusja]

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
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)
  Hash Cond: ("outer".account = "inner".id)
  ->  Seq Scan on taskid  (cost=0.00..698.45 rows=2245 width=10) (actual
time=0.041..29.704 rows=2246 loops=1)
  ->  Hash  (cost=3.09..3.09 rows=109 width=20) (actual time=0.522..0.522
rows=0 loops=1)
->  Seq Scan on accounts  (cost=0.00..3.09 rows=109 width=20)
(actual time=0.090..0.371 rows=109 loops=1)
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)
  Hash Cond: ("outer".account = "inner".id)
  ->  Seq Scan on taskid  (cost=0.00..698.45 rows=2245 width=10) (actual
time=0.041..35.313 rows=2246 loops=1)
  ->  Hash  (cost=3.09..3.09 rows=109 width=20) (actual time=0.529..0.529
rows=0 loops=1)
->  Seq Scan on accounts  (cost=0.00..3.09 rows=109 width=20)
(actual time=0.092..0.376 rows=109 loops=1)
Total runtime: 9098.051 ms
(6 rows)
206.261 ms versus 9098.051 ms!!! What's wrong _t() function?
table taskid structure is:
 Table "public.taskid"
   Column |Type | 
Modifiers
---+-+---
id| integer | not null default 
nextval('"taskid_id_seq"'::text)
task_name | character varying(255)  | not null
task_type | smallint|
account   | integer |
mn_actual | character(10)   |
mh_planned| character(10)   |
finish_planed | timestamp without time zone |
finish_actual | timestamp without time zone |
prioriteet| integer | default 1
created_on| timestamp without time zone | not null
created_by| character varying(50)   | not null
show_to_client| boolean | not null default false
assigned_to   | integer[]   | not null
private   | boolean | default false
t_status  | smallint| default (1)::smallint
problem   | text|
solution  | text|
product   | integer |
area1 | integer |
area2 | integer |
area3 | integer |
project   | integer |
start_planed  | timestamp with time zone|
start_actual  | timestamp with time zone|
team  | integer |
opportunity_id| integer |
split | boolean | not null default false
copy_id   | integer | default 0
task_extid1   | character varying(20)   |
task_extid2   | character varying(20)   |
task_seqno| integer |
task_parentid | integer |
task_color| character varying(10)   |
task_contact_id   | integer |
task_timeless | boolean | default false
task_milestone| boolean | default false
task_notify   | integer |
task_security | integer |
task_location | integer |
task_type2| integer |
task_d