Thread: function executes sql 100 times longer it should

function executes sql 100 times longer it should

From
Julius Tuskenis
Date:
Hello, list.

I have one simple SQL function returning result set that takes around 3
seconds to execute. But if I execute the Select it executes directly -
it takes only around 30 ms. Why so big difference? What should I check?
I must also say, that this started this afternoon.

PG:  8.3.3
OS:  Windows Server 2003

Example below:
//========================================================================================================
CREATE OR REPLACE FUNCTION fnk_grupes_prekes(prm_pardavimo_taskas
integer, prm_grupe integer, prm_filtras character varying)
  RETURNS SETOF frt_grupes_prekes AS
$BODY$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
$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION fnk_grupes_prekes(integer, integer, character varying)
OWNER TO postgres;
GRANT EXECUTE ON FUNCTION fnk_grupes_prekes(integer, integer, character
varying) TO postgres;
GRANT EXECUTE ON FUNCTION fnk_grupes_prekes(integer, integer, character
varying) TO public;


select  *  from fnk_grupes_prekes(18,42,NULL);
Total query runtime: 2172 ms.
0 rows retrieved.


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=42 OR 42 is Null)
  AND ptk_pardavimotaskas=18
  AND ((prek_pavadinimas ILIKE ('%'||null||'%')) OR null is NULL)
  AND fnk_grafikas_galioja(kag_grafikas) = true
ORDER BY prek_pavadinimas;

Total query runtime: 47 ms.
0 rows retrieved.

--

Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


Re: function executes sql 100 times longer it should

From
"Sean Brown"
Date:
On Wed, 2008-11-12 at 16:10 +0200, Julius Tuskenis wrote:
> Hello, list.
>
> I have one simple SQL function returning result set that takes around 3
> seconds to execute. But if I execute the Select it executes directly -
> it takes only around 30 ms. Why so big difference?
Probably caching the results.

>  What should I check?
Explain plan might be a good start

> I must also say, that this started this afternoon.


>
> PG:  8.3.3
> OS:  Windows Server 2003
>
> Example below:
> //========================================================================================================
> CREATE OR REPLACE FUNCTION fnk_grupes_prekes(prm_pardavimo_taskas
> integer, prm_grupe integer, prm_filtras character varying)
>   RETURNS SETOF frt_grupes_prekes AS
> $BODY$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
> $BODY$
>   LANGUAGE 'sql' VOLATILE
>   COST 100
>   ROWS 1000;
> ALTER FUNCTION fnk_grupes_prekes(integer, integer, character varying)
> OWNER TO postgres;
> GRANT EXECUTE ON FUNCTION fnk_grupes_prekes(integer, integer, character
> varying) TO postgres;
> GRANT EXECUTE ON FUNCTION fnk_grupes_prekes(integer, integer, character
> varying) TO public;
>
>
> select  *  from fnk_grupes_prekes(18,42,NULL);
> Total query runtime: 2172 ms.
> 0 rows retrieved.
>
>
> 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=42 OR 42 is Null)
>   AND ptk_pardavimotaskas=18
>   AND ((prek_pavadinimas ILIKE ('%'||null||'%')) OR null is NULL)
>   AND fnk_grafikas_galioja(kag_grafikas) = true
> ORDER BY prek_pavadinimas;
>
> Total query runtime: 47 ms.
> 0 rows retrieved.
>
First run probably put the plan and results into the query cache. The
second run could just pull it from there instead of going to the tables
on the disk.

> --
>
> Julius Tuskenis
> Programavimo skyriaus vadovas
> UAB nSoft
> mob. +37068233050
>


Attachment

Re: function executes sql 100 times longer it should

From
Julius Tuskenis
Date:
Hello Sean,


> Explain plan might be a good start
Its a good start, but leads to nothing because Explain doesn't go into
the function.


EXPLAIN select * -- prek_id, prek_pavadinimas, ks_kaina, mvnt_trumpinys
from fnk_grupes_prekes(18,42,NULL);

"Function Scan on fnk_grupes_prekes  (cost=0.00..25.10 rows=10 width=143)"


EXPLAIN  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=42 OR 40 is Null)
  AND ptk_pardavimotaskas=18
  AND ((prek_pavadinimas ILIKE ('%'||null||'%')) OR null is NULL)
  AND fnk_grafikas_galioja(kag_grafikas) = true
ORDER BY prek_pavadinimas

