Thread
-
Re: BUG #19357: PostgreSQL generates a custom plan that performsworsethan the generic plan for a certain query.
Pavel Stehule <pavel.stehule@gmail.com> — 2025-12-17T16:15:21Z
Hi st 17. 12. 2025 v 17:08 odesílatel ZhangChi <798604270@qq.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.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. :) > > > >