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