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 77757D1B8AC for ; Fri, 29 Aug 2003 14:47:27 +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 27003-10 for ; Fri, 29 Aug 2003 11:46:59 -0300 (ADT) Received: from mta4.adelphia.net (mta4.adelphia.net [68.168.78.184]) by svr1.postgresql.org (Postfix) with ESMTP id 159BBD1B8A4 for ; Fri, 29 Aug 2003 11:46:40 -0300 (ADT) Received: from potentialtech.com ([24.53.179.151]) by mta4.adelphia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030829144645.BXAT10404.mta4.adelphia.net@potentialtech.com>; Fri, 29 Aug 2003 10:46:45 -0400 Message-ID: <3F4F6754.8050900@potentialtech.com> Date: Fri, 29 Aug 2003 10:46:44 -0400 From: Bill Moran User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.3) Gecko/20030429 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Oliver Siegmar Cc: pgsql-performance@postgresql.org Subject: Re: PL/pgSQL functions - text / varchar - havy performance References: <200308291554.46748.o.siegmar@vitrado.de> In-Reply-To: <200308291554.46748.o.siegmar@vitrado.de> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/494 X-Sequence-Number: 3310 Oliver Siegmar wrote: > 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