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
|
Список | 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 по дате отправления: