pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bill Moran <wmoran@potentialtech.com>
To: Oliver Siegmar <o.siegmar@vitrado.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: PL/pgSQL functions - text / varchar - havy performance
Date: Fri, 29 Aug 2003 10:46:44 -0400
Message-ID: <3F4F6754.8050900@potentialtech.com> (raw)
In-Reply-To: <200308291554.46748.o.siegmar@vitrado.de>
References: <200308291554.46748.o.siegmar@vitrado.de>

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




view thread (7+ messages)  latest in thread

Message-ID: <3F4F6754.8050900@potentialtech.com>
Permalink:  ../3F4F6754.8050900@potentialtech.com/
Also on:    postgresql.org/message-id/3F4F6754.8050900@potentialtech.com

 · 

reply

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: wmoran@potentialtech.com, o.siegmar@vitrado.de
  Subject: Re: PL/pgSQL functions - text / varchar - havy performance
  In-Reply-To: <3F4F6754.8050900@potentialtech.com>
  References: <200308291554.46748.o.siegmar@vitrado.de> <3F4F6754.8050900@potentialtech.com>

* 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