From: Alban Medici (NetCentrex) <amedici@fr.netcentrex.net>
To: pgsql-performance@postgresql.org
Subject: Re: Queries slow using stored procedures
Date: Tue, 19 Oct 2004 09:25:08 +0200
Message-ID: <20041019072518.D191332A06D@svr1.postgresql.org> (raw)
In-Reply-To: <E1CJbnc-0003pu-00@shenley.e-rm.co.uk>
You seem to not have index on botnumber, but in your query bot number is
the clause.
I don't explain you why the same query is so long.
but have your try procedure with a loop structure (witch create cursor) ?
you could try
CREATE OR REPLACE FUNCTION sp_test_Alban1 ( ) returns integer
as '
DECLARE
bot char(10);
oldbatch INTEGER;
rec RECORD;
query VARCHAR;
BEGIN
-- initialisation
bot := ''1-7'';
query := '' SELECT batchserial FROM transbatch WHERE botnumber = ' ||
quote_ident(bot) || '' <optionaly your limit clause> ;'';
FOR rec IN EXECUTE var_query LOOP
return rec."batchserial ".;
END LOOP;
--else
return 0;
END;
'
language plpgsql ;
does it return the same results in the same time ?
_____
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Rod Dutton
Sent: lundi 18 octobre 2004 20:01
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Queries slow using stored procedures
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: amedici@fr.netcentrex.net
Subject: Re: Queries slow using stored procedures
In-Reply-To: <20041019072518.D191332A06D@svr1.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox