Обсуждение: Wierd issues

"Andrew Matthews" <andrew.m@corp.dslextreme.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=70809loops=1) 

>                      ->  Seq Scan on users u  (cost=0.00..1888.85 rows=71548 width=4) (actual time=18.38..2277152.51
rows=71028loops=1) 
>                            Filter: (get_pwd(username, '127.0.0.1'::character varying, '101'::character varying,
'MD5'::charactervarying) 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

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@sss.pgh.pa.us]
Sent: Friday, April 09, 2004 8:02 AM
To: Andrew Matthews
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Wierd issues

"Andrew Matthews" <andrew.m@corp.dslextreme.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