Re: Planner producing 100% duplicate subplans when unneeded

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?

On Mon, Oct 4, 2010 at 11:47 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Sep 27, 2010 at 5:09 PM, Daniel Grace <dgrace(at)wingsnw(dot)com> wrote:
>> Is there any chance this might be looked at in a future release?
>
> This is another interesting example of a case where an inlining-type
> optimization (which is effectively what's happening here, I think)
> turns out to be a negative.  We had one a while back that involved
> actual function inlining, which is not quite what's happening here,
> but it's close.  It doesn't seem too hard to figure out whether or not
> inlining is a win (non-trivial subexpressions should probably never
> get duplicated), but nobody's gotten around to writing the logic to
> make it work yet.  One useful technique is to stick "OFFSET 0" into
> the subquery; that prevents it from being inlined and gives you
> something more like the plan you were hoping for.
>
> Whether or not this will get looked at in a future release is a tough
> question to answer.  It's possible that someone (most likely, Tom)
> will get motivated to fix this out of sheer annoyance with the current
> behavior, or will notice a way to improve it incidentally while making
> some other change.  But of course the only way to make sure it gets
> fixed is to do it yourself (or pay someone to do it).
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise Postgres Company
>

--
Daniel Grace
AGE, LLC
System Administrator and Software Developer
dgrace(at)wingsnw(dot)com // (425)327-0079 // www.wingsnw.com