Re: function executes sql 100 times longer it should

["Vyacheslav Kalinin" <vka@xxxxxxxx>]

Most likely, you get different plans because the function has to deal with the parametrized query, hence planner cannot effectively use statistics. For example conditions like this
WHERE (grup_id=$2 OR $2 is Null)
would prevent planner from use of index (if there is one), while with literal query they would be simplified to just
WHERE grup_id=42.

On Thu, Nov 13, 2008 at 11:16 AM, Julius Tuskenis <julius@xxxxxxxx> wrote:

Its a good start, but leads to nothing because Explain doesn't go into the function.

It does, after a bit of street magic:

CREATE OR REPLACE FUNCTION fnk_grupes_prekes(prm_

pardavimo_taskas integer, prm_grupe integer, prm_filtras character varying)
 RETURNS refcursor AS
$BODY$
declare
cur refcursor;
begin
open cur for
explain analyze
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;
return cur;
end;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE;

begin;
select fnk_grupes_prekes(...);
fetch all in cur;

(I didnt test the above commands, but that's what I do to debug in-function plans)