pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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)

view thread (7+ messages)  latest in thread

Message-ID: <9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com>
Permalink:  ../9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com/
Also on:    postgresql.org/message-id/9b1af80e0811130345o37666c5dob841d93954826357@mail.gmail.com

 · 

reply

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