Horton

Thread

  1. Re: BUG #19357: PostgreSQL generates a custom plan that performsworse than the generic plan for a certain query.

    ZhangChi <798604270@qq.com> — 2025-12-17T15:17:30Z

    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&nbsp;plan_cache_mode = force_generic_plan;

CREATE TABLE t0(c0 smallint) USING&nbsp;heap WITH&nbsp;(parallel_workers=852);

CREATE TABLE t2(LIKE&nbsp;t0);

CREATE TABLE t5(LIKE&nbsp;t0);

INSERT INTO&nbsp;t5(c0) VALUES(1::INT8);

INSERT INTO&nbsp;t0(c0) VALUES(1::int8);

CREATE INDEX i0 ON&nbsp;t5(c0 NULLS FIRST);

EXPLAIN (ANALYZE, FORMAT TEXT) SELECT DISTINCT t5.c0 FROM&nbsp;t5, t2, t0*, (SELECT ALL t2.c0  as&nbsp;c0 FROM&nbsp;t2 WHERE&nbsp;((CAST(((''::text)||('[142654042,1443301405)'::int4range)) AS BOOLEAN))) LIMIT&nbsp;(7461843809418659830)::int8) AS&nbsp;subq WHERE&nbsp;((t5.c0)::VARCHAR&nbsp;SIMILAR TO ''::text);

PREPARE prepare_query (text, int4range, int8, text) AS SELECT DISTINCT t5.c0 FROM&nbsp;t5, t2*, t0*, (SELECT t2.c0  as&nbsp;c0 FROM&nbsp;t2 WHERE&nbsp;((CAST((($1)||($2)) AS BOOLEAN))) LIMIT&nbsp;$3) AS&nbsp;subq WHERE&nbsp;((t5.c0)::VARCHAR&nbsp;SIMILAR TO&nbsp;$4);

EXPLAIN (ANALYZE, FORMAT TEXT) EXECUTE&nbsp;prepare_query('', '[142654042,1443301405)'::int4range, 7461843809418659830, '');






```


This is the outputs:


```
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUERY PLAN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
---------------------------------------------------------------------------------------------------------------------------------------
&nbsp;Unique &nbsp;(cost=522496.96..303238953.76 rows=1 width=2) (actual time=65.267..65.311 rows=0 loops=1)
&nbsp; &nbsp;-&gt; &nbsp;Nested Loop &nbsp;(cost=522496.96..252929833.76 rows=20123648000 width=2) (actual time=65.267..65.310 rows=0 loops=1)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Gather Merge &nbsp;(cost=522496.94..1384162.54 rows=7398400 width=2) (actual time=65.266..65.309 rows=0 loops=1)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Workers Planned: 2
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Workers Launched: 2
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Sort &nbsp;(cost=521496.92..529203.59 rows=3082667 width=2) (actual time=21.743..21.744 rows=0 loops=3)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sort Key: t5.c0
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sort Method: quicksort &nbsp;Memory: 25kB
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Worker 0: &nbsp;Sort Method: quicksort &nbsp;Memory: 25kB
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Worker 1: &nbsp;Sort Method: quicksort &nbsp;Memory: 25kB
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Nested Loop &nbsp;(cost=0.00..104956.96 rows=3082667 width=2) (actual time=21.699..21.700 rows=0 loops=3)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Parallel Seq Scan on t0 &nbsp;(cost=0.00..21.33 rows=1133 width=0) (actual time=0.002..0.002 rows=0 loops=3)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Nested Loop &nbsp;(cost=0.00..65.42 rows=2720 width=2) (actual time=65.088..65.089 rows=0 loops=1)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t5 &nbsp;(cost=0.00..1.02 rows=1 width=2) (actual time=65.088..65.088 rows=0 loops=1)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Filter: (((c0)::character varying)::text ~ '^(?:)$'::text)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Rows Removed by Filter: 1
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t2 &nbsp;(cost=0.00..37.20 rows=2720 width=0) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Materialize &nbsp;(cost=0.01..78.01 rows=2720 width=0) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Subquery Scan on subq &nbsp;(cost=0.01..64.41 rows=2720 width=0) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Limit &nbsp;(cost=0.01..37.21 rows=2720 width=2) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Result &nbsp;(cost=0.01..37.21 rows=2720 width=2) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;One-Time Filter: ((''::text || ('[142654042,1443301405)'::int4range)::text))::boolean
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t2 t2_1 &nbsp;(cost=0.01..37.21 rows=2720 width=0) (never executed)
&nbsp;Planning Time: 0.195 ms
&nbsp;JIT:
&nbsp; &nbsp;Functions: 21
&nbsp; &nbsp;Options: Inlining true, Optimization true, Expressions true, Deforming true
&nbsp; &nbsp;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
&nbsp;Execution Time: 74.751 ms
(29 rows)




&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUERY PLAN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
------------------------------------------------------------------------------------------------------------------------------
&nbsp;HashAggregate &nbsp;(cost=30194812.07..30194812.08 rows=1 width=2) (actual time=0.008..0.009 rows=0 loops=1)
&nbsp; &nbsp;Group Key: t5.c0
&nbsp; &nbsp;Batches: 1 &nbsp;Memory Usage: 24kB
&nbsp; &nbsp;-&gt; &nbsp;Nested Loop &nbsp;(cost=0.01..25163900.07 rows=2012364800 width=2) (actual time=0.007..0.008 rows=0 loops=1)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Nested Loop &nbsp;(cost=0.01..9296.07 rows=739840 width=2) (actual time=0.007..0.007 rows=0 loops=1)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t2 &nbsp;(cost=0.00..37.20 rows=2720 width=0) (actual time=0.007..0.007 rows=0 loops=1)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Materialize &nbsp;(cost=0.01..11.55 rows=272 width=2) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Nested Loop &nbsp;(cost=0.01..10.19 rows=272 width=2) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t5 &nbsp;(cost=0.00..1.02 rows=1 width=2) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Filter: (((c0)::character varying)::text ~ similar_to_escape($4))
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Limit &nbsp;(cost=0.01..3.73 rows=272 width=2) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Result &nbsp;(cost=0.01..37.21 rows=2720 width=2) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;One-Time Filter: (($1 || ($2)::text))::boolean
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t2 t2_1 &nbsp;(cost=0.01..37.21 rows=2720 width=0) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Materialize &nbsp;(cost=0.00..50.80 rows=2720 width=0) (never executed)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t0 &nbsp;(cost=0.00..37.20 rows=2720 width=0) (never executed)
&nbsp;Planning Time: 0.110 ms
&nbsp;JIT:
&nbsp; &nbsp;Functions: 15
&nbsp; &nbsp;Options: Inlining true, Optimization true, Expressions true, Deforming true
&nbsp; &nbsp;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
&nbsp;Execution Time: 0.289 ms
(22 rows)
```


         Original
         
       
From: Greg Sabino Mullane <htamfids@gmail.com&gt;
Date: 2025-12-17 22:54
To: 798604270 <798604270@qq.com&gt;, pgsql-bugs <pgsql-bugs@lists.postgresql.org&gt;
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. :)&nbsp;