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)
