MIME-Version: 1.0
References: <19385-b384f1bd063ec84a@postgresql.org>
In-Reply-To: <19385-b384f1bd063ec84a@postgresql.org>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 22 Jan 2026 14:31:35 +1300
Message-ID: <CAApHDvqoKO7jTDoftvzWd2VBouCsYDHwa690xk8xMXPErs8Oqw@mail.gmail.com>
Subject: Re: BUG #19385: Normal SELECT generates an ineffecifient query plan
 compare to the prepared SELECT.
To: 798604270@qq.com, pgsql-bugs@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvqoKO7jTDoftvzWd2VBouCsYDHwa690xk8xMXPErs8Oqw%40mail.gmail.com>
Precedence: bulk

On Wed, 21 Jan 2026 at 23:44, PG Bug reporting form
<noreply@postgresql.org> wrote:
> 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.

Ultimately, the SeqScan -> NestLoop -> Hash Agg plan is only winning
over the Index Only Scan -> NestLoop -> Unique due to the planner's
estimated costs for the Nested Loop's inner scan. If you find the cost
balance between Seq Scan vs Index [Only] Scan isn't accurate for your
hardware, then adjust random_page_cost.

The planner not choosing the fastest to execute plan all the time does
not constitute a bug. You may want to consult the documentation in
[1]. [2] may also be useful to you.

David

[1] https://www.postgresql.org/docs/18/runtime-config-query.html#GUC-RANDOM-PAGE-COST
[2] https://www.postgresql.org/docs/18/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE