From: Sean Brown <sbrown@eaglepress.com>
To: Julius Tuskenis <julius@nsoft.lt>
Cc: pgsql-admin@postgresql.org
Subject: Re: function executes sql 100 times longer it should
Date: Wed, 12 Nov 2008 09:30:47 -0500
Message-ID: <1226500247.2221.2.camel@lin-it1.eaglepress.local> (raw)
In-Reply-To: <491AE3F2.601@nsoft.lt>
References: <491AE3F2.601@nsoft.lt>
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
>
Attachments:
signature.asc (application/pgp-signature, 188B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sbrown@eaglepress.com, julius@nsoft.lt
Subject: Re: function executes sql 100 times longer it should
In-Reply-To: <1226500247.2221.2.camel@lin-it1.eaglepress.local>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox