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

st 17. 12. 2025 v 17:20 odesílatel ZhangChi <798604270(at)qq(dot)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

>
> ```
> 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(dot)stehule(at)gmail(dot)com>
> Date: 2025-12-18 00:15
> To: ZhangChi <798604270(at)qq(dot)com>
> Cc: Greg Sabino Mullane <htamfids(at)gmail(dot)com>, pgsql-bugs <
> pgsql-bugs(at)lists(dot)postgresql(dot)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(at)qq(dot)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(dot)stehule(at)gmail(dot)com>
> Date: 2025-12-17 23:53
> To: ZhangChi <798604270(at)qq(dot)com>
> Cc: Greg Sabino Mullane <htamfids(at)gmail(dot)com>, pgsql-bugs <
> pgsql-bugs(at)lists(dot)postgresql(dot)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(at)qq(dot)com
> <798604270(at)qq(dot)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(at)gmail(dot)com <htamfids(at)gmail(dot)com>*>
> Date: 2025-12-17 22:54
> To: 798604270 <*798604270(at)qq(dot)com <798604270(at)qq(dot)com>*>, pgsql-bugs <*pgsql-bugs(at)lists(dot)postgresql(dot)org
> <pgsql-bugs(at)lists(dot)postgresql(dot)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. :)
>
>
>
>
>