Re: BUG #18751: Sub-optimal UNION ALL plan

Andrei Lepikhov <lepihov(at)gmail(dot)com> writes:
> On 12/23/24 22:18, Tom Lane wrote:
>> Yeah. I believe what is happening is that the addition of the WHERE
>> clause forces the second sub-SELECT to be planned as an independent
>> query. And that level of planning has no idea that it might be
>> useful to produce a result ordered by "t", so it doesn't generate
>> a sub-plan that can do that. Then the best that the outer level
>> can do is sort after-the-fact.

> I didn't discover the case deeply yet, but it looks similar to your
> improvement of CTEs in a65724d.

No, that was about passing information the other way: from the
subquery's planning results out to the outer level. We would
need to do that, sure, but first we have to pass info down to
the subquery to say "results sorted like this could be useful".

As of v17 there is some mechanism to do that (see the setops
argument to subquery_planner), but I now realize that that
was designed in a really short-sighted fashion: it *only*
works with SetOperation nodes. We'd have to refactor that
so that what the upper query passes down is desired pathkeys,
or at least something closer to a pathkey than a SetOperation.

Another thing that's going on here is that the reason the
WHERE clause makes a difference is that it prevents flattening
the sub-query, per is_safe_append_member():

* Also, the child can't have any WHERE quals because there's no place to
* put them in an appendrel. (This is a bit annoying...)

I've never been entirely sure whether it is worth improving that.
Doing so would fix this particular issue, but there are plenty
of other un-flattenable sub-queries, so the other thing has a
potential for improving matters more widely.

regards, tom lane