Queries slow from within plpgsql

From: David Boone <dave(at)iboone(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Queries slow from within plpgsql
Date: 2004-06-04 22:46:15
Message-ID: FC869B24-B678-11D8-915F-000A95A566E4@iboone.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Ochs 2004-06-04 22:52:23 Re: dynamic function question
Previous Message Thomas Hallgren 2004-06-04 22:31:18 Unable to use NNTP server