Thread

BUG #19357: PostgreSQL generates a custom plan that performs worse than the generic plan for a certain query.

PG Bug reporting form <noreply@postgresql.org> — 2025-12-17T06:57:30Z

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.