Message-ID: <e405c80a-49ee-4a37-827d-d53b5189ad3e@gmail.com>
Date: Tue, 24 Dec 2024 09:57:31 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18751: Sub-optimal UNION ALL plan
To: Tom Lane <tgl@sss.pgh.pa.us>, Kirill Reshke <reshkekirill@gmail.com>
Cc: pingw33n@gmail.com, pgsql-bugs@lists.postgresql.org
References: <18751-887c7bf4e67e65a7@postgresql.org>
 <CALdSSPg3Dvw1oy+AJgUX15hDgFHYkjt7w5gGfVABt1vbSNewUg@mail.gmail.com>
 <1347038.1734967136@sss.pgh.pa.us>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <1347038.1734967136@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/e405c80a-49ee-4a37-827d-d53b5189ad3e%40gmail.com>
Precedence: bulk

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