Search Postgresql Archives

query is wery slow with _t() function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux