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

On 12/23/24 22:18, Tom Lane wrote:
> Kirill Reshke <reshkekirill@gmail.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