Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D90F364FD7A for ; Thu, 13 Nov 2008 08:09:26 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 85443-08 for ; Thu, 13 Nov 2008 08:09:23 -0400 (AST) X-Greylist: delayed 00:23:44.126336 by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.236]) by mail.postgresql.org (Postfix) with ESMTP id 3A11964FD64 for ; Thu, 13 Nov 2008 08:09:22 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so823443rvf.43 for ; Thu, 13 Nov 2008 04:09:21 -0800 (PST) Received: by 10.142.53.19 with SMTP id b19mr3748297wfa.154.1226576736663; Thu, 13 Nov 2008 03:45:36 -0800 (PST) Received: by 10.143.157.6 with HTTP; Thu, 13 Nov 2008 03:45:36 -0800 (PST) Message-ID: <9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com> Date: Thu, 13 Nov 2008 14:45:36 +0300 From: "Vyacheslav Kalinin" To: "Julius Tuskenis" Subject: Re: function executes sql 100 times longer it should Cc: pgsql-admin@postgresql.org In-Reply-To: <491BE279.50702@nsoft.lt> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29663_5405159.1226576736661" References: <491AE3F2.601@nsoft.lt> <1226500247.2221.2.camel@lin-it1.eaglepress.local> <491BE279.50702@nsoft.lt> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200811/69 X-Sequence-Number: 30723 ------=_Part_29663_5405159.1226576736661 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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) ------=_Part_29663_5405159.1226576736661 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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)



------=_Part_29663_5405159.1226576736661--