MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-tBmITM3Mt7Vq79iDVfhH"
Content-class: urn:content-classes:message
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>
In-Reply-To: <491AE3F2.601@nsoft.lt>
Thread-Topic: [ADMIN] function executes sql 100 times longer it should
Thread-Index: AclE01WiFNEoyWEdSFikIp1dkLU3BQ==
References: <491AE3F2.601@nsoft.lt>
From: "Sean Brown" <sbrown@eaglepress.com>
To: "Julius Tuskenis" <julius@nsoft.lt>
Cc: <pgsql-admin@postgresql.org>


--=-tBmITM3Mt7Vq79iDVfhH
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable


On Wed, 2008-11-12 at 16:10 +0200, Julius Tuskenis wrote:
> Hello, list.
>=20
> I have one simple SQL function returning result set that takes around 3=20
> seconds to execute. But if I execute the Select it executes directly -=20
> 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.


>=20
> PG:  8.3.3
> OS:  Windows Server 2003
>=20
> Example below:
> //=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D
> CREATE OR REPLACE FUNCTION fnk_grupes_prekes(prm_pardavimo_taskas=20
> 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=3Dprek_id)
>  LEFT OUTER JOIN b_grupe ON (gp_grupe=3Dgrup_id)
>  JOIN b_mato_vienetai ON (mvnt_id=3Dprek_matovnt)
>  JOIN b_kainorascio_sudetis ON (ks_preke =3D prek_id)
>  JOIN b_kainorascio_grafikas ON (ks_kainorastis=3Dkag_kainorastis)
>  JOIN b_pardavimo_tasko_kainorastis ON (ks_kainorastis=3Dptk_kainorastis)
> WHERE (grup_id=3D$2 OR $2 is Null)
>   AND ptk_pardavimotaskas=3D$1
>   AND ((prek_pavadinimas ILIKE ('%'||$3||'%')) OR $3 is NULL)
>   AND fnk_grafikas_galioja(kag_grafikas) =3D true
> ORDER BY prek_pavadinimas
> $BODY$
>   LANGUAGE 'sql' VOLATILE
>   COST 100
>   ROWS 1000;
> ALTER FUNCTION fnk_grupes_prekes(integer, integer, character varying)=20
> OWNER TO postgres;
> GRANT EXECUTE ON FUNCTION fnk_grupes_prekes(integer, integer, character=20
> varying) TO postgres;
> GRANT EXECUTE ON FUNCTION fnk_grupes_prekes(integer, integer, character=20
> varying) TO public;
>=20
>=20
> select  *  from fnk_grupes_prekes(18,42,NULL);
> Total query runtime: 2172 ms.
> 0 rows retrieved.
>=20
>=20
> SELECT DISTINCT prek_id, prek_pavadinimas, ks_kaina, mvnt_trumpinys
> FROM  filter_b_preke_matoma()
>  LEFT OUTER JOIN b_grupes_prekes ON (gp_preke=3Dprek_id)
>  LEFT OUTER JOIN b_grupe ON (gp_grupe=3Dgrup_id)
>  JOIN b_mato_vienetai ON (mvnt_id=3Dprek_matovnt)
>  JOIN b_kainorascio_sudetis ON (ks_preke =3D prek_id)
>  JOIN b_kainorascio_grafikas ON (ks_kainorastis=3Dkag_kainorastis)
>  JOIN b_pardavimo_tasko_kainorastis ON (ks_kainorastis=3Dptk_kainorastis)
> WHERE (grup_id=3D42 OR 42 is Null)
>   AND ptk_pardavimotaskas=3D18
>   AND ((prek_pavadinimas ILIKE ('%'||null||'%')) OR null is NULL)
>   AND fnk_grafikas_galioja(kag_grafikas) =3D true
> ORDER BY prek_pavadinimas;
>=20
> Total query runtime: 47 ms.
> 0 rows retrieved.
>=20
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.

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


--=-tBmITM3Mt7Vq79iDVfhH
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQBJGuiXHADH59cE9zIRAmq8AJ9lIy1dJVh/lL3YMYyzVjn/a/cCTwCfY5s9
17db47I2RF7Tcd9QSCe1cJU=
=DDX5
-----END PGP SIGNATURE-----

--=-tBmITM3Mt7Vq79iDVfhH--