X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id F0C7FD1B55E for ; Thu, 1 Apr 2004 17:40:48 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 04597-03 for ; Thu, 1 Apr 2004 13:40:47 -0400 (AST) Received: from mail.digitro.com.br (unknown [200.247.100.135]) by svr1.postgresql.org (Postfix) with ESMTP id 2C838D1E413 for ; Thu, 1 Apr 2004 13:40:44 -0400 (AST) Received: from serverdgt.daf.digitro.com.br (ias.digitro.com.br [200.247.100.134]) by mail.digitro.com.br (8.12.5/linuxconf) with ESMTP id i31HegEl023072 for ; Thu, 1 Apr 2004 14:40:42 -0300 Received: from dgtat ([192.168.160.169]) by serverdgt.daf.digitro.com.br (8.12.5/linuxconf) with SMTP id i31HdbJP031746 for ; Thu, 1 Apr 2004 14:39:37 -0300 MIME-Version: 1.0 Message-Id: <406C54C7.000001.00680@dgtat> Date: Thu, 1 Apr 2004 14:43:35 -0300 (Hora oficial do Brasil) Content-Type: Multipart/related; type="multipart/alternative"; boundary="------------Boundary-00=_NK6IQL80000000000000" X-Mailer: IncrediMail 2001 (1750690) From: "Eduardo Naschenweng" X-FID: FLAVOR00-NONE-0000-0000-000000000000 X-FVER: X-CNT: ; X-Priority: 3 To: Subject: Does SETOF make queries slower? X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/5 X-Sequence-Number: 12978 --------------Boundary-00=_NK6IQL80000000000000 Content-Type: Multipart/Alternative; boundary="------------Boundary-00=_NK6ILVC0000000000000" --------------Boundary-00=_NK6ILVC0000000000000 Content-Type: Text/Plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0D Greetings,=0D =0D It seems that stored procedures that use SETOF are slower than regular sql= =0D commands. Why does it happens?=0D =0D Please check out the following example.=0D =0D bxs=3D# \d cham_chamada=0D Table "public.cham_chamada"=0D Column | Type | Modifiers=0D -------------------+--------------------------------+-----------=0D dt_inicial | timestamp(0) without time zone | not null=0D cod_bxs | integer | not null=0D chave | integer | not null=0D identificacao | integer | not null=0D identidadea | character varying(25)=0D ....=0D Indexes: xpkcham_chamada primary key btree (dt_inicial, cod_bxs, chave,=0D identificacao),=0D =0D bxs=3D# SELECT COUNT(*) FROM cham_chamada;=0D count=0D --------=0D 392858=0D (1 row)=0D =0D CREATE TYPE rec_teste AS (dt_inicial timestamp(0), identidadea varchar(25))= ;=0D =0D CREATE OR REPLACE FUNCTION teste() RETURNS SETOF rec_teste AS'=0D SELECT dt_inicial, identidadea=0D FROM cham_chamada cc;'=0D LANGUAGE SQL;=0D =0D bxs=3D# EXPLAIN ANALYZE SELECT dt_inicial, identidadea FROM cham_chamada cc= ;=0D QUERY PLAN=0D ---------------------------------------------------------------------------= -=0D ---------------------------------------------------------=0D Seq Scan on cham_chamada cc (cost=3D100000000.00..100011071.72 rows=3D35877= 2=0D width=3D18) (actual time=3D0.29..2887.40 rows=3D392631 loops=3D1)=0D Total runtime: 3092.28 msec=0D (2 rows)=0D =0D bxs=3D# EXPLAIN ANALYZE SELECT * FROM teste();=0D QUERY PLAN=0D ---------------------------------------------------------------------------= -=0D ----------------------------------------=0D Function Scan on teste (cost=3D0.00..12.50 rows=3D1000 width=3D37) (actual= =0D time=3D17527.53..18326.71 rows=3D392631 loops=3D1)=0D Total runtime: 18595.13 msec=0D (2 rows)=0D =0D For the same sql script, execution time is almost 20sec. Compared with=0D execution time of regular sql script, there is a huge difference. I already= =0D tested with other complexity levels (left joins, indexed and sequencial sca= n=0D queries etc) and some times I got 100 times slower when executing inside=0D stored procedure. Could you give me some light?=0D =0D =0D Thanks in advance.=0D =0D Jos=E9 Vilson de Mello de Farias=0D Analista de Sistemas - APC=0D =0D D=CDGITRO TECNOLOGIA=0D E-mail: vilson.farias@digitro.com.br=0D Fone: (0xx48) 281-7158=0D Fax: (0xx48) 281-7000=0D Site: www.digitro.com.br=0D =2E --------------Boundary-00=_NK6ILVC0000000000000 Content-Type: Text/HTML; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0D
 
