Re: Planner producing 100% duplicate subplans when unneeded

On Mon, Oct 4, 2010 at 4:15 PM, Daniel Grace <dgrace(at)wingsnw(dot)com> wrote:
> As a theoretical question (I'm not savvy on Postgres's code but might
> be intrigued enough to beat on it anyways), is it feasible to do an
> additional pass on the query plan that essentially goes:
>
> - Are these two subplans identical?
> - Are they at the same part of the tree?
>
> and if both of these conditions are true, discard one subplan and
> rewrite all references to point to the other one?
>
> Assuming it IS possible, are there any particular situations where it
> wouldn't work?

Well, volatile functions would be a problem, but I don't think that's
really the way to go anyway. I think that deciding whether or not to
collapse subqueries into the main query (which is what's happening
here) seems like it could be done by counting the number of times any
given subexpression is referenced, which seems like it would be a lot
cheaper than checking things against each other pairwise to see if
they match up. Slowing down the planner to detect cases like this
wouldn't be very appealing:

SELECT (SELECT SUM(a) FROM bob, SELECT SUM(b) FROM bob, SELECT SUM(c) FROM bob);

...because very few people are going to write that query that way
anyway, and unless it's almost free to notice the duplication, it
doesn't make sense to spend planning time on it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company