Thread: Indexes in PL/SQL

I'm using PgSQL 7.3.2 and a PL/SQL routine that is posing some performance
problems for me. I have a table of users information as such (trimmed down):

user_main (
    uid int4,
    username character varying(32),
    password character varying(16)
);

Primary Index is uid
Unique Index on username

There are approx 1.5million records in the table.

Now, on the psql command line when i do

    SELECT uid FROM user_main WHERE username='bob';

it works and is very fast - the data is returned almost as fast as I can hit
enter.

Now I have a PL/SQL function (pgsql mail check):

    CREATE FUNCTION valid_user(text) RETURNS text AS '
    DECLARE
        _user ALIAS FOR $1;
        _uid user_main.uid%TYPE;
    BEGIN
        SELECT uid INTO _uid FROM user_main WHERE username=_user;

        IF FOUND THEN
            RETURN ''OK'';
        END IF;

        RETURN ''554 Sender Address Rejected'';
    END;' LANGUAGE 'plpgsql';

It works, however, the select is taking a very long time, 5-10 seconds.

Explain doesn't help much since it's a PL/SQL routine and I could not find
any mention of this in the lists (maybe my search criteria sucked).

Any ideas?

--
Tony Holmes

Founder and Senior Systems Architect
Crosswinds Internet Communications Inc.

Tony Holmes wrote:
[...snip...]
> username character varying(32),
         (1)^^^^^^^^^^^^^^^^^^
[...snip...]
> CREATE FUNCTION valid_user(text) RETURNS text AS '
                           (2)^^^^
> DECLARE
>  _user ALIAS FOR $1;
>  _uid user_main.uid%TYPE;
> BEGIN
> SELECT uid INTO _uid FROM user_main WHERE username=_user;
                                          (3)^^^^^^^^^^^^^^

I think you have a character type mismatch. When you write:

   SELECT uid FROM user_main WHERE username='bob';

the constant 'bob' is initially type "unknown". Postgres is then able to
deduce that it should be varchar given the context, and therefore finds
the index. In your function, try either:

(2) CREATE FUNCTION valid_user(varchar) RETURNS text AS '

or

(3) SELECT uid INTO _uid FROM user_main WHERE username=_user::varchar;

HTH,

Joe

Tony Holmes <tony@crosswinds.net> writes:
> user_main (
>     username character varying(32),
> );

>     CREATE FUNCTION valid_user(text) RETURNS text AS '
>     DECLARE
>         _user ALIAS FOR $1;
>         _uid user_main.uid%TYPE;
>     BEGIN
>         SELECT uid INTO _uid FROM user_main WHERE username=_user;

This won't use the index because it's a cross-datatype comparison
(varchar versus text).  You could change the declared type of the
function argument to varchar, or leave the function signature alone
and cast the argument to varchar in the SELECT:

    SELECT uid INTO _uid FROM user_main WHERE username = _user::varchar;

7.4 will be more forgiving about text-versus-varchar discrepancies, but
you can still get burnt by this problem on most other cross-datatype
cases --- including char(n) versus varchar(n).  Integer versus bigint
is another common gotcha for newbies.

            regards, tom lane

On +May 30, Tom Lane wrote:
>
>     SELECT uid INTO _uid FROM user_main WHERE username = _user::varchar;
>
> 7.4 will be more forgiving about text-versus-varchar discrepancies, but
> you can still get burnt by this problem on most other cross-datatype
> cases --- including char(n) versus varchar(n).  Integer versus bigint
> is another common gotcha for newbies.

Thanks! Got me and that fixed it.

--
Tony Holmes

Founder and Senior Systems Architect
Crosswinds Internet Communications Inc.