Skip site navigation (1) Skip section navigation (2)

Re: function executes sql 100 times longer it should

From: "Sean Brown" <sbrown(at)eaglepress(dot)com>
To: "Julius Tuskenis" <julius(at)nsoft(dot)lt>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: function executes sql 100 times longer it should
Date: 2008-11-12 14:30:47
Message-ID: 1226500247.2221.2.camel@lin-it1.eaglepress.local (view raw or flat)
Thread:
Lists: pgsql-admin
On Wed, 2008-11-12 at 16:10 +0200, Julius Tuskenis wrote:
> Hello, list.
> 
> I have one simple SQL function returning result set that takes around 3 
> seconds to execute. But if I execute the Select it executes directly - 
> it takes only around 30 ms. Why so big difference?
Probably caching the results.

>  What should I check?
Explain plan might be a good start

> I must also say, that this started this afternoon.


> 
> PG:  8.3.3
> OS:  Windows Server 2003
> 
> Example below:
> //========================================================================================================
> CREATE OR REPLACE FUNCTION fnk_grupes_prekes(prm_pardavimo_taskas 
> integer, prm_grupe integer, prm_filtras character varying)
>   RETURNS SETOF frt_grupes_prekes AS
> $BODY$SELECT DISTINCT prek_id, prek_pavadinimas, ks_kaina, mvnt_trumpinys
> FROM  filter_b_preke_matoma()
>  LEFT OUTER JOIN b_grupes_prekes ON (gp_preke=prek_id)
>  LEFT OUTER JOIN b_grupe ON (gp_grupe=grup_id)
>  JOIN b_mato_vienetai ON (mvnt_id=prek_matovnt)
>  JOIN b_kainorascio_sudetis ON (ks_preke = prek_id)
>  JOIN b_kainorascio_grafikas ON (ks_kainorastis=kag_kainorastis)
>  JOIN b_pardavimo_tasko_kainorastis ON (ks_kainorastis=ptk_kainorastis)
> WHERE (grup_id=$2 OR $2 is Null)
>   AND ptk_pardavimotaskas=$1
>   AND ((prek_pavadinimas ILIKE ('%'||$3||'%')) OR $3 is NULL)
>   AND fnk_grafikas_galioja(kag_grafikas) = true
> ORDER BY prek_pavadinimas
> $BODY$
>   LANGUAGE 'sql' VOLATILE
>   COST 100
>   ROWS 1000;
> ALTER FUNCTION fnk_grupes_prekes(integer, integer, character varying) 
> OWNER TO postgres;
> GRANT EXECUTE ON FUNCTION fnk_grupes_prekes(integer, integer, character 
> varying) TO postgres;
> GRANT EXECUTE ON FUNCTION fnk_grupes_prekes(integer, integer, character 
> varying) TO public;
> 
> 
> select  *  from fnk_grupes_prekes(18,42,NULL);
> Total query runtime: 2172 ms.
> 0 rows retrieved.
> 
> 
> SELECT DISTINCT prek_id, prek_pavadinimas, ks_kaina, mvnt_trumpinys
> FROM  filter_b_preke_matoma()
>  LEFT OUTER JOIN b_grupes_prekes ON (gp_preke=prek_id)
>  LEFT OUTER JOIN b_grupe ON (gp_grupe=grup_id)
>  JOIN b_mato_vienetai ON (mvnt_id=prek_matovnt)
>  JOIN b_kainorascio_sudetis ON (ks_preke = prek_id)
>  JOIN b_kainorascio_grafikas ON (ks_kainorastis=kag_kainorastis)
>  JOIN b_pardavimo_tasko_kainorastis ON (ks_kainorastis=ptk_kainorastis)
> WHERE (grup_id=42 OR 42 is Null)
>   AND ptk_pardavimotaskas=18
>   AND ((prek_pavadinimas ILIKE ('%'||null||'%')) OR null is NULL)
>   AND fnk_grafikas_galioja(kag_grafikas) = true
> ORDER BY prek_pavadinimas;
> 
> Total query runtime: 47 ms.
> 0 rows retrieved.
> 
First run probably put the plan and results into the query cache. The
second run could just pull it from there instead of going to the tables
on the disk.

> -- 
> 
> Julius Tuskenis
> Programavimo skyriaus vadovas
> UAB nSoft
> mob. +37068233050
> 

In response to

Responses

pgsql-admin by date

Next:From: paulo matadrDate: 2008-11-12 16:05:19
Subject: MAX_CONNECTIONS ??
Previous:From: Julius TuskenisDate: 2008-11-12 14:10:58
Subject: function executes sql 100 times longer it should

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group