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

Re: function executes sql 100 times longer it should

From: Julius Tuskenis <julius(at)nsoft(dot)lt>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: function executes sql 100 times longer it should
Date: 2008-11-14 12:37:23
Message-ID: 491D7103.7000105@nsoft.lt (view raw or flat)
Thread:
Lists: pgsql-admin
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


In response to

pgsql-admin by date

Next:From: Mark StebenDate: 2008-11-14 18:25:20
Subject: Re: question on warm standby
Previous:From: Tom LaneDate: 2008-11-14 03:05:48
Subject: Re: question on warm standby

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