Thread: Query is fast and function is slow
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
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? I'd try putting a "raise notice '%', explain analyze ..." statement into the function and check the log file. Ciao, Thomas -- Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
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 ----------------------------------------------------------------------------------------------------------------------------- IndexScan 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.535ms (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 > >
Hi, On Thursday 07 December 2006 15:53, Richard Ray wrote: | 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=# ok, seems you can't use a SQL statement as expression here; instead try using a "for statement" then: for v_rec in explain analyze <your_query_here> loop raise notice '%', v_rec; end loop; don't forget to declare "v_rec text;" Ciao, Thomas -- Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
Richard Ray <rray@mstc.state.ms.us> writes: > On Thu, 7 Dec 2006, Thomas Pundt wrote: >> 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 You need to make the second argument type "name", too, if you have a lot of users. regards, tom lane
On Thu, 7 Dec 2006, Tom Lane wrote: > Richard Ray <rray@mstc.state.ms.us> writes: >> On Thu, 7 Dec 2006, Thomas Pundt wrote: >>> 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 > > You need to make the second argument type "name", too, if you have a lot > of users. I said my knowledge was pitiful Changing both parameters to char(9) and name fixed the problem It appears to be using the index If time allows could you explain this a bit Thanks Richard > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
Richard Ray <rray@mstc.state.ms.us> writes: > Changing both parameters to char(9) and name fixed the problem > It appears to be using the index > If time allows could you explain this a bit EXPLAIN will show you what's going on: regression=# create table foo (f1 char(9) unique); NOTICE: CREATE TABLE / UNIQUE will create implicit index "foo_f1_key" for table "foo" CREATE TABLE regression=# explain select * from foo where f1 = 'bar'; QUERY PLAN -----------------------------------------------------------------------Index Scan using foo_f1_key on foo (cost=0.00..8.02rows=1 width=13) Index Cond: (f1 = 'bar'::bpchar) (2 rows) regression=# explain select * from foo where f1 = 'bar'::text; QUERY PLAN -----------------------------------------------------Seq Scan on foo (cost=0.00..35.95 rows=9 width=13) Filter: ((f1)::text= 'bar'::text) (2 rows) The second case is unable to use the index because the query is not really interrogating the value of f1, but the value of CAST(f1 AS text), and that's not what's indexed. This is not just an academic point, because the semantics of comparison for char(n) and text are actually different --- text is sensitive to trailing whitespace, char(n) isn't. So if we ignored the distinction and tried to use the index anyway, we'd probably get wrong answers. The reason the handwritten query comes out OK is that you've got an untyped literal constant, and the heuristic the parser likes to use for resolving the type of such a literal is "make it the same type as whatever it's being compared to". So 'bar' is assumed to be char(n) and all is well. In your function, though, the parameter is specifically declared to be text, so you wrote a char(n) vs text comparison, and that's resolved to mean "promote the char(n) to text and do a text comparison". Which is exactly what we can see it doing in my second example above. Same problem with the other thing: pg_user.usename is type name, not type text. regards, tom lane