BUG #19386: Unnecessary Sort in query plan for SELECT literal with ORDER BY - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      19386
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 queries. One is a
normal SELECT, and the other is a prepared SELECT. In the query plan of the
normal SELECT, there is an unnecessary Sort, which causes it to be slower
than the prepared SELECT. In general, the prepared SELECT should be slower
than the normal SELECT, as its query plan is suboptimal. So there maybe
potential opportunities for further optimization in the query planning of
normal SELECT statements.

```
CREATE UNLOGGED TABLE IF NOT EXISTS t0(c0 DECIMAL  DEFAULT
(0.941408570867201) NULL, c1 boolean  PRIMARY KEY);
CREATE TEMPORARY TABLE IF NOT EXISTS t1(LIKE t0);
CREATE UNLOGGED TABLE t3(LIKE t0);
CREATE TEMPORARY TABLE IF NOT EXISTS t5(c0 integer , c1 money , c2 REAL
PRIMARY KEY) USING heap;
INSERT INTO t1 (c0, c1) VALUES (0.1, true), (0.2, false) ON CONFLICT DO
NOTHING;
INSERT INTO t3 (c0, c1) VALUES (0.3, true), (0.4, false) ON CONFLICT DO
NOTHING;
INSERT INTO t5 (c0, c1, c2) SELECT (random() * 10000)::int, (random() *
1000)::numeric::money, (random() + i)::real FROM generate_series(1, 10000)
i;
ANALYZE t1, t3, t5;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT '178.229.172.255'::inet FROM
t1*, t5, ONLY t3 WHERE (('24186777'::text COLLATE "pg_c_utf8")!~'8E'::text)
IN (t1.c1) ORDER BY t1.c1;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1844.84..1894.84 rows=20000 width=33) (actual time=8.188..9.059
rows=20000.00 loops=1)
   Sort Key: t1.c1
   Sort Method: quicksort  Memory: 1237kB
   Buffers: shared hit=1, local hit=65
   ->  Nested Loop  (cost=0.00..416.06 rows=20000 width=33) (actual
time=0.021..5.233 rows=20000.00 loops=1)
         Buffers: shared hit=1, local hit=65
         ->  Seq Scan on t5  (cost=0.00..164.00 rows=10000 width=0) (actual
time=0.010..0.668 rows=10000.00 loops=1)
               Buffers: local hit=64
         ->  Materialize  (cost=0.00..2.07 rows=2 width=1) (actual
time=0.000..0.000 rows=2.00 loops=10000)
               Storage: Memory  Maximum Storage: 17kB
               Buffers: shared hit=1, local hit=1
               ->  Nested Loop  (cost=0.00..2.06 rows=2 width=1) (actual
time=0.006..0.008 rows=2.00 loops=1)
                     Buffers: shared hit=1, local hit=1
                     ->  Seq Scan on t1  (cost=0.00..1.02 rows=1 width=1)
(actual time=0.003..0.003 rows=1.00 loops=1)
                           Filter: c1
                           Rows Removed by Filter: 1
                           Buffers: local hit=1
                     ->  Seq Scan on t3  (cost=0.00..1.02 rows=2 width=0)
(actual time=0.003..0.003 rows=2.00 loops=1)
                           Buffers: shared hit=1
 Planning:
   Buffers: shared hit=48, local hit=1
 Planning Time: 0.315 ms
 Execution Time: 10.281 ms
(23 rows)


PREPARE prepare_query (inet, text, text) AS SELECT ALL $1 FROM t1*, t5*,
ONLY t3 WHERE (($2 COLLATE "pg_c_utf8")!~$3) IN (t1.c1) ORDER BY t1.c1;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) EXECUTE
prepare_query('178.229.172.255', '24186777', '8E');
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..416.08 rows=20000 width=33) (actual
time=0.012..4.668 rows=20000.00 loops=1)
   Buffers: shared hit=1, local hit=65
   ->  Seq Scan on t5  (cost=0.00..164.00 rows=10000 width=0) (actual
time=0.004..0.538 rows=10000.00 loops=1)
         Buffers: local hit=64
   ->  Materialize  (cost=0.00..2.08 rows=2 width=1) (actual
time=0.000..0.000 rows=2.00 loops=10000)
         Storage: Memory  Maximum Storage: 17kB
         Buffers: shared hit=1, local hit=1
         ->  Nested Loop  (cost=0.00..2.07 rows=2 width=1) (actual
time=0.006..0.008 rows=2.00 loops=1)
               Buffers: shared hit=1, local hit=1
               ->  Seq Scan on t1  (cost=0.00..1.03 rows=1 width=1) (actual
time=0.004..0.005 rows=1.00 loops=1)
                     Filter: ((($2)::text !~ $3) = c1)
                     Rows Removed by Filter: 1
                     Buffers: local hit=1
               ->  Seq Scan on t3  (cost=0.00..1.02 rows=2 width=0) (actual
time=0.001..0.002 rows=2.00 loops=1)
                     Buffers: shared hit=1
 Planning Time: 0.052 ms
 Execution Time: 5.531 ms
(17 rows)
```