"Unique  (cost=281.89..281.91 rows=1 width=132)"
"  ->  Sort  (cost=281.89..281.90 rows=1 width=132)"
"        Sort Key: filter_b_preke_matoma.prek_pavadinimas,
filter_b_preke_matoma.prek_id, b_kainorascio_sudetis.ks_kaina,
b_mato_vienetai.mvnt_trumpinys"
"        ->  Nested Loop  (cost=1.07..281.88 rows=1 width=132)"
"              ->  Nested Loop  (cost=1.07..280.35 rows=1 width=136)"
"                    ->  Nested Loop  (cost=1.07..280.06 rows=1 width=144)"
"                          ->  Nested Loop  (cost=1.07..279.53 rows=1
width=140)"
"                                ->  Nested Loop  (cost=1.07..269.69
rows=1 width=133)"
"                                      ->  Hash Join  (cost=1.07..264.97
rows=15 width=125)"
"                                            Hash Cond:
(filter_b_preke_matoma.prek_matovnt = b_mato_vienetai.mvnt_id)"
"                                            ->  Function Scan on
filter_b_preke_matoma  (cost=0.00..260.00 rows=1000 width=126)"
"                                            ->  Hash  (cost=1.03..1.03
rows=3 width=7)"
"                                                  ->  Seq Scan on
b_mato_vienetai  (cost=0.00..1.03 rows=3 width=7)"
"                                      ->  Index Scan using idx_gp_preke
on b_grupes_prekes  (cost=0.00..0.30 rows=1 width=8)"
"                                            Index Cond:
(b_grupes_prekes.gp_preke = filter_b_preke_matoma.prek_id)"
"                                            Filter:
(b_grupes_prekes.gp_grupe = 42)"
"                                ->  Index Scan using idx_ks_preke on
b_kainorascio_sudetis  (cost=0.00..9.80 rows=3 width=15)"
"                                      Index Cond:
(b_kainorascio_sudetis.ks_preke = filter_b_preke_matoma.prek_id)"
"                          ->  Index Scan using idx_kag_kainorastis on
b_kainorascio_grafikas  (cost=0.00..0.52 rows=1 width=4)"
"                                Index Cond:
(b_kainorascio_grafikas.kag_kainorastis =
b_kainorascio_sudetis.ks_kainorastis)"
"                                Filter:
fnk_grafikas_galioja(b_kainorascio_grafikas.kag_grafikas)"
"                    ->  Index Scan using
unq_kainorastis_pardavimo_taskui on b_pardavimo_tasko_kainorastis
(cost=0.00..0.28 rows=1 width=4)"
"                          Index Cond:
((b_pardavimo_tasko_kainorastis.ptk_pardavimotaskas = 18) AND
(b_pardavimo_tasko_kainorastis.ptk_kainorastis =
b_kainorascio_sudetis.ks_kainorastis))"
"              ->  Seq Scan on b_grupe  (cost=0.00..1.52 rows=1 width=4)"
"                    Filter: (b_grupe.grup_id = 42)"




> First run probably put the plan and results into the query cache. The
> second run could just pull it from there instead of going to the tables
> on the disk.
>
I dont think its true, because even if I change parameter values in SQL
it still returns results fast. So my guess is that the problem is
somewhere else. Maybe theres a way to see Explain plan for function body??

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


Re: function executes sql 100 times longer it should

From
"Vyacheslav Kalinin"
Date:
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)



Re: function executes sql 100 times longer it should

From
Julius Tuskenis
Date:
Thank you Vyacheslav.

your "bit or street magic" worked and now I have Execution Plans for both queries:  from function and simple SQL.
I'm not good in reading and interpreting these please advice witch part should I put my attention to?


FUNCTION:

