From: Vyacheslav Kalinin <vka@mgcp.com>
To: Julius Tuskenis <julius@nsoft.lt>
Cc: pgsql-admin@postgresql.org
Subject: Re: function executes sql 100 times longer it should
Date: Thu, 13 Nov 2008 14:45:36 +0300
Message-ID: <9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com> (raw)
In-Reply-To: <491BE279.50702@nsoft.lt>
References: <491AE3F2.601@nsoft.lt>
<1226500247.2221.2.camel@lin-it1.eaglepress.local>
<491BE279.50702@nsoft.lt>
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@nsoft.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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: vka@mgcp.com, julius@nsoft.lt
Subject: Re: function executes sql 100 times longer it should
In-Reply-To: <9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com>
References: <491AE3F2.601@nsoft.lt> <1226500247.2221.2.camel@lin-it1.eaglepress.local> <491BE279.50702@nsoft.lt> <9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox