
Once again, a slightly convoluted question, but it seems that PG may be
doing a little more work than is necessary when selecting from views with
sub-selects. It seems that every time a view field is being referenced in
an outer select expression, the view field is being re-evaluated. Is there
any way to get PG to know that it only needs to do the aggregate once?

eg.

   create table b(f1 int, f2 int);
   create table r(f1 int);

   create view bv as select f1,f2,
        exists(select * from r where r.f1=b.f1) as has_f1,
        exists(select * from r where r.f1=b.f2) as has_f2
   from b;

   explain select f1,f2,
        case when has_f1 and has_f2 then 'both' 
             when has_f1 then 'f1_only' 
             when has_f2 then 'f2_only' 
             else 'none' 
        end as status
    from bv;

    Seq Scan on b  (cost=0.00..20.00 rows=1000 width=8)
      SubPlan
        ->  Seq Scan on r  (cost=0.00..22.50 rows=5 width=4)
        ->  Seq Scan on r  (cost=0.00..22.50 rows=5 width=4)
        ->  Seq Scan on r  (cost=0.00..22.50 rows=5 width=4)
        ->  Seq Scan on r  (cost=0.00..22.50 rows=5 width=4)


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Thu, 01 Nov 2001 15:16:06 +1100
Philip Warner <pjw@rhyme.com.au>
Another planner/optimizer question...
Philip Warner <pjw@rhyme.com.au> writes:
> Is there any way to get PG to know that it only needs to do the
> aggregate once?

It'd probably be possible to look for duplicated aggrefs being assigned
to the same Agg plan node during planning.  However, I'm not entirely
convinced that it's worth the trouble --- the individual transition
function calls are not usually all that expensive.

But ... the example you are offering has nothing to do with aggregates.
Subplans are a different and much messier deal.  The best I could offer
you (short of a complete redesign of subqueries) would be to not pull up
views that have any subqueries, which would probably be a net loss.

			regards, tom lane

Thu, 01 Nov 2001 10:22:35 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Another planner/optimizer question... 
At 10:22 1/11/01 -0500, Tom Lane wrote:
>The best I could offer
>you (short of a complete redesign of subqueries) would be to not pull up
>views that have any subqueries, which would probably be a net loss.

That's probably true 90% percent of the time; it would be interesting to be
able to turn this on & off on a per-query basis (or even a per-view basis).
Is this hard?




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Fri, 02 Nov 2001 11:02:55 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Another planner/optimizer question... 