Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

Next:From: Tom LaneDate: 2003-08-29 14:00:14
Subject: Re: Indexing question
Previous:From: JeffDate: 2003-08-29 12:52:34
Subject: Re: bad estimates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group