Query is fast and function is slow

Поиск
Список
Период
Сортировка
От Richard Ray
Тема Query is fast and function is slow
Дата
Msg-id Pine.LNX.4.64.0612060934410.15200@rray.drdc.mstc.ms.gov
обсуждение исходный текст
Ответы Re: Query is fast and function is slow  (Thomas Pundt <mlists@rp-online.de>)
Список pgsql-sql
The query

select count(*) from documents where doc_num = '106973821'  and (select 
bit_or(group_access) from mda_groups where group_name in (select groname 
from pg_user,pg_group where usename =  'bbob'  and usesysid = any(grolist) 
and (groname ~ '.*owner$' or groname = 'admin'))) & access > 
'0'::bit(100);

returns very fast

If I create function

create or replace function check_for_update_permission(text,text) returns 
boolean as '
declare  doc_number alias for $1;  user alias for $2;  doc_count integer;
begin
  select count(*) into doc_count from documents where doc_num = doc_number 
and (select bit_or(group_access) from mda_groups where group_name in 
(select groname from pg_user,pg_group where usename = user and usesysid = 
any(grolist) and (groname ~ ''.*owner$'' or groname = ''admin''))) & 
access > ''0''::bit(100);
  if doc_count > 0 then    return(true);  end if;
  return(false);

end;
' language 'plpgsql';


and run "select check_for_update_permission('106973821','bbob');"
it returns the correct info but takes several minutes
Would someone please enlighten me.
Can you do something like explain analyze on a function

Thanks
Richard



В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Proper way of iterating over the column names in a trigger function.
Следующее
От: "Rajesh Kumar Mallah"
Дата:
Сообщение: Re: Proper way of iterating over the column names in a trigger function. [ SOLVED]