From: Andrei Lepikhov <lepihov@gmail.com>
To: 798604270@qq.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Subject: Re: BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
Date: Wed, 21 Jan 2026 12:57:13 +0100
Message-ID: <c8d276c5-9a50-4bfa-b06b-1d5dc84f8f30@gmail.com> (raw)
In-Reply-To: <19385-b384f1bd063ec84a@postgresql.org>
References: <19385-b384f1bd063ec84a@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: lepihov@gmail.com, noreply@postgresql.org
Subject: Re: BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
In-Reply-To: <c8d276c5-9a50-4bfa-b06b-1d5dc84f8f30@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox