should num_custom_plans be reset after plan invalidation?
Hi,
While examining plan caches, I noticed that when a generic plan is invalidated,
the next execution of the prepared statement still results in a
generic plan. This
is of course with the default plan_cache_mode.
This behavior might go unnoticed since plan cache invalidations are
relatively uncommon,
but I’m unsure if this is the intended design. The existing decision
to switch to
a generic plan—based on the cost average of the first five custom
plans—may no longer
be optimal after a relation is modified (e.g., when a new index is added).
Given this, resetting num_custom_plans to 0 after a plan cache invalidation
might be a better approach.
I've attached an example for reference. The fix seems straightforward,
but since generic
plans may already not handle skewed data optimally, I want to see if others have
thoughts on this being something to fix.
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
Sami Imseih <samimseih@gmail.com> writes:
While examining plan caches, I noticed that when a generic plan is invalidated,
the next execution of the prepared statement still results in a
generic plan. This
is of course with the default plan_cache_mode.
This behavior might go unnoticed since plan cache invalidations are
relatively uncommon,
but I’m unsure if this is the intended design.
Yes, it is. There's little reason to expect that the invalidation
would change our decision, and re-planning five times to confirm that
is a high price to pay.
Sure, the invalidation *might* have been because of a new index
that happens to fit the query, but the odds of that seem small to me.
It's much more likely because of auto-vacuum tweaking the stats, or
just a random sinval queue overrun.
regards, tom lane
Yes, it is. There's little reason to expect that the invalidation
would change our decision, and re-planning five times to confirm that
is a high price to pay.
Re-planning 5 times did not sound appealing to me either, but I was
on the fence as to what the proper behavior should be.
Thanks for confirming!
--
Sami