X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B1CEA3A3F85 for ; Sun, 24 Oct 2004 19:12:00 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88806-05 for ; Sun, 24 Oct 2004 18:11:30 +0000 (GMT) Received: from mra04.ex.eclipse.net.uk (mra04.ex.eclipse.net.uk [212.104.129.139]) by svr1.postgresql.org (Postfix) with ESMTP id B58123A3F9E for ; Sun, 24 Oct 2004 19:11:49 +0100 (BST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mra04.ex.eclipse.net.uk (Postfix) with ESMTP id 53475133D81 for ; Sun, 24 Oct 2004 19:06:42 +0100 (BST) Received: from mra04.ex.eclipse.net.uk ([127.0.0.1]) by localhost (mra04.ex.eclipse.net.uk [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 20604-01-13 for ; Sun, 24 Oct 2004 19:06:41 +0100 (BST) Received: from dev1 (unknown [82.152.145.238]) by mra04.ex.eclipse.net.uk (Postfix) with ESMTP id F2D16133C61 for ; Sun, 24 Oct 2004 19:06:40 +0100 (BST) From: "Rod Dutton" To: Subject: Queries slow using stored procedures Date: Sun, 24 Oct 2004 19:13:23 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0000_01C4B9FD.887BB690" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcS1PHuwz718w8JtTi2M+MPIPiou1gEuIX0Q X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Message-Id: <20041024180640.F2D16133C61@mra04.ex.eclipse.net.uk> X-Virus-Scanned: by Eclipse VIRUSshield at eclipse.net.uk X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=HTML_50_60, HTML_FONTCOLOR_BLUE, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200410/395 X-Sequence-Number: 8871 This is a multi-part message in MIME format. ------=_NextPart_000_0000_01C4B9FD.887BB690 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, Has anybody got any ideas on my recent posting ? (thanks in advance) :- 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) ------=_NextPart_000_0000_01C4B9FD.887BB690 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
 
Hi,=  
 
Has anybody got any ideas = on my=20 recent posting ? (thanks in advance) :-<= /DIV>
 
 
I have a = problem=20 where a query inside a function is up to 100 times slower inside a function= than=20 as a stand alone query run in psql.
 
The colum= n=20 'botnumber' is a character(10), is indexed and there are 125000 rows i= n the=20 table.
 
Help=20 please!
 
This quer= y is=20 fast:-
 
explain= =20 analyze  
&nb= sp;=20 SELECT batchserial
  FROM transbatch
  WHERE botnumb= er =3D=20 '1-7'
  LIMIT 1;
           =             &nb= sp;            =             &nb= sp;         =20 QUERY=20 PLAN            = ;            &n= bsp;            = ;            &n= bsp;        =20
-----------------------------------------------------------------------= ---------------------------------------------------------
 Limit&nb= sp;=20 (cost=3D0.00..0.42 rows=3D1 width=3D4) (actual time=3D0.73..148.23 rows=3D1= =20 loops=3D1)
   ->  Index Scan using ind_tbatchx on=20 transbatch  (cost=3D0.00..18.73 rows=3D45 width=3D4) (actual time=3D0.= 73..148.22=20 rows=3D1 loops=3D1)
         Ind= ex Cond:=20 (botnumber =3D '1-7'::bpchar)
 Total runtime: 148.29 msec
(4=20 rows)
 
 
This=20 function is slow:-
 
CREATE=20 OR REPLACE FUNCTION  sp_test_rod3 ( ) returns=20 integer         
as=20 '
DECLARE
  bot char(10);
  oldbatch=20 INTEGER;
BEGIN
 
 =20 bot :=3D ''1-7'';
 
 =20 SELECT INTO oldbatch batchserial
  FROM transbatch
  WHERE= =20 botnumber =3D bot
  LIMIT 1;
 
 =20 IF FOUND THEN
    RETURN 1;
 =20 ELSE
    RETURN 0;
  END=20 IF;
 
END;
'
language plpgsql  ;
 
explain analyze SELECT sp_test_rod3();
           =             &nb= sp;            =   =20 QUERY=20 PLAN            = ;            &n= bsp;            = ; =20
-----------------------------------------------------------------------= -----------------
 Result =20 (cost=3D0.00..0.01 rows=3D1 width=3D0) (actual time=3D1452.39..1452.40 rows= =3D1=20 loops=3D1)
 Total runtime: 1452.42 msec
(2=20 rows)
------=_NextPart_000_0000_01C4B9FD.887BB690--