Re: optimizing constant quals within outer joins - Mailing list pgsql-hackers

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.67rows=151221 width=187)  Merge Cond: ("outer".objectid = "inner".objectid)  ->  Sort
(cost=441.55..454.06rows=5004 width=45)        Sort Key: oip.objectid        ->  Subquery Scan oip  (cost=0.00..134.08
rows=5004width=45)              ->  Limit  (cost=0.00..84.04 rows=5004 width=23)                    ->  Result
(cost=0.00..84.04rows=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
Scanon 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 :(