BUG #19357: PostgreSQL generates a custom plan that performs worse than the generic plan for a certain query.
The following bug has been logged on the website:
Bug reference: 19357
Logged by: Chi Zhang
Email address: 798604270@qq.com
PostgreSQL version: 17.6
Operating system: ubuntu 24.04 with docker
Description:
Hi,
In the following test case, I found that the non-prepared SELECT statement
is much slower than the equivalent prepared SELECT statement with a generic
query plan. In general, the query plan generated for prepared statements is
not optimal, so I believe there is still room to further optimize the query
plan of normal queries.
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 smallint) USING heap WITH (parallel_workers=852);
CREATE TABLE t2(LIKE t0);
CREATE TABLE t5(LIKE t0);
INSERT INTO t5(c0) VALUES(1::INT8);
INSERT INTO t0(c0) VALUES(1::int8);
CREATE INDEX i0 ON t5(c0 NULLS FIRST);
EXPLAIN (ANALYZE, FORMAT JSON) SELECT DISTINCT t5.c0 FROM t5, t2, t0*,
(SELECT ALL t2.c0 as c0 FROM t2 WHERE
((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS BOOLEAN)))
LIMIT (7461843809418659830)::int8) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR
TO ''::text);
PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT t5.c0
FROM t5, t2*, t0*, (SELECT t2.c0 as c0 FROM t2 WHERE ((CAST((($1)||($2)) AS
BOOLEAN))) LIMIT $3) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR TO $4);
EXPLAIN (ANALYZE, FORMAT JSON) EXECUTE prepare_query('',
'[142654042,1443301405)'::int4range, 7461843809418659830, '');
```
This is the output:
```
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
[
+
{
+
"Plan": {
+
"Node Type": "Unique",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 522496.96,
+
"Total Cost": 303238953.76,
+
"Plan Rows": 1,
+
"Plan Width": 2,
+
"Actual Startup Time": 64.759,
+
"Actual Total Time": 64.801,
+
"Actual Rows": 0,
+
"Actual Loops": 1,
+
"Plans": [
+
{
+
"Node Type": "Nested Loop",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Join Type": "Inner",
+
"Startup Cost": 522496.96,
+
"Total Cost": 252929833.76,
+
"Plan Rows": 20123648000,
+
"Plan Width": 2,
+
"Actual Startup Time": 64.758,
+
"Actual Total Time": 64.800,
+
"Actual Rows": 0,
+
"Actual Loops": 1,
+
"Inner Unique": false,
+
"Plans": [
+
{
+
"Node Type": "Gather Merge",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 522496.94,
+
"Total Cost": 1384162.54,
+
"Plan Rows": 7398400,
+
"Plan Width": 2,
+
"Actual Startup Time": 64.758,
+
"Actual Total Time": 64.799,
+
"Actual Rows": 0,
+
"Actual Loops": 1,
+
"Workers Planned": 2,
+
"Workers Launched": 2,
+
"Plans": [
+
{
+
"Node Type": "Sort",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 521496.92,
+
"Total Cost": 529203.59,
+
"Plan Rows": 3082667,
+
"Plan Width": 2,
+
"Actual Startup Time": 21.585,
+
"Actual Total Time": 21.585,
+
"Actual Rows": 0,
+
"Actual Loops": 3,
+
"Sort Key": ["t5.c0"],
+
"Sort Method": "quicksort",
+
"Sort Space Used": 25,
+
"Sort Space Type": "Memory",
+
"Workers": [
+
{
+
"Worker Number": 0,
+
"Sort Method": "quicksort",
+
"Sort Space Used": 25,
+
"Sort Space Type": "Memory"
+
},
+
{
+
"Worker Number": 1,
+
"Sort Method": "quicksort",
+
"Sort Space Used": 25,
+
"Sort Space Type": "Memory"
+
}
+
],
+
"Plans": [
+
{
+
"Node Type": "Nested Loop",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Join Type": "Inner",
+
"Startup Cost": 0.00,
+
"Total Cost": 104956.96,
+
"Plan Rows": 3082667,
+
"Plan Width": 2,
+
"Actual Startup Time": 21.533,
+
"Actual Total Time": 21.534,
+
"Actual Rows": 0,
+
"Actual Loops": 3,
+
"Inner Unique": false,
+
"Workers": [
+
],
+
"Plans": [
+
{
+
"Node Type": "Seq Scan",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": true,
+
"Async Capable": false,
+
"Relation Name": "t0",
+
"Alias": "t0",
+
"Startup Cost": 0.00,
+
"Total Cost": 21.33,
+
"Plan Rows": 1133,
+
"Plan Width": 0,
+
"Actual Startup Time": 0.001,
+
"Actual Total Time": 0.002,
+
"Actual Rows": 0,
+
"Actual Loops": 3,
+
"Workers": [
+
]
+
},
+
{
+
"Node Type": "Nested Loop",
+
"Parent Relationship": "Inner",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Join Type": "Inner",
+
"Startup Cost": 0.00,
+
"Total Cost": 65.42,
+
"Plan Rows": 2720,
+
"Plan Width": 2,
+
"Actual Startup Time": 64.593,
+
"Actual Total Time": 64.593,
+
"Actual Rows": 0,
+
"Actual Loops": 1,
+
"Inner Unique": false,
+
"Workers": [
+
],
+
"Plans": [
+
{
+
"Node Type": "Seq Scan",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Relation Name": "t5",
+
"Alias": "t5",
+
"Startup Cost": 0.00,
+
"Total Cost": 1.02,
+
"Plan Rows": 1,
+
"Plan Width": 2,
+
"Actual Startup Time": 64.592,
+
"Actual Total Time": 64.593,
+
"Actual Rows": 0,
+
"Actual Loops": 1,
+
"Filter": "(((c0)::character varying)::text ~
'^(?:)$'::text)", +
"Rows Removed by Filter": 1,
+
"Workers": [
+
]
+
},
+
{
+
"Node Type": "Seq Scan",
+
"Parent Relationship": "Inner",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Relation Name": "t2",
+
"Alias": "t2",
+
"Startup Cost": 0.00,
+
"Total Cost": 37.20,
+
"Plan Rows": 2720,
+
"Plan Width": 0,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"Workers": [
+
]
+
}
+
]
+
}
+
]
+
}
+
]
+
}
+
]
+
},
+
{
+
"Node Type": "Materialize",
+
"Parent Relationship": "Inner",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 0.01,
+
"Total Cost": 78.01,
+
"Plan Rows": 2720,
+
"Plan Width": 0,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"Plans": [
+
{
+
"Node Type": "Subquery Scan",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Alias": "subq",
+
"Startup Cost": 0.01,
+
"Total Cost": 64.41,
+
"Plan Rows": 2720,
+
"Plan Width": 0,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"Plans": [
+
{
+
"Node Type": "Limit",
+
"Parent Relationship": "Subquery",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 0.01,
+
"Total Cost": 37.21,
+
"Plan Rows": 2720,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"Plans": [
+
{
+
"Node Type": "Result",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 0.01,
+
"Total Cost": 37.21,
+
"Plan Rows": 2720,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"One-Time Filter": "((''::text ||
('[142654042,1443301405)'::int4range)::text))::boolean",+
"Plans": [
+
{
+
"Node Type": "Seq Scan",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Relation Name": "t2",
+
"Alias": "t2_1",
+
"Startup Cost": 0.01,
+
"Total Cost": 37.21,
+
"Plan Rows": 2720,
+
"Plan Width": 0,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0
+
}
+
]
+
}
+
]
+
}
+
]
+
}
+
]
+
}
+
]
+
}
+
]
+
},
+
"Planning Time": 0.185,
+
"Triggers": [
+
],
+
"JIT": {
+
"Functions": 21,
+
"Options": {
+
"Inlining": true,
+
"Optimization": true,
+
"Expressions": true,
+
"Deforming": true
+
},
+
"Timing": {
+
"Generation": {
+
"Deform": 0.117,
+
"Total": 0.550
+
},
+
"Inlining": 34.385,
+
"Optimization": 18.477,
+
"Emission": 11.708,
+
"Total": 65.119
+
}
+
},
+
"Execution Time": 74.180
+
}
+
]
(1 row)PREPARE
QUERY PLAN
--------------------------------------------------------------------------------------------------
[
+
{
+
"Plan": {
+
"Node Type": "Aggregate",
+
"Strategy": "Hashed",
+
"Partial Mode": "Simple",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 30194812.07,
+
"Total Cost": 30194812.08,
+
"Plan Rows": 1,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.006,
+
"Actual Total Time": 0.007,
+
"Actual Rows": 0,
+
"Actual Loops": 1,
+
"Group Key": ["t5.c0"],
+
"Planned Partitions": 0,
+
"HashAgg Batches": 1,
+
"Peak Memory Usage": 24,
+
"Disk Usage": 0,
+
"Plans": [
+
{
+
"Node Type": "Nested Loop",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Join Type": "Inner",
+
"Startup Cost": 0.01,
+
"Total Cost": 25163900.07,
+
"Plan Rows": 2012364800,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.005,
+
"Actual Total Time": 0.006,
+
"Actual Rows": 0,
+
"Actual Loops": 1,
+
"Inner Unique": false,
+
"Plans": [
+
{
+
"Node Type": "Nested Loop",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Join Type": "Inner",
+
"Startup Cost": 0.01,
+
"Total Cost": 9296.07,
+
"Plan Rows": 739840,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.005,
+
"Actual Total Time": 0.005,
+
"Actual Rows": 0,
+
"Actual Loops": 1,
+
"Inner Unique": false,
+
"Plans": [
+
{
+
"Node Type": "Seq Scan",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Relation Name": "t2",
+
"Alias": "t2",
+
"Startup Cost": 0.00,
+
"Total Cost": 37.20,
+
"Plan Rows": 2720,
+
"Plan Width": 0,
+
"Actual Startup Time": 0.005,
+
"Actual Total Time": 0.005,
+
"Actual Rows": 0,
+
"Actual Loops": 1
+
},
+
{
+
"Node Type": "Materialize",
+
"Parent Relationship": "Inner",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 0.01,
+
"Total Cost": 11.55,
+
"Plan Rows": 272,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"Plans": [
+
{
+
"Node Type": "Nested Loop",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Join Type": "Inner",
+
"Startup Cost": 0.01,
+
"Total Cost": 10.19,
+
"Plan Rows": 272,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"Inner Unique": false,
+
"Plans": [
+
{
+
"Node Type": "Seq Scan",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Relation Name": "t5",
+
"Alias": "t5",
+
"Startup Cost": 0.00,
+
"Total Cost": 1.02,
+
"Plan Rows": 1,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"Filter": "(((c0)::character varying)::text ~
similar_to_escape($4))",+
"Rows Removed by Filter": 0
+
},
+
{
+
"Node Type": "Limit",
+
"Parent Relationship": "Inner",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 0.01,
+
"Total Cost": 3.73,
+
"Plan Rows": 272,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"Plans": [
+
{
+
"Node Type": "Result",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 0.01,
+
"Total Cost": 37.21,
+
"Plan Rows": 2720,
+
"Plan Width": 2,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"One-Time Filter": "(($1 ||
($2)::text))::boolean", +
"Plans": [
+
{
+
"Node Type": "Seq Scan",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Relation Name": "t2",
+
"Alias": "t2_1",
+
"Startup Cost": 0.01,
+
"Total Cost": 37.21,
+
"Plan Rows": 2720,
+
"Plan Width": 0,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0
+
}
+
]
+
}
+
]
+
}
+
]
+
}
+
]
+
}
+
]
+
},
+
{
+
"Node Type": "Materialize",
+
"Parent Relationship": "Inner",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Startup Cost": 0.00,
+
"Total Cost": 50.80,
+
"Plan Rows": 2720,
+
"Plan Width": 0,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0,
+
"Plans": [
+
{
+
"Node Type": "Seq Scan",
+
"Parent Relationship": "Outer",
+
"Parallel Aware": false,
+
"Async Capable": false,
+
"Relation Name": "t0",
+
"Alias": "t0",
+
"Startup Cost": 0.00,
+
"Total Cost": 37.20,
+
"Plan Rows": 2720,
+
"Plan Width": 0,
+
"Actual Startup Time": 0.000,
+
"Actual Total Time": 0.000,
+
"Actual Rows": 0,
+
"Actual Loops": 0
+
}
+
]
+
}
+
]
+
}
+
]
+
},
+
"Planning Time": 0.113,
+
"Triggers": [
+
],
+
"JIT": {
+
"Functions": 15,
+
"Options": {
+
"Inlining": true,
+
"Optimization": true,
+
"Expressions": true,
+
"Deforming": true
+
},
+
"Timing": {
+
"Generation": {
+
"Deform": 0.050,
+
"Total": 0.205
+
},
+
"Inlining": 0.000,
+
"Optimization": 0.000,
+
"Emission": 0.000,
+
"Total": 0.205
+
}
+
},
+
"Execution Time": 0.292
+
}
+
]
(1 row)
```In this example, the planning time plus execution time of the non-prepared
statement is much greater than that of the prepared statement.
Please simplify your test query as much as possible and use "text" format
in your explain results; those will improve your chances of getting a
useful reply. :)
Hi Greg Sabino Mullane,
Thanks for your work.
This is already the test case that I can simplify as much as possbile. I also generate the corresponding query plan in TEXT format.
In this test case, the prepared statement (with a generic plan) is much more efficitive than the normal SELECT (with a custom plan).
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 smallint) USING heap WITH (parallel_workers=852);
CREATE TABLE t2(LIKE t0);
CREATE TABLE t5(LIKE t0);
INSERT INTO t5(c0) VALUES(1::INT8);
INSERT INTO t0(c0) VALUES(1::int8);
CREATE INDEX i0 ON t5(c0 NULLS FIRST);
EXPLAIN (ANALYZE, FORMAT TEXT) SELECT DISTINCT t5.c0 FROM t5, t2, t0*, (SELECT ALL t2.c0 as c0 FROM t2 WHERE ((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS BOOLEAN))) LIMIT (7461843809418659830)::int8) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR TO ''::text);
PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT t5.c0 FROM t5, t2*, t0*, (SELECT t2.c0 as c0 FROM t2 WHERE ((CAST((($1)||($2)) AS BOOLEAN))) LIMIT $3) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR TO $4);
EXPLAIN (ANALYZE, FORMAT TEXT) EXECUTE prepare_query('', '[142654042,1443301405)'::int4range, 7461843809418659830, '');
```
This is the outputs:
```
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=522496.96..303238953.76 rows=1 width=2) (actual time=65.267..65.311 rows=0 loops=1)
-> Nested Loop (cost=522496.96..252929833.76 rows=20123648000 width=2) (actual time=65.267..65.310 rows=0 loops=1)
-> Gather Merge (cost=522496.94..1384162.54 rows=7398400 width=2) (actual time=65.266..65.309 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=521496.92..529203.59 rows=3082667 width=2) (actual time=21.743..21.744 rows=0 loops=3)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..104956.96 rows=3082667 width=2) (actual time=21.699..21.700 rows=0 loops=3)
-> Parallel Seq Scan on t0 (cost=0.00..21.33 rows=1133 width=0) (actual time=0.002..0.002 rows=0 loops=3)
-> Nested Loop (cost=0.00..65.42 rows=2720 width=2) (actual time=65.088..65.089 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (actual time=65.088..65.088 rows=0 loops=1)
Filter: (((c0)::character varying)::text ~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..37.20 rows=2720 width=0) (never executed)
-> Materialize (cost=0.01..78.01 rows=2720 width=0) (never executed)
-> Subquery Scan on subq (cost=0.01..64.41 rows=2720 width=0) (never executed)
-> Limit (cost=0.01..37.21 rows=2720 width=2) (never executed)
-> Result (cost=0.01..37.21 rows=2720 width=2) (never executed)
One-Time Filter: ((''::text || ('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..37.21 rows=2720 width=0) (never executed)
Planning Time: 0.195 ms
JIT:
Functions: 21
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.636 ms (Deform 0.114 ms), Inlining 34.664 ms, Optimization 18.423 ms, Emission 11.975 ms, Total 65.698 ms
Execution Time: 74.751 ms
(29 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=30194812.07..30194812.08 rows=1 width=2) (actual time=0.008..0.009 rows=0 loops=1)
Group Key: t5.c0
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.01..25163900.07 rows=2012364800 width=2) (actual time=0.007..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.01..9296.07 rows=739840 width=2) (actual time=0.007..0.007 rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..37.20 rows=2720 width=0) (actual time=0.007..0.007 rows=0 loops=1)
-> Materialize (cost=0.01..11.55 rows=272 width=2) (never executed)
-> Nested Loop (cost=0.01..10.19 rows=272 width=2) (never executed)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (never executed)
Filter: (((c0)::character varying)::text ~ similar_to_escape($4))
-> Limit (cost=0.01..3.73 rows=272 width=2) (never executed)
-> Result (cost=0.01..37.21 rows=2720 width=2) (never executed)
One-Time Filter: (($1 || ($2)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..37.21 rows=2720 width=0) (never executed)
-> Materialize (cost=0.00..50.80 rows=2720 width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..37.20 rows=2720 width=0) (never executed)
Planning Time: 0.110 ms
JIT:
Functions: 15
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.208 ms (Deform 0.053 ms), Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 0.208 ms
Execution Time: 0.289 ms
(22 rows)
```
Original
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: 2025-12-17 22:54
To: 798604270 <798604270@qq.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that performsworse than the generic plan for a certain query.
Please simplify your test query as much as possible and use "text" format in your explain results; those will improve your chances of getting a useful reply. :)
Greg Sabino Mullane <htamfids@gmail.com> writes:
Please simplify your test query as much as possible and use "text" format
in your explain results; those will improve your chances of getting a
useful reply. :)
More to the point: it's hard to get excited about optimizing a query
that was so obviously built by a fuzzer rather than corresponding to
any real-world use-case. We have finite manpower and we'd much rather
put it into solving users' real problems.
regards, tom lane
Hi Tom Lane,
I strongly agree with your point. My objective is not to make PostgreSQL capable of optimizing all queries. My intention is simply to note that, for this test case, PostgreSQL is fully capable of optimizing the query, as demonstrated by the generic plan. However, I am unsure why the performance of the custom plan actually degrades. I believe that understanding the reasons behind this performance degradation (or suboptimality) could be important. This facilitates PostgreSQL’s selection of the optimal query plan.
Best,
Chi
Original
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: 2025-12-17 23:39
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: 798604270 <798604270@qq.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that performs worse than the generic plan for a certain query.
Greg Sabino Mullane <htamfids@gmail.com> writes:
> Please simplify your test query as much as possible and use "text" format
> in your explain results; those will improve your chances of getting a
> useful reply. :)
More to the point: it's hard to get excited about optimizing a query
that was so obviously built by a fuzzer rather than corresponding to
any real-world use-case. We have finite manpower and we'd much rather
put it into solving users' real problems.
regards, tom lane
st 17. 12. 2025 v 16:17 odesílatel ZhangChi <798604270@qq.com> napsal:
Hi Greg Sabino Mullane,
Thanks for your work.
This is already the test case that I can simplify as much as possbile. I
also generate the corresponding query plan in TEXT format.In this test case, the prepared statement (with a generic plan) is much
more efficitive than the normal SELECT (with a custom plan).```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 smallint) USING heap WITH (parallel_workers=852);
CREATE TABLE t2(LIKE t0);
CREATE TABLE t5(LIKE t0);
INSERT INTO t5(c0) VALUES(1::INT8);
INSERT INTO t0(c0) VALUES(1::int8);
CREATE INDEX i0 ON t5(c0 NULLS FIRST);
EXPLAIN (ANALYZE, FORMAT TEXT) SELECT DISTINCT t5.c0 FROM t5, t2, t0*,
(SELECT ALL t2.c0 as c0 FROM t2
WHERE ((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS
BOOLEAN))) LIMIT (7461843809418659830)::int8) AS subq
WHERE ((t5.c0)::VARCHAR SIMILAR TO ''::text);
PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT
t5.c0 FROM t5, t2*, t0*, (SELECT t2.c0 as c0 FROM t2
WHERE ((CAST((($1)||($2)) AS BOOLEAN))) LIMIT $3) AS subq
WHERE ((t5.c0)::VARCHAR SIMILAR TO $4);
EXPLAIN (ANALYZE, FORMAT TEXT) EXECUTE prepare_query('',
'[142654042,1443301405)'::int4range, 7461843809418659830, '');```
This is the outputs:
```
QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=522496.96..303238953.76 rows=1 width=2) (actual
time=65.267..65.311 rows=0 loops=1)
-> Nested Loop (cost=522496.96..252929833.76 rows=20123648000
width=2) (actual time=65.267..65.310 rows=0 loops=1)
-> Gather Merge (cost=522496.94..1384162.54 rows=7398400
width=2) (actual time=65.266..65.309 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=521496.92..529203.59 rows=3082667 width=2)
(actual time=21.743..21.744 rows=0 loops=3)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..104956.96 rows=3082667
width=2) (actual time=21.699..21.700 rows=0 loops=3)
-> Parallel Seq Scan on t0 (cost=0.00..21.33
rows=1133 width=0) (actual time=0.002..0.002 rows=0 loops=3)
-> Nested Loop (cost=0.00..65.42 rows=2720
width=2) (actual time=65.088..65.089 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02
rows=1 width=2) (actual time=65.088..65.088 rows=0 loops=1)
Filter: (((c0)::character
varying)::text ~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..37.20
rows=2720 width=0) (never executed)
-> Materialize (cost=0.01..78.01 rows=2720 width=0) (never
executed)
-> Subquery Scan on subq (cost=0.01..64.41 rows=2720
width=0) (never executed)
-> Limit (cost=0.01..37.21 rows=2720 width=2)
(never executed)
-> Result (cost=0.01..37.21 rows=2720
width=2) (never executed)
One-Time Filter: ((''::text ||
('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1
(cost=0.01..37.21 rows=2720 width=0) (never executed)
Planning Time: 0.195 ms
JIT:
Functions: 21
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Timing: Generation 0.636 ms (Deform 0.114 ms), Inlining 34.664 ms,
Optimization 18.423 ms, Emission 11.975 ms, Total 65.698 ms
Execution Time: 74.751 ms
(29 rows)QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=30194812.07..30194812.08 rows=1 width=2) (actual
time=0.008..0.009 rows=0 loops=1)
Group Key: t5.c0
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.01..25163900.07 rows=2012364800 width=2)
(actual time=0.007..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.01..9296.07 rows=739840 width=2) (actual
time=0.007..0.007 rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..37.20 rows=2720 width=0)
(actual time=0.007..0.007 rows=0 loops=1)
-> Materialize (cost=0.01..11.55 rows=272 width=2) (never
executed)
-> Nested Loop (cost=0.01..10.19 rows=272 width=2)
(never executed)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1
width=2) (never executed)
Filter: (((c0)::character varying)::text
~ similar_to_escape($4))
-> Limit (cost=0.01..3.73 rows=272 width=2)
(never executed)
-> Result (cost=0.01..37.21 rows=2720
width=2) (never executed)
One-Time Filter: (($1 ||
($2)::text))::boolean
-> Seq Scan on t2 t2_1
(cost=0.01..37.21 rows=2720 width=0) (never executed)
-> Materialize (cost=0.00..50.80 rows=2720 width=0) (never
executed)
-> Seq Scan on t0 (cost=0.00..37.20 rows=2720 width=0)
(never executed)
Planning Time: 0.110 ms
JIT:
Functions: 15
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Timing: Generation 0.208 ms (Deform 0.053 ms), Inlining 0.000 ms,
Optimization 0.000 ms, Emission 0.000 ms, Total 0.208 ms
Execution Time: 0.289 ms
(22 rows)
```
There are brutal estimation errors - you missing ANALYZE after
initialization.
When there are too big estimation errors, the planner behaviour can be not
intuitive - and nobody should to expect good results
Regards
Pavel
Show quoted text
Original
------------------------------
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: 2025-12-17 22:54
To: 798604270 <798604270@qq.com>, pgsql-bugs <
pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that
performsworse than the generic plan for a certain query.Please simplify your test query as much as possible and use "text" format
in your explain results; those will improve your chances of getting a
useful reply. :)
Hi Pavel,
Thank you very much for your reply. I have two follow-up questions. First, before running ANALYZE, why does the generic plan perform much better than the custom plan? Second, after I ran ANALYZE, the performance of the custom plan improved significantly. However, even though the custom plan is now identical to the generic plan, its execution time is still about twice that of the generic plan. Why is this the case?
Best,
Chi
```
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.012..0.012 rows=0 loops=1)
-> Sort (cost=2.09..2.09 rows=1 width=2) (actual time=0.012..0.012 rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.00..1.03 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
Filter: (((c0)::character varying)::text ~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1 width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0) (never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never executed)
One-Time Filter: ((''::text || ('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01 rows=1 width=0) (never executed)
Planning Time: 0.143 ms
Execution Time: 0.033 ms
(18 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
-> Sort (cost=2.09..2.10 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
-> Nested Loop (cost=0.00..1.03 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (((c0)::character varying)::text ~ similar_to_escape($4))
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1 width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0) (never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never executed)
One-Time Filter: (($1 || ($2)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01 rows=1 width=0) (never executed)
Planning Time: 0.084 ms
Execution Time: 0.017 ms
(18 rows)
```
Original
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: 2025-12-17 23:53
To: ZhangChi <798604270@qq.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that performsworsethan the generic plan for a certain query.
st 17. 12. 2025 v 16:17 odesílatel ZhangChi <798604270@qq.com> napsal:
Hi Greg Sabino Mullane,
Thanks for your work.
This is already the test case that I can simplify as much as possbile. I also generate the corresponding query plan in TEXT format.
In this test case, the prepared statement (with a generic plan) is much more efficitive than the normal SELECT (with a custom plan).
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 smallint) USING heap WITH (parallel_workers=852);
CREATE TABLE t2(LIKE t0);
CREATE TABLE t5(LIKE t0);
INSERT INTO t5(c0) VALUES(1::INT8);
INSERT INTO t0(c0) VALUES(1::int8);
CREATE INDEX i0 ON t5(c0 NULLS FIRST);
EXPLAIN (ANALYZE, FORMAT TEXT) SELECT DISTINCT t5.c0 FROM t5, t2, t0*, (SELECT ALL t2.c0 as c0 FROM t2 WHERE ((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS BOOLEAN))) LIMIT (7461843809418659830)::int8) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR TO ''::text);
PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT t5.c0 FROM t5, t2*, t0*, (SELECT t2.c0 as c0 FROM t2 WHERE ((CAST((($1)||($2)) AS BOOLEAN))) LIMIT $3) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR TO $4);
EXPLAIN (ANALYZE, FORMAT TEXT) EXECUTE prepare_query('', '[142654042,1443301405)'::int4range, 7461843809418659830, '');
```
This is the outputs:
```
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=522496.96..303238953.76 rows=1 width=2) (actual time=65.267..65.311 rows=0 loops=1)
-> Nested Loop (cost=522496.96..252929833.76 rows=20123648000 width=2) (actual time=65.267..65.310 rows=0 loops=1)
-> Gather Merge (cost=522496.94..1384162.54 rows=7398400 width=2) (actual time=65.266..65.309 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=521496.92..529203.59 rows=3082667 width=2) (actual time=21.743..21.744 rows=0 loops=3)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..104956.96 rows=3082667 width=2) (actual time=21.699..21.700 rows=0 loops=3)
-> Parallel Seq Scan on t0 (cost=0.00..21.33 rows=1133 width=0) (actual time=0.002..0.002 rows=0 loops=3)
-> Nested Loop (cost=0.00..65.42 rows=2720 width=2) (actual time=65.088..65.089 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (actual time=65.088..65.088 rows=0 loops=1)
Filter: (((c0)::character varying)::text ~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..37.20 rows=2720 width=0) (never executed)
-> Materialize (cost=0.01..78.01 rows=2720 width=0) (never executed)
-> Subquery Scan on subq (cost=0.01..64.41 rows=2720 width=0) (never executed)
-> Limit (cost=0.01..37.21 rows=2720 width=2) (never executed)
-> Result (cost=0.01..37.21 rows=2720 width=2) (never executed)
One-Time Filter: ((''::text || ('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..37.21 rows=2720 width=0) (never executed)
Planning Time: 0.195 ms
JIT:
Functions: 21
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.636 ms (Deform 0.114 ms), Inlining 34.664 ms, Optimization 18.423 ms, Emission 11.975 ms, Total 65.698 ms
Execution Time: 74.751 ms
(29 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=30194812.07..30194812.08 rows=1 width=2) (actual time=0.008..0.009 rows=0 loops=1)
Group Key: t5.c0
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.01..25163900.07 rows=2012364800 width=2) (actual time=0.007..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.01..9296.07 rows=739840 width=2) (actual time=0.007..0.007 rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..37.20 rows=2720 width=0) (actual time=0.007..0.007 rows=0 loops=1)
-> Materialize (cost=0.01..11.55 rows=272 width=2) (never executed)
-> Nested Loop (cost=0.01..10.19 rows=272 width=2) (never executed)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (never executed)
Filter: (((c0)::character varying)::text ~ similar_to_escape($4))
-> Limit (cost=0.01..3.73 rows=272 width=2) (never executed)
-> Result (cost=0.01..37.21 rows=2720 width=2) (never executed)
One-Time Filter: (($1 || ($2)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..37.21 rows=2720 width=0) (never executed)
-> Materialize (cost=0.00..50.80 rows=2720 width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..37.20 rows=2720 width=0) (never executed)
Planning Time: 0.110 ms
JIT:
Functions: 15
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.208 ms (Deform 0.053 ms), Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 0.208 ms
Execution Time: 0.289 ms
(22 rows)
```
There are brutal estimation errors - you missing ANALYZE after initialization.
When there are too big estimation errors, the planner behaviour can be not intuitive - and nobody should to expect good results
Regards
Pavel
Original
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: 2025-12-17 22:54
To: 798604270 <798604270@qq.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that performsworse than the generic plan for a certain query.
Please simplify your test query as much as possible and use "text" format in your explain results; those will improve your chances of getting a useful reply. :)
Hi
st 17. 12. 2025 v 17:08 odesílatel ZhangChi <798604270@qq.com> napsal:
Hi Pavel,
Thank you very much for your reply. I have two follow-up questions. First,
before running ANALYZE, why does the generic plan perform much better than
the custom plan?
when you have wrong estimation - anything is possible
Second, after I ran ANALYZE, the performance of the custom plan improved
significantly. However, even though the custom plan is now identical to the
generic plan, its execution time is still about twice that of the generic
plan. Why is this the case?
can you send a output of EXPLAIN (ANALYZE, BUFFERS) ?
maybe data are in cache - maybe some different - the small times are not
too stable - you should to run query multiple times, and use an average
Show quoted text
Best,
Chi```
QUERY PLAN--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.012..0.012 rows=0
loops=1)
-> Sort (cost=2.09..2.09 rows=1 width=2) (actual time=0.012..0.012
rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual
time=0.008..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual
time=0.008..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.00..1.03 rows=1 width=2)
(actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1
width=2) (actual time=0.008..0.008 rows=0 loops=1)
Filter: (((c0)::character varying)::text
~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1
width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0)
(never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never
executed)
One-Time Filter: ((''::text ||
('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01
rows=1 width=0) (never executed)
Planning Time: 0.143 ms
Execution Time: 0.033 ms
(18 rows)QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.008..0.008 rows=0
loops=1)
-> Sort (cost=2.09..2.10 rows=1 width=2) (actual time=0.008..0.008
rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual
time=0.005..0.005 rows=0 loops=1)
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual
time=0.005..0.005 rows=0 loops=1)
-> Nested Loop (cost=0.00..1.03 rows=1 width=2)
(actual time=0.005..0.005 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1
width=2) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (((c0)::character varying)::text
~ similar_to_escape($4))
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1
width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0)
(never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never
executed)
One-Time Filter: (($1 || ($2)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01
rows=1 width=0) (never executed)
Planning Time: 0.084 ms
Execution Time: 0.017 ms
(18 rows)
```Original
------------------------------
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: 2025-12-17 23:53
To: ZhangChi <798604270@qq.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, pgsql-bugs <
pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that
performsworsethan the generic plan for a certain query.st 17. 12. 2025 v 16:17 odesílatel ZhangChi <798604270@qq.com> napsal:
Hi Greg Sabino Mullane,
Thanks for your work.
This is already the test case that I can simplify as much as possbile. I
also generate the corresponding query plan in TEXT format.In this test case, the prepared statement (with a generic plan) is much
more efficitive than the normal SELECT (with a custom plan).```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 smallint) USING heap WITH (parallel_workers=852);
CREATE TABLE t2(LIKE t0);
CREATE TABLE t5(LIKE t0);
INSERT INTO t5(c0) VALUES(1::INT8);
INSERT INTO t0(c0) VALUES(1::int8);
CREATE INDEX i0 ON t5(c0 NULLS FIRST);
EXPLAIN (ANALYZE, FORMAT TEXT) SELECT DISTINCT t5.c0 FROM t5, t2, t0*,
(SELECT ALL t2.c0 as c0 FROM t2
WHERE ((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS
BOOLEAN))) LIMIT (7461843809418659830)::int8) AS subq
WHERE ((t5.c0)::VARCHAR SIMILAR TO ''::text);
PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT
t5.c0 FROM t5, t2*, t0*, (SELECT t2.c0 as c0 FROM t2
WHERE ((CAST((($1)||($2)) AS BOOLEAN))) LIMIT $3) AS subq
WHERE ((t5.c0)::VARCHAR SIMILAR TO $4);
EXPLAIN (ANALYZE, FORMAT TEXT) EXECUTE prepare_query('',
'[142654042,1443301405)'::int4range, 7461843809418659830, '');```
This is the outputs:
```
QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=522496.96..303238953.76 rows=1 width=2) (actual
time=65.267..65.311 rows=0 loops=1)
-> Nested Loop (cost=522496.96..252929833.76 rows=20123648000
width=2) (actual time=65.267..65.310 rows=0 loops=1)
-> Gather Merge (cost=522496.94..1384162.54 rows=7398400
width=2) (actual time=65.266..65.309 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=521496.92..529203.59 rows=3082667 width=2)
(actual time=21.743..21.744 rows=0 loops=3)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..104956.96 rows=3082667
width=2) (actual time=21.699..21.700 rows=0 loops=3)
-> Parallel Seq Scan on t0 (cost=0.00..21.33
rows=1133 width=0) (actual time=0.002..0.002 rows=0 loops=3)
-> Nested Loop (cost=0.00..65.42 rows=2720
width=2) (actual time=65.088..65.089 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02
rows=1 width=2) (actual time=65.088..65.088 rows=0 loops=1)
Filter: (((c0)::character
varying)::text ~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..37.20
rows=2720 width=0) (never executed)
-> Materialize (cost=0.01..78.01 rows=2720 width=0) (never
executed)
-> Subquery Scan on subq (cost=0.01..64.41 rows=2720
width=0) (never executed)
-> Limit (cost=0.01..37.21 rows=2720 width=2)
(never executed)
-> Result (cost=0.01..37.21 rows=2720
width=2) (never executed)
One-Time Filter: ((''::text ||
('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1
(cost=0.01..37.21 rows=2720 width=0) (never executed)
Planning Time: 0.195 ms
JIT:
Functions: 21
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Timing: Generation 0.636 ms (Deform 0.114 ms), Inlining 34.664 ms,
Optimization 18.423 ms, Emission 11.975 ms, Total 65.698 ms
Execution Time: 74.751 ms
(29 rows)QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=30194812.07..30194812.08 rows=1 width=2) (actual
time=0.008..0.009 rows=0 loops=1)
Group Key: t5.c0
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.01..25163900.07 rows=2012364800 width=2)
(actual time=0.007..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.01..9296.07 rows=739840 width=2) (actual
time=0.007..0.007 rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..37.20 rows=2720 width=0)
(actual time=0.007..0.007 rows=0 loops=1)
-> Materialize (cost=0.01..11.55 rows=272 width=2) (never
executed)
-> Nested Loop (cost=0.01..10.19 rows=272 width=2)
(never executed)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1
width=2) (never executed)
Filter: (((c0)::character varying)::text
~ similar_to_escape($4))
-> Limit (cost=0.01..3.73 rows=272 width=2)
(never executed)
-> Result (cost=0.01..37.21 rows=2720
width=2) (never executed)
One-Time Filter: (($1 ||
($2)::text))::boolean
-> Seq Scan on t2 t2_1
(cost=0.01..37.21 rows=2720 width=0) (never executed)
-> Materialize (cost=0.00..50.80 rows=2720 width=0) (never
executed)
-> Seq Scan on t0 (cost=0.00..37.20 rows=2720 width=0)
(never executed)
Planning Time: 0.110 ms
JIT:
Functions: 15
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Timing: Generation 0.208 ms (Deform 0.053 ms), Inlining 0.000 ms,
Optimization 0.000 ms, Emission 0.000 ms, Total 0.208 ms
Execution Time: 0.289 ms
(22 rows)
```There are brutal estimation errors - you missing ANALYZE after
initialization.When there are too big estimation errors, the planner behaviour can be not
intuitive - and nobody should to expect good resultsRegards
Pavel
Original
------------------------------
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: 2025-12-17 22:54
To: 798604270 <798604270@qq.com>, pgsql-bugs <
pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that
performsworse than the generic plan for a certain query.Please simplify your test query as much as possible and use "text" format
in your explain results; those will improve your chances of getting a
useful reply. :)
Hi Pavel,
This is the test case I added BUFFERS:
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 smallint) USING heap WITH (parallel_workers=852);
CREATE TABLE t2(LIKE t0);
CREATE TABLE t5(LIKE t0);
INSERT INTO t5(c0) VALUES(1::INT8);
INSERT INTO t0(c0) VALUES(1::int8);
CREATE INDEX i0 ON t5(c0 NULLS FIRST);
ANALYZE t5;
ANALYZE t0;
ANALYZE t2;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT DISTINCT t5.c0 FROM t5, t2, t0*, (SELECT ALL t2.c0 as c0 FROM t2 WHERE ((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS BOOLEAN))) LIMIT (7461843809418659830)::int8) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR TO ''::text);
PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT t5.c0 FROM t5, t2*, t0*, (SELECT t2.c0 as c0 FROM t2 WHERE ((CAST((($1)||($2)) AS BOOLEAN))) LIMIT $3) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR TO $4);
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) EXECUTE prepare_query('', '[142654042,1443301405)'::int4range, 7461843809418659830, '');
```
This is the final output that I run the test case 10 times, it seems the prepared statement always faster than the normal SELECT:
```
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.012..0.012 rows=0 loops=1)
Buffers: shared hit=1
-> Sort (cost=2.09..2.09 rows=1 width=2) (actual time=0.011..0.012 rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.00..1.03 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
Buffers: shared hit=1
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (actual time=0.007..0.008 rows=0 loops=1)
Filter: (((c0)::character varying)::text ~ '^(?:)$'::text)
Rows Removed by Filter: 1
Buffers: shared hit=1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1 width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0) (never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never executed)
One-Time Filter: ((''::text || ('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01 rows=1 width=0) (never executed)
Planning:
Buffers: shared hit=38 read=1
Planning Time: 0.125 ms
Execution Time: 0.032 ms
(26 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.007..0.008 rows=0 loops=1)
Buffers: shared hit=1
-> Sort (cost=2.09..2.10 rows=1 width=2) (actual time=0.007..0.008 rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual time=0.005..0.006 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.00..1.03 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
Buffers: shared hit=1
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (((c0)::character varying)::text ~ similar_to_escape($4))
Rows Removed by Filter: 1
Buffers: shared hit=1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1 width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0) (never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never executed)
One-Time Filter: (($1 || ($2)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01 rows=1 width=0) (never executed)
Planning Time: 0.079 ms
Execution Time: 0.016 ms
(24 rows)
```
Original
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: 2025-12-18 00:15
To: ZhangChi <798604270@qq.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan thatperformsworsethan the generic plan for a certain query.
Hi
st 17. 12. 2025 v 17:08 odesílatel ZhangChi <798604270@qq.com> napsal:
Hi Pavel,
Thank you very much for your reply. I have two follow-up questions. First, before running ANALYZE, why does the generic plan perform much better than the custom plan?
when you have wrong estimation - anything is possible
Second, after I ran ANALYZE, the performance of the custom plan improved significantly. However, even though the custom plan is now identical to the generic plan, its execution time is still about twice that of the generic plan. Why is this the case?
can you send a output of EXPLAIN (ANALYZE, BUFFERS) ?
maybe data are in cache - maybe some different - the small times are not too stable - you should to run query multiple times, and use an average
Best,
Chi
```
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.012..0.012 rows=0 loops=1)
-> Sort (cost=2.09..2.09 rows=1 width=2) (actual time=0.012..0.012 rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.00..1.03 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
Filter: (((c0)::character varying)::text ~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1 width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0) (never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never executed)
One-Time Filter: ((''::text || ('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01 rows=1 width=0) (never executed)
Planning Time: 0.143 ms
Execution Time: 0.033 ms
(18 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
-> Sort (cost=2.09..2.10 rows=1 width=2) (actual time=0.008..0.008 rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
-> Nested Loop (cost=0.00..1.03 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (((c0)::character varying)::text ~ similar_to_escape($4))
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1 width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0) (never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never executed)
One-Time Filter: (($1 || ($2)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01 rows=1 width=0) (never executed)
Planning Time: 0.084 ms
Execution Time: 0.017 ms
(18 rows)
```
Original
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: 2025-12-17 23:53
To: ZhangChi <798604270@qq.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that performsworsethan the generic plan for a certain query.
st 17. 12. 2025 v 16:17 odesílatel ZhangChi <798604270@qq.com> napsal:
Hi Greg Sabino Mullane,
Thanks for your work.
This is already the test case that I can simplify as much as possbile. I also generate the corresponding query plan in TEXT format.
In this test case, the prepared statement (with a generic plan) is much more efficitive than the normal SELECT (with a custom plan).
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 smallint) USING heap WITH (parallel_workers=852);
CREATE TABLE t2(LIKE t0);
CREATE TABLE t5(LIKE t0);
INSERT INTO t5(c0) VALUES(1::INT8);
INSERT INTO t0(c0) VALUES(1::int8);
CREATE INDEX i0 ON t5(c0 NULLS FIRST);
EXPLAIN (ANALYZE, FORMAT TEXT) SELECT DISTINCT t5.c0 FROM t5, t2, t0*, (SELECT ALL t2.c0 as c0 FROM t2 WHERE ((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS BOOLEAN))) LIMIT (7461843809418659830)::int8) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR TO ''::text);
PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT t5.c0 FROM t5, t2*, t0*, (SELECT t2.c0 as c0 FROM t2 WHERE ((CAST((($1)||($2)) AS BOOLEAN))) LIMIT $3) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR TO $4);
EXPLAIN (ANALYZE, FORMAT TEXT) EXECUTE prepare_query('', '[142654042,1443301405)'::int4range, 7461843809418659830, '');
```
This is the outputs:
```
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=522496.96..303238953.76 rows=1 width=2) (actual time=65.267..65.311 rows=0 loops=1)
-> Nested Loop (cost=522496.96..252929833.76 rows=20123648000 width=2) (actual time=65.267..65.310 rows=0 loops=1)
-> Gather Merge (cost=522496.94..1384162.54 rows=7398400 width=2) (actual time=65.266..65.309 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=521496.92..529203.59 rows=3082667 width=2) (actual time=21.743..21.744 rows=0 loops=3)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..104956.96 rows=3082667 width=2) (actual time=21.699..21.700 rows=0 loops=3)
-> Parallel Seq Scan on t0 (cost=0.00..21.33 rows=1133 width=0) (actual time=0.002..0.002 rows=0 loops=3)
-> Nested Loop (cost=0.00..65.42 rows=2720 width=2) (actual time=65.088..65.089 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (actual time=65.088..65.088 rows=0 loops=1)
Filter: (((c0)::character varying)::text ~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..37.20 rows=2720 width=0) (never executed)
-> Materialize (cost=0.01..78.01 rows=2720 width=0) (never executed)
-> Subquery Scan on subq (cost=0.01..64.41 rows=2720 width=0) (never executed)
-> Limit (cost=0.01..37.21 rows=2720 width=2) (never executed)
-> Result (cost=0.01..37.21 rows=2720 width=2) (never executed)
One-Time Filter: ((''::text || ('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..37.21 rows=2720 width=0) (never executed)
Planning Time: 0.195 ms
JIT:
Functions: 21
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.636 ms (Deform 0.114 ms), Inlining 34.664 ms, Optimization 18.423 ms, Emission 11.975 ms, Total 65.698 ms
Execution Time: 74.751 ms
(29 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=30194812.07..30194812.08 rows=1 width=2) (actual time=0.008..0.009 rows=0 loops=1)
Group Key: t5.c0
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.01..25163900.07 rows=2012364800 width=2) (actual time=0.007..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.01..9296.07 rows=739840 width=2) (actual time=0.007..0.007 rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..37.20 rows=2720 width=0) (actual time=0.007..0.007 rows=0 loops=1)
-> Materialize (cost=0.01..11.55 rows=272 width=2) (never executed)
-> Nested Loop (cost=0.01..10.19 rows=272 width=2) (never executed)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1 width=2) (never executed)
Filter: (((c0)::character varying)::text ~ similar_to_escape($4))
-> Limit (cost=0.01..3.73 rows=272 width=2) (never executed)
-> Result (cost=0.01..37.21 rows=2720 width=2) (never executed)
One-Time Filter: (($1 || ($2)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..37.21 rows=2720 width=0) (never executed)
-> Materialize (cost=0.00..50.80 rows=2720 width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..37.20 rows=2720 width=0) (never executed)
Planning Time: 0.110 ms
JIT:
Functions: 15
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.208 ms (Deform 0.053 ms), Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 0.208 ms
Execution Time: 0.289 ms
(22 rows)
```
There are brutal estimation errors - you missing ANALYZE after initialization.
When there are too big estimation errors, the planner behaviour can be not intuitive - and nobody should to expect good results
Regards
Pavel
Original
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: 2025-12-17 22:54
To: 798604270 <798604270@qq.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that performsworse than the generic plan for a certain query.
Please simplify your test query as much as possible and use "text" format in your explain results; those will improve your chances of getting a useful reply. :)
st 17. 12. 2025 v 17:20 odesílatel ZhangChi <798604270@qq.com> napsal:
Hi Pavel,
This is the test case I added BUFFERS:
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 smallint) USING heap WITH (parallel_workers=852);
CREATE TABLE t2(LIKE t0);
CREATE TABLE t5(LIKE t0);
INSERT INTO t5(c0) VALUES(1::INT8);
INSERT INTO t0(c0) VALUES(1::int8);
CREATE INDEX i0 ON t5(c0 NULLS FIRST);
ANALYZE t5;
ANALYZE t0;
ANALYZE t2;
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT DISTINCT t5.c0 FROM t5, t2,
t0*, (SELECT ALL t2.c0 as c0 FROM t2 WHERE
((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS BOOLEAN)))
LIMIT (7461843809418659830)::int8) AS subq WHERE ((t5.c0)::VARCHAR SIMILAR
TO ''::text);
PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT
t5.c0 FROM t5, t2*, t0*, (SELECT t2.c0 as c0 FROM t2 WHERE
((CAST((($1)||($2)) AS BOOLEAN))) LIMIT $3) AS subq WHERE ((t5.c0)::VARCHAR
SIMILAR TO $4);
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) EXECUTE prepare_query('',
'[142654042,1443301405)'::int4range, 7461843809418659830, '');
```This is the final output that I run the test case 10 times, it seems the
prepared statement always faster than the normal SELECT:
I don't know - in this query it is almost zero work, - maybe reusing an
already used plan to better map memory to CPU.
You can use profiler and execute this query a thousand times, and maybe
some info will be in the profile. Personally I think the reason for this
difference can be really deep - maybe inside CPU caches.
Generally, if you want to do some "real" benchmark, you need "real" data -
or "real size" data. Tests on almost empty tables says nothing - Postgres
is not optimized for CPU effectivity
Show quoted text
```
QUERY PLAN--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.012..0.012 rows=0
loops=1)
Buffers: shared hit=1
-> Sort (cost=2.09..2.09 rows=1 width=2) (actual time=0.011..0.012
rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual
time=0.008..0.008 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual
time=0.008..0.008 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.00..1.03 rows=1 width=2)
(actual time=0.008..0.008 rows=0 loops=1)
Buffers: shared hit=1
-> Seq Scan on t5 (cost=0.00..1.02 rows=1
width=2) (actual time=0.007..0.008 rows=0 loops=1)
Filter: (((c0)::character varying)::text
~ '^(?:)$'::text)
Rows Removed by Filter: 1
Buffers: shared hit=1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1
width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0)
(never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never
executed)
One-Time Filter: ((''::text ||
('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01
rows=1 width=0) (never executed)
Planning:
Buffers: shared hit=38 read=1
Planning Time: 0.125 ms
Execution Time: 0.032 ms
(26 rows)QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.007..0.008 rows=0
loops=1)
Buffers: shared hit=1
-> Sort (cost=2.09..2.10 rows=1 width=2) (actual time=0.007..0.008
rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual
time=0.005..0.006 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual
time=0.005..0.005 rows=0 loops=1)
Buffers: shared hit=1
-> Nested Loop (cost=0.00..1.03 rows=1 width=2)
(actual time=0.005..0.005 rows=0 loops=1)
Buffers: shared hit=1
-> Seq Scan on t5 (cost=0.00..1.02 rows=1
width=2) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (((c0)::character varying)::text
~ similar_to_escape($4))
Rows Removed by Filter: 1
Buffers: shared hit=1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1
width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0)
(never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never
executed)
One-Time Filter: (($1 || ($2)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01
rows=1 width=0) (never executed)
Planning Time: 0.079 ms
Execution Time: 0.016 ms
(24 rows)
```Original
------------------------------
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: 2025-12-18 00:15
To: ZhangChi <798604270@qq.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, pgsql-bugs <
pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan
thatperformsworsethan the generic plan for a certain query.Hi
st 17. 12. 2025 v 17:08 odesílatel ZhangChi <798604270@qq.com> napsal:
Hi Pavel,
Thank you very much for your reply. I have two follow-up questions. First,
before running ANALYZE, why does the generic plan perform much better than
the custom plan?when you have wrong estimation - anything is possible
Second, after I ran ANALYZE, the performance of the custom plan improved
significantly. However, even though the custom plan is now identical to the
generic plan, its execution time is still about twice that of the generic
plan. Why is this the case?can you send a output of EXPLAIN (ANALYZE, BUFFERS) ?
maybe data are in cache - maybe some different - the small times are not
too stable - you should to run query multiple times, and use an averageBest,
Chi```
QUERY PLAN--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.012..0.012 rows=0
loops=1)
-> Sort (cost=2.09..2.09 rows=1 width=2) (actual time=0.012..0.012
rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual
time=0.008..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual
time=0.008..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.00..1.03 rows=1 width=2)
(actual time=0.008..0.008 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1
width=2) (actual time=0.008..0.008 rows=0 loops=1)
Filter: (((c0)::character varying)::text
~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1
width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0)
(never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never
executed)
One-Time Filter: ((''::text ||
('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01
rows=1 width=0) (never executed)
Planning Time: 0.143 ms
Execution Time: 0.033 ms
(18 rows)QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=2.09..2.10 rows=1 width=2) (actual time=0.008..0.008 rows=0
loops=1)
-> Sort (cost=2.09..2.10 rows=1 width=2) (actual time=0.008..0.008
rows=0 loops=1)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.01..2.08 rows=1 width=2) (actual
time=0.005..0.005 rows=0 loops=1)
-> Nested Loop (cost=0.00..2.05 rows=1 width=2) (actual
time=0.005..0.005 rows=0 loops=1)
-> Nested Loop (cost=0.00..1.03 rows=1 width=2)
(actual time=0.005..0.005 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1
width=2) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (((c0)::character varying)::text
~ similar_to_escape($4))
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..0.00 rows=1
width=0) (never executed)
-> Seq Scan on t0 (cost=0.00..1.01 rows=1 width=0)
(never executed)
-> Limit (cost=0.01..0.01 rows=1 width=2) (never executed)
-> Result (cost=0.01..0.01 rows=1 width=2) (never
executed)
One-Time Filter: (($1 || ($2)::text))::boolean
-> Seq Scan on t2 t2_1 (cost=0.01..0.01
rows=1 width=0) (never executed)
Planning Time: 0.084 ms
Execution Time: 0.017 ms
(18 rows)
```Original
------------------------------
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: 2025-12-17 23:53
To: ZhangChi <798604270@qq.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, pgsql-bugs <
pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that
performsworsethan the generic plan for a certain query.st 17. 12. 2025 v 16:17 odesílatel ZhangChi <*798604270@qq.com
<798604270@qq.com>*> napsal:Hi Greg Sabino Mullane,
Thanks for your work.
This is already the test case that I can simplify as much as possbile. I
also generate the corresponding query plan in TEXT format.In this test case, the prepared statement (with a generic plan) is much
more efficitive than the normal SELECT (with a custom plan).```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t0(c0 smallint) USING heap WITH (parallel_workers=852);
CREATE TABLE t2(LIKE t0);
CREATE TABLE t5(LIKE t0);
INSERT INTO t5(c0) VALUES(1::INT8);
INSERT INTO t0(c0) VALUES(1::int8);
CREATE INDEX i0 ON t5(c0 NULLS FIRST);
EXPLAIN (ANALYZE, FORMAT TEXT) SELECT DISTINCT t5.c0 FROM t5, t2, t0*,
(SELECT ALL t2.c0 as c0 FROM t2
WHERE ((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS
BOOLEAN))) LIMIT (7461843809418659830)::int8) AS subq
WHERE ((t5.c0)::VARCHAR SIMILAR TO ''::text);
PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT
t5.c0 FROM t5, t2*, t0*, (SELECT t2.c0 as c0 FROM t2
WHERE ((CAST((($1)||($2)) AS BOOLEAN))) LIMIT $3) AS subq
WHERE ((t5.c0)::VARCHAR SIMILAR TO $4);
EXPLAIN (ANALYZE, FORMAT TEXT) EXECUTE prepare_query('',
'[142654042,1443301405)'::int4range, 7461843809418659830, '');```
This is the outputs:
```
QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=522496.96..303238953.76 rows=1 width=2) (actual
time=65.267..65.311 rows=0 loops=1)
-> Nested Loop (cost=522496.96..252929833.76 rows=20123648000
width=2) (actual time=65.267..65.310 rows=0 loops=1)
-> Gather Merge (cost=522496.94..1384162.54 rows=7398400
width=2) (actual time=65.266..65.309 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=521496.92..529203.59 rows=3082667 width=2)
(actual time=21.743..21.744 rows=0 loops=3)
Sort Key: t5.c0
Sort Method: quicksort Memory: 25kB
Worker 0: Sort Method: quicksort Memory: 25kB
Worker 1: Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..104956.96 rows=3082667
width=2) (actual time=21.699..21.700 rows=0 loops=3)
-> Parallel Seq Scan on t0 (cost=0.00..21.33
rows=1133 width=0) (actual time=0.002..0.002 rows=0 loops=3)
-> Nested Loop (cost=0.00..65.42 rows=2720
width=2) (actual time=65.088..65.089 rows=0 loops=1)
-> Seq Scan on t5 (cost=0.00..1.02
rows=1 width=2) (actual time=65.088..65.088 rows=0 loops=1)
Filter: (((c0)::character
varying)::text ~ '^(?:)$'::text)
Rows Removed by Filter: 1
-> Seq Scan on t2 (cost=0.00..37.20
rows=2720 width=0) (never executed)
-> Materialize (cost=0.01..78.01 rows=2720 width=0) (never
executed)
-> Subquery Scan on subq (cost=0.01..64.41 rows=2720
width=0) (never executed)
-> Limit (cost=0.01..37.21 rows=2720 width=2)
(never executed)
-> Result (cost=0.01..37.21 rows=2720
width=2) (never executed)
One-Time Filter: ((''::text ||
('[142654042,1443301405)'::int4range)::text))::boolean
-> Seq Scan on t2 t2_1
(cost=0.01..37.21 rows=2720 width=0) (never executed)
Planning Time: 0.195 ms
JIT:
Functions: 21
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Timing: Generation 0.636 ms (Deform 0.114 ms), Inlining 34.664 ms,
Optimization 18.423 ms, Emission 11.975 ms, Total 65.698 ms
Execution Time: 74.751 ms
(29 rows)QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=30194812.07..30194812.08 rows=1 width=2) (actual
time=0.008..0.009 rows=0 loops=1)
Group Key: t5.c0
Batches: 1 Memory Usage: 24kB
-> Nested Loop (cost=0.01..25163900.07 rows=2012364800 width=2)
(actual time=0.007..0.008 rows=0 loops=1)
-> Nested Loop (cost=0.01..9296.07 rows=739840 width=2) (actual
time=0.007..0.007 rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..37.20 rows=2720 width=0)
(actual time=0.007..0.007 rows=0 loops=1)
-> Materialize (cost=0.01..11.55 rows=272 width=2) (never
executed)
-> Nested Loop (cost=0.01..10.19 rows=272 width=2)
(never executed)
-> Seq Scan on t5 (cost=0.00..1.02 rows=1
width=2) (never executed)
Filter: (((c0)::character varying)::text
~ similar_to_escape($4))
-> Limit (cost=0.01..3.73 rows=272 width=2)
(never executed)
-> Result (cost=0.01..37.21 rows=2720
width=2) (never executed)
One-Time Filter: (($1 ||
($2)::text))::boolean
-> Seq Scan on t2 t2_1
(cost=0.01..37.21 rows=2720 width=0) (never executed)
-> Materialize (cost=0.00..50.80 rows=2720 width=0) (never
executed)
-> Seq Scan on t0 (cost=0.00..37.20 rows=2720 width=0)
(never executed)
Planning Time: 0.110 ms
JIT:
Functions: 15
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Timing: Generation 0.208 ms (Deform 0.053 ms), Inlining 0.000 ms,
Optimization 0.000 ms, Emission 0.000 ms, Total 0.208 ms
Execution Time: 0.289 ms
(22 rows)
```There are brutal estimation errors - you missing ANALYZE after
initialization.When there are too big estimation errors, the planner behaviour can be not
intuitive - and nobody should to expect good resultsRegards
Pavel
Original
------------------------------
From: Greg Sabino Mullane <*htamfids@gmail.com <htamfids@gmail.com>*>
Date: 2025-12-17 22:54
To: 798604270 <*798604270@qq.com <798604270@qq.com>*>, pgsql-bugs <*pgsql-bugs@lists.postgresql.org
<pgsql-bugs@lists.postgresql.org>*>
Subject: Re: BUG #19357: PostgreSQL generates a custom plan that
performsworse than the generic plan for a certain query.Please simplify your test query as much as possible and use "text" format
in your explain results; those will improve your chances of getting a
useful reply. :)
Everything in the second query is slightly faster (and heavy emphasis on
"slightly") - it could simply be caching at some level. Run each query
multiple times and interleave them for better results. However, we are now
discussing that a query runs in .017ms versus .033ms. Add some real data
and demonstrate something that would trigger a real-world concern. Nobody
is going to get excited about microsecond differences. At that level, it's
near impossible to distinguish anything due to the overall noise and
effects of things out of Postgres' control. In short, not a bug, unless you
can demonstrate otherwise. :)
Cheers,
Greg