From: | Oliver Siegmar <o(dot)siegmar(at)vitrado(dot)de> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | PL/pgSQL functions - text / varchar - havy performance issue?! |
Date: | 2003-08-29 13:54:46 |
Message-ID: | 200308291554.46748.o.siegmar@vitrado.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-08-29 14:00:14 | Re: Indexing question |
Previous Message | Jeff | 2003-08-29 12:52:34 | Re: bad estimates |