Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19385: Normal SELECT generates an ineffecifient query plan
 compare to the prepared SELECT.
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 798604270@qq.com
Reply-To: 798604270@qq.com, pgsql-bugs@lists.postgresql.org
Date: Wed, 21 Jan 2026 06:53:11 +0000
Message-ID: <19385-b384f1bd063ec84a@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19385-b384f1bd063ec84a%40postgresql.org>
Precedence: bulk

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:       =20

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.

```
CREATE TABLE IF NOT EXISTS t2(c0 int4range );
CREATE TABLE IF NOT EXISTS t3(LIKE t2 INCLUDING CONSTRAINTS);
CREATE UNIQUE INDEX i2 ON ONLY t3(c0);
INSERT INTO t2 (c0) SELECT int4range(start_val, start_val + (random() * 50 +
1)::int) FROM ( SELECT (random() * 10000)::int AS start_val FROM
generate_series(1, 100)) AS random_data;
INSERT INTO t3 (c0) SELECT int4range(start_val, start_val + (random() * 50 +
1)::int) FROM ( SELECT (random() * 10000)::int AS start_val FROM
generate_series(1, 100)) AS random_data;
VACUUM ANALYZE t2, t3;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT DISTINCT t3.c0 FROM t2*, t3
LIMIT (4016369050220296505)::int8;
                                                         QUERY PLAN
---------------------------------------------------------------------------=
-------------------------------------------------
 Limit  (cost=3D154.25..155.25 rows=3D100 width=3D14) (actual time=3D4.110.=
.4.125
rows=3D100.00 loops=3D1)
   Buffers: shared hit=3D2
   ->  HashAggregate  (cost=3D154.25..155.25 rows=3D100 width=3D14) (actual
time=3D4.109..4.118 rows=3D100.00 loops=3D1)
         Group Key: t3.c0
         Batches: 1  Memory Usage: 32kB
         Buffers: shared hit=3D2
         ->  Nested Loop  (cost=3D0.00..129.25 rows=3D10000 width=3D14) (ac=
tual
time=3D0.010..1.297 rows=3D10000.00 loops=3D1)
               Buffers: shared hit=3D2
               ->  Seq Scan on t2  (cost=3D0.00..2.00 rows=3D100 width=3D0)
(actual time=3D0.003..0.008 rows=3D100.00 loops=3D1)
                     Buffers: shared hit=3D1
               ->  Materialize  (cost=3D0.00..2.50 rows=3D100 width=3D14) (=
actual
time=3D0.000..0.004 rows=3D100.00 loops=3D100)
                     Storage: Memory  Maximum Storage: 20kB
                     Buffers: shared hit=3D1
                     ->  Seq Scan on t3  (cost=3D0.00..2.00 rows=3D100 widt=
h=3D14)
(actual time=3D0.002..0.007 rows=3D100.00 loops=3D1)
                           Buffers: shared hit=3D1
 Planning:
   Buffers: shared hit=3D50
 Planning Time: 0.166 ms
 Execution Time: 4.166 ms
(19 rows)


PREPARE prepare_query (int8) AS SELECT DISTINCT t3.c0 FROM t2, t3* LIMIT $1;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) EXECUTE
prepare_query(4016369050220296505);
                                                              QUERY PLAN
---------------------------------------------------------------------------=
-----------------------------------------------------------
 Limit  (cost=3D0.14..16.32 rows=3D10 width=3D14) (actual time=3D0.018..2.6=
16
rows=3D100.00 loops=3D1)
   Buffers: shared hit=3D3
   ->  Unique  (cost=3D0.14..161.89 rows=3D100 width=3D14) (actual
time=3D0.018..2.608 rows=3D100.00 loops=3D1)
         Buffers: shared hit=3D3
         ->  Nested Loop  (cost=3D0.14..136.89 rows=3D10000 width=3D14) (ac=
tual
time=3D0.017..1.237 rows=3D10000.00 loops=3D1)
               Buffers: shared hit=3D3
               ->  Index Only Scan using i2 on t3  (cost=3D0.14..9.64 rows=
=3D100
width=3D14) (actual time=3D0.009..0.018 rows=3D100.00 loops=3D1)
                     Heap Fetches: 0
                     Index Searches: 1
                     Buffers: shared hit=3D2
               ->  Materialize  (cost=3D0.00..2.50 rows=3D100 width=3D0) (a=
ctual
time=3D0.000..0.004 rows=3D100.00 loops=3D100)
                     Storage: Memory  Maximum Storage: 19kB
                     Buffers: shared hit=3D1
                     ->  Seq Scan on t2  (cost=3D0.00..2.00 rows=3D100 widt=
h=3D0)
(actual time=3D0.005..0.010 rows=3D100.00 loops=3D1)
                           Buffers: shared hit=3D1
 Planning Time: 0.041 ms
 Execution Time: 2.629 ms
(17 rows)
```