Skip site navigation (1) Skip section navigation (2)

Re: query is wery slow with _t() function

From: Richard Huxton <dev(at)archonet(dot)com>
To: Margusja <margusja(at)kodila(dot)ee>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: query is wery slow with _t() function
Date: 2005-05-03 11:03:10
Message-ID: 42775A6E.80206@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: FERREIRA, William (COFRAMI)Date: 2005-05-03 11:45:57
Subject: some questions : psql
Previous:From: MargusjaDate: 2005-05-03 10:41:06
Subject: query is wery slow with _t() function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group