Re: BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.

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!

-- 
regards, Andrei Lepikhov,
pgEdge