On Wed, Oct 25, 2006 at 07:55:38AM -0300, Mara Dalponte wrote:
> On 10/24/06, Jim C. Nasby <jim@nasby.net> 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@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
