Обсуждение: Problems using a function in a where clause

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!

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.

How can I explicitly use this index? which type of functions shall I
use (VOLATILE | INMUTABLE | STABLE)?

Thanks in advance

Mara

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.

> 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?

> How can I explicitly use this index? which type of functions shall I
> use (VOLATILE | INMUTABLE | STABLE)?

That depends on what exactly the function does. There's a pretty good
description in the CREATE FUNCTION docs.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

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)