Message-ID: <71c18447-5545-401d-b1aa-4f73ae8cb5aa@gmail.com>
Date: Wed, 21 Jan 2026 16:30:57 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #19385: Normal SELECT generates an ineffecifient query plan
 compare to the prepared SELECT.
From: Andrei Lepikhov <lepihov@gmail.com>
To: 798604270@qq.com, pgsql-bugs@lists.postgresql.org,
 PG Bug reporting form <noreply@postgresql.org>
References: <19385-b384f1bd063ec84a@postgresql.org>
 <c8d276c5-9a50-4bfa-b06b-1d5dc84f8f30@gmail.com>
Content-Language: en-US
In-Reply-To: <c8d276c5-9a50-4bfa-b06b-1d5dc84f8f30@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/71c18447-5545-401d-b1aa-4f73ae8cb5aa%40gmail.com>
Precedence: bulk

On 21/1/26 12:57, Andrei Lepikhov wrote:
> On 21/1/26 07:53, PG Bug reporting form wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      19385
>> Logged by:          Chi Zhang
>> Email address:      798604270@qq.com
>> PostgreSQL version: 18.1
>> Operating system:   ubuntu 24.04 with docker
>> Description:
>>
>> Hi,
>>
>> In the following test case, there are two equivalent simple SELECTs with
>> DISTINCT, however, the normal SELECT is slower than the prepared SELECT.
>> Given that prepared SELECT statements typically generate suboptimal query
>> plans due to the presence of unknown literals, one would expect prepared
>> SELECT to be slower than normal SELECT. However, in this example, the
>> prepared SELECT executes faster, suggesting that there may still be 
>> room for
>> optimization in the query plan generation for normal SELECT.
> There is no equivalence.
> 
> In the 'simple query' case with such a big LIMIT, you force the planner 
> to choose a full-scan-optimal path. In the generic case, the planner 
> uses 'magic constants' to provide some glue during planning, and LIMIT 
> 10 is one of them, as we see from your example.
> 
> I once proposed an alternative solution: use a 'reference' value to 
> create a generic plan, as SQL Server does. If you're really interested 
> in such optimisation, support it!
> 

That is [1] the reference to the patch that allows you to choose 
specific constants when building a generic plan.

[1] 
https://www.postgresql.org/message-id/flat/19919494-92a8-4905-a250-6cf17b89f7c3@gmail.com

-- 
regards, Andrei Lepikhov,
pgEdge