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?
I don't know if it's normal for it to be that slow, but I would expect it to be slower.
Postgres has to convert the text to a varchar before it can actually do anything. It's possible (though I'm not sure) that it has to do the conversion with each record it looks at.
Every language I know of hits performance issues when you have to convert between types. I wouldn't _think_ that it would be that much work converting between text and varchar, but I'm not familiar enough with the server code to know what's actually involved.
What kind of performance do you get if you accept a text value and then manually convert it to a varchar?
i.e.
CREATE OR REPLACE FUNCTION get_foo_exists2 (text)
RETURNS bool
AS '
DECLARE
tempvar VARCHAR(100);
BEGIN
tempvar := $1;
PERFORM username
FROM user_login_table
WHERE username = tempvar; RETURN FOUND;
END;
'
LANGUAGE 'plpgsql';-- Bill Moran Potential Technologies http://www.potentialtech.com
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