Greetings,

It seems that stored procedures th= at=20 use SETOF are slower than regular sql
commands. Why does it=20 happens?

Please check out the following example.

bxs=3D= # \d=20 cham_chamada
Table "public.cham_chamada"
Column | Type |=20 Modifiers
-------------------+--------------------------------+---= --------
dt_inicial=20 | timestamp(0) without time zone | not null
cod_bxs | integer | no= t=20 null
chave | integer | not null
identificacao | integer | not= =20 null
identidadea | character varying(25)
....
Indexes:=20 xpkcham_chamada primary key btree (dt_inicial, cod_bxs,=20 chave,
identificacao),

bxs=3D# SELECT COUNT(*) FROM=20 cham_chamada;
count
--------
392858
(1 row)

CREATE= TYPE=20 rec_teste AS (dt_inicial timestamp(0), identidadea=20 varchar(25));

CREATE OR REPLACE FUNCTION teste() RETURNS SETOF= =20 rec_teste AS'
SELECT dt_inicial, identidadea
FROM cham_chamada= =20 cc;'
LANGUAGE SQL;

bxs=3D# EXPLAIN ANALYZE SELECT dt_inicia= l,=20 identidadea FROM cham_chamada cc;
QUERY=20 PLAN
-------------------------------------------------------------= ---------------
--------------------------------------------------------= -
Seq=20 Scan on cham_chamada cc (cost=3D100000000.00..100011071.72=20 rows=3D358772
width=3D18) (actual time=3D0.29..2887.40 rows=3D3926= 31=20 loops=3D1)
Total runtime: 3092.28 msec
(2 rows)

bxs=3D# = EXPLAIN=20 ANALYZE SELECT * FROM teste();
QUERY=20 PLAN
-------------------------------------------------------------= ---------------
----------------------------------------
Function=20 Scan on teste (cost=3D0.00..12.50 rows=3D1000 width=3D37)=20 (actual
time=3D17527.53..18326.71 rows=3D392631 loops=3D1)
Tota= l runtime:=20 18595.13 msec
(2 rows)

For the same sql script, execution t= ime=20 is almost 20sec. Compared with
execution time of regular sql scrip= t,=20 there is a huge difference. I already
tested with other complexity= =20 levels (left joins, indexed and sequencial scan
queries etc) and s= ome=20 times I got 100 times slower when executing inside
stored procedur= e.=20 Could you give me some light?


Thanks in advance.

Jo= s=E9=20 Vilson de Mello de Farias
Analista de Sistemas - APC

D=CDGI= TRO=20 TECNOLOGIA
E-mail: vilson.farias@digitro.co= m.br
Fone:=20 (0xx48) 281-7158
Fax: (0xx48) 281-7000
Site: www.digitro.com.br
.
____________________________________________________
  IncrediMail - Email has finally evolved= -=20
Click=20 Here
--------------Boundary-00=_NK6ILVC0000000000000-- --------------Boundary-00=_NK6IQL80000000000000 Content-Type: image/gif Content-Transfer-Encoding: base64 Content-ID: R0lGODlhFAAPALMIAP9gAM9gAM8vAM9gL/+QL5AvAGAvAP9gL////wAAAAAA AAAAAAAAAAAAAAAAAAAAACH/C05FVFNDQVBFMi4wAwEAAAAh+QQJFAAIACwA AAAAFAAPAAAEVRDJSaudJuudrxlEKI6BURlCUYyjKpgYAKSgOBSCDEuGDKgr AtC3Q/R+hkPJEDgYCjpKr5A8WK9OaPFZwHoPqm3366VKyeRtE30tVVRscMHD qV/u+AgAIfkEBWQACAAsAAAAABQADwAABBIQyUmrvTjrzbv/YCiOZGmeaAQA IfkECRQACAAsAgABABAADQAABEoQIUOrpXIOwrsPxiQUheeRAgUA49YNhbCq K1kS9grQhXGAhsDBUJgZAL2Dcqkk7ogFpvRAokSn0p4PO6UIuUsQggSmFjKX dAgRAQAh+QQFCgAIACwAAAAAFAAPAAAEEhDJSau9OOvNu/9gKI5kaZ5oBAAh +QQJFAAIACwCAAEAEAANAAAEShAhQ6ulcg7Cuw/GJBSF55ECBQDj1g2FsKor WRL2CtCFcYCGwMFQmBkAvYNyqSTuiAWm9ECiRKfSng87pQi5SxCCBKYWMpd0 CBEBACH5BAVkAAgALAAAAAAUAA8AAAQSEMlJq7046827/2AojmRpnmgEADs= --------------Boundary-00=_NK6IQL80000000000000--