public inbox for [email protected]
help / color / mirror / Atom feedinefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
16+ messages / 4 participants
[nested] [flat]
* inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
@ 2025-05-12 11:49 Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
0 siblings, 1 reply; 16+ messages in thread
From: Maxim Boguk @ 2025-05-12 11:49 UTC (permalink / raw)
To: [email protected]
Hi,
I found a case where plan cache all time switching to custom plans forces
query replan each call (and thus slows down the whole query for 10x or
more).
What makes the situation intriguing - that both custom and generic plans
are the same.
job_stats_master - partitioned table with 24 partitions (per month last 2
year).
Problem query:
prepare qqq(timestamp, timestamp) AS
SELECT *
FROM "job_stats_master"
WHERE
"job_stats_master"."created_at" BETWEEN $1 AND $2 AND
"job_stats_master"."job_reference" = '******' AND
"job_stats_master"."job_board_id" = 27068
ORDER BY "created_at" DESC LIMIT 1;
plan (after 6th execution):
explain analyze execute qqq('2025-04-11 09:22:00.193'::timestamp without
time zone, '2025-05-12 09:22:00.203'::timestamp without time zone);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.14..1.29 rows=1 width=384) (actual time=0.026..0.026 rows=1
loops=1)
-> Append (cost=1.14..9.10 rows=50 width=384) (actual
time=0.025..0.026 rows=1 loops=1)
-> Index Scan Backward using
job_stats_new_2025_05_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_05 job_stats_master_2 (cost=0.56..3.28 rows=18
width=368) (actual time=0.025..0.025 rows=1 loops=1)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '******'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
09:22:00.203'::timestamp without time zone))
-> Index Scan Backward using
job_stats_new_2025_04_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_04 job_stats_master_1 (cost=0.57..5.32 rows=32
width=394) (never executed)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '******'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
09:22:00.203'::timestamp without time zone))
Planning Time: 0.611 ms
Execution Time: 0.057 ms
(8 rows)
plan with set plan_cache_mode to force_generic_plan ;
explain analyze execute qqq('2025-04-11 09:22:00.193'::timestamp without
time zone, '2025-05-12 09:22:00.203'::timestamp without time zone);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=19.06..19.32 rows=1 width=407) (actual time=0.030..0.030
rows=1 loops=1)
-> Append (cost=19.06..26.74 rows=29 width=407) (actual
time=0.029..0.030 rows=1 loops=1)
Subplans Removed: 27
-> Index Scan Backward using
job_stats_new_2025_05_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_05 job_stats_master_2 (cost=0.56..0.82 rows=1
width=368) (actual time=0.029..0.029 rows=1 loops=1)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*******'::text) AND (created_at >= $1) AND
(created_at <= $2))
-> Index Scan Backward using
job_stats_new_2025_04_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_04 job_stats_master_1 (cost=0.57..0.83 rows=1
width=394) (never executed)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*******'::text) AND (created_at >= $1) AND
(created_at <= $2))
Planning Time: 0.033 ms
Execution Time: 0.086 ms
Plan "de facto" the same, performance almost the same but with custom plans
there is 20x more time spent on planning.
With over 1M RPS - it's become quite an issue even for the best available
servers.
No playing with cost parameters provides any changes in selection custom
plan over generic.
As I understand there is an issue with costing model - generic plan thinks
it will visit all 24 partitions but custom plan does prune partitions
during planning thus custom plan always wins in this case "by cost" and in
the same time huge loss in performance (but actual plans are the same in
both cases).
I suspect this situation should be quite common with queries over
partitioned tables (where planning time is usually quite a high).
Any suggestions what could be done there outside of using
force_generic_plan for a particular db user (which will kill performance in
other queries for sure)?
--
Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
@ 2025-05-12 12:08 ` Andrei Lepikhov <[email protected]>
2025-05-12 13:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 15:01 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) David Rowley <[email protected]>
0 siblings, 2 replies; 16+ messages in thread
From: Andrei Lepikhov @ 2025-05-12 12:08 UTC (permalink / raw)
To: Maxim Boguk <[email protected]>; [email protected]
On 5/12/25 13:49, Maxim Boguk wrote:
> I suspect this situation should be quite common with queries over
> partitioned tables (where planning time is usually quite a high).
>
> Any suggestions what could be done there outside of using
> force_generic_plan for a particular db user (which will kill performance
> in other queries for sure)?
Thanks for this puzzle!
I suppose, in case generic planning is much faster than custom one,
there are two candidates exist:
1. Touching the index during planning causes too much overhead - see
get_actual_variable_range
2. You have a massive default_statistics_target for a table involved.
So, to clarify the problem, may you provide EXPLAIN (without analyze)
with BUFFERS ON ?
Also, could you provide extra information on the statistics involved?
For each column (I think created_at is the most important one), show the
size of MCV and histogram arrays.
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
@ 2025-05-12 13:08 ` Maxim Boguk <[email protected]>
2025-05-12 13:48 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
1 sibling, 1 reply; 16+ messages in thread
From: Maxim Boguk @ 2025-05-12 13:08 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; +Cc: [email protected]
On Mon, May 12, 2025 at 3:08 PM Andrei Lepikhov <[email protected]> wrote:
> On 5/12/25 13:49, Maxim Boguk wrote:
> > I suspect this situation should be quite common with queries over
> > partitioned tables (where planning time is usually quite a high).
> >
> > Any suggestions what could be done there outside of using
> > force_generic_plan for a particular db user (which will kill performance
> > in other queries for sure)?
> Thanks for this puzzle!
> I suppose, in case generic planning is much faster than custom one,
> there are two candidates exist:
> 1. Touching the index during planning causes too much overhead - see
> get_actual_variable_range
> 2. You have a massive default_statistics_target for a table involved.
>
> So, to clarify the problem, may you provide EXPLAIN (without analyze)
> with BUFFERS ON ?
> Also, could you provide extra information on the statistics involved?
> For each column (I think created_at is the most important one), show the
> size of MCV and histogram arrays.
>
> --
> regards, Andrei Lepikhov
>
clickcast=# explain (buffers) execute qqq('2025-04-11
09:22:00.193'::timestamp without time zone, '2025-05-12
09:22:00.203'::timestamp without time zone);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.14..1.29 rows=1 width=385)
-> Append (cost=1.14..9.10 rows=50 width=385)
-> Index Scan Backward using
job_stats_new_2025_05_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_05 job_stats_master_2 (cost=0.56..3.28 rows=18
width=371)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*****'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
09:22:00.203'::timestamp without time zone))
-> Index Scan Backward using
job_stats_new_2025_04_job_board_id_job_reference_created_at_idx on
job_stats_new_2025_04 job_stats_master_1 (cost=0.57..5.32 rows=32
width=394)
Index Cond: ((job_board_id = 27068) AND
((job_reference)::text = '*******'::text) AND (created_at >= '2025-04-11
09:22:00.193'::timestamp without time zone) AND (created_at <= '2025-05-12
09:22:00.203'::timestamp without time zone))
Planning:
Buffers: shared hit=16
16 buffers - most times, sometimes 12k Buffers: shared hit=12511 (like 5%
cases) - I have no idea why.
show default_statistics_target ;
default_statistics_target
---------------------------
100
No custom statistic targets on this table or partitions.
select
tablename,attname,inherited,null_frac,n_distinct,array_length(most_common_vals,1)
mcv, array_length(histogram_bounds,1) hist from pg_stats where tablename IN
('job_stats_master', 'job_stats_new_2025_04', 'job_stats_new_2025_05') and
attname in ('created_at', 'job_board_id', 'job_reference') order by
tablename, attname;
tablename | attname | inherited | null_frac |
n_distinct | mcv | hist
-----------------------+---------------+-----------+------------+--------------+-----+------
job_stats_master | created_at | t | 0 |
1.066586e+06 | 15 | 101
job_stats_master | job_board_id | t | 0.52743334 |
1716 | 100 | 101
job_stats_master | job_reference | t | 0 |
-0.1 | 39 | 101
job_stats_new_2025_04 | created_at | f | 0 |
832508 | 39 | 101
job_stats_new_2025_04 | job_board_id | f | 0.47096667 |
1096 | 100 | 101
job_stats_new_2025_04 | job_reference | f | 0 |
-0.1 | 93 | 101
job_stats_new_2025_05 | created_at | f | 0 |
709166 | 42 | 101
job_stats_new_2025_05 | job_board_id | f | 0.4703 |
1142 | 100 | 101
job_stats_new_2025_05 | job_reference | f | 0 |
-0.1 | 100 | 101
PS: problem not with difference between custom and generic planning time
but with prepared statements
generic plan plans only once, but custom plan plan every call (and plan
time cost 95% on total query runtime).
--
Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 13:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
@ 2025-05-12 13:48 ` Andrei Lepikhov <[email protected]>
2025-05-12 14:04 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 14:21 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
0 siblings, 2 replies; 16+ messages in thread
From: Andrei Lepikhov @ 2025-05-12 13:48 UTC (permalink / raw)
To: Maxim Boguk <[email protected]>; +Cc: [email protected]
On 5/12/25 15:08, Maxim Boguk wrote:
> PS: problem not with difference between custom and generic planning time
> but with prepared statements
> generic plan plans only once, but custom plan plan every call (and plan
> time cost 95% on total query runtime).
Ah, now I got it.
I'm aware of this problem from at least two sources of regular complaints.
What can you do here? Let's imagine a palliative solution:
Having pg_stat_statements data and the list of prepared statements (see
pg_prepared_statement) and queryId enabled, there is a way to force a
custom or generic plan in specific cases only: look up into min/max
query execution time. If no big difference exists and planning time is
sufficient, setting force_generic_plan for this plan makes sense. In
another case, if the planning time is too short or the generic plan is
unstable - switch to force_custom_plan.
It is not hard to write such a tiny extension. As I see, the only extra
stored "C" procedure is needed to set up force-plan-type flag employing
FetchPreparedStatement(). The rest of the code - querying
pg_stat_statements and switching between plan types may be written in
plpgsql.
If I'm not mistaken, it will work with all PG versions that are
currently in support. What do you think?
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 13:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 13:48 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
@ 2025-05-12 14:04 ` Maxim Boguk <[email protected]>
2025-05-12 17:22 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-18 16:54 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
1 sibling, 2 replies; 16+ messages in thread
From: Maxim Boguk @ 2025-05-12 14:04 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; +Cc: [email protected]
On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <[email protected]> wrote:
> On 5/12/25 15:08, Maxim Boguk wrote:
> > PS: problem not with difference between custom and generic planning time
> > but with prepared statements
> > generic plan plans only once, but custom plan plan every call (and plan
> > time cost 95% on total query runtime).
> Ah, now I got it.
> I'm aware of this problem from at least two sources of regular complaints.
> What can you do here? Let's imagine a palliative solution:
> Having pg_stat_statements data and the list of prepared statements (see
> pg_prepared_statement) and queryId enabled, there is a way to force a
> custom or generic plan in specific cases only: look up into min/max
> query execution time. If no big difference exists and planning time is
> sufficient, setting force_generic_plan for this plan makes sense. In
> another case, if the planning time is too short or the generic plan is
> unstable - switch to force_custom_plan.
>
> It is not hard to write such a tiny extension. As I see, the only extra
> stored "C" procedure is needed to set up force-plan-type flag employing
> FetchPreparedStatement(). The rest of the code - querying
> pg_stat_statements and switching between plan types may be written in
> plpgsql.
>
> If I'm not mistaken, it will work with all PG versions that are
> currently in support. What do you think?
Such extension would be very useful (and in general - the solution based on
the actual execution data - seems more stable/predictable than the plan
cost based selection which is currently used by postgresql).
--
Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 13:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 13:48 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 14:04 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
@ 2025-05-12 17:22 ` Andrei Lepikhov <[email protected]>
1 sibling, 0 replies; 16+ messages in thread
From: Andrei Lepikhov @ 2025-05-12 17:22 UTC (permalink / raw)
To: Maxim Boguk <[email protected]>; +Cc: [email protected]; Tom Lane <[email protected]>; David Rowley <[email protected]>
On 12/5/2025 16:04, Maxim Boguk wrote:
> On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <[email protected]
> It is not hard to write such a tiny extension. As I see, the only extra
> stored "C" procedure is needed to set up force-plan-type flag employing
> FetchPreparedStatement(). The rest of the code - querying
> pg_stat_statements and switching between plan types may be written in
> plpgsql.
>
> If I'm not mistaken, it will work with all PG versions that are
> currently in support. What do you think?
>
>
> Such extension would be very useful (and in general - the solution based
> on the actual execution data - seems more stable/predictable than the
> plan cost based selection which is currently used by postgresql).
Okay, as far as I can see now, it costs a couple of weeks to develop. It
would be more profitable in terms of speed and usage in older versions
than any core patch.
What's more, if, as you predict, it will work, it may provide a
rationale for opening the entire plan cache for extensions and allow a
wide audience to impact the extended protocol (and query plans in stored
procedures) in many curious ways.
As I may envision, a dummy routine providing a link to the
saved_plan_list will spend a few lines of code. A subscription to cached
statements may cost more time and effort but seems even more profitable.
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 13:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 13:48 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 14:04 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
@ 2025-05-18 16:54 ` Andrei Lepikhov <[email protected]>
1 sibling, 0 replies; 16+ messages in thread
From: Andrei Lepikhov @ 2025-05-18 16:54 UTC (permalink / raw)
To: Maxim Boguk <[email protected]>; +Cc: [email protected]
On 5/12/25 16:04, Maxim Boguk wrote:
> On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <[email protected]
> If I'm not mistaken, it will work with all PG versions that are
> currently in support. What do you think?
>
>
> Such extension would be very useful (and in general - the solution based
> on the actual execution data - seems more stable/predictable than the
> plan cost based selection which is currently used by postgresql).
I've written a sketch of such an extension; see [1]. A trivial strategy
is implemented to force prepared statements to use a generic plan if the
planning time exceeds the maximum execution time.
It is just an example - it is written in plpgsql, and you can implement
alternative strategies independently.
I would be happy if it covered your use case. Any feedback is welcome.
[1] https://github.com/danolivo/pg_mentor/tree/main
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 13:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 13:48 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
@ 2025-05-12 14:21 ` Maxim Boguk <[email protected]>
1 sibling, 0 replies; 16+ messages in thread
From: Maxim Boguk @ 2025-05-12 14:21 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; +Cc: [email protected]
On Mon, May 12, 2025 at 4:48 PM Andrei Lepikhov <[email protected]> wrote:
> On 5/12/25 15:08, Maxim Boguk wrote:
> > PS: problem not with difference between custom and generic planning time
> > but with prepared statements
> > generic plan plans only once, but custom plan plan every call (and plan
> > time cost 95% on total query runtime).
> Ah, now I got it.
> I'm aware of this problem from at least two sources of regular complaints.
> What can you do here? Let's imagine a palliative solution:
> Having pg_stat_statements data and the list of prepared statements (see
> pg_prepared_statement) and queryId enabled, there is a way to force a
> custom or generic plan in specific cases only: look up into min/max
> query execution time. If no big difference exists and planning time is
> sufficient, setting force_generic_plan for this plan makes sense. In
> another case, if the planning time is too short or the generic plan is
> unstable - switch to force_custom_plan.
>
> It is not hard to write such a tiny extension. As I see, the only extra
> stored "C" procedure is needed to set up force-plan-type flag employing
> FetchPreparedStatement(). The rest of the code - querying
> pg_stat_statements and switching between plan types may be written in
> plpgsql.
>
> If I'm not mistaken, it will work with all PG versions that are
> currently in support. What do you think?
But a more general question - this exact issue will affect every prepared
query logic which selects only a subset of partitions.
In this case - current logic will always select custom plan over generic
plan (even in case the both plans are actually the same).
E.g. If a fast/cheap query over a partitioned table has conditions that
allow use of only a few partitions - custom plan always wins whatever
database settings is (outside of force_custom_plan hammer).
Seems there could be something done about the cost calculation of generic
plan.
--
Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
@ 2025-05-12 15:01 ` David Rowley <[email protected]>
2025-05-12 15:18 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
1 sibling, 1 reply; 16+ messages in thread
From: David Rowley @ 2025-05-12 15:01 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; +Cc: Maxim Boguk <[email protected]>; Pgsql Performance <[email protected]>
On Mon, 12 May 2025, 05:08 Andrei Lepikhov, <[email protected]> wrote:
> Thanks for this puzzle!
> I suppose, in case generic planning is much faster than custom one,
> there are two candidates exist:
> 1. Touching the index during planning causes too much overhead - see
> get_actual_variable_range
> 2. You have a massive default_statistics_target for a table involved.
>
This is just an artifact of the fact that runtime pruning is not factored
into the costs. Note the cost of the generic plan. The plan_cache_mode GUC
is about the only way to overrule the choice to use the custom plan.
David
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 15:01 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) David Rowley <[email protected]>
@ 2025-05-12 15:18 ` Maxim Boguk <[email protected]>
2025-05-12 15:33 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Tom Lane <[email protected]>
2025-05-13 11:20 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) David Rowley <[email protected]>
0 siblings, 2 replies; 16+ messages in thread
From: Maxim Boguk @ 2025-05-12 15:18 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Andrei Lepikhov <[email protected]>; Pgsql Performance <[email protected]>
On Mon, May 12, 2025 at 6:01 PM David Rowley <[email protected]> wrote:
> On Mon, 12 May 2025, 05:08 Andrei Lepikhov, <[email protected]> wrote:
>
>> Thanks for this puzzle!
>> I suppose, in case generic planning is much faster than custom one,
>> there are two candidates exist:
>> 1. Touching the index during planning causes too much overhead - see
>> get_actual_variable_range
>> 2. You have a massive default_statistics_target for a table involved.
>>
>
> This is just an artifact of the fact that runtime pruning is not factored
> into the costs. Note the cost of the generic plan. The plan_cache_mode GUC
> is about the only way to overrule the choice to use the custom plan.
>
Situation quite the opposite - I need to force a generic plan because it
has the same execution time as a custom plan but performs 20-50x faster
(because in custom plan case - 95-98% time spent in planning not in
execution).
And the problem is that the cost of a custom plan ignores the cost of
planning itself (which is like 2x orders of magnitude worse than the cost
of real time partition pruning of a generic plan). I started thinking of
something like cost_planner GUC to help with similar issues (where planning
cost calculated as cost_planned*(some heuristic function with amount
involved in query tables).
In my case the high cost of planning itself should force the database to
use generic plan.
--
Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 15:01 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) David Rowley <[email protected]>
2025-05-12 15:18 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
@ 2025-05-12 15:33 ` Tom Lane <[email protected]>
2025-05-12 17:55 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
1 sibling, 1 reply; 16+ messages in thread
From: Tom Lane @ 2025-05-12 15:33 UTC (permalink / raw)
To: Maxim Boguk <[email protected]>; +Cc: David Rowley <[email protected]>; Andrei Lepikhov <[email protected]>; Pgsql Performance <[email protected]>
Maxim Boguk <[email protected]> writes:
> And the problem is that the cost of a custom plan ignores the cost of
> planning itself (which is like 2x orders of magnitude worse than the cost
> of real time partition pruning of a generic plan).
False. The estimate is evidently pretty wrong, but it's not that
there is no consideration at all. See around line 1370 in
src/backend/utils/cache/plancache.c.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 15:01 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) David Rowley <[email protected]>
2025-05-12 15:18 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 15:33 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Tom Lane <[email protected]>
@ 2025-05-12 17:55 ` Maxim Boguk <[email protected]>
2025-05-12 18:07 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Tom Lane <[email protected]>
0 siblings, 1 reply; 16+ messages in thread
From: Maxim Boguk @ 2025-05-12 17:55 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David Rowley <[email protected]>; Andrei Lepikhov <[email protected]>; Pgsql Performance <[email protected]>
On Mon, May 12, 2025 at 6:33 PM Tom Lane <[email protected]> wrote:
> Maxim Boguk <[email protected]> writes:
> > And the problem is that the cost of a custom plan ignores the cost of
> > planning itself (which is like 2x orders of magnitude worse than the cost
> > of real time partition pruning of a generic plan).
>
> False. The estimate is evidently pretty wrong, but it's not that
> there is no consideration at all. See around line 1370 in
> src/backend/utils/cache/plancache.c.
>
> regards, tom lane
>
Thank you.
Reading the code - probably the lowest hanging fruit is to make
'The current multiplier of 1000 * cpu_operator_cost' configurable in the
future versions.
PS: it's always nice to see when my ad-hoc idea (about N*nrelations as cost
planner estimate) is already implemented.
--
Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 15:01 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) David Rowley <[email protected]>
2025-05-12 15:18 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 15:33 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Tom Lane <[email protected]>
2025-05-12 17:55 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
@ 2025-05-12 18:07 ` Tom Lane <[email protected]>
2025-05-13 04:25 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-14 00:47 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
0 siblings, 2 replies; 16+ messages in thread
From: Tom Lane @ 2025-05-12 18:07 UTC (permalink / raw)
To: Maxim Boguk <[email protected]>; +Cc: David Rowley <[email protected]>; Andrei Lepikhov <[email protected]>; Pgsql Performance <[email protected]>
Maxim Boguk <[email protected]> writes:
> Reading the code - probably the lowest hanging fruit is to make
> 'The current multiplier of 1000 * cpu_operator_cost' configurable in the
> future versions.
I'm wondering whether we should try to make the planner not expend
the effort in the first place, but leave partition pruning to the
executor, at least in cases where it can determine that that will be
possible.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 15:01 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) David Rowley <[email protected]>
2025-05-12 15:18 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 15:33 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Tom Lane <[email protected]>
2025-05-12 17:55 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 18:07 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Tom Lane <[email protected]>
@ 2025-05-13 04:25 ` Andrei Lepikhov <[email protected]>
1 sibling, 0 replies; 16+ messages in thread
From: Andrei Lepikhov @ 2025-05-13 04:25 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Maxim Boguk <[email protected]>; +Cc: David Rowley <[email protected]>; Pgsql Performance <[email protected]>
On 5/12/25 20:07, Tom Lane wrote:
> Maxim Boguk <[email protected]> writes:
>> Reading the code - probably the lowest hanging fruit is to make
>> 'The current multiplier of 1000 * cpu_operator_cost' configurable in the
>> future versions.
>
> I'm wondering whether we should try to make the planner not expend
> the effort in the first place, but leave partition pruning to the
> executor, at least in cases where it can determine that that will be
> possible.
Significant planning time is a sorting out lots of scan paths, applying
partition statistics etc. planner-stage partitioning reduces these
efforts drastically.
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 15:01 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) David Rowley <[email protected]>
2025-05-12 15:18 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 15:33 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Tom Lane <[email protected]>
2025-05-12 17:55 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 18:07 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Tom Lane <[email protected]>
@ 2025-05-14 00:47 ` Maxim Boguk <[email protected]>
1 sibling, 0 replies; 16+ messages in thread
From: Maxim Boguk @ 2025-05-14 00:47 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David Rowley <[email protected]>; Andrei Lepikhov <[email protected]>; Pgsql Performance <[email protected]>
On Mon, May 12, 2025 at 9:07 PM Tom Lane <[email protected]> wrote:
> Maxim Boguk <[email protected]> writes:
> > Reading the code - probably the lowest hanging fruit is to make
> > 'The current multiplier of 1000 * cpu_operator_cost' configurable in the
> > future versions.
Is the 100x backend memory usage per cached plan difference expected
between generic and custom plans?
There are sample memory context dump with
alter role app_server set plan_cache_mode to force_custom_plan ;
reconnect pgbouncers/wait 5 min/check sample
***=> begin;
BEGIN
****=*> select count(*), count(*) filter (where generic_plans>0) as
generic_plans, count(*) filter (where custom_plans>0) as custom_plans from
pg_prepared_statements ;
count | generic_plans | custom_plans
-------+---------------+--------------
177 | 3 | 174
(1 row)
***=*> select name,parent,level,count(*), pg_size_pretty(sum(total_bytes))
as bytes, sum(total_nblocks) as nblocks, pg_size_pretty(sum(free_bytes)) as
free_bytes, sum(free_chunks) as free_chunks,
pg_size_pretty(sum(used_bytes)) as used_bytes from
pg_backend_memory_contexts group by 1,2,3 having sum(total_bytes)>128*1024
order by 3, sum(total_bytes) desc;
name | parent | level | count | bytes |
nblocks | free_bytes | free_chunks | used_bytes
-------------------------+--------------------+-------+-------+---------+---------+------------+-------------+------------
TopMemoryContext | | 0 | 1 | 769 kB |
15 | 236 kB | 574 | 532 kB
CacheMemoryContext | TopMemoryContext | 1 | 1 | 9856 kB |
125 | 223 kB | 2 | 9633 kB
CachedPlanSource | CacheMemoryContext | 2 | 264 | 5228 kB |
1142 | 2142 kB | 456 | 3086 kB
index info | CacheMemoryContext | 2 | 776 | 1612 kB |
1483 | 575 kB | 908 | 1037 kB
CachedPlan | CacheMemoryContext | 2 | 62 | 154 kB |
137 | 41 kB | 31 | 113 kB
CachedPlanQuery | CachedPlanSource | 3 | 264 | 4777 kB |
1147 | 1628 kB | 133 | 3149 kB
And with:
alter role app_server set plan_cache_mode to force_generic_plan ;
reconnect pgbouncers/wait 5 min/check sample
***=> begin;
BEGIN
***=*> select count(*), count(*) filter (where generic_plans>0) as
generic_plans, count(*) filter (where custom_plans>0) as custom_plans from
pg_prepared_statements ;
count | generic_plans | custom_plans
-------+---------------+--------------
165 | 165 | 0
(1 row)
***=*> select name,parent,level,count(*), pg_size_pretty(sum(total_bytes))
as bytes, sum(total_nblocks) as nblocks, pg_size_pretty(sum(free_bytes)) as
free_bytes, sum(free_chunks) as free_chunks,
pg_size_pretty(sum(used_bytes)) as used_bytes from
pg_backend_memory_contexts group by 1,2,3 having sum(total_bytes)>128*1024
order by 3, sum(total_bytes) desc;
name | parent | level | count | bytes |
nblocks | free_bytes | free_chunks | used_bytes
-------------------------+--------------------+-------+-------+---------+---------+------------+-------------+------------
TopMemoryContext | | 0 | 1 | 809 kB |
16 | 236 kB | 712 | 573 kB
CacheMemoryContext | TopMemoryContext | 1 | 1 | 18 MB |
126 | 8137 kB | 3 | 9910 kB
CachedPlan | CacheMemoryContext | 2 | 252 | 73 MB |
1490 | 29 MB | 127 | 43 MB
CachedPlanSource | CacheMemoryContext | 2 | 252 | 4942 kB |
1095 | 1926 kB | 381 | 3016 kB
index info | CacheMemoryContext | 2 | 794 | 1655 kB |
1516 | 579 kB | 926 | 1076 kB
CachedPlanQuery | CachedPlanSource | 3 | 252 | 4502 kB |
1096 | 1460 kB | 134 | 3041 kB
In the first case 2.5Kb per CachedPlan
in the second case 300Kb per CachedPlan
Problem with force_generic_plan that backends quickly eat up 1GB per
backend exhausting available server memory.
Postgresql version 17.4 and no complicated query in this workload (1-2-3
tables per query, sometimes two tables could be partitioned to 24
partitions each, third table always monolitic).
Regards,
Maxim
--
Maxim Boguk
Senior Postgresql DBA
Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17)
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Andrei Lepikhov <[email protected]>
2025-05-12 15:01 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) David Rowley <[email protected]>
2025-05-12 15:18 ` Re: inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
@ 2025-05-13 11:20 ` David Rowley <[email protected]>
1 sibling, 0 replies; 16+ messages in thread
From: David Rowley @ 2025-05-13 11:20 UTC (permalink / raw)
To: Maxim Boguk <[email protected]>; +Cc: Andrei Lepikhov <[email protected]>; Pgsql Performance <[email protected]>
On Tue, 13 May 2025 at 03:19, Maxim Boguk <[email protected]> wrote:
> On Mon, May 12, 2025 at 6:01 PM David Rowley <[email protected]> wrote:
>> This is just an artifact of the fact that runtime pruning is not factored into the costs. Note the cost of the generic plan. The plan_cache_mode GUC is about the only way to overrule the choice to use the custom plan.
>
> Situation quite the opposite - I need to force a generic plan because it has the same execution time as a custom plan but performs 20-50x faster (because in custom plan case - 95-98% time spent in planning not in execution).
You misunderstood. The choice the planner (or choose_custom_plan) made
to use the custom plan can be overridden with SET plan_cache_mode =
force_generic_plan;, which seems to be what performs better for you,
per your example EXPLAIN ANALYZE outputs.
> And the problem is that the cost of a custom plan ignores the cost of planning itself (which is like 2x orders of magnitude worse than the cost of real time partition pruning of a generic plan). I started thinking of something like cost_planner GUC to help with similar issues (where planning cost calculated as cost_planned*(some heuristic function with amount involved in query tables).
>
> In my case the high cost of planning itself should force the database to use generic plan.
Certainly the cost estimate for planning there is quite crude. I doubt
you'll find anyone arguing that it's not. It is however designed to be
low-overhead. The estimated planning cost isn't the issue here. It's
(as I mentioned) related to no cost consideration being given to
run-time pruning. We could certainly adjust things so that is
accounted for, and we (I think Robert and I) have talked about it in
the past. The problem is that doing that is a wild stab in the dark,
especially so for your range partitioned case where the amount of
actual partitions pruned during executor startup could range from 0 to
all of them. Unfortunately when we tag those costs onto the plan,
we've no idea what the parameter values are going to be when the plan
is executed. I think Robert suggested multiplying the Append cost by
DEFAULT_INEQ_SEL for this bounded range type pruning. Whether that
will help you or not depends on how many partitions you have and how
evenly populated they are.
In order words, it's a tricky problem with no one-size-fits-all solution.
David
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2025-05-18 16:54 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-12 11:49 inefficient/wrong plan cache mode selection for queries with partitioned tables (postgresql 17) Maxim Boguk <[email protected]>
2025-05-12 12:08 ` Andrei Lepikhov <[email protected]>
2025-05-12 13:08 ` Maxim Boguk <[email protected]>
2025-05-12 13:48 ` Andrei Lepikhov <[email protected]>
2025-05-12 14:04 ` Maxim Boguk <[email protected]>
2025-05-12 17:22 ` Andrei Lepikhov <[email protected]>
2025-05-18 16:54 ` Andrei Lepikhov <[email protected]>
2025-05-12 14:21 ` Maxim Boguk <[email protected]>
2025-05-12 15:01 ` David Rowley <[email protected]>
2025-05-12 15:18 ` Maxim Boguk <[email protected]>
2025-05-12 15:33 ` Tom Lane <[email protected]>
2025-05-12 17:55 ` Maxim Boguk <[email protected]>
2025-05-12 18:07 ` Tom Lane <[email protected]>
2025-05-13 04:25 ` Andrei Lepikhov <[email protected]>
2025-05-14 00:47 ` Maxim Boguk <[email protected]>
2025-05-13 11:20 ` David Rowley <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox