PL/pgSQL functions - text / varchar - havy performance issue?!

Поиск
Список
Период
Сортировка
От Oliver Siegmar
Тема PL/pgSQL functions - text / varchar - havy performance issue?!
Дата
Msg-id 200308291554.46748.o.siegmar@vitrado.de
обсуждение исходный текст
Ответы Re: PL/pgSQL functions - text / varchar - havy performance  (Bill Moran <wmoran@potentialtech.com>)
Список pgsql-performance
Hi,

I'm using PostgreSQL 7.3.4 and noticed a havy performance issue when
using the datatype text for PL/pgSQL functions instead of varchar.

This is the table:

CREATE TABLE user_login_table (
    id serial,
    username varchar(100),
    PRIMARY ID (id),
    UNIQUE (username)
);

This table contains ~ 500.000 records. The database runs on a P4 with
512 MB RAM. When using the following functions, I notice a havy
speed difference:


CREATE OR REPLACE FUNCTION get_foo_exists (varchar(100))
RETURNS bool
AS '
    BEGIN
        PERFORM username
        FROM    user_login_table
        WHERE   username = $1;

        RETURN FOUND;
    END;
'
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION get_foo_exists2 (text)
RETURNS bool
AS '
    BEGIN
        PERFORM username
        FROM    user_login_table
        WHERE   username = $1;

        RETURN FOUND;
    END;
'
LANGUAGE 'plpgsql';



The function 'get_foo_exists (varchar(100))' is extremly fast
(can't estimate - < 0.5 seconds). The function 'get_foo_exists2 (text)'
takes about 3 seconds for the same operation.
Is that normal?


Bye,
Oliver


В списке pgsql-performance по дате отправления:

Предыдущее
От: Jeff
Дата:
Сообщение: Re: bad estimates
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Indexing question