Re: Automatic function replanning - Mailing list pgsql-hackers

Jim C. Nasby wrote:
> Is cardinality the only thing we'd need to worry about? My idea was
> actually to track the amount of work normally required by a stored query
> plan, and if a query uses that plan but requires a very different amount
> of work it's a good indication that we either need to replan or store
> multiple plans for that query. Though if we're certain that cardinality
> is the only thing that could make a cached plan go bad it would
> certainly simplify things greatly.

This gets into another area of re-optimizing when the executor finds
that the actual tables don't match the optimizer estimates.  I think we
decided that was too hard/risky, but invalidating the plan might help,
though I am thinking re-planning might just generate the same plan as
before.  I think something would need to have happened since the last
planning, like ANALYZE or something.

Updated TODO:
* Flush cached query plans when the dependent objects change,  when the cardinality of parameters changes dramatically,
or when new ANALYZE statistics are available
 

---------------------------------------------------------------------------


> 
> On Fri, Dec 16, 2005 at 11:10:43PM -0500, Bruce Momjian wrote:
> > 
> > Good idea, TODO updated:
> > 
> >     * Flush cached query plans when the dependent objects change or
> >       when the cardinality of parameters changes dramatically
> > 
> > 
> > ---------------------------------------------------------------------------
> > 
> > Jim C. Nasby wrote:
> > > On Tue, Dec 13, 2005 at 04:49:10PM -0500, Neil Conway wrote:
> > > > On Tue, 2005-12-13 at 22:32 +0100, Joachim Wieland wrote:
> > > > > there's a topic that comes up from time to time on the lists, the problem
> > > > > that pgsql functions get planned only once and thereafter the same query
> > > > > plan is used until server shutdown or explicit recreation of the function.
> > > > 
> > > > The problem really has nothing to do with functions, per se: whenever a
> > > > plan is created and then stored for future use, the assumptions made by
> > > > that plan may be invalidated by the time the plan is executed. This
> > > > applies to PREPARE, pl/pgsql functions, perhaps the plan caching done by
> > > > the RI triggers, and so forth.
> > > > 
> > > > I also think that invalidating cached plans on a periodic basis is the
> > > > wrong approach -- we can use sinval to invalidate plans as soon as a
> > > > dependent database object changes and not before. This thread contains
> > > > some ideas on how to do this:
> > > > 
> > > >     http://archives.postgresql.org/pgsql-hackers/2005-03/msg00426.php
> > > > 
> > > > I got somewhat sidetracked by the complexities of the "central plan
> > > > caching module" that Tom would like to see, but I'm still hoping to take
> > > > a look at this for 8.2.
> > > 
> > > As for predicate-driven plan changes (ie: query is planned the first
> > > time with a predicate that has high cardinality, but there are also low
> > > cardinality values that will be queried on), it would make more sense to
> > > track the amount of work (probably tuples fetched) normally required to
> > > execute a prepared statement. Any time that prepared statement is
> > > executed with a set of predicates that substantially changes the amount
> > > of work required it should be remembered and considered for re-planning
> > > the next time the query is executed with those predicates.
> > > -- 
> > > Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> > > Pervasive Software      http://pervasive.com    work: 512-231-6117
> > > vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Have you searched our list archives?
> > > 
> > >                http://archives.postgresql.org
> > > 
> > 
> > -- 
> >   Bruce Momjian                        |  http://candle.pha.pa.us
> >   pgman@candle.pha.pa.us               |  (610) 359-1001
> >   +  If your life is a hard drive,     |  13 Roberts Road
> >   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> > 
> 
> -- 
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073