Re: optimizing constant quals within outer joins

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.