"Unique  (cost=290.13..290.15 rows=1 width=132) (actual time=1834.211..1834.222 rows=2 loops=1)"
"  ->  Sort  (cost=290.13..290.14 rows=1 width=132) (actual time=1834.207..1834.209 rows=2 loops=1)"
"        Sort Key: filter_b_preke_matoma.prek_pavadinimas, filter_b_preke_matoma.prek_id,
b_kainorascio_sudetis.ks_kaina,b_mato_vienetai.mvnt_trumpinys" 
"        Sort Method:  quicksort  Memory: 17kB"
"        ->  Nested Loop Left Join  (cost=1.03..290.12 rows=1 width=132) (actual time=374.851..1834.169 rows=2
loops=1)"
"              Join Filter: (b_grupes_prekes.gp_grupe = b_grupe.grup_id)"
"              Filter: ((b_grupe.grup_id = $1) OR ($1 IS NULL))"
"              ->  Nested Loop Left Join  (cost=1.03..288.07 rows=1 width=136) (actual time=45.915..1795.235 rows=694
loops=1)"
"                    ->  Nested Loop  (cost=1.03..281.39 rows=1 width=132) (actual time=45.902..1789.018 rows=694
loops=1)"
"                          ->  Nested Loop  (cost=1.03..281.11 rows=1 width=140) (actual time=8.021..1734.222 rows=6367
loops=1)"
"                                ->  Nested Loop  (cost=1.03..280.58 rows=1 width=136) (actual time=6.618..79.367
rows=6000loops=1)" 
"                                      ->  Nested Loop  (cost=1.03..268.87 rows=1 width=125) (actual time=6.595..34.114
rows=2820loops=1)" 
"                                            Join Filter: (filter_b_preke_matoma.prek_matovnt =
b_mato_vienetai.mvnt_id)"
"                                            ->  Function Scan on filter_b_preke_matoma  (cost=0.00..267.50 rows=5
width=126)(actual time=6.580..11.766 rows=2820 loops=1)" 
"                                                  Filter: (((prek_pavadinimas)::text ~~* (('%'::text || ($3)::text) ||
'%'::text))OR ($3 IS NULL))" 
"                                            ->  Materialize  (cost=1.03..1.06 rows=3 width=7) (actual
time=0.001..0.002rows=3 loops=2820)" 
"                                                  ->  Seq Scan on b_mato_vienetai  (cost=0.00..1.03 rows=3 width=7)
(actualtime=0.005..0.009 rows=3 loops=1)" 
"                                      ->  Index Scan using idx_ks_preke on b_kainorascio_sudetis  (cost=0.00..11.67
rows=3width=15) (actual time=0.007..0.010 rows=2 loops=2820)" 
"                                            Index Cond: (b_kainorascio_sudetis.ks_preke =
filter_b_preke_matoma.prek_id)"
"                                ->  Index Scan using idx_kag_kainorastis on b_kainorascio_grafikas  (cost=0.00..0.52
rows=1width=4) (actual time=0.199..0.272 rows=1 loops=6000)" 
"                                      Index Cond: (b_kainorascio_grafikas.kag_kainorastis =
b_kainorascio_sudetis.ks_kainorastis)"
"                                      Filter: fnk_grafikas_galioja(b_kainorascio_grafikas.kag_grafikas)"
"                          ->  Index Scan using unq_kainorastis_pardavimo_taskui on b_pardavimo_tasko_kainorastis
(cost=0.00..0.28rows=1 width=4) (actual time=0.005..0.006 rows=0 loops=6367)" 
"                                Index Cond: ((b_pardavimo_tasko_kainorastis.ptk_pardavimotaskas = $2) AND
(b_pardavimo_tasko_kainorastis.ptk_kainorastis= b_kainorascio_sudetis.ks_kainorastis))" 
"                    ->  Index Scan using idx_gp_preke on b_grupes_prekes  (cost=0.00..6.67 rows=1 width=8) (actual
time=0.006..0.006rows=0 loops=694)" 
"                          Index Cond: (b_grupes_prekes.gp_preke = filter_b_preke_matoma.prek_id)"
"              ->  Seq Scan on b_grupe  (cost=0.00..1.42 rows=42 width=4) (actual time=0.004..0.023 rows=42 loops=694)"
"Total runtime: 1834.686 ms"


