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 3351132A0E7 for ; Tue, 19 Oct 2004 08:25:34 +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 82887-04 for ; Tue, 19 Oct 2004 07:25:23 +0000 (GMT) Received: from mailer.unicite.fr.netcentrex.net (unknown [62.161.167.249]) by svr1.postgresql.org (Postfix) with ESMTP id D191332A06D for ; Tue, 19 Oct 2004 08:25:18 +0100 (BST) Received: from akira ([192.168.101.228]) by mailer.unicite.fr.netcentrex.net with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id 42CYALCL; Tue, 19 Oct 2004 09:25:17 +0200 From: "Alban Medici (NetCentrex)" To: Subject: Re: Queries slow using stored procedures Date: Tue, 19 Oct 2004 09:25:08 +0200 Organization: NetCentrex MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_007C_01C4B5BD.8C390510" X-Mailer: Microsoft Office Outlook, Build 11.0.6353 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcS1PHuwz718w8JtTi2M+MPIPiou1gAbm7fg In-Reply-To: Message-Id: <20041019072518.D191332A06D@svr1.postgresql.org> 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/271 X-Sequence-Number: 8747 This is a multi-part message in MIME format. ------=_NextPart_000_007C_01C4B5BD.8C390510 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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) || '' ;''; 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) ------=_NextPart_000_007C_01C4B5BD.8C390510 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
You seem to not have index on botnumber,  b= ut in=20 your query bot number is the clause.
 
I don't explain you why the same query is so=20 long.
but have your try procedure with a loop structur= e=20 (witch create cursor) ?
 
you could try
 
 
CR= EATE OR=20 REPLACE FUNCTION  sp_test_Alban1 ( )=20 returns integer         
a= s=20 '
DECLARE
  bot char(10);
  oldbatch=20 INTEGER;
  rec=20 RECORD;
=   query=20 VARCHAR;
=
BEGIN
 
  -- initialisation
  bot :=3D ''1-7'';
  query  :=3D ''= =20 SELECT  batchserial FROM transbatch WHERE=20 botnumber  =3D ' || quote_ident(bot) || '' <optionaly your limit=20 clause> ;'';
 
 
   FOR rec = IN=20 EXECUTE var_query  LOOP
        = return=20 rec."batchserial ".;
    
   END=20 LOOP;
    <= /DIV>
   =20 --else
    return=20 0;
 
END;
'
language plpgsql =20 ;
does it return the same results in the same time= =20 ? 


From: pgsql-performance-owner@postgresq= l.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Rod=20 Dutton
Sent: lundi 18 octobre 2004 20:01
To:=20 pgsql-performance@postgresql.org
Subject: [PERFORM] Queries slow = using=20 stored procedures

Hi,
 
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_007C_01C4B5BD.8C390510--