Обсуждение: BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
От
PG Bug reporting form
Дата:
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)
```
Re: BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
От
Andrei Lepikhov
Дата:
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
Re: BUG #19385: Normal SELECT generates an ineffecifient query plan compare to the prepared SELECT.
От
Andrei Lepikhov
Дата:
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
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