Re: Automatic function replanning

[Rick Gigger <rick(at)alpinenetworking(dot)com>]

It seems to me like there are two classes of problems here:

1) Simply invalidating plans made with out of date statistics.
2) Using run-time collected data to update the plan to something more  
intelligent.

It also seems like #1 would be fairly straightforward and simple  
whereas #2 would be much more complex.  #1 would do me a world of  
good and probably other people as well.  Postgres's query planning  
has always been fine for me, or at least I have always been able to  
optimize my queries when I've got a representative data set to work  
with.  Query plan caching only gets me when the query plan is created  
before the statistics are present to create a good plan.

Just one users 2 cents.

- Rick Gigger


On Dec 19, 2005, at 12:00 PM, Jim C. Nasby wrote:

> On Sat, Dec 17, 2005 at 01:07:10PM -0500, Bruce Momjian wrote:
> > 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.
>
> Well, in the stored plan case, presumably what's changed is one of the
> bound parameters. And if we want to be sophisticated about it, we  
> won't
> just throw out the old plan; rather we'll try and figure out what
> parameter it is that's wanting a different plan.
>
> > 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
>
> Probably worth pointing to this therad in the TODO...
> --
> 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 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match