Re: function executes sql 100 times longer it should
| От | Vyacheslav Kalinin |
|---|---|
| Тема | Re: function executes sql 100 times longer it should |
| Дата | |
| Msg-id | 9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: function executes sql 100 times longer it should (Julius Tuskenis <julius@nsoft.lt>) |
| Ответы |
Re: function executes sql 100 times longer it should
|
| Список | pgsql-admin |
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.
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@nsoft.lt> wrote:
It does, after a bit of street magic:
CREATE OR REPLACE FUNCTION fnk_grupes_prekes(prm_
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)
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)
В списке pgsql-admin по дате отправления: