Skip site navigation (1) Skip section navigation (2)

Re: optimizing constant quals within outer joins

From: Phil Frost <indigo(at)bitglue(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: optimizing constant quals within outer joins
Date: 2006-06-28 15:24:25
Message-ID: 20060628152424.GA23509@unununium.org (view raw or flat)
Thread:
Lists: 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.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 :(

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2006-06-28 15:40:52
Subject: Re: optimizing constant quals within outer joins
Previous:From: Tom LaneDate: 2006-06-28 15:12:43
Subject: Re: Help with casting and comparing.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group