Re: Problems using a function in a where clause

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(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)