Обсуждение: Another planner/optimizer question...

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
wherer.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   |/

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

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   |/

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   |/