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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match