This is exactly my issue.
Using raw query, planning takes 22ms (custom plan), but using PreparedStatement planning takes 11ms (generic plan).
It choose the faster generic plan 11ms, wining 11ms faster than custom plan, but loosing 14seconds!!! to execution...
The auto choose algorithm should be changed to include execution time in the decision.
On Wednesday, May 5, 2021, 9:57:20 AM GMT+3, David Rowley <dgrowleyml@xxxxxxxxx> wrote:
On Tue, 4 May 2021 at 22:05, Alex <cdalxndr@xxxxxxxxx> wrote:
> Shouldn't this process be automatic based on some heuristics?
When plan_cache_mode is set to "auto", then the decision to use a
generic or custom plan is cost-based. See [1]. There's a fairly crude
method there for estimating the effort required to replan the query.
The remainder is based on the average cost of the previous custom
plans + estimated planning effort vs cost of the generic plan. The
cheaper one wins.
Certainly, what's there is far from perfect. There are various
problems with it. The estimated planning cost is pretty crude and
could do with an overhaul. There are also issues with the plan costs
not being true to the cost of the query. One problem there is that
run-time partition pruning is not costed into the plan. This might
cause choose_custom_plan() to pick a custom plan when a generic one
with run-time pruning might have been better.
In order to get a better idea of where things are going wrong for you,
we'd need to see the EXPLAIN ANALYZE output for both the custom and
the generic plan.
David
[1]https://github.com/postgres/postgres/blob/master/src/backend/utils/cache/plancache.c#L1019
> Shouldn't this process be automatic based on some heuristics?
When plan_cache_mode is set to "auto", then the decision to use a
generic or custom plan is cost-based. See [1]. There's a fairly crude
method there for estimating the effort required to replan the query.
The remainder is based on the average cost of the previous custom
plans + estimated planning effort vs cost of the generic plan. The
cheaper one wins.
Certainly, what's there is far from perfect. There are various
problems with it. The estimated planning cost is pretty crude and
could do with an overhaul. There are also issues with the plan costs
not being true to the cost of the query. One problem there is that
run-time partition pruning is not costed into the plan. This might
cause choose_custom_plan() to pick a custom plan when a generic one
with run-time pruning might have been better.
In order to get a better idea of where things are going wrong for you,
we'd need to see the EXPLAIN ANALYZE output for both the custom and
the generic plan.
David
[1]
