Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19386: Unnecessary Sort in query plan for SELECT literal with ORDER BY
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 08:26:39 +0000
Message-ID: <19386-be594598921461b9@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: <https://www.postgresql.org/message-id/19386-be594598921461b9%40postgresql.org>
Precedence: bulk

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

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=3D1844.84..1894.84 rows=3D20000 width=3D33) (actual time=3D8.1=
88..9.059
rows=3D20000.00 loops=3D1)
   Sort Key: t1.c1
   Sort Method: quicksort  Memory: 1237kB
   Buffers: shared hit=3D1, local hit=3D65
   ->  Nested Loop  (cost=3D0.00..416.06 rows=3D20000 width=3D33) (actual
time=3D0.021..5.233 rows=3D20000.00 loops=3D1)
         Buffers: shared hit=3D1, local hit=3D65
         ->  Seq Scan on t5  (cost=3D0.00..164.00 rows=3D10000 width=3D0) (=
actual
time=3D0.010..0.668 rows=3D10000.00 loops=3D1)
               Buffers: local hit=3D64
         ->  Materialize  (cost=3D0.00..2.07 rows=3D2 width=3D1) (actual
time=3D0.000..0.000 rows=3D2.00 loops=3D10000)
               Storage: Memory  Maximum Storage: 17kB
               Buffers: shared hit=3D1, local hit=3D1
               ->  Nested Loop  (cost=3D0.00..2.06 rows=3D2 width=3D1) (act=
ual
time=3D0.006..0.008 rows=3D2.00 loops=3D1)
                     Buffers: shared hit=3D1, local hit=3D1
                     ->  Seq Scan on t1  (cost=3D0.00..1.02 rows=3D1 width=
=3D1)
(actual time=3D0.003..0.003 rows=3D1.00 loops=3D1)
                           Filter: c1
                           Rows Removed by Filter: 1
                           Buffers: local hit=3D1
                     ->  Seq Scan on t3  (cost=3D0.00..1.02 rows=3D2 width=
=3D0)
(actual time=3D0.003..0.003 rows=3D2.00 loops=3D1)
                           Buffers: shared hit=3D1
 Planning:
   Buffers: shared hit=3D48, local hit=3D1
 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=3D0.00..416.08 rows=3D20000 width=3D33) (actual
time=3D0.012..4.668 rows=3D20000.00 loops=3D1)
   Buffers: shared hit=3D1, local hit=3D65
   ->  Seq Scan on t5  (cost=3D0.00..164.00 rows=3D10000 width=3D0) (actual
time=3D0.004..0.538 rows=3D10000.00 loops=3D1)
         Buffers: local hit=3D64
   ->  Materialize  (cost=3D0.00..2.08 rows=3D2 width=3D1) (actual
time=3D0.000..0.000 rows=3D2.00 loops=3D10000)
         Storage: Memory  Maximum Storage: 17kB
         Buffers: shared hit=3D1, local hit=3D1
         ->  Nested Loop  (cost=3D0.00..2.07 rows=3D2 width=3D1) (actual
time=3D0.006..0.008 rows=3D2.00 loops=3D1)
               Buffers: shared hit=3D1, local hit=3D1
               ->  Seq Scan on t1  (cost=3D0.00..1.03 rows=3D1 width=3D1) (=
actual
time=3D0.004..0.005 rows=3D1.00 loops=3D1)
                     Filter: ((($2)::text !~ $3) =3D c1)
                     Rows Removed by Filter: 1
                     Buffers: local hit=3D1
               ->  Seq Scan on t3  (cost=3D0.00..1.02 rows=3D2 width=3D0) (=
actual
time=3D0.001..0.002 rows=3D2.00 loops=3D1)
                     Buffers: shared hit=3D1
 Planning Time: 0.052 ms
 Execution Time: 5.531 ms
(17 rows)
```