Re: Queries slow from within plpgsql

David Boone <dave(at)iboone(dot)net> wrote:

> 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)

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

>
> dave=# \d zips
> Table "public.zips"
> 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

--
Bill Moran
Potential Technologies
http://www.potentialtech.com