Re: Planning problem: pushing conditions through GROUP BY

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> I've having a smaller planner problem (server version 8.3.5). I have a
> table with revisions (revs) and I have a view on top of that gives the
> latest revision of each rule. When I request a specific rule, it works
> fine.
> ...
> 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.

The simpler case isn't treated as a join, just a simple WHERE condition
on individual rows, so it does what you want.

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

regards, tom lane