Re: BUG #6359: excessively inlining subquery leads to slow queries

On Mon, Dec 26, 2011 at 8:50 AM, <maxim(dot)boguk(at)gmail(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6359
> Logged by:          Maksym Boguk
> Email address:      maxim(dot)boguk(at)gmail(dot)com
> PostgreSQL version: 9.1.2
> Operating system:   Ubuntu linux
> Description:
>
> Sometime Postgres inline subrequest even if it produce slower plan (and that
> slow plan have higher actual cost than non-inlined plan):

I'm pretty sure this has been discussed before. In the case where a
non-trivial expression is going to get inlined in multiple places, it
would probably be better to replace it with a placeholder referencing
a subplan that evaluates it just once. But, we don't currently try to
do that. In practice, this only bites us occasionally, because it's
actually fairly rare to write a query that references the same
subquery output twice, let alone 17 times. Also, detecting the
scenario where we shouldn't inline probably isn't free, so we'd have
to weigh the benefit of helping some people quite a bit against the
cost of slowing down query planning slightly for everyone else. All
in all, it's a complicated problem... so I'd classify this more as an
unimplemented feature than a bug.

That having been said, I hope we'll get around to improving it at some
point. Even though in most cases inlining is a huge win, there are
certainly boundary cases where it's not, and this is one of them.

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