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

Search Discussions

  • Bill Moran at Aug 29, 2003 at 2:47 pm

    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
  • Andrew Sullivan at Aug 29, 2003 at 3:02 pm

    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
  • Bill Moran at Aug 29, 2003 at 3:34 pm

    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
  • Andrew Sullivan at Aug 29, 2003 at 3:54 pm

    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
  • Tom Lane at Aug 29, 2003 at 4:18 pm

    Andrew Sullivan 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
  • Oliver Siegmar at Aug 29, 2003 at 3:02 pm
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedAug 29, '03 at 1:55p
activeAug 29, '03 at 4:18p
posts7
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase