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

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.

In some cases, the planner already optimizes this by moving the "where
i_have_global_priv(n)" qualification out of the seq scan filter and into
the one-time filter of a result node. The relevant function in the code
seems to be pull_constant_clauses, called from query_planner in
planmain.c around line 118.

By experimentation, it seems that this optimization will not be made on
either side of an outer join. For example:

dew=# explain select * from   (select * from private.orderitem where i_have_global_priv(28)) as oi   join (
select* from private.orderitemproduct where i_have_global_priv(32)   ) as oip using (objectid);
           QUERY PLAN                                       
 
---------------------------------------------------------------------------------------Result  (cost=96.56..402.70
rows=5004width=325)  One-Time Filter: (i_have_global_priv(28) AND i_have_global_priv(32))  ->  Hash Join
(cost=96.55..402.69rows=5004 width=325)        Hash Cond: ("outer".objectid = "inner".objectid)        ->  Seq Scan on
orderitem (cost=0.00..165.44 rows=6044 width=306)        ->  Hash  (cost=84.04..84.04 rows=5004 width=23)
-> Seq Scan on orderitemproduct  (cost=0.00..84.04 rows=5004 width=23)
 

dew=# explain select * from   (select * from private.orderitem where i_have_global_priv(28)) as oi   left join (
select* from private.orderitemproduct where i_have_global_priv(32)   ) as oip using (objectid);
        QUERY PLAN                                    
 
---------------------------------------------------------------------------------Hash Left Join  (cost=100.72..301.94
rows=2015width=325)  Hash Cond: ("outer".objectid = "inner".objectid)  ->  Seq Scan on orderitem  (cost=0.00..180.55
rows=2015width=306)        Filter: i_have_global_priv(28)  ->  Hash  (cost=96.55..96.55 rows=1668 width=23)        ->
SeqScan on orderitemproduct  (cost=0.00..96.55 rows=1668 width=23)              Filter: i_have_global_priv(32)
 


Notice that the cross join plan results in i_have_global_priv being
called just twice -- once for each privilege being checked, while the
left join plan will result in it being called once for each row.

So, is this something I can coerce someone into doing? It would be very
much appreciated here.


[1] <http://veil.projects.postgresql.org/>