Re: BUG #19357: PostgreSQL generates a custom plan thatperformsworsethan the generic plan for a certain query.

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. :)