pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
4+ messages / 3 participants
[nested] [flat]
* BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
@ 2026-01-21 06:53 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2026-01-21 06:53 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 798604270@qq.com
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:
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)
```
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
@ 2026-01-21 11:57 Andrei Lepikhov <lepihov@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 4+ messages in thread
From: Andrei Lepikhov @ 2026-01-21 11:57 UTC (permalink / raw)
To: 798604270@qq.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
On 21/1/26 07:53, PG Bug reporting form wrote:
> 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:
>
> 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.
There is no equivalence.
In the 'simple query' case with such a big LIMIT, you force the planner
to choose a full-scan-optimal path. In the generic case, the planner
uses 'magic constants' to provide some glue during planning, and LIMIT
10 is one of them, as we see from your example.
I once proposed an alternative solution: use a 'reference' value to
create a generic plan, as SQL Server does. If you're really interested
in such optimisation, support it!
--
regards, Andrei Lepikhov,
pgEdge
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
@ 2026-01-21 15:30 Andrei Lepikhov <lepihov@gmail.com>
parent: Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Andrei Lepikhov @ 2026-01-21 15:30 UTC (permalink / raw)
To: 798604270@qq.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
On 21/1/26 12:57, Andrei Lepikhov wrote:
> On 21/1/26 07:53, PG Bug reporting form wrote:
>> 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:
>>
>> 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.
> There is no equivalence.
>
> In the 'simple query' case with such a big LIMIT, you force the planner
> to choose a full-scan-optimal path. In the generic case, the planner
> uses 'magic constants' to provide some glue during planning, and LIMIT
> 10 is one of them, as we see from your example.
>
> I once proposed an alternative solution: use a 'reference' value to
> create a generic plan, as SQL Server does. If you're really interested
> in such optimisation, support it!
>
That is [1] the reference to the patch that allows you to choose
specific constants when building a generic plan.
[1]
https://www.postgresql.org/message-id/flat/19919494-92a8-4905-a250-6cf17b89f7c3@gmail.com
--
regards, Andrei Lepikhov,
pgEdge
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
@ 2026-01-22 01:31 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 4+ messages in thread
From: David Rowley @ 2026-01-22 01:31 UTC (permalink / raw)
To: 798604270@qq.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-01-22 01:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-21 06:53 BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT. PG Bug reporting form <noreply@postgresql.org>
2026-01-21 11:57 ` Andrei Lepikhov <lepihov@gmail.com>
2026-01-21 15:30 ` Andrei Lepikhov <lepihov@gmail.com>
2026-01-22 01:31 ` David Rowley <dgrowleyml@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox