Does SETOF make queries slower? - Mailing list pgsql-admin
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 . | |||
Attachment
pgsql-admin by date: