Queries slow from within plpgsql

**Bill Moran** · Nov 23 '05, 01:05 AM

Re: Queries slow from within plpgsql

David Boone <dave@iboone.ne t> wrote:
[color=blue]
> I've been trying to create functions with postgres, but it seems that
> queries run within a function take wayyy too long to complete. The
> increased time seems to be in the actual queries, not function call
> overhead or something, but I can't for the life of me figure out why
> it's slower like this. I've simplified it to what you see below.
>
> Any insight would be *much* appreciated. Thanks!
> - Dave
>
>
> dave=# SELECT * FROM testfunc('V2P 6H3');
> testfunc
> ----------
> 1
> (1 row)
>
> Time: 1120.634 ms
> dave=# SELECT * FROM zips WHERE zip='V2P 6H3';
> city | state | zip | areacode |
> county | time_zone | dst | country | latitude | longitude
> | zip_type | fips
> -----------------------------------+-------+---------+----------
> +---------------------------+-----------+-----+---------+----------
> +-----------+----------+-------
> Chilliwack | BC | V2P 6H3 | 604 |
> | PST | Y | C | 49.1757 | 121.9301
> | |
> (1 row)
>
> Time: 0.895 ms
> dave=# SELECT * FROM testfunc('V2P 6H3');
> testfunc
> ----------
> 1
> (1 row)
>
> Time: 1287.793 ms
> dave=# \df+ testfunc
>
> List of functions
> Result data type | Schema | Name | Argument data types | Owner |
> Language | Source code
> | Description
> ------------------+--------+----------+---------------------+-------
> +----------
> +-----------------------------------------------------------------------
> ---------------+-------------
> integer | public | testfunc | text | dave |
> plpgsql | DECLARE zip1 RECORD; BEGIN SELECT INTO zip1 * FROM zips
> WHERE zip=$1; RETURN 1; END; |
> (1 row)[/color]

This function is not very well optimized ... it doesn't even seem to work
correctly.

Why not just create an SQL function that has the SQL you need in it?

Why? Because of a few things I've learned in my own function writing:
1) plpgsql is slower than stored SQL
2) When you call SQL in plpgsql, you invoke overhead of the SQL parser in
addition to the plpgsql parser. If all you're doing is calling SQL,
this is a waste.
3) Try declaring zip1 zips%ROWTYPE ... I think that will speed things up
as well.

See what performance you get with:

CREATE FUNCTION testfunc2(TEXT)
RETURNS zips
AS '
SELECT * FROM zips WHERE zip = $1;
' LANGUAGE SQL;

HTH
[color=blue]
>
> dave=# \d zips
> Table "public.zip s"
> Column | Type | Modifiers
> -----------+---------------+-----------------------------
> city | character(33) |
> state | character(2) |
> zip | character(7) | not null default ''::bpchar
> areacode | character(3) |
> county | character(25) |
> time_zone | character(5) |
> dst | character(1) |
> country | character(1) |
> latitude | numeric(6,4) |
> longitude | numeric(7,4) |
> zip_type | character(1) |
> fips | character(5) |
> Indexes:
> "zip_idx" btree (zip)
>
> dave=# select version();
> version
> ------------------------------------------------------------------------
> --------------------------------------------------
> PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
> 20031218 (Gentoo Linux 3.3.2-r5, propolice-3.3-7)
> (1 row)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org[/color]

--
Bill Moran
Potential Technologies

http://www.potentialtech.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Tom Lane** · Nov 23 '05, 01:05 AM

Re: Queries slow from within plpgsql

David Boone <dave@iboone.ne t> writes:[color=blue]
> I've been trying to create functions with postgres, but it seems that
> queries run within a function take wayyy too long to complete. The
> increased time seems to be in the actual queries, not function call
> overhead or something, but I can't for the life of me figure out why
> it's slower like this.[/color]

The problem here looks to be that you've declared the function parameter
as "text" while the table column is "char(7)". When you write
select ... where zip = 'V2P 6H3';
the unadorned literal is taken to be char(7) to match the compared-to
column, but in the function case the datatype of $1 is predetermined,
and so
select ... where zip = $1;
involves a cross-data-type-comparison ... which is non-indexable
in current releases. (There's a fix in place for 7.5.) Either
change the declared type of the function parameter, or put a cast
into the body of the function.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

Queries slow from within plpgsql

Queries slow from within plpgsql

Comment

Comment