Re: Plan invalidation design

Tom Lane wrote:

> Relcache inval casts a fairly wide net; for example, adding or dropping an
> index will invalidate all plans using the index's table whether or not
> they used that particular index, and I believe that VACUUM will also
> result in a relcache inval due to updating the table's pg_class row.
> I think this is a good thing though --- for instance, after adding an
> index it seems a good idea to replan to see if the new index is useful,
> and replanning after a VACUUM is useful if the table has changed size
> enough to warrant a different plan. OTOH this might mean that plans on a
> high-update-traffic table never survive very long because of autovacuum's
> efforts. If that proves to be a problem in practice we can look at ways
> to dial down the number of replans, but for the moment I think it's more
> important to be sure we *can* replan at need than to find ways to avoid
> replans.

I remember that there was discussion about invalidating plans who's
estimated cost turn out to be severely off when executed. That is
probably a more reliable metric (than invalidating with every VACCUM -
unless of course the amount of changed rows is considered), though it
will probably put a fixed overhead on all relevant queries. So it might
not be feasible. Of course this checking after a query runs longer than
expected also means that at least one execution will in fact have to run
slow instead of preempting this from happening at all.

Also while not directly related it might be thing to keep in mind. It
would also be cool to support multiple plans for different sets of
parameters, since obviously the data distribution and therefore the
optimal plan will potentially vary greatly with different parameters.

regards,
Lukas

PS: I moved "Plan invalidation" to confirmed on the wishlist ..