Re: Plan invalidation design

On Sat, 2007-02-17 at 12:48 -0500, 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.

Just some info on that: In an update-intensive scenario, I'm seeing
VACUUMs every 2 minutes on the heaviest hit tables on CVS HEAD on a
medium-powered 4-CPU server. Re-planning multiple queries on 100+
sessions every few minutes would not be good.

It seems a reasonable working assumption that HOT will reduce that
requirement considerably, but its something to watch. Thanks for drawing
attention to it.

Presumably ANALYZE would have the same effect?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com