Re: Problems using a function in a where clause

From: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
To: Mara Dalponte <dalponte(at)sol(dot)info(dot)unlp(dot)edu(dot)ar>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Problems using a function in a where clause
Date: 2006-10-25 15:32:37
Message-ID: 20061025153236.GR26892@nasby.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2006-10-25 15:38:46 Re: Best COPY Performance
Previous Message Jim C. Nasby 2006-10-25 15:28:13 Re: Best COPY Performance