Re: Problems using a function in a where clause

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Oct 25, 2006 at 07:55:38AM -0300, Mara Dalponte wrote:
> On 10/24/06, Jim C. Nasby <jim@xxxxxxxxx> wrote:
> >On Mon, Oct 23, 2006 at 04:54:00PM -0300, Mara Dalponte wrote:
> >> Hello,
> >>
> >> I have a query with several join operations and applying the same
> >> filter condition over each involved table. This condition is a complex
> >> predicate over an indexed  timestamp field, depending on some
> >> parameters.
> >> To factorize code,  I wrote the filter into a plpgsql function, but
> >> the resulting query is much more slower than the first one!
> >
> >A view would probably be a better idea... or create some code that
> >generates the code for you.
> 
> Thank, but the filter function needs some external parameters, so a
> view wont be appropiate. Anyway, your second possibility could work!
> 
> >> The explain command over the original query gives the following info
> >> for the WHERE clause that uses the filter:
> >>
> >> ...
> >> Index Cond: ((_timestamp >= '2006-02-23 03:00:00'::timestamp without
> >> time zone) AND (_timestamp <= '2006-02-27 20:00:00.989999'::timestamp
> >> without time zone))
> >> ...
> >>
> >> The explain command for the WHERE clause using the filtering function is:
> >>
> >> ...
> >> Filter: include_time_date('2006-02-23'::date, '2006-02-27'::date,
> >> '03:00:00'::time without time zone, '20:00:00'::time without time
> >> zone, (_timestamp)::timestamp without time zone)
> >> ...
> >>
> >> It seems to not be using the index, and I think this is the reason of
> >> the performance gap between both solutions.
> >
> >Well, it looks like include_time_date just returns a boolean, so how
> >could it use the index?
> 
> I mean that in the old query the index is used (because is a
> comparative condition over an indexed timestamp field), but not in the
> new one, where the function is used. Is there some kind of "inline"
> function type?

No, unfortunately. Your best bet is to add the most important filter
criteria by hand, or write code that writes the code (which is what I'd
probably do).
-- 
Jim Nasby                                            jim@xxxxxxxxx
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux