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

function executes sql 100 times longer it should

From: Julius Tuskenis <julius(at)nsoft(dot)lt>
To: pgsql-admin(at)postgresql(dot)org
Subject: function executes sql 100 times longer it should
Date: 2008-11-12 14:10:58
Message-ID: 491AE3F2.601@nsoft.lt (view raw or flat)
Thread:
Lists: pgsql-admin
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? What should I check?
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.

-- 

Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


Responses

pgsql-admin by date

Next:From: Sean BrownDate: 2008-11-12 14:30:47
Subject: Re: function executes sql 100 times longer it should
Previous:From: Scott MarloweDate: 2008-11-11 19:12:55
Subject: Re: Ideal way to upgrade to postgres 8.3 with less downtime

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