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

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

Responses

Browse pgsql-performance by date

  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