[PERFORM] PL/pgSQL functions - text / varchar - havy performance issue?!

[Oliver Siegmar]

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
FROMuser_login_table
WHERE   username = $1;

RETURN FOUND;
END;
'
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION get_foo_exists2 (text)
RETURNS bool
AS '
BEGIN
PERFORM username
FROMuser_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


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] PL/pgSQL functions - text / varchar - havy performance issue?!

[Oliver Siegmar]

Hi Bill,

On Friday 29 August 2003 16:46, you wrote:
 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.

Nope. I tested you function with the temporary varchar variable...it
is as slow as the 'text-only' varayity.

 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.

I have absolutely no idea how pgsql handles text/varchar stuff
in its server code. But ~ 3 seconds for that small function is ways
to slow in any case.


Bye,
Oliver


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])