Queries slow using stored procedures

From: "Rod Dutton" <rod(at)e-rm(dot)co(dot)uk>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Queries slow using stored procedures
Date: 2004-10-18 18:01:25
Message-ID: E1CJbnc-0003pu-00@shenley.e-rm.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I have a problem where a query inside a function is up to 100 times slower
inside a function than as a stand alone query run in psql.

The column 'botnumber' is a character(10), is indexed and there are 125000
rows in the table.

Help please!

This query is fast:-

explain analyze
SELECT batchserial
FROM transbatch
WHERE botnumber = '1-7'
LIMIT 1;

QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------
Limit (cost=0.00..0.42 rows=1 width=4) (actual time=0.73..148.23 rows=1
loops=1)
-> Index Scan using ind_tbatchx on transbatch (cost=0.00..18.73 rows=45
width=4) (actual time=0.73..148.22 rows=1 loops=1)
Index Cond: (botnumber = '1-7'::bpchar)
Total runtime: 148.29 msec
(4 rows)


This function is slow:-

CREATE OR REPLACE FUNCTION sp_test_rod3 ( ) returns integer
as '
DECLARE
bot char(10);
oldbatch INTEGER;
BEGIN

bot := ''1-7'';

SELECT INTO oldbatch batchserial
FROM transbatch
WHERE botnumber = bot
LIMIT 1;

IF FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;

END;
'
language plpgsql ;


explain analyze SELECT sp_test_rod3();

QUERY PLAN

----------------------------------------------------------------------------
------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=1452.39..1452.40
rows=1 loops=1)
Total runtime: 1452.42 msec
(2 rows)

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Joshua D. Drake 2004-10-18 18:11:27 Re: How to time several queries?
Previous Message Mark Wong 2004-10-18 15:28:10 Re: mmap (was First set of OSDL Shared Mem scalability results, some wierdness ...