Re: Automatic function replanning

Rick Gigger wrote:
> 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.

Agreed.  I just can't add #2 unless we get more agreement from the
group, because it has been a disputed issue in the past.

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


> 
> - 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@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 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
> >
> 
> 

--  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