Postgres Pro
Downloads
Home   >   mailing lists

Thread: Does SETOF make queries slower?

Does SETOF make queries slower?

From
"Eduardo Naschenweng"
Date:
 
Greetings,

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

Please check out the following example.

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

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

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

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

bxs=# EXPLAIN ANALYZE SELECT dt_inicial, identidadea FROM cham_chamada cc;
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------------------------
Seq Scan on cham_chamada cc (cost=100000000.00..100011071.72 rows=358772
width=18) (actual time=0.29..2887.40 rows=392631 loops=1)
Total runtime: 3092.28 msec
(2 rows)

bxs=# EXPLAIN ANALYZE SELECT * FROM teste();
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------
Function Scan on teste (cost=0.00..12.50 rows=1000 width=37) (actual
time=17527.53..18326.71 rows=392631 loops=1)
Total runtime: 18595.13 msec
(2 rows)

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


Thanks in advance.

José Vilson de Mello de Farias
Analista de Sistemas - APC

DÍGITRO TECNOLOGIA
E-mail: vilson.farias@digitro.com.br
Fone: (0xx48) 281-7158
Fax: (0xx48) 281-7000
Site: www.digitro.com.br
.
____________________________________________________
  IncrediMail - Email has finally evolved - Click Here
Attachment

Re: Does SETOF make queries slower?

From
Tom Lane
Date:
"Eduardo Naschenweng" <eduardo.naschenweng@digitro.com.br> writes:
> bxs=3D# EXPLAIN ANALYZE SELECT dt_inicial, identidadea FROM cham_chamada cc=
> ;=0D
> [ is faster than ]
> bxs=3D# EXPLAIN ANALYZE SELECT * FROM teste();=0D

nodeFunctionscan.c insists on cramming the results of the function into
a tuplestore and then reading them back.  This is fairly expensive for a
large result set.  I complained about this back when the implementation
was first proposed, but we set the problem aside for the time being, and
it hasn't been revisited.  One reason for it is that a function is by
nature not very transparent, so it's hard to tell whether the storage
overhead is necessary or not.

            regards, tom lane