Re: Automatic function replanning

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(at)pervasive(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073