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

On 12/23/24 22:18, Tom Lane wrote:
> Kirill Reshke <reshkekirill(at)gmail(dot)com> writes:
>> I reproduced this on REL_16_STABLE, HEAD & REL_13_STABLE, so this is
>> not really a bug, just a missing optimization?
>
> 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.
BTW, SQL Server also uses bad variant of the plan for this query, or I
just don't know how to cook that soup properly.

--
regards, Andrei Lepikhov