pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgSQL functions - text / varchar - havy performance issue?!
7+ messages / 4 participants
[nested] [flat]
* PL/pgSQL functions - text / varchar - havy performance issue?!
@ 2003-08-29 13:54 Oliver Siegmar <o.siegmar@vitrado.de>
2003-08-29 14:46 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
0 siblings, 1 reply; 7+ messages in thread
From: Oliver Siegmar @ 2003-08-29 13:54 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/pgSQL functions - text / varchar - havy performance
2003-08-29 13:54 PL/pgSQL functions - text / varchar - havy performance issue?! Oliver Siegmar <o.siegmar@vitrado.de>
@ 2003-08-29 14:46 ` Bill Moran <wmoran@potentialtech.com>
2003-08-29 15:01 ` Re: PL/pgSQL functions - text / varchar - havy performance issue?! Oliver Siegmar <o.siegmar@vitrado.de>
2003-08-29 15:01 ` Re: PL/pgSQL functions - text / varchar - havy performance Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 2 replies; 7+ messages in thread
From: Bill Moran @ 2003-08-29 14:46 UTC (permalink / raw)
To: Oliver Siegmar <o.siegmar@vitrado.de>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/pgSQL functions - text / varchar - havy performance issue?!
2003-08-29 13:54 PL/pgSQL functions - text / varchar - havy performance issue?! Oliver Siegmar <o.siegmar@vitrado.de>
2003-08-29 14:46 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
@ 2003-08-29 15:01 ` Oliver Siegmar <o.siegmar@vitrado.de>
1 sibling, 0 replies; 7+ messages in thread
From: Oliver Siegmar @ 2003-08-29 15:01 UTC (permalink / raw)
To: Bill Moran <wmoran@potentialtech.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/pgSQL functions - text / varchar - havy performance
2003-08-29 13:54 PL/pgSQL functions - text / varchar - havy performance issue?! Oliver Siegmar <o.siegmar@vitrado.de>
2003-08-29 14:46 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
@ 2003-08-29 15:01 ` Andrew Sullivan <andrew@libertyrms.info>
2003-08-29 15:34 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
1 sibling, 1 reply; 7+ messages in thread
From: Andrew Sullivan @ 2003-08-29 15:01 UTC (permalink / raw)
To: pgsql-performance
On Fri, Aug 29, 2003 at 10:46:44AM -0400, Bill Moran 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.
It does? According to the docs, varchar is just syntactic sugar for
text. In fact, text and varchar() are supposed to be exactly the
same.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/pgSQL functions - text / varchar - havy performance
2003-08-29 13:54 PL/pgSQL functions - text / varchar - havy performance issue?! Oliver Siegmar <o.siegmar@vitrado.de>
2003-08-29 14:46 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
2003-08-29 15:01 ` Re: PL/pgSQL functions - text / varchar - havy performance Andrew Sullivan <andrew@libertyrms.info>
@ 2003-08-29 15:34 ` Bill Moran <wmoran@potentialtech.com>
2003-08-29 15:54 ` Re: PL/pgSQL functions - text / varchar - havy performance Andrew Sullivan <andrew@libertyrms.info>
0 siblings, 1 reply; 7+ messages in thread
From: Bill Moran @ 2003-08-29 15:34 UTC (permalink / raw)
To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance
Andrew Sullivan wrote:
> On Fri, Aug 29, 2003 at 10:46:44AM -0400, Bill Moran 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.
>
> It does? According to the docs, varchar is just syntactic sugar for
> text. In fact, text and varchar() are supposed to be exactly the
> same.
Really? Well, if I'm wrong, I'm wrong. Wouldn't be the first time.
Have any explanation as to why that function is so slow?
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/pgSQL functions - text / varchar - havy performance
2003-08-29 13:54 PL/pgSQL functions - text / varchar - havy performance issue?! Oliver Siegmar <o.siegmar@vitrado.de>
2003-08-29 14:46 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
2003-08-29 15:01 ` Re: PL/pgSQL functions - text / varchar - havy performance Andrew Sullivan <andrew@libertyrms.info>
2003-08-29 15:34 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
@ 2003-08-29 15:54 ` Andrew Sullivan <andrew@libertyrms.info>
2003-08-29 16:18 ` Re: PL/pgSQL functions - text / varchar - havy performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Sullivan @ 2003-08-29 15:54 UTC (permalink / raw)
To: pgsql-performance
On Fri, Aug 29, 2003 at 11:34:13AM -0400, Bill Moran wrote:
> Have any explanation as to why that function is so slow?
Sorry, no. It might have to do with the planning, though. I believe
the funciton is planned the first time it is run. It may need to be
marked as "STABLE" in order to use any indexes, and that could be
part of the problem.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/pgSQL functions - text / varchar - havy performance
2003-08-29 13:54 PL/pgSQL functions - text / varchar - havy performance issue?! Oliver Siegmar <o.siegmar@vitrado.de>
2003-08-29 14:46 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
2003-08-29 15:01 ` Re: PL/pgSQL functions - text / varchar - havy performance Andrew Sullivan <andrew@libertyrms.info>
2003-08-29 15:34 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
2003-08-29 15:54 ` Re: PL/pgSQL functions - text / varchar - havy performance Andrew Sullivan <andrew@libertyrms.info>
@ 2003-08-29 16:18 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-08-29 16:18 UTC (permalink / raw)
To: Andrew Sullivan <andrew@libertyrms.info>; +Cc: pgsql-performance@postgresql.org, Oliver Siegmar <o.siegmar@vitrado.de>; Bill Moran <wmoran@potentialtech.com>
Andrew Sullivan <andrew@libertyrms.info> writes:
> On Fri, Aug 29, 2003 at 11:34:13AM -0400, Bill Moran wrote:
>> Have any explanation as to why that function is so slow?
> Sorry, no. It might have to do with the planning, though.
Specifically, I'll bet he's getting an indexscan plan with one and not
with the other. It's just ye olde cross-datatype-comparisons-aren't-
indexable problem. "varchar = varchar" matches the index on the varchar
column, but "text = text" is a different operator that doesn't match.
Guess which one gets selected when the initial input is "varchar = text".
7.4 has fixed this particular problem by essentially eliminating the
separate operators for varchar, but in prior releases the behavior
Oliver describes is entirely to be expected. A workaround is to
cast inside the function:
... where varcharcolumn = textarg::varchar;
so that "=" gets interpreted as "varchar = varchar".
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-08-29 16:18 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-29 13:54 PL/pgSQL functions - text / varchar - havy performance issue?! Oliver Siegmar <o.siegmar@vitrado.de>
2003-08-29 14:46 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
2003-08-29 15:01 ` Oliver Siegmar <o.siegmar@vitrado.de>
2003-08-29 15:01 ` Re: PL/pgSQL functions - text / varchar - havy performance Andrew Sullivan <andrew@libertyrms.info>
2003-08-29 15:34 ` Re: PL/pgSQL functions - text / varchar - havy performance Bill Moran <wmoran@potentialtech.com>
2003-08-29 15:54 ` Re: PL/pgSQL functions - text / varchar - havy performance Andrew Sullivan <andrew@libertyrms.info>
2003-08-29 16:18 ` Re: PL/pgSQL functions - text / varchar - havy performance Tom Lane <tgl@sss.pgh.pa.us>
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