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

The following bug has been logged on the website:

Bug reference: 19385
Logged by: Chi Zhang
Email address: 798604270(at)qq(dot)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.

```
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=154.25..155.25 rows=100 width=14) (actual time=4.110..4.125
rows=100.00 loops=1)
Buffers: shared hit=2
-> HashAggregate (cost=154.25..155.25 rows=100 width=14) (actual
time=4.109..4.118 rows=100.00 loops=1)
Group Key: t3.c0
Batches: 1 Memory Usage: 32kB
Buffers: shared hit=2
-> Nested Loop (cost=0.00..129.25 rows=10000 width=14) (actual
time=0.010..1.297 rows=10000.00 loops=1)
Buffers: shared hit=2
-> Seq Scan on t2 (cost=0.00..2.00 rows=100 width=0)
(actual time=0.003..0.008 rows=100.00 loops=1)
Buffers: shared hit=1
-> Materialize (cost=0.00..2.50 rows=100 width=14) (actual
time=0.000..0.004 rows=100.00 loops=100)
Storage: Memory Maximum Storage: 20kB
Buffers: shared hit=1
-> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=14)
(actual time=0.002..0.007 rows=100.00 loops=1)
Buffers: shared hit=1
Planning:
Buffers: shared hit=50
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=0.14..16.32 rows=10 width=14) (actual time=0.018..2.616
rows=100.00 loops=1)
Buffers: shared hit=3
-> Unique (cost=0.14..161.89 rows=100 width=14) (actual
time=0.018..2.608 rows=100.00 loops=1)
Buffers: shared hit=3
-> Nested Loop (cost=0.14..136.89 rows=10000 width=14) (actual
time=0.017..1.237 rows=10000.00 loops=1)
Buffers: shared hit=3
-> Index Only Scan using i2 on t3 (cost=0.14..9.64 rows=100
width=14) (actual time=0.009..0.018 rows=100.00 loops=1)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=2
-> Materialize (cost=0.00..2.50 rows=100 width=0) (actual
time=0.000..0.004 rows=100.00 loops=100)
Storage: Memory Maximum Storage: 19kB
Buffers: shared hit=1
-> Seq Scan on t2 (cost=0.00..2.00 rows=100 width=0)
(actual time=0.005..0.010 rows=100.00 loops=1)
Buffers: shared hit=1
Planning Time: 0.041 ms
Execution Time: 2.629 ms
(17 rows)
```