Problems using a function in a where clause

From: "Mara Dalponte" <dalponte(at)sol(dot)info(dot)unlp(dot)edu(dot)ar>
To: pgsql-performance(at)postgresql(dot)org
Subject: Problems using a function in a where clause
Date: 2006-10-23 19:54:00
Message-ID: 52cedf850610231254y11c036d4g436fa54370690689@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Bucky Jordan 2006-10-23 20:28:44 Re: New hardware thoughts
Previous Message Tom Lane 2006-10-23 16:01:41 Re: Index on two columns not used