Re: Planning problem: pushing conditions through GROUP BY

On Sun, Nov 22, 2009 at 12:38:36PM -0500, Tom Lane wrote:
> > But when I want to join on this view, it all goes pear shaped, as can
> > be seen in the following (completely equivalent) query:
>
> > # explain select * from maxrevs where rul_id=ANY(select rul_id from rules where rul_sid=15895);
>
> There isn't any provision in the planner for trying to exchange the
> order of a join and a GROUP BY operation. In the general case it
> wouldn't even be allowed anyway since it'd change the results.
> In this specific case --- semijoin using an equality condition on the
> grouping key --- I agree that it would be a legitimate transformation.
> But it's not obvious that it would be a win. I think if we wanted to
> consider it we'd have to develop plans both ways and compare costs.
> And that would be a really major amount of work.

It's not obvious it would be a win, but it would be a nice
transformation to have. In the actual case it's an inner join, but
since we know the other side only produces unique entries, the result
is the same.

> I think you'll have to write out your queries without benefit of the
> view :-(. Sorry the news isn't better.

It's not the end of the world. At least I know it's it's not something
I'm missing. (I wonder if window functions work differently here.
That's 8.4 territory though).

Thanks anyway,

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.