BUG #19357: PostgreSQL generates a custom plan that performs worse than the generic plan for a certain query. - Mailing list pgsql-bugs
| From | PG Bug reporting form |
|---|---|
| Subject | BUG #19357: PostgreSQL generates a custom plan that performs worse than the generic plan for a certain query. |
| Date | |
| Msg-id | 19357-060d926bff47a613@postgresql.org Whole thread Raw |
| Responses |
Re: BUG #19357: PostgreSQL generates a custom plan that performs worse than the generic plan for a certain query.
|
| List | pgsql-bugs |
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.
pgsql-bugs by date: