Re: Wierd issues

Yes I did do analyze.... the here is the get_pwd function

-- Function: public.get_pwd(varchar, varchar, varchar, varchar)

-- DROP FUNCTION public.get_pwd(varchar, varchar, varchar, varchar);

CREATE OR REPLACE FUNCTION public.get_pwd(varchar, varchar, varchar,
varchar)
RETURNS varchar AS
'
DECLARE
p_username ALIAS for $1;
p_server ALIAS for $2;
p_service ALIAS for $3;
p_pwd_type ALIAS for $4;

l_resource_id integer;
l_server_id integer;
l_service_id integer;
l_allow_deny char(1);
l_user_id integer;
l_account_id integer;
l_passwd varchar(40);
begin

-- get server identifier
select id
into l_server_id
from servers s
where address = p_server;

if NOT FOUND then
-- try to get default server
select id
into l_server_id
from servers s
where address = \'default\';
end if;

if l_server_id isnull then
return NULL;
end if;

-- get service identifier
select id
into l_service_id
from services s
where radius_service = p_service;

if l_service_id isnull then
return NULL;
end if;

-- get resource identifier (server/service combination)
select id
into l_resource_id
from resources r
where service_id = l_service_id
and server_id = l_server_id;

-- could not find resource via server_id, now look via server\'s group if
any
if l_resource_id isnull then
select id
into l_resource_id
from resources r
where service_id = l_service_id
and server_group_id = (select server_group_id from servers where id =
l_server_id);
end if;

-- could not determine resource user wants to access, so deny by returning
NULL passwd
if l_resource_id isnull then
return NULL;
end if;

-- at this point we have a valid resource_id
-- determine if valid username
select u.id, u.account_id
into l_user_id, l_account_id
from users u, accounts a
where u.username = upper(p_username) -- always uppercase in DB
and u.del_id = 0
and u.status = \'A\'
and a.status = \'A\'
and u.account_id = a.id;

-- if active user not found then return NULL for passwd
if l_user_id isnull then
return null;
end if;

-- user specific control
select allow_deny
into l_allow_deny
from users_acl
where resource_id = l_resource_id
and user_id = l_user_id;

if l_allow_deny = \'D\' then
return NULL;
elsif l_allow_deny isnull then -- no user-specific control
select max(allow_deny) -- \'D\' is > \'A\' hence deny takes precedence
if conflict across groups
into l_allow_deny
from users_acl
where resource_id = l_resource_id
and user_group_id in (select user_group_id from
user_group_assignments
where user_id = l_user_id);
elsif l_allow_deny = \'A\' then
-- do nothing; -- get and return passwd below
end if;

if l_allow_deny isnull or l_allow_deny = \'D\' then
return NULL;
elsif l_allow_deny = \'A\' then
select password
into l_passwd
from user_pwds
where password_type = upper(p_pwd_type)
and user_id = l_user_id;

return l_passwd;
else
return null;
end if;

end;

'
LANGUAGE 'plpgsql' VOLATILE;

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Friday, April 09, 2004 8:02 AM
To: Andrew Matthews
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Wierd issues

"Andrew Matthews" <andrew(dot)m(at)corp(dot)dslextreme(dot)com> writes:
> [ PG 7.3.4 much slower than 7.2.1 ]
>
> Both have same databases, Both have had vacume full ran on them.

You did ANALYZE too, right?

The bulk of the time is evidently going into the seqscan on users in
each case:

> -> Seq Scan on users u (cost=0.00..1938.51
rows=71283 width=4) (actual time=0.81..30119.58 rows=70809 loops=1)

> -> Seq Scan on users u (cost=0.00..1888.85
rows=71548 width=4) (actual time=18.38..2277152.51 rows=71028 loops=1)
> Filter: (get_pwd(username,
'127.0.0.1'::character varying, '101'::character varying, 'MD5'::character
varying) IS NOT NULL)

I have to suspect that the inefficiency is inside this get_pwd()
function, but you didn't tell us anything about that...

regards, tom lane