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