Thread: 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)

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