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

Re: function executes sql 100 times longer it should

From: "Vyacheslav Kalinin" <vka(at)mgcp(dot)com>
To: "Julius Tuskenis" <julius(at)nsoft(dot)lt>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: function executes sql 100 times longer it should
Date: 2008-11-13 11:45:36
Message-ID: 9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com (view raw or flat)
Thread:
Lists: 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.

On Thu, Nov 13, 2008 at 11:16 AM, Julius Tuskenis <julius(at)nsoft(dot)lt> 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)

In response to

Responses

pgsql-admin by date

Next:From: Julius TuskenisDate: 2008-11-13 13:29:09
Subject: Re: function executes sql 100 times longer it should
Previous:From: Julius TuskenisDate: 2008-11-13 08:16:57
Subject: Re: function executes sql 100 times longer it should

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