Обсуждение: 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