Re: [POC] Allow flattening of subquery with a link to upper query

On 20/2/2024 17:43, David Rowley wrote:
> On Tue, 20 Feb 2024 at 22:57, Andrei Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru> wrote:
> I agree that it would be nice to teach the planner how to do this, but
> I think it just has to be a cost-based decision. Imagine how the
> transformed query would perform of pg_am had a billion rows and
> pg_class had 1 row. That's quite a costly hash table build to be
> probing it just once.
True, the origins of this work lie in foreign tables where such a query
generates an even worse situation.

> I didn't follow the patch, but there was a patch to push aggregate
> function evaluation down [1]. I imagine this has the same problem as
> if you just blindly pushed and aggregate function evaluation as deep
> as you could evaluate all the aggregate's parameters and group by vars
> then you may end up aggregating far more than you need to as some join
> could eliminate the majority of the groups. I think we'd need to come
> up with some way to have the planner consider these types of
> optimisations as alternatives to what happens today and only apply
> them when we estimate that they're cheaper. Right now a Path has no
> ability to describe that it's performed GROUP BY.
Thanks for the link. We also ended up with the idea of an alternative
subtree (inspired by the approach of AlternativeSubplan). Here, we just
explain the current state of the pull-up sublink technique.

--
regards,
Andrei Lepikhov
Postgres Professional