Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 8227B9FA0FB for ; Thu, 7 Dec 2006 10:53:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 72816-01 for ; Thu, 7 Dec 2006 10:53:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from itspmx2.state.ms.us (itspmx2.state.ms.us [205.144.224.112]) by postgresql.org (Postfix) with ESMTP id ABC7B9F9F46 for ; Thu, 7 Dec 2006 10:53:39 -0400 (AST) Received: from rray.drdc.mstc.ms.gov ([10.3.192.2]) by itspmx2.state.ms.us (8.13.4/8.13.4) with ESMTP id kB7ErW07025810 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 7 Dec 2006 08:53:37 -0600 (CST) Received: from rray.drdc.mstc.ms.gov (localhost.localdomain [127.0.0.1]) by rray.drdc.mstc.ms.gov (8.13.6/8.13.6) with ESMTP id kB7ErV4M019796 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 7 Dec 2006 08:53:32 -0600 Received: from localhost (rray@localhost) by rray.drdc.mstc.ms.gov (8.13.6/8.13.6/Submit) with ESMTP id kB7ErVGO019793 for ; Thu, 7 Dec 2006 08:53:31 -0600 X-Authentication-Warning: rray.drdc.mstc.ms.gov: rray owned process doing -bs Date: Thu, 7 Dec 2006 08:53:31 -0600 (CST) From: Richard Ray X-X-Sender: rray@rray.drdc.mstc.ms.gov To: pgsql-sql@postgresql.org Subject: Re: Query is fast and function is slow In-Reply-To: <200612071053.01287.mlists@rp-online.de> Message-ID: References: <200612071053.01287.mlists@rp-online.de> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Archive-Number: 200612/74 X-Sequence-Number: 27030 On Thu, 7 Dec 2006, Thomas Pundt wrote: > Hi, > > On Wednesday 06 December 2006 16:44, Richard Ray wrote: > | 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 > ... > | 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 > > Just a guess: is the column "doc_num" really of type text? Maybe using "text" > in the function lets the planner choose a sequential scan? Actually "doc_num" is char(9) I changed text to char(9) and got same slow results > > I'd try putting a "raise notice '%', explain analyze ..." statement into the > function and check the log file. It appears that the function is not using the index The table documents has a index on doc_num and doc_num is a unique value dcc=# explain analyze select doc_num from documents where doc_num = '106973821'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Index Scan using documents_pkey on documents (cost=0.00..5.48 rows=1 width=13) (actual time=37.475..37.481 rows=1 loops=1) Index Cond: (doc_num = '106973821'::bpchar) Total runtime: 37.535 ms (3 rows) dcc=# But this same statement in a function takes several minutes; My SQL knowledge is pitiful so would you explain how to use "explain analyze" in the function I get errors when I try to load the file with raise notice ''%'',explain analyze select doc_num from documents where doc_num = doc_number; dcc=# \i /src/check_for_update_permission psql:/src/check_for_update_permission:52: ERROR: syntax error at or near "analyze" at character 16 QUERY: SELECT explain analyze select doc_num from documents where doc_num = $1 CONTEXT: SQL statement in PL/PgSQL function "check_for_update_permission" near line 18 psql:/src/check_for_update_permission:52: LINE 1: SELECT explain analyze select doc_num from documents where d... psql:/src/check_for_update_permission:52: ^ dcc=# > > Ciao, > Thomas > >