query is wery slow with _t() function
| От | Margusja |
|---|---|
| Тема | query is wery slow with _t() function |
| Дата | |
| Msg-id | 42775542.80409@kodila.ee обсуждение исходный текст |
| Ответы |
Re: query is wery slow with _t() function
(Richard Huxton <dev@archonet.com>)
|
| Список | pgsql-general |
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_dur_min | integer |
multy_clf | integer[] |
task_dur_act | numeric |
task_dur_pln | numeric |
task_resp | integer | not null
task_dur_minutes | integer |
sys_assigned_total | integer | default 0
sys_assigned_accepted | integer | default 0
sys_assigned_rejected | integer | default 0
channel | integer |
modify_on | timestamp without time zone | default now()
modify_by | character varying(50) |
Indexes:
"taskid_id_key" unique, btree (id)
"taskid_id_ukey" unique, btree (id)
"taskid_modify_on_key" btree (modify_on)
table sys_txt structure is:
Table "public.sys_txt"
Column | Type | Modifiers
---------+------------------------+---------------------------------------------------------
id | integer | not null default nextval('public.sys_txt_id_seq'::text)
lang_id | integer |
code_id | integer |
txt | character varying(255) |
Indexes:
"sys_txt__id_id_key" btree (id)
table sys_txt_code structure is:
Table "public.sys_txt_code"
Column | Type | Modifiers
----------+------------------------+--------------------------------------------------------------
id | integer | not null default nextval('public.sys_txt_code_id_seq'::text)
code | character varying(100) |
descr | character varying(255) |
group_id | integer[] |
code_new | character varying(100) |
Indexes:
"sys_txt_code__code_ukey" unique, btree (code)
"sys_txt_code_ukey" unique, btree (id)
Reg, Margusja
В списке pgsql-general по дате отправления: