Re: optimizing constant quals within outer joins

Поиск
Список
Период
Сортировка
От Martijn van Oosterhout
Тема Re: optimizing constant quals within outer joins
Дата
Msg-id 20060628151159.GD3521@svana.org
обсуждение исходный текст
Ответ на optimizing constant quals within outer joins  (Phil Frost <indigo@bitglue.com>)
Ответы Re: optimizing constant quals within outer joins  (Phil Frost <indigo@bitglue.com>)
Список pgsql-hackers
On Wed, Jun 28, 2006 at 10:35:37AM -0400, Phil Frost wrote:
> I have an optimization I'd like to see which I think should be pretty
> easy for someone familiar with the planner code to implement. My
> situation is this: I have an application using veil[1]. Essentially, I
> have a schema "private" and another "public". Private contains regular
> tables, where private contains views on those tables, like "create view
> public.foo as select * from foo where i_have_global_priv('select_foo')",
> and i_have_global_priv is a stable function.
>
> My problem is that in several situations, postgresql is planning a
> sequential scan with i_have_global_priv(n) as a filter, where N is some
> constant literal specified in the view definition. This leads to the
> function being called hundreds of thousands of times, which makes my
> query orders of magnitude slower.

Is the function marked stable or immutable?

In the examples you give the planner can't move the function around the
tree because that would change the output of the query. For inner joins
it's ok, for outer joins it's much more tricky.

I thought the planner would evaluate constant conditions early on which
I why I'm asking about the function.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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

Предыдущее
От: Phil Frost
Дата:
Сообщение: optimizing constant quals within outer joins
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Help with casting and comparing.