From: Oliver Siegmar <o.siegmar@vitrado.de>
To: pgsql-performance@postgresql.org
Subject: PL/pgSQL functions - text / varchar - havy performance issue?!
Date: Fri, 29 Aug 2003 15:54:46 +0200
Message-ID: <200308291554.46748.o.siegmar@vitrado.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: o.siegmar@vitrado.de
Subject: Re: PL/pgSQL functions - text / varchar - havy performance issue?!
In-Reply-To: <200308291554.46748.o.siegmar@vitrado.de>
References: <200308291554.46748.o.siegmar@vitrado.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox