On Wed, Oct 25, 2006 at 07:55:38AM -0300, Mara Dalponte wrote:
On 10/24/06, Jim C. Nasby [EMAIL PROTECTED] 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.98'::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[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
