optimizing constant quals within outer joins

From: Phil Frost <indigo(at)bitglue(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: optimizing constant quals within outer joins
Date: 2006-06-28 14:35:37
Message-ID: 20060628143536.GA11399@unununium.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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=5004 width=325)
One-Time Filter: (i_have_global_priv(28) AND i_have_global_priv(32))
-> Hash Join (cost=96.55..402.69 rows=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=2015 width=325)
Hash Cond: ("outer".objectid = "inner".objectid)
-> Seq Scan on orderitem (cost=0.00..180.55 rows=2015 width=306)
Filter: i_have_global_priv(28)
-> Hash (cost=96.55..96.55 rows=1668 width=23)
-> Seq Scan 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/>

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-06-28 14:42:43 Re: UUID's as primary keys
Previous Message Martijn van Oosterhout 2006-06-28 14:28:44 Re: UUID's as primary keys