Re: optimizing constant quals within outer joins

On Wed, Jun 28, 2006 at 05:11:59PM +0200, Martijn van Oosterhout wrote:
> 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.

i_have_global_priv is a stable function.

The planner in fact can move the function around without changing the
output. I can make it do so by putting "offset 0" in the subqueries:

dew=# explain select * from
(select * from private.orderitem where i_have_global_priv(28) offset 0) as oi
left join (
select * from private.orderitemproduct where i_have_global_priv(32) offset 0
) as oip using (objectid);
QUERY PLAN
---------------------------------------------------------------------------------------------------
Merge Right Join (cost=1310.33..3603.67 rows=151221 width=187)
Merge Cond: ("outer".objectid = "inner".objectid)
-> Sort (cost=441.55..454.06 rows=5004 width=45)
Sort Key: oip.objectid
-> Subquery Scan oip (cost=0.00..134.08 rows=5004 width=45)
-> Limit (cost=0.00..84.04 rows=5004 width=23)
-> Result (cost=0.00..84.04 rows=5004 width=23)
One-Time Filter: i_have_global_priv(32)
-> Seq Scan on orderitemproduct (cost=0.00..84.04 rows=5004 width=23)
-> Sort (cost=868.78..883.89 rows=6044 width=146)
Sort Key: oi.objectid
-> Limit (cost=0.00..165.44 rows=6044 width=306)
-> Result (cost=0.00..165.44 rows=6044 width=306)
One-Time Filter: i_have_global_priv(28)
-> Seq Scan on orderitem (cost=0.00..165.44 rows=6044 width=306)

The transformation is from this:

-> Seq Scan on orderitem (cost=0.00..180.55 rows=2015 width=306)
Filter: i_have_global_priv(28)

to this:

-> Result (cost=0.00..165.44 rows=6044 width=306)
One-Time Filter: i_have_global_priv(28)
-> Seq Scan on orderitem (cost=0.00..165.44 rows=6044 width=306)

which produce the same result. However, I'm not about to put "offset 0"
in all my view definitions, as that would prevent a number of other
extremely desirable optimizations.

Can a Result node not be an input to an outer join node? That would make
me sad :(