SIMPLE SQL:
"Unique  (cost=281.91..281.93 rows=1 width=132) (actual time=34.438..34.447 rows=2 loops=1)"
"  ->  Sort  (cost=281.91..281.92 rows=1 width=132) (actual time=34.435..34.436 rows=2 loops=1)"
"        Sort Key: filter_b_preke_matoma.prek_pavadinimas, filter_b_preke_matoma.prek_id,
b_kainorascio_sudetis.ks_kaina,b_mato_vienetai.mvnt_trumpinys" 
"        Sort Method:  quicksort  Memory: 17kB"
"        ->  Nested Loop  (cost=1.07..281.90 rows=1 width=132) (actual time=15.226..34.396 rows=2 loops=1)"
"              ->  Nested Loop  (cost=1.07..280.37 rows=1 width=136) (actual time=15.197..34.331 rows=2 loops=1)"
"                    ->  Nested Loop  (cost=1.07..280.08 rows=1 width=144) (actual time=13.976..34.233 rows=7 loops=1)"
"                          ->  Nested Loop  (cost=1.07..279.55 rows=1 width=140) (actual time=12.178..29.760 rows=7
loops=1)"
"                                ->  Nested Loop  (cost=1.07..269.69 rows=1 width=133) (actual time=12.165..29.700
rows=2loops=1)" 
"                                      ->  Hash Join  (cost=1.07..264.97 rows=15 width=125) (actual time=6.452..13.617
rows=2820loops=1)" 
"                                            Hash Cond: (filter_b_preke_matoma.prek_matovnt = b_mato_vienetai.mvnt_id)"
"                                            ->  Function Scan on filter_b_preke_matoma  (cost=0.00..260.00 rows=1000
width=126)(actual time=6.418..7.828 rows=2820 loops=1)" 
"                                            ->  Hash  (cost=1.03..1.03 rows=3 width=7) (actual time=0.013..0.013
rows=3loops=1)" 
"                                                  ->  Seq Scan on b_mato_vienetai  (cost=0.00..1.03 rows=3 width=7)
(actualtime=0.005..0.007 rows=3 loops=1)" 
"                                      ->  Index Scan using idx_gp_preke on b_grupes_prekes  (cost=0.00..0.30 rows=1
width=8)(actual time=0.004..0.004 rows=0 loops=2820)" 
"                                            Index Cond: (b_grupes_prekes.gp_preke = filter_b_preke_matoma.prek_id)"
"                                            Filter: (b_grupes_prekes.gp_grupe = 43)"
"                                ->  Index Scan using idx_ks_preke on b_kainorascio_sudetis  (cost=0.00..9.82 rows=3
width=15)(actual time=0.009..0.019 rows=4 loops=2)" 
"                                      Index Cond: (b_kainorascio_sudetis.ks_preke = filter_b_preke_matoma.prek_id)"
"                          ->  Index Scan using idx_kag_kainorastis on b_kainorascio_grafikas  (cost=0.00..0.52 rows=1
width=4)(actual time=0.536..0.634 rows=1 loops=7)" 
"                                Index Cond: (b_kainorascio_grafikas.kag_kainorastis =
b_kainorascio_sudetis.ks_kainorastis)"
"                                Filter: fnk_grafikas_galioja(b_kainorascio_grafikas.kag_grafikas)"
"                    ->  Index Scan using unq_kainorastis_pardavimo_taskui on b_pardavimo_tasko_kainorastis
(cost=0.00..0.28rows=1 width=4) (actual time=0.009..0.009 rows=0 loops=7)" 
"                          Index Cond: ((b_pardavimo_tasko_kainorastis.ptk_pardavimotaskas = 18) AND
(b_pardavimo_tasko_kainorastis.ptk_kainorastis= b_kainorascio_sudetis.ks_kainorastis))" 
"              ->  Seq Scan on b_grupe  (cost=0.00..1.52 rows=1 width=4) (actual time=0.012..0.020 rows=1 loops=2)"
"                    Filter: (b_grupe.grup_id = 43)"
"Total runtime: 34.804 ms"


Vyacheslav Kalinin rašė:

> Most likely, you get different plans because the function has to deal with the parametrized query, hence planner
cannoteffectively 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 <mailto: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)
>
>
>

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050


Re: function executes sql 100 times longer it should

From
"Vyacheslav Kalinin"
Date:
Apparently your problem starts here:

> ->  Function Scan on filter_b_preke_matoma  (cost=0.00..267.50 rows=5 width=126) (actual time=6.580..11.766 rows=2820 loops=1)
>                                                  Filter: (((prek_pavadinimas)::text ~~* (('%'::text || ($3)::text) || '%'::text)) OR ($3 IS NULL))

Planner expects to see only somewhat 5 rows after function scan with the filter but get ~3000, which is not a surprise if one looks at your plain SQL query, corresponding WHERE part:

 AND ((prek_pavadinimas ILIKE ('%'||null||'%')) OR null is NULL)

As I mentioned conditions like this get wrapped (to TRUE in your case), so with plain SQL planner does not even try to estimate ILIKE filter effect.


Re: function executes sql 100 times longer it should

From
Julius Tuskenis
Date:
once again - thank you Vyacheslav for your quick answer.

I have to ask you one more question - is it possible to make a planer
act according to passed parameters, or is the plan predefined on
creating the function?


Vyacheslav Kalinin rašė:
> Apparently your problem starts here:
>
> > ->  Function Scan on filter_b_preke_matoma  (cost=0.00..267.50
> rows=5 width=126) (actual time=6.580..11.766 rows=2820 loops=1)
> >                                                  Filter:
> (((prek_pavadinimas)::text ~~* (('%'::text || ($3)::text) ||
> '%'::text)) OR ($3 IS NULL))
>
> Planner expects to see only somewhat 5 rows after function scan with
> the filter but get ~3000, which is not a surprise if one looks at your
> plain SQL query, corresponding WHERE part:
>
>  AND ((prek_pavadinimas ILIKE ('%'||null||'%')) OR null is NULL)
>
> As I mentioned conditions like this get wrapped (to TRUE in your
> case), so with plain SQL planner does not even try to estimate ILIKE
> filter effect.
>
>


--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050