X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3CF2BD1B8B1 for ; Fri, 29 Aug 2003 13:55:32 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 24437-06 for ; Fri, 29 Aug 2003 10:55:04 -0300 (ADT) Received: from router.intranet.de (morpheus.vitrado.de [194.97.5.114]) by svr1.postgresql.org (Postfix) with ESMTP id 85D07D1B8AD for ; Fri, 29 Aug 2003 10:54:59 -0300 (ADT) Received: from 192.168.1.50 ([192.168.1.50]) by router.intranet.de (8.11.6/8.11.6/SuSE Linux 0.5) with ESMTP id h7TDsMY19092 for ; Fri, 29 Aug 2003 15:54:24 +0200 From: Oliver Siegmar Organization: Vitrado GmbH To: pgsql-performance@postgresql.org Subject: PL/pgSQL functions - text / varchar - havy performance issue?! Date: Fri, 29 Aug 2003 15:54:46 +0200 User-Agent: KMail/1.5.3 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200308291554.46748.o.siegmar@vitrado.de> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/490 X-Sequence-Number: 3306 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