Re: Problems using a function in a where clause

Поиск
Список
Период
Сортировка
От Jim C. Nasby
Тема Re: Problems using a function in a where clause
Дата
Msg-id 20061025002131.GB26892@nasby.net
обсуждение исходный текст
Ответ на Problems using a function in a where clause  ("Mara Dalponte" <dalponte@sol.info.unlp.edu.ar>)
Список pgsql-performance
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@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Jim C. Nasby"
Дата:
Сообщение: Re: Best COPY Performance
Следующее
От: "Jim C. Nasby"
Дата:
Сообщение: Re: Copy database performance issue