Re: left outer join vs subplan

2007/9/6, Simon Riggs <simon@2ndquadrant.com>:

> The query formulation does seem a fairly common one.
>
> > First query:
> > explain analyze
> > select *
> > from
> >      a
> >      left outer join (
> >          select b.id, sum(b.val)
> >          from b
> >          group by b.id
> >      ) bagg
> >          on bagg.id = a.id
> > where
> >      a.id > 10000
> > order by a.addon, a.id
> > limit 100;

>
> The value of sum(b.val) is never used in the query, so the aggregate
> itself could be discarded. I suspect there are other conditions you
> aren't showing us that would make this impossible?

The value of sum(b.val) is being output in the "select *", so saying
it's never used is an oversimplification. But it's actually not used
in any join, or filter. That should be enough to optimize...

>
> The aggregate prevents the condition bagg.id = a.id from being pushed
> down so that we know b.id = a.id. If we knew that then we could use b.id
> = ? as an index condition to retrieve the rows.

That's exactly the point... But if we all can see it, maybe it's
possible to code it?


Cheers,
Filip Rembiałkowski