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

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