Re: Plan invalidation design

Simon Riggs wrote:
> 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.

I would think the inval would be sent if relpages changed by more than a
certain threshold, say 10%. In steady state, a high-update table that's
under continuous vacuum should not change size much, thus no replan.

But clearly the point here is to get the inval to be sent at all, and
look for inhibitions mechanisms later.

> Presumably ANALYZE would have the same effect?

It would be nice to have a way to calculate a delta from the previous
statistics snapshot and send an inval if it's appropriate. Can it be
done?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.