Re: Queries slow from within plpgsql

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)

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)

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

David Boone <dave(at)iboone(dot)net> writes:
> 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.

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

Question:

Would this problem exist if zip were defined as varchar with no specific
length defined? Is there a difference between varchar and text, at least in
the context of this discussion?

Thanks...

On Saturday 05 June 2004 12:36 am, Tom Lane saith:
> David Boone <dave(at)iboone(dot)net> writes:
> > 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.
>
> 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(at)postgresql(dot)org

--
Quote: 32
"The world we have created is a product of our thinking. It cannot be
changed without changing our thinking."

--Albert Einstein

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry(at)esc1(dot)com

Terry Lee Tucker <terry(at)esc1(dot)com> writes:
> Would this problem exist if zip were defined as varchar with no specific
> length defined? Is there a difference between varchar and text, at least in
> the context of this discussion?

In 7.4 there is not, but in prior releases there was.

regards, tom lane