Re: left outer join vs subplan

2007/9/6, Simon Riggs <simon(at)2ndquadrant(dot)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