Queries slow from within plpgsql

[David Boone]

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)
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bill Moran]

David Boone <da**@iboone.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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

David Boone <da**@iboone.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 ma*******@postgresql.org